sql database integrity: working

Showing posts with label working. Show all posts

Thursday, March 29, 2012

Credentials blank out-default behaviour?

Hi we have projects that a number of people work on,

When someone opens a project that someone else has been working on all the credentials for the different data sources have disappeared. This seems to be a default behaviour of reporting services, is there a way to change this?

if so how?

Have you tried using shared data sources?|||These are shared datasources

Tuesday, March 27, 2012

Creating xsd schema file for a xml file ...

Hi ...
I need to bulk insert the xml data below. I am working on some issues related to the Itentity column (you may have read the other question).
The xml below uses elements with attributes. Is the schema file formated correctly to extract the attributes from the SITE element and then also extract the attributes from the ATMOSPHERIC element ... ? Not sure about the schema file ?
Thanks,
Chris

////////////////////////////////////////////////////
// schema file
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="site" sql:relation="MacgowanTestRWISRawAtmospheric" >
<xsd:complexType>
<xsd:attribute name="sysid" type="xsd:string" sql:field="SystemId"/>
<xsd:attribute name="rpuid" type="xsd:string" sql:field="RpuId"/>
</xsd:complexType>
</xsd:element>
<xsd:element name="atmospheric" sql:relation="MacgowanTestRWISRawAtmospheric" >
<xsd:complexType>
<xsd:attribute name="datetime" type="xsd:date" sql:field="ObsDateTime"/>
<xsd:attribute name="airtemp" type="xsd:string" sql:field="Temperature"/>
<xsd:attribute name="dewpoint" type="xsd:string" sql:field="DewPoint"/>
</xsd:complexType>
</xsd:element>
</xsd:schema>

////////////////////////////////////////////////////
// xml data
<?xml version="1.0"?>
<odot_rwis_site_info>
<site id="200000" number="1" sysid="200" rpuid="0" name="1-SR127 @. SR249" longitude="-84.554946" latitude="41.383527">
<atmospheric datetime="12/05/2005 03:48:00 PM" airtemp="-490" dewpoint="-800" relativehumidity="73" windspeedavg="11" windspeedgust="19" winddirectionavg="265" winddirectiongust="295" pressure="65535" precipitationintensity="None" precipitationtype="None" precipitationrate="0" precipitationaccumulation="-1" visibility="2000" />
<sensors>
<surface id="0" datetime="12/05/2005 03:48:00 PM" name="North Bound Driving Lane" surfacecondition="Dry" surfacetemp="1900" freezingtemp="32767" chemicalfactor="255" chemicalpercent="255" depth="32767" icepercent="255" subsurfacetemp="450" waterlevel="0">
<traffic datetime="12/05/2005 03:48:00 PM" occupancy="0" avgspeed="82" volume="21" sftemp="1900" sfstate="255">
<normalbins>
<bin datetime="12/05/2005 03:48:00 PM" binnumber="0" bincount="7" />
<bin datetime="12/05/2005 03:48:00 PM" binnumber="1" bincount="0" />
</normalbins>
<longbins>
<bin datetime="12/05/2005 03:48:00 PM" binnumber="2" bincount="0" />
<bin datetime="12/05/2005 03:48:00 PM" binnumber="3" bincount="0" />
<bin datetime="12/05/2005 03:48:00 PM" binnumber="4" bincount="1" />
<bin datetime="12/05/2005 03:48:00 PM" binnumber="5" bincount="0" />
</longbins>
</traffic>
</surface>
<surface id="1" datetime="12/05/2005 03:48:00 PM" name="Bridge Deck Simulator" surfacecondition="Other" surfacetemp="-60" freezingtemp="32767" chemicalfactor="255" chemicalpercent="255" depth="32767" icepercent="255" subsurfacetemp="-999999" waterlevel="0" />
</sensors>
</site>
</odot_rwis_site_info>

////////////////////////////////////////////////////
// table
CREATE TABLE [MacgowanTestRWISRawAtmospheric] (
[RecordId] [int] IDENTITY (1, 1) NOT NULL ,
[DataSourceId] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_MacgowanTestRWISRawAtmospheric_DataSourceId] DEFAULT ('OH'),
[ProductInstanceId] [char] (38) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_MacgowanTestRWISRawAtmospheric_ProductInstanceId] DEFAULT ('5abbbc86-fb2c-4703-9589-b55f763ee150'),
[SystemId] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[RpuId] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[SensorId] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ObsDateTime] [datetime] NULL ,
[InsertDateTime] [datetime] NOT NULL CONSTRAINT [DF_MacgowanTestRWISRawAtmospheric_InsertDateTime] DEFAULT (getdate()),
[Temperature] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DewPoint] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RelativeHumidity] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WSPD] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WSPDGust] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WDIRMin] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WDIRAvg] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WDIRMax] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PrecipIntensity] [char] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PrecipType] [char] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PrecipRate] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PrecipAccum] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Pressure] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Visability] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AirTempMax] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AirTempMin] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WetBulbTemp] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LastPrecipStart] [datetime] NULL ,
[LastPrecipEnd] [datetime] NULL ,
[PrecipAccum1Hour] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PrecipAccum3Hour] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PrecipAccum6Hour] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PrecipAccum12Hour] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PrecipAccum24Hour] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_MacgowanTestRWISRawAtmospheric] PRIMARY KEY NONCLUSTERED
(
[RecordId]
) WITH FILLFACTOR = 70 ON [PRIMARY]
) ON [PRIMARY]
GO

Not sure if I understand the question fully. Are you having any specific issues with bulk loading this data? or the issue is validating your XML data against XSD?

thanks

|||Hi ...

Sorry about the confusion. I was having some issues with bulk

loading using the IDENTITY Column in the target table. I was

setting the COM attribute KeepIdentity to TRUE rather than FALSE.

The schema and xml are correct.

Thanks,

Chris

Creating xsd schema file for a xml file ...

Not sure if I understand the question fully. Are you having any specific issues with bulk loading this data? or the issue is validating your XML data against XSD?

thanks

|||Hi ...
Sorry about the confusion. I was having some issues with bulk loading using the IDENTITY Column in the target table. I was setting the COM attribute KeepIdentity to TRUE rather than FALSE.
The schema and xml are correct.
Thanks,
Chris

Sunday, March 25, 2012

Creating View

I have a database I am working with that has three tables in it.
Two of the tables will be filled with information once a record is
submitted. The other one requires a response and will only get populated with
information when another transaction is fired.
(i.e. Record Submission sends an e-mail with an html link. When the link is
clicked by a user it loads an ASPX page that will fire a stored procedure
that creates a record in the third table that identifies the submission has
been viewed by someone on staff)
The problem I am running into with the view is that it will only retun
records that have been 'viewed' by someone. How can I do the view where it
will return all records regardless if a record exist in the child table?
The Table is joined with a PK and FK relationship.
If no PK from the master table exist as a FK in the child table I do not get
the reults.
I want to be able to see all records regardless if they have been viewed are
not. Can this be done?
IronSights,
You are asking about an OUTER JOIN such as:
CREATE MyView
AS
SELECT A.*, B.*, C.* -- Specify column names in real life
FROM Table1 A
JOIN Table2 B
ON A.ID = B.ID
LEFT OUTER JOIN Table3 C
ON A.ID = C.ID
Of course, the C.* columns will return NULL since no data exists, but the
row will show up in your view.
RLF
"IronSights" <IronSights@.discussions.microsoft.com> wrote in message
news:CDAFBEE7-2D51-4121-9CEA-9D70A873815D@.microsoft.com...
>I have a database I am working with that has three tables in it.
> Two of the tables will be filled with information once a record is
> submitted. The other one requires a response and will only get populated
> with
> information when another transaction is fired.
> (i.e. Record Submission sends an e-mail with an html link. When the link
> is
> clicked by a user it loads an ASPX page that will fire a stored procedure
> that creates a record in the third table that identifies the submission
> has
> been viewed by someone on staff)
> The problem I am running into with the view is that it will only retun
> records that have been 'viewed' by someone. How can I do the view where it
> will return all records regardless if a record exist in the child table?
> The Table is joined with a PK and FK relationship.
> If no PK from the master table exist as a FK in the child table I do not
> get
> the reults.
> I want to be able to see all records regardless if they have been viewed
> are
> not. Can this be done?
>
sql

Thursday, March 22, 2012

Creating Tables for Auditing

I am working on a DB which requires auditing on certain tables.
I have decided the best way to do this is to replicate some tables (the ones
I require auditing on) and use triggers to insert the original records into
the audit tables when a change is made to the original record.
When i create my audit tables, is there any need to have primary keys,
relationshops, and indexes which also mirror those of my normal tables or
would you say these are unecessary and simple 'basic' tables are all that is
required?
I personally think that basic tables to accept data and give it when
required are all that is needed, but this is the first time I have attempted
anything like this so I wanted some advice from some more knowledgeable
people.
Thanks
Every table should have a Primary Key but you may not want to declare
Foreign Keys on your audit tables if you want to keep rows for which the
parent values may have been deleted. On the other hand if you want to
capture changes to the parent tables as well then your Audit table keys can
mirror your live table keys.
Always declare foreign keys if you can, if only because having the RI in
place will make it simpler to write queries to analyse your audit data.
Here's an article that shows some techniques for capturing changes.
http://www.aspfaq.com/show.asp?id=2448
David Portas
SQL Server MVP
|||If you would like to have a tool to create the tables, generate the triggers, and manage the audit trail, consider WT6 - one of the SQL Server Tools applications...
http://www.sqlservertools.us
If it's compatible with your database architecture it will do what you have indicated, as well as a lot more.
|||Hi
As you will (probably) have before and after images in these tables and
exact replication of the key is not possible because of duplicate rows. To
make them unique then additional column(s) need adding to make it unique.
You may still want to index on the original tables primary key columns if it
improves the performance of your reporting system.
The tables will be appended too often therefore you are going to slow the
inserts down with more indexes etc.. This may affect the overall performance
of you application. If you are wanting comprehensive reporting then you may
want a compromise on insert speed or alternatively process the audit data
into a more indexed set of tables.
There are third party tools that can do this including:
http://www.lumigent.com/Products/
HTH
John
"Keith" <@..> wrote in message news:ujMmepgTEHA.3660@.tk2msftngp13.phx.gbl...
> I am working on a DB which requires auditing on certain tables.
> I have decided the best way to do this is to replicate some tables (the
ones
> I require auditing on) and use triggers to insert the original records
into
> the audit tables when a change is made to the original record.
> When i create my audit tables, is there any need to have primary keys,
> relationshops, and indexes which also mirror those of my normal tables or
> would you say these are unecessary and simple 'basic' tables are all that
is
> required?
> I personally think that basic tables to accept data and give it when
> required are all that is needed, but this is the first time I have
attempted
> anything like this so I wanted some advice from some more knowledgeable
> people.
> Thanks
>

Creating Tables for Auditing

I am working on a DB which requires auditing on certain tables.
I have decided the best way to do this is to replicate some tables (the ones
I require auditing on) and use triggers to insert the original records into
the audit tables when a change is made to the original record.
When i create my audit tables, is there any need to have primary keys,
relationshops, and indexes which also mirror those of my normal tables or
would you say these are unecessary and simple 'basic' tables are all that is
required?
I personally think that basic tables to accept data and give it when
required are all that is needed, but this is the first time I have attempted
anything like this so I wanted some advice from some more knowledgeable
people.
ThanksEvery table should have a Primary Key but you may not want to declare
Foreign Keys on your audit tables if you want to keep rows for which the
parent values may have been deleted. On the other hand if you want to
capture changes to the parent tables as well then your Audit table keys can
mirror your live table keys.
Always declare foreign keys if you can, if only because having the RI in
place will make it simpler to write queries to analyse your audit data.
Here's an article that shows some techniques for capturing changes.
http://www.aspfaq.com/show.asp?id=2448
David Portas
SQL Server MVP
--|||If you would like to have a tool to create the tables, generate the triggers
, and manage the audit trail, consider WT6 - one of the SQL Server Tools app
lications...
http://www.sqlservertools.us
If it's compatible with your database architecture it will do what you have
indicated, as well as a lot more.|||Hi
As you will (probably) have before and after images in these tables and
exact replication of the key is not possible because of duplicate rows. To
make them unique then additional column(s) need adding to make it unique.
You may still want to index on the original tables primary key columns if it
improves the performance of your reporting system.
The tables will be appended too often therefore you are going to slow the
inserts down with more indexes etc.. This may affect the overall performance
of you application. If you are wanting comprehensive reporting then you may
want a compromise on insert speed or alternatively process the audit data
into a more indexed set of tables.
There are third party tools that can do this including:
http://www.lumigent.com/Products/
HTH
John
"Keith" <@..> wrote in message news:ujMmepgTEHA.3660@.tk2msftngp13.phx.gbl...
> I am working on a DB which requires auditing on certain tables.
> I have decided the best way to do this is to replicate some tables (the
ones
> I require auditing on) and use triggers to insert the original records
into
> the audit tables when a change is made to the original record.
> When i create my audit tables, is there any need to have primary keys,
> relationshops, and indexes which also mirror those of my normal tables or
> would you say these are unecessary and simple 'basic' tables are all that
is
> required?
> I personally think that basic tables to accept data and give it when
> required are all that is needed, but this is the first time I have
attempted
> anything like this so I wanted some advice from some more knowledgeable
> people.
> Thanks
>sql

Creating Tables for Auditing

I am working on a DB which requires auditing on certain tables.
I have decided the best way to do this is to replicate some tables (the ones
I require auditing on) and use triggers to insert the original records into
the audit tables when a change is made to the original record.
When i create my audit tables, is there any need to have primary keys,
relationshops, and indexes which also mirror those of my normal tables or
would you say these are unecessary and simple 'basic' tables are all that is
required?
I personally think that basic tables to accept data and give it when
required are all that is needed, but this is the first time I have attempted
anything like this so I wanted some advice from some more knowledgeable
people.
ThanksEvery table should have a Primary Key but you may not want to declare
Foreign Keys on your audit tables if you want to keep rows for which the
parent values may have been deleted. On the other hand if you want to
capture changes to the parent tables as well then your Audit table keys can
mirror your live table keys.
Always declare foreign keys if you can, if only because having the RI in
place will make it simpler to write queries to analyse your audit data.
Here's an article that shows some techniques for capturing changes.
http://www.aspfaq.com/show.asp?id=2448
--
David Portas
SQL Server MVP
--|||If you would like to have a tool to create the tables, generate the triggers, and manage the audit trail, consider WT6 - one of the SQL Server Tools applications..
http://www.sqlservertools.u
If it's compatible with your database architecture it will do what you have indicated, as well as a lot more|||Hi
As you will (probably) have before and after images in these tables and
exact replication of the key is not possible because of duplicate rows. To
make them unique then additional column(s) need adding to make it unique.
You may still want to index on the original tables primary key columns if it
improves the performance of your reporting system.
The tables will be appended too often therefore you are going to slow the
inserts down with more indexes etc.. This may affect the overall performance
of you application. If you are wanting comprehensive reporting then you may
want a compromise on insert speed or alternatively process the audit data
into a more indexed set of tables.
There are third party tools that can do this including:
http://www.lumigent.com/Products/
HTH
John
"Keith" <@..> wrote in message news:ujMmepgTEHA.3660@.tk2msftngp13.phx.gbl...
> I am working on a DB which requires auditing on certain tables.
> I have decided the best way to do this is to replicate some tables (the
ones
> I require auditing on) and use triggers to insert the original records
into
> the audit tables when a change is made to the original record.
> When i create my audit tables, is there any need to have primary keys,
> relationshops, and indexes which also mirror those of my normal tables or
> would you say these are unecessary and simple 'basic' tables are all that
is
> required?
> I personally think that basic tables to accept data and give it when
> required are all that is needed, but this is the first time I have
attempted
> anything like this so I wanted some advice from some more knowledgeable
> people.
> Thanks
>

Wednesday, March 7, 2012

Creating Parent/Child table with Sum for each group

Hi,
i've been working on this report for the whole day now and have come
to the conclusion that this can't be accieved with Reporting Services
SP1.
Or maybe i'm wrong...
I'm trying to display a report like this:
-- Group 1
-- SubGroup 1,1
-- SubGroup 1,2
-- SubGroup 1,3
-- SubSubGroup 3,1
-- SubSubSubGroup 3,1,1
-- Totals for SubSubSubGroup 3,1,1
-- Total for SubSubGroup 3,1
-- Totals for SubGroup 1,3
-- Total for Group 1
My data looks like this:
Text, Amount, Group, ParentGroup
I have done it like described here:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rscreate/htm/rcr_creating_structure_objects_v1_3cok.asp
The problem is getting the Total to display correctly. I have tried
adding a table footer but this will only display the Total for the
entire table and not for each group.
Anybody know a solution to making this kind of report?
-MartinI had a similar problem. In the end I got around the problem by
creating a query that returned the hierarchised sub totals in the
dataset...this is relatively easy if your datasource is OLAP -
admittedly not so ideal if you are using relational datasources ...|||I found a way to work around it, and almost getting the desired result.
The result looks like this:
-- Group # 1, Total Amount
-- SubGroup # 1.1, Total Amount
-- SubGroup # 1.1.1, Total Amount
-- Element # 1.1.1.1, Amount
-- Element # 1.1.1.2, Amount
-- SubGroup # 1.1.2, Total Amount
-- Element # 1.1.2.1, Amount
I have added a Columns, "Headline", which tells me if a Row is a group
or an Element.
Then when i want the Amount displayed i use this expression:
=IIF(Fields!Headline.Value = 0, Fields!Amount.Value,
(Sum(Fields!Amount.Value, "table2_Details_Group", recursive)))
This seems to be the only way to calculate the Totals for a recursive
table...
-Martin

Saturday, February 25, 2012

Creating New Packages - Saving in Project Folder Unnecessarily

In BI Dev Studio, I have a Project/Solution created successfully, which has got say 5 packages working fine. I created a new package by Right Clicking on a solution explorer and selecting "New SSIS Package". System has given itself a default name and I decided to leave the BI Dev Studio WITHOUT SAVING project / solution.

Even though I haven't saved the project / solution still it left a package in project folder with a name "Package1.dtsx". I think this is a bug? Any ideas please?

Thanks

SuthaThis is true. I am sure this is a bug. Tongue Tied |||If it is a bug, it is one common to all VS products, and has been since VS .Net came out. The IDE will always create files on disk first. You must supply the filename upfront for a solution, project or file, although in the later case in can be inferred as it is here. I'd rather it save it for me, than me forget and loose work. VS will also save the package every time you execute it as well.|||Daran, thanks.

I understand your argument. I suppose if it is unnecessary we can delete manaually, rather than losing hours of work incase of any disaster. Tongue Tied

Friday, February 24, 2012

Creating Local cube Using SQL Server / Analysis Services 2005

I am creating a local cube through a web page and everything is working properly when I am debugging but if I am not debugging, the following exception is returned:

x= Exception:File system error: Error opening file; \\?\C:\Program Files\Common Files\System\Ole DB\MDTempStore_1088_1_fm3by.tmp is not a disk file or file is not accessible.

StackTrace: at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.XmlaClientProvider.HandleCreateLocalCube(AdomdErrorResponseException ex) at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.XmlaClientProvider.Microsoft.AnalysisServices.AdomdClient.IExecuteProvider.Execute(ICommandContentProvider contentProvider, AdomdPropertyCollection commandProperties, IDataParameterCollection parameters) at Microsoft.AnalysisServices.AdomdClient.AdomdCommand.Execute()

The role defined for the cube is:

General tab: Read definition

Cubes tab: Access: read

Cubes: Local Cube/Drillthrough Access

Thanks,

Yones

Looks like a problem with security. Make sure the process you running within IIS has sufficient privileges to create a files local cube files.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Hi Edward,

Yes, it seems like it is a security problem. I have just found that it is working properly when using AS 2005 installed with (local) SQL server 2005 but it does not work with a named instance such as (ServerName\SQLServerInstanceName)

Could please be more specific about what privileges should be given?

Thanks,

Yones

|||

Not sure how named instance plays the role here.

Are you talking about SQL Server relational engine named instance or about Analysis Server named instance here?

If it is working with default instance, try and see what are the differences in the way named instance is setup.

There should be a little difference in the way you work against named instance.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Hi Edward,

Concerning named instance (MachineName\InstanceName) and default instance ((local)), I think if we have a named instance, it is for both SQL Server relational engine and Analysis Services. I do not think we can have a named instance for SQL Server relational engine and a default instance for Analysis Services (Please correct me if I am wrong)

It is working for the default instance (local) because the Web Server (IIS) and Analysis Services are on the same machine but for a named instance (MachineName\InstanceName), it is not working even the “MachineName” is the same for the Web Server (IIS)

Thanks,

Yones

|||

Here is a similar thread :

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=686728&SiteID=1

Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

Sunday, February 19, 2012

Creating index - Error 229 (Permission denied)

I have been working with my DBA and have not yet found the issue.
I have my development database on a SQL Server 2005 server. I enabled
it for full-text catalogs and created a catalog with no issues
whatsoever. When I go to create the index, however, I run through the
wizard (using SMS) until the last step. Upon finishing the wizard, I
get the following error:
Failed to retrieve data for this request (Microsoft.SqlServer.SmoEnum)
Additional Information:
A exception occurred while executing a Transact-SQL statement or
batch. (Microsoft.SqlServer.ConnectionInfo)
EXECUTE permission denied on object 'sp_help_category', database
'msdb', schema 'dbo'.
SELECT permission denied on object 'sysjobs_view', database 'msdb',
schema 'dbo'. (Microsoft SQL Server, Error: 229).
I am the dbowner, and the DBA granted me temporary DBA privileges with
no difference (still received the same error). The DBA created the
index for me with no problems. I can see the index (I have not yet
tried using it). I can pull up the property pages and see "General"
and "Columns" views fine, but when I click on "Schedules", I receive
the same error as above.
Any thoughts?
Thanks.
You will need to be in the dbo_role on the msdb database as well.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<tlchurch@.gmail.com> wrote in message
news:1170424716.096179.254400@.l53g2000cwa.googlegr oups.com...
>I have been working with my DBA and have not yet found the issue.
> I have my development database on a SQL Server 2005 server. I enabled
> it for full-text catalogs and created a catalog with no issues
> whatsoever. When I go to create the index, however, I run through the
> wizard (using SMS) until the last step. Upon finishing the wizard, I
> get the following error:
> Failed to retrieve data for this request (Microsoft.SqlServer.SmoEnum)
> Additional Information:
> A exception occurred while executing a Transact-SQL statement or
> batch. (Microsoft.SqlServer.ConnectionInfo)
> EXECUTE permission denied on object 'sp_help_category', database
> 'msdb', schema 'dbo'.
> SELECT permission denied on object 'sysjobs_view', database 'msdb',
> schema 'dbo'. (Microsoft SQL Server, Error: 229).
> I am the dbowner, and the DBA granted me temporary DBA privileges with
> no difference (still received the same error). The DBA created the
> index for me with no problems. I can see the index (I have not yet
> tried using it). I can pull up the property pages and see "General"
> and "Columns" views fine, but when I click on "Schedules", I receive
> the same error as above.
> Any thoughts?
> Thanks.
>
|||Hello tlchurch,
I am surprised your DBA hasn't ben able to help, as the error is fairly self
explanatory.
You do not have permissions to access sysjobs_view in database msdb and also
execute sp_help_category in msdb.
Your DBA needs to give you the correct permissions to see the schedules,
which are store in msdb.
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons

> I have been working with my DBA and have not yet found the issue.
> I have my development database on a SQL Server 2005 server. I enabled
> it for full-text catalogs and created a catalog with no issues
> whatsoever. When I go to create the index, however, I run through the
> wizard (using SMS) until the last step. Upon finishing the wizard, I
> get the following error:
> Failed to retrieve data for this request (Microsoft.SqlServer.SmoEnum)
> Additional Information:
> A exception occurred while executing a Transact-SQL statement or
> batch. (Microsoft.SqlServer.ConnectionInfo)
> EXECUTE permission denied on object 'sp_help_category', database
> 'msdb', schema 'dbo'.
> SELECT permission denied on object 'sysjobs_view', database 'msdb',
> schema 'dbo'. (Microsoft SQL Server, Error: 229).
> I am the dbowner, and the DBA granted me temporary DBA privileges with
> no difference (still received the same error). The DBA created the
> index for me with no problems. I can see the index (I have not yet
> tried using it). I can pull up the property pages and see "General"
> and "Columns" views fine, but when I click on "Schedules", I receive
> the same error as above.
> Any thoughts?
> Thanks.
>

Tuesday, February 14, 2012

creating database

I am working on site in asp.net with c# and using database sql server 2000 and managing all the data on server i have created one database on server and in that i have created 15 table. should i continue with the same or create another database for table.

tell me how many tables should i maintain in one database. and what precausion should i take while mainting database to increse the speed of site.and what precausion should i take to avoid serverload

please guide me i dont have an idea.

thanks for spending ur valuable time for me.

I suppose that would depend on the database structure of your application. If your application required more tables i guess you create one in the existing database it self.

I dont think you need to worry much with only 15 tables in the database. Frankly your db can handle many tables still.... :) so chill

I would suggest create a unique db for each application. Again if there are very few tables in each application u can use the same database as well. There are many tweaks in the database than just the no. of tables to make it faster... so i will not consider more tables as a major problem for bad database perfomance.

|||

Sharvari:
I am working on site in asp.net with c# and using database sql server 2000 and managing all the data on server i have created one database on server and in that i have created 15 table. should i continue with the same or create another database for table.

You would generally create a new database for each individual application unless several applications may share the data.

Sharvari:
tell me how many tables should i maintain in one database. and what precausion should i take while mainting database to increse the speed of site.and what precausion should i take to avoid serverload

There is no set limit that defines how many tables you should have. The important thing when it comes to database efficiency is the table design, adequate levels of normalisation, proper indexing, and write good optimised SQL for data retrieval.

|||

hi,

read this link this may be helpful to you.

http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=/com.ibm.db2.udb.admin.doc/doc/r0001029.htm

|||

Sharvari:
i have created one database on server and in that i have created 15 table. should i continue with the same or create another database for table.

You can go on creatng as many tables/stored procedure/functions/views as you want in a database. There is indeed alimit of maximum number of objects that can be created in a database but don't worry you are far...far away from that.

Sharvari:
what precausion should i take while mainting database to increse the speed of site.and what precausion should i take to avoid serverload

There are many techniques for better administration of SQL Server. Just try a google and you'll find so many informative and useful articles.

|||whats the effect on server by using table and whats the effect on server by using store procedure which is more convinient. thanks for spending ur valuable time for me.|||

Do you mean that you will use stored procedure to get the data or use a table to get the data?

|||

Well, I can't exactly understand what you are trying to ask. Anyways, I think you are trying to compare tables and stored procedures. But, tables and stored procedures have different objectives.

Tables are used to store the data in a database.

Stored Procedures are used to modify the data stored in the tables. By modify I mean Insert/Update/Delete the data.

So, basically both of these 2 have different purposes and there should be no comparison between these 2.

|||

Sharvari:
whats the effect on server by using table and whats the effect on server by using store procedure which is more convinient. thanks for spending ur valuable time for me.

As mentioned, tables are where you store the data. Stored procedures provide a mechanism which allows you to encapsulate logic into a unit which can be re-used. Therefore a stored procedure can contain logic on how to select/update/delete data and various other things.

Thursday, March 29, 2012

Credentials blank out-default behaviour?

Tuesday, March 27, 2012

Creating xsd schema file for a xml file ...

Creating xsd schema file for a xml file ...

Sunday, March 25, 2012

Creating View

Thursday, March 22, 2012

Creating Tables for Auditing

Creating Tables for Auditing

Creating Tables for Auditing

Wednesday, March 7, 2012

Creating Parent/Child table with Sum for each group

Saturday, February 25, 2012

Creating New Packages - Saving in Project Folder Unnecessarily

Friday, February 24, 2012

Creating Local cube Using SQL Server / Analysis Services 2005

Sunday, February 19, 2012

Creating index - Error 229 (Permission denied)

Tuesday, February 14, 2012

creating database

sql database integrity

Blog Archive

About Me