Adding a date to a report

I'd like to add a date in the heading of the report. Since the current date is not part of the stored procedure that I'm using how can I get the date? I was hoping for a built-in function of some kind.

you should be able to just add a textbox and enter the expression

= System.DateTime.Now()

|||

Are you referring to referencing a date/time other than the current date/time? If so, I would like to know how to do this as well. I have a DTS (SSIS) job that i want to reference the time it ran in my report. I could do the same thing with a stored proc if I had to.

Thanks everyone.

|||No, all I needed was the date/time the report is produced and I already received that answer.sql

added two more user parameters

I have a stored procedure that works fine in reporting services. It grabs the total of Yes's and No's by dates . But then i went ahead and added 2 more parameters to the proc, and now the totals are all wrong. I dont understand how that can mess everything up. Here is the previous stored proc, that gives the correct sum.

Code Snippet

ALTER PROCEDURE [dbo].[Testing_Questions_ALL_YESOrNO]

@.Question char(80)

AS

BEGIN

SELECT

Qry_Questions.Question

, Qry_Questions.Date

, Qry_Questions.response

, B.Total

FROM Qry_Questions

INNER JOIN Qry_Sales_Group

ON dbo.Qry_Questions.sales_person_code COLLATE SQL_Latin1_General_CP1_CI_AS = dbo.Qry_Sales_Group.SalesPerson_Purchaser_Code

INNER JOIN

( Select COUNT(qq.response)as Total, Question, Date, response

FROM Qry_Questions qq

Where qq.response in ('Yes','No')

GROUP by qq.[Question] , qq.Date,qq.response ) B

ON Qry_Questions.Date = B.Date AND

Qry_Questions.Question =B.Question and

Qry_Questions.response=B.response

WHERE Qry_Questions.[Response Type]='YesNo' and Qry_Questions.Question=@.Question

GROUP BY Qry_Questions.question,Qry_Questions.Date,Qry_questions.Response,B.Total

ORDER BY Qry_Questions.Question, Qry_Questions.Date

END

SET NOCOUNT OFF

Here is the edited version which only has two new parameters added to the proc. The edits are highlighted.

Code Snippet

ALTER PROCEDURE [dbo].[Testing_Questions_ALL_YESOrNO_Totals]

(@.Region_Key int=null,@.QuestionCode char(5),@.Question char(80))

AS

BEGIN

SELECT

Qry_Questions.Question

, Qry_Questions.Date

, Qry_Questions.response

, B.Total

FROM Qry_Questions

INNER JOIN Qry_Sales_Group

ON dbo.Qry_Questions.sales_person_code COLLATE SQL_Latin1_General_CP1_CI_AS = dbo.Qry_Sales_Group.SalesPerson_Purchaser_Code

INNER JOIN

( Select COUNT(qq.response)as Total, Question, Date, response

FROM Qry_Questions qq

Where qq.response in ('Yes','No')

GROUP by qq.[Question] , qq.Date,qq.response ) B

ON Qry_Questions.Date = B.Date AND

Qry_Questions.Question =B.Question and

Qry_Questions.response=B.response

WHERE Qry_Questions.[Response Type]='YesNo'

AND REGION_KEY=@.Region_Key

AND LEFT(Qry_Questions.[Question Code],2)IN (@.QuestionCode)

AND Qry_Questions.Question=@.Question

GROUP BY Qry_Questions.question,Qry_Questions.Date,Qry_questions.Response,B.Total

ORDER BY Qry_Questions.Question, Qry_Questions.Date

END

SET NOCOUNT OFF

Looks to me like when you're calling the SPROC, you're not passing in all of the Region_Key's or all of the possible Question_Code's. The SPROC syntax looks fine, assuming you're sure that the LEFT function is working correctly.

To find out exactly what's wrong, I would recommend isolating your changes one by one. Test the SPROC with only the Region_Key param and see what you get. If it's right, test with only the Question_Code param. Then try together. Problems like this are frequently caused by you not realizing that there are NULLs or blanks in your data, and so when you pass certain parameters, it will exclude other rows and therefore throw off your totals.

Hope that helps.
|||Just to add to above post, you have defined QuestionCode as char(5) in the param list, yet inside the proc you are doing left(question code, 2). You may be truncating some data and comparing with incorrect data.

|||

It's actually giving me a number way too big. A Sum way more then when i take the paramaters out. The Stored procedure works fine for another report i have with the same detail. But when i added these two additional parameters, for some reason it gives me a number way higher then if it didnt have any parameters at all.

|||Could you post the DDL and input params for the updated SP. I am hoping RegionKey is coming out of Qry_sales_Group and is it part of key or does it have multiple rows for the same value?
|||

I got it to work. I figured out that the sub query needed to be changed for this report because i was totaling based on region and question this time.

Code Snippet

INNER JOIN

( Select COUNT(qq.response)as Total, Question, Date, response

FROM Qry_Questions qq

INNER JOIN Qry_Sales_Group

ON Qry_Sales_Group.SalesPerson_Purchaser_Code = qq.sales_person_code COLLATE SQL_Latin1_General_CP1_CI_AS

Where qq.response in ('Yes','No')and Region_Key=@.Region_Key

GROUP by qq.[Question] , qq.Date,qq.response ) B

Thanks for the help. I went and piece every little piece til i relized it wasnt seperating them by regions.

Add User/Set Roles in Code and Read Roles

Can you write a stored procedure to add a user to your DB and set the roles the user belongs to?

I want to write a stored proc. to add users and set roles so it can be used in code instead of doing it manually.

After the user has been added and their roles set, can you write another stored proc. to give you what roles they belong to?Look for following sprocs in BOL and sp_helptext sprocs in QA

Might be a big help

sp_helprole
sp_helprotect
sp_helprolemember

sp_addrole
sp_addrolemember

However something you might need to check out

The Holy Book says:
sp_addrole cannot be used inside a user-defined transaction.|||insert into sysusers values
('new_user_id', 0, 'new_role_name', NULL, 0x00, getdate(), getdate(), 'dbo', NULL)|||Originally posted by Enigma
Look for following sprocs in BOL and sp_helptext sprocs in QA

Might be a big help

sp_helprole
sp_helprotect
sp_helprolemember

sp_addrole
sp_addrolemember

However something you might need to check out

The Holy Book says:
sp_addrole cannot be used inside a user-defined transaction.
sp_grantlogin, sp_grantdbaccess, sp_addrolemember, and sp_helpuser worked great. thanks.

Add user assembly in managed stored procedure

I am developing a managed stored procedure in VS.NET 2005 and I am trying to add a reference to an user developed assembly (not a system one) but adding a reference to it is not possible as it doesnt let me import assemblies but rather reference the few limited ones in a list. Why is this??

There are limitations to what you can do with CLR assemblies, that is the reason for the new SQL Server project template in VS 2005. Try the link below for more detailed info from Microsoft. Hope this helps.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sqlclrguidance.asp

|||I found out how to do it, it can be done. Just add the user developed assembly to the SQL database assemblies and that will make it callable from a managed database object. The downsize as I see it is that you need to keep a repository of assemblies outside of the GAC...

Add Temp Parameters to a function

I am writing a set of functions and then a stored procedure to allow me to view some data in Reporting Services. I have written the first part of the function as shown ;

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

GO

ALTER FUNCTION [dbo].[fnWTRTerrierData]

(@.vch_site_ref nvarchar (3), @.dt_src_date datetime)

RETURNS @.WeeklyTerrierRSPI TABLE

(Areacode varchar(2),siteref nvarchar(3),

estatename nvarchar(100), Securitised nvarchar(255),

unitref nvarchar(15), unittype nvarchar(30),

unittype_count int, tenantname nvarchar(100),

tenantstatus nvarchar(25), tenantstatus_count int,

unitstatus nvarchar(15), unitstatus_count int,

floortotal float, floortotocc float,

initialvacarea float, initialvacnet float,

TotalRent float, NetRent float,

FinalRtLsincSC float, DiscEndDate datetime,

ErvTot float, Leaseterm int,

leasestart datetime, rentreview nvarchar(255),

leaseend datetime, breakclause datetime,

tenancyterm datetime, landact nvarchar(255),

datadate datetime)

AS

BEGIN

INSERT @.WeeklyTerrierRSPI

SELECT Areacode, siteref, estatename, Securitised, unitref, unittype, unittype_count, tenantname,

tenantstatus, tenantstatus_count, unitstatus, unitstatus_count, floortotal, floortotocc,

initialvacarea, initialvacnet, TotalRent, NetRent, FinalRtLsincSC, DiscEndDate, ErvTot,

Leaseterm, leasestart, rentreview, leaseend, breakclause, tenancyterm, landact, datadate

FROM dbo.src_terrier

WHERE (datadate = @.dt_src_date) AND (siteref = @.vch_site_ref)

RETURN

END

I have then written a stored procedure which picks up the result set from the function and ultimately will deliver it to Reporting Services. The problem I have is that as soon as I run the CREATE PROCEDURE script, I get an error saying ;

Msg 216, Level 16, State 1, Procedure spWTRWeeklyTerrierData, Line 16

Parameters were not supplied for the function 'fnWTRTerrierData'.

How can I add parameters to the function sufficently so that I can run the Create procedure element of my code?

Regards

Post your Stored Procedure codes.|||

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[spWTRWeeklyTerrierData]

(@.vch_site_ref nvarchar(3), @.dt_src_date datetime)

AS

BEGIN

SET NOCOUNT ON;

SELECT fnWTRTerrierData.Areacode, fnWTRTerrierData.siteref, fnWTRTerrierData.estatename, fnWTRTerrierData.Securitised, fnWTRTerrierData.unitref, fnWTRTerrierData.unittype, fnWTRTerrierData.unittype_count, fnWTRTerrierData.tenantname,fnWTRTerrierData.tenantstatus, fnWTRTerrierData.tenantstatus_count, fnWTRTerrierData.unitstatus, fnWTRTerrierData.unitstatus_count, fnWTRTerrierData.floortotal, fnWTRTerrierData.floortotocc, fnWTRTerrierData.initialvacarea, fnWTRTerrierData.initialvacnet, fnWTRTerrierData.TotalRent, fnWTRTerrierData.NetRent, fnWTRTerrierData.FinalRtLsincSC, fnWTRTerrierData.ErvTot, fnWTRTerrierData.tenancyterm, fnWTRTerrierData.landact, fnWTRTerrierData.datadate

FROM fnWTRTerrierData

RETURN

END

|||

Looking at a stored procedure I pulled over from a SQL 2000 DB, I took out the elements that seems to resemble the function side

BEGIN

SET NOCOUNT ON;

RETURN

END

But I am still met with the same parameters message when I try to execute the SQL code to create the SP.

Regards

|||

ALTER FUNCTION [dbo].[fnWTRTerrierData]

(

@.vch_site_ref nvarchar (3),

@.dt_src_date datetime

)

Your table function is expecting 2 input parameter which you did not supply.
You will need to supply these 2 value to the function
for example

SELECT ...
FROM fnWTRTerrierData ('?', '2006-01-01')

Add relation to new table

If i add a new table to my database with a stored procedure, using that same sp or another can i also add a 1-M relationship with another table that is currently in the database. Please if you can provide an example.

Thank you,You need to add a foreign key constraint to the child table. Here's an example from SQL Server Books Online, which creates a job_id field that is a FK to the jobs table:

job_id smallint NOT NULL
DEFAULT 1
REFERENCES jobs(job_id)

That enough information? You can also modify an existing table.

Don

Add Permission Stored Procedure!

Hi all,
Just wondering, is their a system stored procedure i can use to grant
permission to all objects in a specified db to a specified user ?
Cheers,
AdamAdam,
What is your goal - read access or modification capability? Might look at
the db_datareader and db_datawriter fixed databse roles.
HTH
Jerry
"Adam Knight" <adam@.pertrain.com.au> wrote in message
news:%23Xs4Z5d1FHA.1032@.TK2MSFTNGP12.phx.gbl...
> Hi all,
> Just wondering, is their a system stored procedure i can use to grant
> permission to all objects in a specified db to a specified user ?
> Cheers,
> Adam
>|||Few hours ago:
http://groups.google.de/group/micro...9344927e9915da5
HTH, Jens Suessmeyer.

Add consecutive Id in Insert mode

Hi,
I am using the following procedure to fill Product table from LanTable:

BEGIN
insert into Product (Product_Num,Sticker_type)
Select LanTable.ProductNum,LanTable.StickerType
From LanTable left Join Product p On LanTable.ProductNum=p.Product_Num where p.Product_Num is null
END

In Product table i have an additional ID column.

I need to fill this field with consecutive numbers according to the Insert above.
If current ID value is 10 and I have 20 new products to insert, the ID field will be filled with 11 to 31.
How can I insert into ID column consecutive numbers starting with 11 that dependes on the number of rows added to Product table?
Thanks
YossiCould you set your ID attribute to an IDENTITY and let the system sort it out?|||Originally posted by Paul Young
Could you set your ID attribute to an IDENTITY and let the system sort it out?
Thanks for your replay.
The Id column has a meaning.
Not every time I will use the Insert routine the Id should get the consecutive value. Thats why i need to know the current Id and from that value to work on. with Identity the values will raise up to the roof.
Yours
Yossi|||Okay, just wanted to rule out the obveous...

How about something like:
declare @.Product_Num int, @.Sticker_Type as int

select @.Product_Num = min(LanTable.ProductNum) From LanTable left Join Product p On LanTable.ProductNum=p.Product_Num where p.Product_Num is null
select @.Sticker_Type = Sticker_Type from LanTable where ProductNum = @.Product_Num
while (@.Product_Num is not null) begin
insert into Product (ID_Column,Product_Num,Sticker_type)
select max(ID_Column) + 1, @.Product_Num int, @.Sticker_Type from Product
select @.Product_Num = min(LanTable.ProductNum) From LanTable left Join Product p On LanTable.ProductNum=p.Product_Num where p.Product_Num is null and LanTable.ProductNum > @.Product_Num
select @.Sticker_Type = Sticker_Type from LanTable where ProductNum = @.Product_Num
end

Of course this is UNTESTED and you will need to change datatypes and attribute names, but look it over and let me know your thoughts.|||Originally posted by Paul Young
Okay, just wanted to rule out the obveous...

How about something like:
declare @.Product_Num int, @.Sticker_Type as int

select @.Product_Num = min(LanTable.ProductNum) From LanTable left Join Product p On LanTable.ProductNum=p.Product_Num where p.Product_Num is null
select @.Sticker_Type = Sticker_Type from LanTable where ProductNum = @.Product_Num
while (@.Product_Num is not null) begin
insert into Product (ID_Column,Product_Num,Sticker_type)
select max(ID_Column) + 1, @.Product_Num int, @.Sticker_Type from Product
select @.Product_Num = min(LanTable.ProductNum) From LanTable left Join Product p On LanTable.ProductNum=p.Product_Num where p.Product_Num is null and LanTable.ProductNum > @.Product_Num
select @.Sticker_Type = Sticker_Type from LanTable where ProductNum = @.Product_Num
end

Of course this is UNTESTED and you will need to change datatypes and attribute names, but look it over and let me know your thoughts.

I will work on it....

Thanks a bunch mate.|||Originally posted by Paul Young
Okay, just wanted to rule out the obveous...

How about something like:
declare @.Product_Num int, @.Sticker_Type as int

select @.Product_Num = min(LanTable.ProductNum) From LanTable left Join Product p On LanTable.ProductNum=p.Product_Num where p.Product_Num is null
select @.Sticker_Type = Sticker_Type from LanTable where ProductNum = @.Product_Num
while (@.Product_Num is not null) begin
insert into Product (ID_Column,Product_Num,Sticker_type)
select max(ID_Column) + 1, @.Product_Num int, @.Sticker_Type from Product
select @.Product_Num = min(LanTable.ProductNum) From LanTable left Join Product p On LanTable.ProductNum=p.Product_Num where p.Product_Num is null and LanTable.ProductNum > @.Product_Num
select @.Sticker_Type = Sticker_Type from LanTable where ProductNum = @.Product_Num
end

Of course this is UNTESTED and you will need to change datatypes and attribute names, but look it over and let me know your thoughts.
Hi Paul,
I have tried that and I have the following problems:
1.Product_Num is a barcode string, min function cannot help us here.
2.ID_Column can be with no value at first (when the table is empty there is no value) but it cannot be nulled because its the PK so I got an Error trying to draw the Max value. (i can deal with that).

The main problem is 1.
Can you help with that?
Thanks|||Sorry,

1. Yes, min will work with strings. You will need to adjust the local variable datatypes to match your table attributes. Of course if ProductNum is not unique this approach probably wont work well but then I suspect you will have other problems as well. if this is still giving you fits, post the ddl from the Product and LanTable tables.

2. The quick fix on the ID_Column is to test for a null i.e. isnull(max(ID_COLUMN) + 1,1)|||Again, I would encourage you to look at using an Identity Attribute as it will accomplish exactly what you are trying to create. Yes the value just keeps growing but so what. IMHO trying to reclaime gaps in IDs is a wast of time.

Assuming you want to persue the DIY approach, what will you insert into the Product.LabelId attribute untill the trigger can assign the correct ID? You can't leave it blank or insert a default value? PK suggests Not Null and unique.

Triggers should always be written to handle multiple rows, it only takes a little more effort.

If your primary key is NOT unique (bad idea) you can probably make the trigger approach work. The key is to process each record of the temporary inserted table one at a time. Basically take the code I provided earlier and modify it to start a transaction, update the FreeID table to the next value, select the next ID, end the transaction, process one record from the isnerted table and then repeat until all reacords are processed.

Digest all of this and let me know.|||Originally posted by Paul Young
Again, I would encourage you to look at using an Identity Attribute as it will accomplish exactly what you are trying to create. Yes the value just keeps growing but so what. IMHO trying to reclaime gaps in IDs is a wast of time.

Assuming you want to persue the DIY approach, what will you insert into the Product.LabelId attribute untill the trigger can assign the correct ID? You can't leave it blank or insert a default value? PK suggests Not Null and unique.

Triggers should always be written to handle multiple rows, it only takes a little more effort.

If your primary key is NOT unique (bad idea) you can probably make the trigger approach work. The key is to process each record of the temporary inserted table one at a time. Basically take the code I provided earlier and modify it to start a transaction, update the FreeID table to the next value, select the next ID, end the transaction, process one record from the isnerted table and then repeat until all reacords are processed.

Digest all of this and let me know.

I will do that.
cheers|||I am glad to see that you are following Paul's advice. While I was reading your post, the description was a primary key field that needed to be incremented - and I was curious why you said you did not want to use IDENTITY (I could only think of the gaps as Paul mentioned - as a down side). Anyway, you changed your mind - so good luck.

Add column with variable name

HI!

Im trying to use an stored procedure to add a new column.
The problem is that the column name is stored in a variable, and I get syntax error.

Here is my code:

CREATE PROCEDURE test AS
declare @.colname as varchar(10)

set @.colname = 'new_col'

alter table prueba add @.colname int

And here is the error I get:
Error 170: Line 6 Incorrect syntax near '@.colname'

Any idea?Originally posted by soy_lore_lore
HI!

Im trying to use an stored procedure to add a new column.
The problem is that the column name is stored in a variable, and I get syntax error.

Here is my code:

CREATE PROCEDURE test AS
declare @.colname as varchar(10)

set @.colname = 'new_col'

alter table prueba add @.colname int

And here is the error I get:
Error 170: Line 6 Incorrect syntax near '@.colname'

Any idea?

Try this:

exec('alter table prueba add '+@.colname+' int')|||Originally posted by snail
Try this:

exec('alter table prueba add '+@.colname+' int')

THANKS!

Add Column Values To the Bottom Of the Row By Condition

Hi
I am getting some results from stored procedure.
Name Value1 Value2 Value3 Total
North 1 2 3 6
North 4 5 6 15
South 5 6 7 18
Footer (North) 5 7 9 21
Footer(South) 5 6 7 18
I displaying footer for each group and i want to display Total value of
the column for that respective group Name.Is therea way to add all
column value depending up on the condition.
For North add only 2 rows.
Can any one help on this issue.
Thankstry sum in the group
=Sum(field!abc.value, "group1") you can have scope in the sum.
Amarnath
"vamsi" wrote:
> Hi
> I am getting some results from stored procedure.
> Name Value1 Value2 Value3 Total
> North 1 2 3 6
> North 4 5 6 15
> South 5 6 7 18
> Footer (North) 5 7 9 21
> Footer(South) 5 6 7 18
>
> I displaying footer for each group and i want to display Total value of
> the column for that respective group Name.Is therea way to add all
> column value depending up on the condition.
> For North add only 2 rows.
> Can any one help on this issue.
> Thanks
>

Add a new column in temp table in sp cannot be used immediately

I created a temp table in my stored procedure and then added a new identity column to it.

However, I am not able to use this new column immediately, it says column not found.

SELECT * INTO #temp FROM table_name

ALTER TABLE #temp ADD __Identity int IDENTITY(1,1)

SELECT * FROM #temp WHERE __Identity >= 10

Here __Identity column is not found. If I just did SELECT * FROM #temp without the where clause, the final result does have the __Identity column correctly added to the table. Why can't I query it?

Thanks!

That is a good question. I originally had the problem, but then it just went away and I can't get it to come back again. From Management Studio, try placing a "GO" between each statement. That's what I did to get the problem to go away and can't get it to come back again. Are you issuing these to SQL Server from 3 different command objects on the same connection?

|||

All these statements are in one stored procedure, so "GO" is not going to work. Thanks!

|||

Hi,

Based on my understanding ,these three statements will be pre-checked at the same time, and then each statement will be executed. The third statement can't pass the check because the first two statements has't been executed.

I suggest you to write seperate these two SP, one is for creating a column, another is for query.

create proc CreateColumnas ALTER TABLE table ADD _identityint IDENTITY(1,1) go --For the CreatingColumn exec procfinal --execute the CreateColumn SP.create proc QueryResultas select *from table where _identity>'10'goexec QueryResult

Thanks.

Ad hoc update to system catalogs is not supported?

Hi
SQL Server 2005 Express.
25 january 2007 I added a new procedure to my database using ole automation
procedures such as sp_OACreate and sp_OAMethod.
These system procedures requires 'Ole Automation Procedures' to be enabled.
So I added the following to my installation script:
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO
The script run fine and my procedue also runs fine.
But suddenly today installaing a new database I get an error mesage:
"Ad hoc update to system catalogs is not supported"
when I run the commands above.
Does anyone now what has happend since january? I installed a SP2 of SQL
Server Express. Has there been any changes?
Running "reconfigure with override" works but it is not recommended to use
"with override" I have read.
Is there any other way of enabling 'Ole Automation Procedures' which is
allowed?
Help is appreciated.
Regards Kjell Arne Johansen
We need to know what or who is trying to modify the system tables. Without seeing any code, we can't
say where the problem it. My guess is that you instantiate a COM object, which in turn connect back
to SQL Server and try to modify the system table - something you cannot do in 2005.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Kjell Arne Johansen" <KjellArneJohansen@.discussions.microsoft.com> wrote in message
news:EAA6BCF7-ED40-41EF-9968-AB473829DBD8@.microsoft.com...
> Hi
> SQL Server 2005 Express.
> 25 january 2007 I added a new procedure to my database using ole automation
> procedures such as sp_OACreate and sp_OAMethod.
> These system procedures requires 'Ole Automation Procedures' to be enabled.
> So I added the following to my installation script:
> sp_configure 'show advanced options', 1;
> GO
> RECONFIGURE;
> GO
> sp_configure 'Ole Automation Procedures', 1;
> GO
> RECONFIGURE;
> GO
> The script run fine and my procedue also runs fine.
> But suddenly today installaing a new database I get an error mesage:
> "Ad hoc update to system catalogs is not supported"
> when I run the commands above.
> Does anyone now what has happend since january? I installed a SP2 of SQL
> Server Express. Has there been any changes?
> Running "reconfigure with override" works but it is not recommended to use
> "with override" I have read.
> Is there any other way of enabling 'Ole Automation Procedures' which is
> allowed?
> Help is appreciated.
> Regards Kjell Arne Johansen
>
>
|||Hi
This is the code causing the error message to occur when it is executed.
A month ago it worked fine.
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO
Regards Kjell Arne Johansen
"Tibor Karaszi" wrote:

> We need to know what or who is trying to modify the system tables. Without seeing any code, we can't
> say where the problem it. My guess is that you instantiate a COM object, which in turn connect back
> to SQL Server and try to modify the system table - something you cannot do in 2005.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Kjell Arne Johansen" <KjellArneJohansen@.discussions.microsoft.com> wrote in message
> news:EAA6BCF7-ED40-41EF-9968-AB473829DBD8@.microsoft.com...
>
>
|||Are you saying that below code, in itself, generates the error you posted? I just tried on my sp2
with GDR, no error message...
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Kjell Arne Johansen" <KjellArneJohansen@.discussions.microsoft.com> wrote in message
news:AC5E975F-39E6-482A-9C1D-700C613AA3C6@.microsoft.com...[vbcol=seagreen]
> Hi
> This is the code causing the error message to occur when it is executed.
> A month ago it worked fine.
> sp_configure 'show advanced options', 1;
> GO
> RECONFIGURE;
> GO
> sp_configure 'Ole Automation Procedures', 1;
> GO
> RECONFIGURE;
> GO
>
> Regards Kjell Arne Johansen
> "Tibor Karaszi" wrote:
|||Yes you are right.
The command RECONFIGURE causes this error to occur while RECONFIGURE WITH
OVERRIDE works fine.
Regards Kjell Arne Johansen
"Tibor Karaszi" wrote:

> Are you saying that below code, in itself, generates the error you posted? I just tried on my sp2
> with GDR, no error message...
> sp_configure 'show advanced options', 1;
> GO
> RECONFIGURE;
> GO
> sp_configure 'Ole Automation Procedures', 1;
> GO
> RECONFIGURE;
> GO
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Kjell Arne Johansen" <KjellArneJohansen@.discussions.microsoft.com> wrote in message
> news:AC5E975F-39E6-482A-9C1D-700C613AA3C6@.microsoft.com...
>
>
|||> This is because you have the option "allow updates" set to 1
Good catch, Jasper. I never thought of trying with allow updates set, and I wouldn't have thought
that having it set would cause this strange error message...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:OumUYWcgHHA.4900@.TK2MSFTNGP05.phx.gbl...
> This is because you have the option "allow updates" set to 1 (this is obsolete in 2005 and causes
> the error you are seeing when you do not use WITH OVERRIDE. Run the following
> exec sp_configure 'show advanced options', 1;
> GO
> RECONFIGURE WITH OVERRIDE;
> GO
> sp_configure 'allow updates, 0;
> GO
> RECONFIGURE WITH OVERRIDE;
> GO
> You should now be able to run your original script with no errors.
>
> --
> HTH,
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> http://sqlblogcasts.com/blogs/sqldbatips
>
> "Kjell Arne Johansen" <KjellArneJohansen@.discussions.microsoft.com> wrote in message
> news:07F98FD2-5464-4F0D-ACEC-8FC4CF604FD2@.microsoft.com...
>
|||Thank you both for your help.
I don't remember setting allow updates either.
Thanks again.
Regards Kjell Arne Johansen
"Jasper Smith" wrote:

> Only reason I knew is because it happened to me :-) It took me a while to
> figure out why I was getting errors doing reconfigures and to be honest I
> don't remember setting allow updates on at all.
> --
> HTH,
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> http://sqlblogcasts.com/blogs/sqldbatips
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:OL$AQjdgHHA.4552@.TK2MSFTNGP04.phx.gbl...
>
>

Ad hoc update to system catalogs is not supported?

Hi
SQL Server 2005 Express.
25 january 2007 I added a new procedure to my database using ole automation
procedures such as sp_OACreate and sp_OAMethod.
These system procedures requires 'Ole Automation Procedures' to be enabled.
So I added the following to my installation script:
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO
The script run fine and my procedue also runs fine.
But suddenly today installaing a new database I get an error mesage:
"Ad hoc update to system catalogs is not supported"
when I run the commands above.
Does anyone now what has happend since january? I installed a SP2 of SQL
Server Express. Has there been any changes?
Running "reconfigure with override" works but it is not recommended to use
"with override" I have read.
Is there any other way of enabling 'Ole Automation Procedures' which is
allowed?
Help is appreciated.
Regards Kjell Arne JohansenWe need to know what or who is trying to modify the system tables. Without seeing any code, we can't
say where the problem it. My guess is that you instantiate a COM object, which in turn connect back
to SQL Server and try to modify the system table - something you cannot do in 2005.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Kjell Arne Johansen" <KjellArneJohansen@.discussions.microsoft.com> wrote in message
news:EAA6BCF7-ED40-41EF-9968-AB473829DBD8@.microsoft.com...
> Hi
> SQL Server 2005 Express.
> 25 january 2007 I added a new procedure to my database using ole automation
> procedures such as sp_OACreate and sp_OAMethod.
> These system procedures requires 'Ole Automation Procedures' to be enabled.
> So I added the following to my installation script:
> sp_configure 'show advanced options', 1;
> GO
> RECONFIGURE;
> GO
> sp_configure 'Ole Automation Procedures', 1;
> GO
> RECONFIGURE;
> GO
> The script run fine and my procedue also runs fine.
> But suddenly today installaing a new database I get an error mesage:
> "Ad hoc update to system catalogs is not supported"
> when I run the commands above.
> Does anyone now what has happend since january? I installed a SP2 of SQL
> Server Express. Has there been any changes?
> Running "reconfigure with override" works but it is not recommended to use
> "with override" I have read.
> Is there any other way of enabling 'Ole Automation Procedures' which is
> allowed?
> Help is appreciated.
> Regards Kjell Arne Johansen
>
>|||Hi
This is the code causing the error message to occur when it is executed.
A month ago it worked fine.
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO
Regards Kjell Arne Johansen
"Tibor Karaszi" wrote:
> We need to know what or who is trying to modify the system tables. Without seeing any code, we can't
> say where the problem it. My guess is that you instantiate a COM object, which in turn connect back
> to SQL Server and try to modify the system table - something you cannot do in 2005.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Kjell Arne Johansen" <KjellArneJohansen@.discussions.microsoft.com> wrote in message
> news:EAA6BCF7-ED40-41EF-9968-AB473829DBD8@.microsoft.com...
> > Hi
> >
> > SQL Server 2005 Express.
> > 25 january 2007 I added a new procedure to my database using ole automation
> > procedures such as sp_OACreate and sp_OAMethod.
> >
> > These system procedures requires 'Ole Automation Procedures' to be enabled.
> > So I added the following to my installation script:
> > sp_configure 'show advanced options', 1;
> > GO
> > RECONFIGURE;
> > GO
> > sp_configure 'Ole Automation Procedures', 1;
> > GO
> > RECONFIGURE;
> > GO
> >
> > The script run fine and my procedue also runs fine.
> >
> > But suddenly today installaing a new database I get an error mesage:
> > "Ad hoc update to system catalogs is not supported"
> > when I run the commands above.
> > Does anyone now what has happend since january? I installed a SP2 of SQL
> > Server Express. Has there been any changes?
> >
> > Running "reconfigure with override" works but it is not recommended to use
> > "with override" I have read.
> >
> > Is there any other way of enabling 'Ole Automation Procedures' which is
> > allowed?
> >
> > Help is appreciated.
> >
> > Regards Kjell Arne Johansen
> >
> >
> >
>
>|||Are you saying that below code, in itself, generates the error you posted? I just tried on my sp2
with GDR, no error message...
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Kjell Arne Johansen" <KjellArneJohansen@.discussions.microsoft.com> wrote in message
news:AC5E975F-39E6-482A-9C1D-700C613AA3C6@.microsoft.com...
> Hi
> This is the code causing the error message to occur when it is executed.
> A month ago it worked fine.
> sp_configure 'show advanced options', 1;
> GO
> RECONFIGURE;
> GO
> sp_configure 'Ole Automation Procedures', 1;
> GO
> RECONFIGURE;
> GO
>
> Regards Kjell Arne Johansen
> "Tibor Karaszi" wrote:
>> We need to know what or who is trying to modify the system tables. Without seeing any code, we
>> can't
>> say where the problem it. My guess is that you instantiate a COM object, which in turn connect
>> back
>> to SQL Server and try to modify the system table - something you cannot do in 2005.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Kjell Arne Johansen" <KjellArneJohansen@.discussions.microsoft.com> wrote in message
>> news:EAA6BCF7-ED40-41EF-9968-AB473829DBD8@.microsoft.com...
>> > Hi
>> >
>> > SQL Server 2005 Express.
>> > 25 january 2007 I added a new procedure to my database using ole automation
>> > procedures such as sp_OACreate and sp_OAMethod.
>> >
>> > These system procedures requires 'Ole Automation Procedures' to be enabled.
>> > So I added the following to my installation script:
>> > sp_configure 'show advanced options', 1;
>> > GO
>> > RECONFIGURE;
>> > GO
>> > sp_configure 'Ole Automation Procedures', 1;
>> > GO
>> > RECONFIGURE;
>> > GO
>> >
>> > The script run fine and my procedue also runs fine.
>> >
>> > But suddenly today installaing a new database I get an error mesage:
>> > "Ad hoc update to system catalogs is not supported"
>> > when I run the commands above.
>> > Does anyone now what has happend since january? I installed a SP2 of SQL
>> > Server Express. Has there been any changes?
>> >
>> > Running "reconfigure with override" works but it is not recommended to use
>> > "with override" I have read.
>> >
>> > Is there any other way of enabling 'Ole Automation Procedures' which is
>> > allowed?
>> >
>> > Help is appreciated.
>> >
>> > Regards Kjell Arne Johansen
>> >
>> >
>> >
>>|||Yes you are right.
The command RECONFIGURE causes this error to occur while RECONFIGURE WITH
OVERRIDE works fine.
Regards Kjell Arne Johansen
"Tibor Karaszi" wrote:
> Are you saying that below code, in itself, generates the error you posted? I just tried on my sp2
> with GDR, no error message...
> sp_configure 'show advanced options', 1;
> GO
> RECONFIGURE;
> GO
> sp_configure 'Ole Automation Procedures', 1;
> GO
> RECONFIGURE;
> GO
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Kjell Arne Johansen" <KjellArneJohansen@.discussions.microsoft.com> wrote in message
> news:AC5E975F-39E6-482A-9C1D-700C613AA3C6@.microsoft.com...
> > Hi
> >
> > This is the code causing the error message to occur when it is executed.
> > A month ago it worked fine.
> >
> > sp_configure 'show advanced options', 1;
> > GO
> > RECONFIGURE;
> > GO
> > sp_configure 'Ole Automation Procedures', 1;
> > GO
> > RECONFIGURE;
> > GO
> >
> >
> > Regards Kjell Arne Johansen
> >
> > "Tibor Karaszi" wrote:
> >
> >> We need to know what or who is trying to modify the system tables. Without seeing any code, we
> >> can't
> >> say where the problem it. My guess is that you instantiate a COM object, which in turn connect
> >> back
> >> to SQL Server and try to modify the system table - something you cannot do in 2005.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "Kjell Arne Johansen" <KjellArneJohansen@.discussions.microsoft.com> wrote in message
> >> news:EAA6BCF7-ED40-41EF-9968-AB473829DBD8@.microsoft.com...
> >> > Hi
> >> >
> >> > SQL Server 2005 Express.
> >> > 25 january 2007 I added a new procedure to my database using ole automation
> >> > procedures such as sp_OACreate and sp_OAMethod.
> >> >
> >> > These system procedures requires 'Ole Automation Procedures' to be enabled.
> >> > So I added the following to my installation script:
> >> > sp_configure 'show advanced options', 1;
> >> > GO
> >> > RECONFIGURE;
> >> > GO
> >> > sp_configure 'Ole Automation Procedures', 1;
> >> > GO
> >> > RECONFIGURE;
> >> > GO
> >> >
> >> > The script run fine and my procedue also runs fine.
> >> >
> >> > But suddenly today installaing a new database I get an error mesage:
> >> > "Ad hoc update to system catalogs is not supported"
> >> > when I run the commands above.
> >> > Does anyone now what has happend since january? I installed a SP2 of SQL
> >> > Server Express. Has there been any changes?
> >> >
> >> > Running "reconfigure with override" works but it is not recommended to use
> >> > "with override" I have read.
> >> >
> >> > Is there any other way of enabling 'Ole Automation Procedures' which is
> >> > allowed?
> >> >
> >> > Help is appreciated.
> >> >
> >> > Regards Kjell Arne Johansen
> >> >
> >> >
> >> >
> >>
> >>
> >>
>
>|||This is because you have the option "allow updates" set to 1 (this is
obsolete in 2005 and causes the error you are seeing when you do not use
WITH OVERRIDE. Run the following
exec sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
sp_configure 'allow updates, 0;
GO
RECONFIGURE WITH OVERRIDE;
GO
You should now be able to run your original script with no errors.
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
http://sqlblogcasts.com/blogs/sqldbatips
"Kjell Arne Johansen" <KjellArneJohansen@.discussions.microsoft.com> wrote in
message news:07F98FD2-5464-4F0D-ACEC-8FC4CF604FD2@.microsoft.com...
> Yes you are right.
> The command RECONFIGURE causes this error to occur while RECONFIGURE WITH
> OVERRIDE works fine.
> Regards Kjell Arne Johansen
> "Tibor Karaszi" wrote:
>> Are you saying that below code, in itself, generates the error you
>> posted? I just tried on my sp2
>> with GDR, no error message...
>> sp_configure 'show advanced options', 1;
>> GO
>> RECONFIGURE;
>> GO
>> sp_configure 'Ole Automation Procedures', 1;
>> GO
>> RECONFIGURE;
>> GO
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Kjell Arne Johansen" <KjellArneJohansen@.discussions.microsoft.com> wrote
>> in message
>> news:AC5E975F-39E6-482A-9C1D-700C613AA3C6@.microsoft.com...
>> > Hi
>> >
>> > This is the code causing the error message to occur when it is
>> > executed.
>> > A month ago it worked fine.
>> >
>> > sp_configure 'show advanced options', 1;
>> > GO
>> > RECONFIGURE;
>> > GO
>> > sp_configure 'Ole Automation Procedures', 1;
>> > GO
>> > RECONFIGURE;
>> > GO
>> >
>> >
>> > Regards Kjell Arne Johansen
>> >
>> > "Tibor Karaszi" wrote:
>> >
>> >> We need to know what or who is trying to modify the system tables.
>> >> Without seeing any code, we
>> >> can't
>> >> say where the problem it. My guess is that you instantiate a COM
>> >> object, which in turn connect
>> >> back
>> >> to SQL Server and try to modify the system table - something you
>> >> cannot do in 2005.
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://www.solidqualitylearning.com/
>> >>
>> >>
>> >> "Kjell Arne Johansen" <KjellArneJohansen@.discussions.microsoft.com>
>> >> wrote in message
>> >> news:EAA6BCF7-ED40-41EF-9968-AB473829DBD8@.microsoft.com...
>> >> > Hi
>> >> >
>> >> > SQL Server 2005 Express.
>> >> > 25 january 2007 I added a new procedure to my database using ole
>> >> > automation
>> >> > procedures such as sp_OACreate and sp_OAMethod.
>> >> >
>> >> > These system procedures requires 'Ole Automation Procedures' to be
>> >> > enabled.
>> >> > So I added the following to my installation script:
>> >> > sp_configure 'show advanced options', 1;
>> >> > GO
>> >> > RECONFIGURE;
>> >> > GO
>> >> > sp_configure 'Ole Automation Procedures', 1;
>> >> > GO
>> >> > RECONFIGURE;
>> >> > GO
>> >> >
>> >> > The script run fine and my procedue also runs fine.
>> >> >
>> >> > But suddenly today installaing a new database I get an error mesage:
>> >> > "Ad hoc update to system catalogs is not supported"
>> >> > when I run the commands above.
>> >> > Does anyone now what has happend since january? I installed a SP2
>> >> > of SQL
>> >> > Server Express. Has there been any changes?
>> >> >
>> >> > Running "reconfigure with override" works but it is not recommended
>> >> > to use
>> >> > "with override" I have read.
>> >> >
>> >> > Is there any other way of enabling 'Ole Automation Procedures' which
>> >> > is
>> >> > allowed?
>> >> >
>> >> > Help is appreciated.
>> >> >
>> >> > Regards Kjell Arne Johansen
>> >> >
>> >> >
>> >> >
>> >>
>> >>
>> >>
>>|||> This is because you have the option "allow updates" set to 1
Good catch, Jasper. I never thought of trying with allow updates set, and I wouldn't have thought
that having it set would cause this strange error message...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:OumUYWcgHHA.4900@.TK2MSFTNGP05.phx.gbl...
> This is because you have the option "allow updates" set to 1 (this is obsolete in 2005 and causes
> the error you are seeing when you do not use WITH OVERRIDE. Run the following
> exec sp_configure 'show advanced options', 1;
> GO
> RECONFIGURE WITH OVERRIDE;
> GO
> sp_configure 'allow updates, 0;
> GO
> RECONFIGURE WITH OVERRIDE;
> GO
> You should now be able to run your original script with no errors.
>
> --
> HTH,
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> http://sqlblogcasts.com/blogs/sqldbatips
>
> "Kjell Arne Johansen" <KjellArneJohansen@.discussions.microsoft.com> wrote in message
> news:07F98FD2-5464-4F0D-ACEC-8FC4CF604FD2@.microsoft.com...
>> Yes you are right.
>> The command RECONFIGURE causes this error to occur while RECONFIGURE WITH
>> OVERRIDE works fine.
>> Regards Kjell Arne Johansen
>> "Tibor Karaszi" wrote:
>> Are you saying that below code, in itself, generates the error you posted? I just tried on my
>> sp2
>> with GDR, no error message...
>> sp_configure 'show advanced options', 1;
>> GO
>> RECONFIGURE;
>> GO
>> sp_configure 'Ole Automation Procedures', 1;
>> GO
>> RECONFIGURE;
>> GO
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Kjell Arne Johansen" <KjellArneJohansen@.discussions.microsoft.com> wrote in message
>> news:AC5E975F-39E6-482A-9C1D-700C613AA3C6@.microsoft.com...
>> > Hi
>> >
>> > This is the code causing the error message to occur when it is executed.
>> > A month ago it worked fine.
>> >
>> > sp_configure 'show advanced options', 1;
>> > GO
>> > RECONFIGURE;
>> > GO
>> > sp_configure 'Ole Automation Procedures', 1;
>> > GO
>> > RECONFIGURE;
>> > GO
>> >
>> >
>> > Regards Kjell Arne Johansen
>> >
>> > "Tibor Karaszi" wrote:
>> >
>> >> We need to know what or who is trying to modify the system tables. Without seeing any code,
>> >> we
>> >> can't
>> >> say where the problem it. My guess is that you instantiate a COM object, which in turn
>> >> connect
>> >> back
>> >> to SQL Server and try to modify the system table - something you cannot do in 2005.
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://www.solidqualitylearning.com/
>> >>
>> >>
>> >> "Kjell Arne Johansen" <KjellArneJohansen@.discussions.microsoft.com> wrote in message
>> >> news:EAA6BCF7-ED40-41EF-9968-AB473829DBD8@.microsoft.com...
>> >> > Hi
>> >> >
>> >> > SQL Server 2005 Express.
>> >> > 25 january 2007 I added a new procedure to my database using ole automation
>> >> > procedures such as sp_OACreate and sp_OAMethod.
>> >> >
>> >> > These system procedures requires 'Ole Automation Procedures' to be enabled.
>> >> > So I added the following to my installation script:
>> >> > sp_configure 'show advanced options', 1;
>> >> > GO
>> >> > RECONFIGURE;
>> >> > GO
>> >> > sp_configure 'Ole Automation Procedures', 1;
>> >> > GO
>> >> > RECONFIGURE;
>> >> > GO
>> >> >
>> >> > The script run fine and my procedue also runs fine.
>> >> >
>> >> > But suddenly today installaing a new database I get an error mesage:
>> >> > "Ad hoc update to system catalogs is not supported"
>> >> > when I run the commands above.
>> >> > Does anyone now what has happend since january? I installed a SP2 of SQL
>> >> > Server Express. Has there been any changes?
>> >> >
>> >> > Running "reconfigure with override" works but it is not recommended to use
>> >> > "with override" I have read.
>> >> >
>> >> > Is there any other way of enabling 'Ole Automation Procedures' which is
>> >> > allowed?
>> >> >
>> >> > Help is appreciated.
>> >> >
>> >> > Regards Kjell Arne Johansen
>> >> >
>> >> >
>> >> >
>> >>
>> >>
>> >>
>>
>|||Only reason I knew is because it happened to me :-) It took me a while to
figure out why I was getting errors doing reconfigures and to be honest I
don't remember setting allow updates on at all.
--
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
http://sqlblogcasts.com/blogs/sqldbatips
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OL$AQjdgHHA.4552@.TK2MSFTNGP04.phx.gbl...
>> This is because you have the option "allow updates" set to 1
> Good catch, Jasper. I never thought of trying with allow updates set, and
> I wouldn't have thought that having it set would cause this strange error
> message...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
> news:OumUYWcgHHA.4900@.TK2MSFTNGP05.phx.gbl...
>> This is because you have the option "allow updates" set to 1 (this is
>> obsolete in 2005 and causes the error you are seeing when you do not use
>> WITH OVERRIDE. Run the following
>> exec sp_configure 'show advanced options', 1;
>> GO
>> RECONFIGURE WITH OVERRIDE;
>> GO
>> sp_configure 'allow updates, 0;
>> GO
>> RECONFIGURE WITH OVERRIDE;
>> GO
>> You should now be able to run your original script with no errors.
>>
>> --
>> HTH,
>> Jasper Smith (SQL Server MVP)
>> http://www.sqldbatips.com
>> http://sqlblogcasts.com/blogs/sqldbatips
>>
>> "Kjell Arne Johansen" <KjellArneJohansen@.discussions.microsoft.com> wrote
>> in message news:07F98FD2-5464-4F0D-ACEC-8FC4CF604FD2@.microsoft.com...
>> Yes you are right.
>> The command RECONFIGURE causes this error to occur while RECONFIGURE
>> WITH
>> OVERRIDE works fine.
>> Regards Kjell Arne Johansen
>> "Tibor Karaszi" wrote:
>> Are you saying that below code, in itself, generates the error you
>> posted? I just tried on my sp2
>> with GDR, no error message...
>> sp_configure 'show advanced options', 1;
>> GO
>> RECONFIGURE;
>> GO
>> sp_configure 'Ole Automation Procedures', 1;
>> GO
>> RECONFIGURE;
>> GO
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Kjell Arne Johansen" <KjellArneJohansen@.discussions.microsoft.com>
>> wrote in message
>> news:AC5E975F-39E6-482A-9C1D-700C613AA3C6@.microsoft.com...
>> > Hi
>> >
>> > This is the code causing the error message to occur when it is
>> > executed.
>> > A month ago it worked fine.
>> >
>> > sp_configure 'show advanced options', 1;
>> > GO
>> > RECONFIGURE;
>> > GO
>> > sp_configure 'Ole Automation Procedures', 1;
>> > GO
>> > RECONFIGURE;
>> > GO
>> >
>> >
>> > Regards Kjell Arne Johansen
>> >
>> > "Tibor Karaszi" wrote:
>> >
>> >> We need to know what or who is trying to modify the system tables.
>> >> Without seeing any code, we
>> >> can't
>> >> say where the problem it. My guess is that you instantiate a COM
>> >> object, which in turn connect
>> >> back
>> >> to SQL Server and try to modify the system table - something you
>> >> cannot do in 2005.
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://www.solidqualitylearning.com/
>> >>
>> >>
>> >> "Kjell Arne Johansen" <KjellArneJohansen@.discussions.microsoft.com>
>> >> wrote in message
>> >> news:EAA6BCF7-ED40-41EF-9968-AB473829DBD8@.microsoft.com...
>> >> > Hi
>> >> >
>> >> > SQL Server 2005 Express.
>> >> > 25 january 2007 I added a new procedure to my database using ole
>> >> > automation
>> >> > procedures such as sp_OACreate and sp_OAMethod.
>> >> >
>> >> > These system procedures requires 'Ole Automation Procedures' to be
>> >> > enabled.
>> >> > So I added the following to my installation script:
>> >> > sp_configure 'show advanced options', 1;
>> >> > GO
>> >> > RECONFIGURE;
>> >> > GO
>> >> > sp_configure 'Ole Automation Procedures', 1;
>> >> > GO
>> >> > RECONFIGURE;
>> >> > GO
>> >> >
>> >> > The script run fine and my procedue also runs fine.
>> >> >
>> >> > But suddenly today installaing a new database I get an error
>> >> > mesage:
>> >> > "Ad hoc update to system catalogs is not supported"
>> >> > when I run the commands above.
>> >> > Does anyone now what has happend since january? I installed a SP2
>> >> > of SQL
>> >> > Server Express. Has there been any changes?
>> >> >
>> >> > Running "reconfigure with override" works but it is not
>> >> > recommended to use
>> >> > "with override" I have read.
>> >> >
>> >> > Is there any other way of enabling 'Ole Automation Procedures'
>> >> > which is
>> >> > allowed?
>> >> >
>> >> > Help is appreciated.
>> >> >
>> >> > Regards Kjell Arne Johansen
>> >> >
>> >> >
>> >> >
>> >>
>> >>
>> >>
>>
>>
>|||Thank you both for your help.
I don't remember setting allow updates either.
Thanks again.
Regards Kjell Arne Johansen
"Jasper Smith" wrote:
> Only reason I knew is because it happened to me :-) It took me a while to
> figure out why I was getting errors doing reconfigures and to be honest I
> don't remember setting allow updates on at all.
> --
> HTH,
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> http://sqlblogcasts.com/blogs/sqldbatips
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:OL$AQjdgHHA.4552@.TK2MSFTNGP04.phx.gbl...
> >> This is because you have the option "allow updates" set to 1
> >
> > Good catch, Jasper. I never thought of trying with allow updates set, and
> > I wouldn't have thought that having it set would cause this strange error
> > message...
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://sqlblog.com/blogs/tibor_karaszi
> >
> >
> > "Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
> > news:OumUYWcgHHA.4900@.TK2MSFTNGP05.phx.gbl...
> >> This is because you have the option "allow updates" set to 1 (this is
> >> obsolete in 2005 and causes the error you are seeing when you do not use
> >> WITH OVERRIDE. Run the following
> >>
> >> exec sp_configure 'show advanced options', 1;
> >> GO
> >> RECONFIGURE WITH OVERRIDE;
> >> GO
> >> sp_configure 'allow updates, 0;
> >> GO
> >> RECONFIGURE WITH OVERRIDE;
> >> GO
> >>
> >> You should now be able to run your original script with no errors.
> >>
> >>
> >> --
> >> HTH,
> >> Jasper Smith (SQL Server MVP)
> >> http://www.sqldbatips.com
> >> http://sqlblogcasts.com/blogs/sqldbatips
> >>
> >>
> >> "Kjell Arne Johansen" <KjellArneJohansen@.discussions.microsoft.com> wrote
> >> in message news:07F98FD2-5464-4F0D-ACEC-8FC4CF604FD2@.microsoft.com...
> >> Yes you are right.
> >>
> >> The command RECONFIGURE causes this error to occur while RECONFIGURE
> >> WITH
> >> OVERRIDE works fine.
> >>
> >> Regards Kjell Arne Johansen
> >>
> >> "Tibor Karaszi" wrote:
> >>
> >> Are you saying that below code, in itself, generates the error you
> >> posted? I just tried on my sp2
> >> with GDR, no error message...
> >>
> >> sp_configure 'show advanced options', 1;
> >> GO
> >> RECONFIGURE;
> >> GO
> >> sp_configure 'Ole Automation Procedures', 1;
> >> GO
> >> RECONFIGURE;
> >> GO
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "Kjell Arne Johansen" <KjellArneJohansen@.discussions.microsoft.com>
> >> wrote in message
> >> news:AC5E975F-39E6-482A-9C1D-700C613AA3C6@.microsoft.com...
> >> > Hi
> >> >
> >> > This is the code causing the error message to occur when it is
> >> > executed.
> >> > A month ago it worked fine.
> >> >
> >> > sp_configure 'show advanced options', 1;
> >> > GO
> >> > RECONFIGURE;
> >> > GO
> >> > sp_configure 'Ole Automation Procedures', 1;
> >> > GO
> >> > RECONFIGURE;
> >> > GO
> >> >
> >> >
> >> > Regards Kjell Arne Johansen
> >> >
> >> > "Tibor Karaszi" wrote:
> >> >
> >> >> We need to know what or who is trying to modify the system tables.
> >> >> Without seeing any code, we
> >> >> can't
> >> >> say where the problem it. My guess is that you instantiate a COM
> >> >> object, which in turn connect
> >> >> back
> >> >> to SQL Server and try to modify the system table - something you
> >> >> cannot do in 2005.
> >> >>
> >> >> --
> >> >> Tibor Karaszi, SQL Server MVP
> >> >> http://www.karaszi.com/sqlserver/default.asp
> >> >> http://www.solidqualitylearning.com/
> >> >>
> >> >>
> >> >> "Kjell Arne Johansen" <KjellArneJohansen@.discussions.microsoft.com>
> >> >> wrote in message
> >> >> news:EAA6BCF7-ED40-41EF-9968-AB473829DBD8@.microsoft.com...
> >> >> > Hi
> >> >> >
> >> >> > SQL Server 2005 Express.
> >> >> > 25 january 2007 I added a new procedure to my database using ole
> >> >> > automation
> >> >> > procedures such as sp_OACreate and sp_OAMethod.
> >> >> >
> >> >> > These system procedures requires 'Ole Automation Procedures' to be
> >> >> > enabled.
> >> >> > So I added the following to my installation script:
> >> >> > sp_configure 'show advanced options', 1;
> >> >> > GO
> >> >> > RECONFIGURE;
> >> >> > GO
> >> >> > sp_configure 'Ole Automation Procedures', 1;
> >> >> > GO
> >> >> > RECONFIGURE;
> >> >> > GO
> >> >> >
> >> >> > The script run fine and my procedue also runs fine.
> >> >> >
> >> >> > But suddenly today installaing a new database I get an error
> >> >> > mesage:
> >> >> > "Ad hoc update to system catalogs is not supported"
> >> >> > when I run the commands above.
> >> >> > Does anyone now what has happend since january? I installed a SP2
> >> >> > of SQL
> >> >> > Server Express. Has there been any changes?
> >> >> >
> >> >> > Running "reconfigure with override" works but it is not
> >> >> > recommended to use
> >> >> > "with override" I have read.
> >> >> >
> >> >> > Is there any other way of enabling 'Ole Automation Procedures'
> >> >> > which is
> >> >> > allowed?
> >> >> >
> >> >> > Help is appreciated.
> >> >> >
> >> >> > Regards Kjell Arne Johansen
> >> >> >
> >> >> >
> >> >> >
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
> >>
> >>
> >
>
>

Ad hoc update to system catalogs is not supported?

Hi
SQL Server 2005 Express.
25 january 2007 I added a new procedure to my database using ole automation
procedures such as sp_OACreate and sp_OAMethod.
These system procedures requires 'Ole Automation Procedures' to be enabled.
So I added the following to my installation script:
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO
The script run fine and my procedue also runs fine.
But suddenly today installaing a new database I get an error mesage:
"Ad hoc update to system catalogs is not supported"
when I run the commands above.
Does anyone now what has happend since january? I installed a SP2 of SQL
Server Express. Has there been any changes?
Running "reconfigure with override" works but it is not recommended to use
"with override" I have read.
Is there any other way of enabling 'Ole Automation Procedures' which is
allowed?
Help is appreciated.
Regards Kjell Arne JohansenWe need to know what or who is trying to modify the system tables. Without s
eeing any code, we can't
say where the problem it. My guess is that you instantiate a COM object, whi
ch in turn connect back
to SQL Server and try to modify the system table - something you cannot do i
n 2005.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Kjell Arne Johansen" <KjellArneJohansen@.discussions.microsoft.com> wrote in
message
news:EAA6BCF7-ED40-41EF-9968-AB473829DBD8@.microsoft.com...
> Hi
> SQL Server 2005 Express.
> 25 january 2007 I added a new procedure to my database using ole automatio
n
> procedures such as sp_OACreate and sp_OAMethod.
> These system procedures requires 'Ole Automation Procedures' to be enabled
.
> So I added the following to my installation script:
> sp_configure 'show advanced options', 1;
> GO
> RECONFIGURE;
> GO
> sp_configure 'Ole Automation Procedures', 1;
> GO
> RECONFIGURE;
> GO
> The script run fine and my procedue also runs fine.
> But suddenly today installaing a new database I get an error mesage:
> "Ad hoc update to system catalogs is not supported"
> when I run the commands above.
> Does anyone now what has happend since january? I installed a SP2 of SQL
> Server Express. Has there been any changes?
> Running "reconfigure with override" works but it is not recommended to use
> "with override" I have read.
> Is there any other way of enabling 'Ole Automation Procedures' which is
> allowed?
> Help is appreciated.
> Regards Kjell Arne Johansen
>
>|||Hi
This is the code causing the error message to occur when it is executed.
A month ago it worked fine.
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO
Regards Kjell Arne Johansen
"Tibor Karaszi" wrote:

> We need to know what or who is trying to modify the system tables. Without
seeing any code, we can't
> say where the problem it. My guess is that you instantiate a COM object, w
hich in turn connect back
> to SQL Server and try to modify the system table - something you cannot do
in 2005.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Kjell Arne Johansen" <KjellArneJohansen@.discussions.microsoft.com> wrote
in message
> news:EAA6BCF7-ED40-41EF-9968-AB473829DBD8@.microsoft.com...
>
>|||Are you saying that below code, in itself, generates the error you posted? I
just tried on my sp2
with GDR, no error message...
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Kjell Arne Johansen" <KjellArneJohansen@.discussions.microsoft.com> wrote in
message
news:AC5E975F-39E6-482A-9C1D-700C613AA3C6@.microsoft.com...[vbcol=seagreen]
> Hi
> This is the code causing the error message to occur when it is executed.
> A month ago it worked fine.
> sp_configure 'show advanced options', 1;
> GO
> RECONFIGURE;
> GO
> sp_configure 'Ole Automation Procedures', 1;
> GO
> RECONFIGURE;
> GO
>
> Regards Kjell Arne Johansen
> "Tibor Karaszi" wrote:
>|||Yes you are right.
The command RECONFIGURE causes this error to occur while RECONFIGURE WITH
OVERRIDE works fine.
Regards Kjell Arne Johansen
"Tibor Karaszi" wrote:

> Are you saying that below code, in itself, generates the error you posted?
I just tried on my sp2
> with GDR, no error message...
> sp_configure 'show advanced options', 1;
> GO
> RECONFIGURE;
> GO
> sp_configure 'Ole Automation Procedures', 1;
> GO
> RECONFIGURE;
> GO
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Kjell Arne Johansen" <KjellArneJohansen@.discussions.microsoft.com> wrote
in message
> news:AC5E975F-39E6-482A-9C1D-700C613AA3C6@.microsoft.com...
>
>|||This is because you have the option "allow updates" set to 1 (this is
obsolete in 2005 and causes the error you are seeing when you do not use
WITH OVERRIDE. Run the following
exec sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
sp_configure 'allow updates, 0;
GO
RECONFIGURE WITH OVERRIDE;
GO
You should now be able to run your original script with no errors.
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
http://sqlblogcasts.com/blogs/sqldbatips
"Kjell Arne Johansen" <KjellArneJohansen@.discussions.microsoft.com> wrote in
message news:07F98FD2-5464-4F0D-ACEC-8FC4CF604FD2@.microsoft.com...[vbcol=seagreen]
> Yes you are right.
> The command RECONFIGURE causes this error to occur while RECONFIGURE WITH
> OVERRIDE works fine.
> Regards Kjell Arne Johansen
> "Tibor Karaszi" wrote:
>|||> This is because you have the option "allow updates" set to 1
Good catch, Jasper. I never thought of trying with allow updates set, and I
wouldn't have thought
that having it set would cause this strange error message...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:OumUYWcgHHA.4900@.TK2MSFTNGP05.phx.gbl...
> This is because you have the option "allow updates" set to 1 (this is obso
lete in 2005 and causes
> the error you are seeing when you do not use WITH OVERRIDE. Run the follow
ing
> exec sp_configure 'show advanced options', 1;
> GO
> RECONFIGURE WITH OVERRIDE;
> GO
> sp_configure 'allow updates, 0;
> GO
> RECONFIGURE WITH OVERRIDE;
> GO
> You should now be able to run your original script with no errors.
>
> --
> HTH,
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> http://sqlblogcasts.com/blogs/sqldbatips
>
> "Kjell Arne Johansen" <KjellArneJohansen@.discussions.microsoft.com> wrote
in message
> news:07F98FD2-5464-4F0D-ACEC-8FC4CF604FD2@.microsoft.com...
>|||Only reason I knew is because it happened to me :-) It took me a while to
figure out why I was getting errors doing reconfigures and to be honest I
don't remember setting allow updates on at all.
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
http://sqlblogcasts.com/blogs/sqldbatips
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OL$AQjdgHHA.4552@.TK2MSFTNGP04.phx.gbl...
> Good catch, Jasper. I never thought of trying with allow updates set, and
> I wouldn't have thought that having it set would cause this strange error
> message...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
> news:OumUYWcgHHA.4900@.TK2MSFTNGP05.phx.gbl...
>|||Thank you both for your help.
I don't remember setting allow updates either.
Thanks again.
Regards Kjell Arne Johansen
"Jasper Smith" wrote:

> Only reason I knew is because it happened to me :-) It took me a while to
> figure out why I was getting errors doing reconfigures and to be honest I
> don't remember setting allow updates on at all.
> --
> HTH,
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> http://sqlblogcasts.com/blogs/sqldbatips
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message news:OL$AQjdgHHA.4552@.TK2MSFTNGP04.phx.gbl...
>
>

Ad hoc access to OLE DB provider 'Microsoft.Jet.OLEDB.40' has been denied.

Hi all,

I am currently working on a stored procedure in SQL 2000 where I use OPENROWSET function to read data from an Excel file into a temporary table.

It works fine when I logged in with username 'sa' and psswrd 'sa' but when I log in with another user name and password I get the following error:

"Ad hoc access to OLE DB provider 'Microsoft.Jet.OLEDB.40' has been denied.
You must access this provider through a linked server."

I am using VB 6.0 as front end. Is there anyway i can overcome this error?

Please help.

Dhiraj

I just started having this issue too. I had this working in SQLEXPRESS, but now I am moving to a new SQL Server (Version 3054) and started getting this error.

Ad hoc access to OLE DB provider 'Microsoft.Jet.OLEDB.40' has been denied.

Hi all,

I am currently working on a stored procedure in SQL 2000 where I use OPENROWSET function to read data from an Excel file into a temporary table.

It works fine when I logged in with username 'sa' and psswrd 'sa' but when I log in with another user name and password I get the following error:

"Ad hoc access to OLE DB provider 'Microsoft.Jet.OLEDB.40' has been denied.
You must access this provider through a linked server."

I am using VB 6.0 as front end. Is there anyway i can overcome this error?

Please help.

Dhiraj

I just started having this issue too. I had this working in SQLEXPRESS, but now I am moving to a new SQL Server (Version 3054) and started getting this error.

Actual vs. Estimated Row Counts

I have a stored procedure that contains a complex query of approximately 16
joins, (a mixture of inner and left joins). These joins are mostly on
standard user tables, but includes joins on two temporary tables and two non-
indexable views.
I have rewritten the query so as to reduce the reads from 5 million plus, to
about 1.6 million reads using some dynamic sql and adding additional indexes.
Still, in the execution plan, several of the user tables have estimated vs.
actual row counts that are quite different, such as 1 Estimated row versus
135,000 actual rows. These tables are using, what I consider the appropriate
index, but, as stated, the actual vs. estimated are way off.
The procedure takes several arguments and I set those arguments as local
variables in the procedure to prevent parameter sniffing.
I was hoping to get a better plan by getting the actual versus estimated row
counts closer. I have ran "update statistics <table_name> with fullscan" on
all the major tables used in the query and especially on those tables whose
actual vs. estimated are way off. This had no effect on the actual vs.
estimated row counts. I have also run sp_updatestats, dbcc dbreindex on the
tables, sp_recompile, but in each case, the estimated vs. actual is still way
off.
I have considered reordering some of the inner and left joins, but was not
sure if that would have any affect.
Would you be able to advise on what else one might do to get the actual vs.
estimated more in line?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200610/1
its difficult to get a better and more accurate plan after your actions
(updating stats etc...)
Specially with left joins...
the only way is to force the way you want to do your joins (manually
specify the hash/ merge option in the joins, setup the better order for your
joins etc...)
in this case you force SQL Server to use your hints instead of using its own
generated plan.
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:679360284efd2@.uwe...
>I have a stored procedure that contains a complex query of approximately 16
> joins, (a mixture of inner and left joins). These joins are mostly on
> standard user tables, but includes joins on two temporary tables and two
> non-
> indexable views.
> I have rewritten the query so as to reduce the reads from 5 million plus,
> to
> about 1.6 million reads using some dynamic sql and adding additional
> indexes.
> Still, in the execution plan, several of the user tables have estimated
> vs.
> actual row counts that are quite different, such as 1 Estimated row versus
> 135,000 actual rows. These tables are using, what I consider the
> appropriate
> index, but, as stated, the actual vs. estimated are way off.
> The procedure takes several arguments and I set those arguments as local
> variables in the procedure to prevent parameter sniffing.
> I was hoping to get a better plan by getting the actual versus estimated
> row
> counts closer. I have ran "update statistics <table_name> with fullscan"
> on
> all the major tables used in the query and especially on those tables
> whose
> actual vs. estimated are way off. This had no effect on the actual vs.
> estimated row counts. I have also run sp_updatestats, dbcc dbreindex on
> the
> tables, sp_recompile, but in each case, the estimated vs. actual is still
> way
> off.
> I have considered reordering some of the inner and left joins, but was not
> sure if that would have any affect.
> Would you be able to advise on what else one might do to get the actual
> vs.
> estimated more in line?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...erver/200610/1
>
|||> The procedure takes several arguments and I set those arguments as local
> variables in the procedure to prevent parameter sniffing.
That way, the optimizer uses hard-coded values to determine selectivity. Something like:
= 10% (actually, it might go by density in the statistics, if available)
> 30%
BETWEEN 25%
The percentage for the values differs from release to release, and can change even between service
packs. So, in other words, a variable is *not* the same thing as the optimizer knowing the value you
are looking for.
For the optimizer to *know* the value and have a static plan, you have to rely on parameter
sniffing. This value in the plan might of course be off for the subsequent usages of the plan.
Or, you would have to get a new plan for each execution. Either break out the critical parts of the
procedure to its own procedures and create them WITH RECOMPILE, or in 2005 you can add RECOMPILE
hint at the query level. Or you can in 2005 even say OPTIMIZE FOR a certain value.
You can of course add WITH RECOMPILE for the whole procedure, but then none of the DML statements in
the procedure will be pre-optimized.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"cbrichards via droptable.com" <u3288@.uwe> wrote in message news:679360284efd2@.uwe...
>I have a stored procedure that contains a complex query of approximately 16
> joins, (a mixture of inner and left joins). These joins are mostly on
> standard user tables, but includes joins on two temporary tables and two non-
> indexable views.
> I have rewritten the query so as to reduce the reads from 5 million plus, to
> about 1.6 million reads using some dynamic sql and adding additional indexes.
> Still, in the execution plan, several of the user tables have estimated vs.
> actual row counts that are quite different, such as 1 Estimated row versus
> 135,000 actual rows. These tables are using, what I consider the appropriate
> index, but, as stated, the actual vs. estimated are way off.
> The procedure takes several arguments and I set those arguments as local
> variables in the procedure to prevent parameter sniffing.
> I was hoping to get a better plan by getting the actual versus estimated row
> counts closer. I have ran "update statistics <table_name> with fullscan" on
> all the major tables used in the query and especially on those tables whose
> actual vs. estimated are way off. This had no effect on the actual vs.
> estimated row counts. I have also run sp_updatestats, dbcc dbreindex on the
> tables, sp_recompile, but in each case, the estimated vs. actual is still way
> off.
> I have considered reordering some of the inner and left joins, but was not
> sure if that would have any affect.
> Would you be able to advise on what else one might do to get the actual vs.
> estimated more in line?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...erver/200610/1
>
|||Thanks for your reply Jeje.
In regards to your comment of setting up a better order on my joins, is the
guide to setting up joins...ordering joins with the most selective join
first, followed by the next most selective join, etc., etc.?
Jeje wrote:[vbcol=seagreen]
>its difficult to get a better and more accurate plan after your actions
>(updating stats etc...)
>Specially with left joins...
>the only way is to force the way you want to do your joins (manually
>specify the hash/ merge option in the joins, setup the better order for your
>joins etc...)
>in this case you force SQL Server to use your hints instead of using its own
>generated plan.
>[quoted text clipped - 36 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200610/1

Actual vs. Estimated Row Counts

I have a stored procedure that contains a complex query of approximately 16
joins, (a mixture of inner and left joins). These joins are mostly on
standard user tables, but includes joins on two temporary tables and two non
-
indexable views.
I have rewritten the query so as to reduce the reads from 5 million plus, to
about 1.6 million reads using some dynamic sql and adding additional indexes
.
Still, in the execution plan, several of the user tables have estimated vs.
actual row counts that are quite different, such as 1 Estimated row versus
135,000 actual rows. These tables are using, what I consider the appropriate
index, but, as stated, the actual vs. estimated are way off.
The procedure takes several arguments and I set those arguments as local
variables in the procedure to prevent parameter sniffing.
I was hoping to get a better plan by getting the actual versus estimated row
counts closer. I have ran "update statistics <table_name> with fullscan" on
all the major tables used in the query and especially on those tables whose
actual vs. estimated are way off. This had no effect on the actual vs.
estimated row counts. I have also run sp_updatestats, dbcc dbreindex on the
tables, sp_recompile, but in each case, the estimated vs. actual is still wa
y
off.
I have considered reordering some of the inner and left joins, but was not
sure if that would have any affect.
Would you be able to advise on what else one might do to get the actual vs.
estimated more in line?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200610/1its difficult to get a better and more accurate plan after your actions
(updating stats etc...)
Specially with left joins...
the only way is to force the way you want to do your joins (manually
specify the hash/ merge option in the joins, setup the better order for your
joins etc...)
in this case you force SQL Server to use your hints instead of using its own
generated plan.
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:679360284efd2@.uwe...
>I have a stored procedure that contains a complex query of approximately 16
> joins, (a mixture of inner and left joins). These joins are mostly on
> standard user tables, but includes joins on two temporary tables and two
> non-
> indexable views.
> I have rewritten the query so as to reduce the reads from 5 million plus,
> to
> about 1.6 million reads using some dynamic sql and adding additional
> indexes.
> Still, in the execution plan, several of the user tables have estimated
> vs.
> actual row counts that are quite different, such as 1 Estimated row versus
> 135,000 actual rows. These tables are using, what I consider the
> appropriate
> index, but, as stated, the actual vs. estimated are way off.
> The procedure takes several arguments and I set those arguments as local
> variables in the procedure to prevent parameter sniffing.
> I was hoping to get a better plan by getting the actual versus estimated
> row
> counts closer. I have ran "update statistics <table_name> with fullscan"
> on
> all the major tables used in the query and especially on those tables
> whose
> actual vs. estimated are way off. This had no effect on the actual vs.
> estimated row counts. I have also run sp_updatestats, dbcc dbreindex on
> the
> tables, sp_recompile, but in each case, the estimated vs. actual is still
> way
> off.
> I have considered reordering some of the inner and left joins, but was not
> sure if that would have any affect.
> Would you be able to advise on what else one might do to get the actual
> vs.
> estimated more in line?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200610/1
>|||> The procedure takes several arguments and I set those arguments as local
> variables in the procedure to prevent parameter sniffing.
That way, the optimizer uses hard-coded values to determine selectivity. Som
ething like:
= 10% (actually, it might go by density in the statistics, if available)
> 30%
BETWEEN 25%
The percentage for the values differs from release to release, and can chang
e even between service
packs. So, in other words, a variable is *not* the same thing as the optimiz
er knowing the value you
are looking for.
For the optimizer to *know* the value and have a static plan, you have to re
ly on parameter
sniffing. This value in the plan might of course be off for the subsequent u
sages of the plan.
Or, you would have to get a new plan for each execution. Either break out th
e critical parts of the
procedure to its own procedures and create them WITH RECOMPILE, or in 2005 y
ou can add RECOMPILE
hint at the query level. Or you can in 2005 even say OPTIMIZE FOR a certain
value.
You can of course add WITH RECOMPILE for the whole procedure, but then none
of the DML statements in
the procedure will be pre-optimized.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"cbrichards via droptable.com" <u3288@.uwe> wrote in message news:679360284efd2@.uwe...[vbcol
=seagreen]
>I have a stored procedure that contains a complex query of approximately 16
> joins, (a mixture of inner and left joins). These joins are mostly on
> standard user tables, but includes joins on two temporary tables and two n
on-
> indexable views.
> I have rewritten the query so as to reduce the reads from 5 million plus,
to
> about 1.6 million reads using some dynamic sql and adding additional index
es.
> Still, in the execution plan, several of the user tables have estimated vs
.
> actual row counts that are quite different, such as 1 Estimated row versus
> 135,000 actual rows. These tables are using, what I consider the appropria
te
> index, but, as stated, the actual vs. estimated are way off.
> The procedure takes several arguments and I set those arguments as local
> variables in the procedure to prevent parameter sniffing.
> I was hoping to get a better plan by getting the actual versus estimated r
ow
> counts closer. I have ran "update statistics <table_name> with fullscan" o
n
> all the major tables used in the query and especially on those tables whos
e
> actual vs. estimated are way off. This had no effect on the actual vs.
> estimated row counts. I have also run sp_updatestats, dbcc dbreindex on th
e
> tables, sp_recompile, but in each case, the estimated vs. actual is still
way
> off.
> I have considered reordering some of the inner and left joins, but was not
> sure if that would have any affect.
> Would you be able to advise on what else one might do to get the actual vs
.
> estimated more in line?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200610/1
>[/vbcol]|||Thanks for your reply Jeje.
In regards to your comment of setting up a better order on my joins, is the
guide to setting up joins...ordering joins with the most selective join
first, followed by the next most selective join, etc., etc.?
Jeje wrote:[vbcol=seagreen]
>its difficult to get a better and more accurate plan after your actions
>(updating stats etc...)
>Specially with left joins...
>the only way is to force the way you want to do your joins (manually
>specify the hash/ merge option in the joins, setup the better order for you
r
>joins etc...)
>in this case you force SQL Server to use your hints instead of using its ow
n
>generated plan.
>
>[quoted text clipped - 36 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200610/1

Actual vs. Estimated Row Counts

I have a stored procedure that contains a complex query of approximately 16
joins, (a mixture of inner and left joins). These joins are mostly on
standard user tables, but includes joins on two temporary tables and two non-
indexable views.
I have rewritten the query so as to reduce the reads from 5 million plus, to
about 1.6 million reads using some dynamic sql and adding additional indexes.
Still, in the execution plan, several of the user tables have estimated vs.
actual row counts that are quite different, such as 1 Estimated row versus
135,000 actual rows. These tables are using, what I consider the appropriate
index, but, as stated, the actual vs. estimated are way off.
The procedure takes several arguments and I set those arguments as local
variables in the procedure to prevent parameter sniffing.
I was hoping to get a better plan by getting the actual versus estimated row
counts closer. I have ran "update statistics <table_name> with fullscan" on
all the major tables used in the query and especially on those tables whose
actual vs. estimated are way off. This had no effect on the actual vs.
estimated row counts. I have also run sp_updatestats, dbcc dbreindex on the
tables, sp_recompile, but in each case, the estimated vs. actual is still way
off.
I have considered reordering some of the inner and left joins, but was not
sure if that would have any affect.
Would you be able to advise on what else one might do to get the actual vs.
estimated more in line?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200610/1its difficult to get a better and more accurate plan after your actions
(updating stats etc...)
Specially with left joins...
the only way is to force the way you want to do your joins (manually
specify the hash/ merge option in the joins, setup the better order for your
joins etc...)
in this case you force SQL Server to use your hints instead of using its own
generated plan.
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:679360284efd2@.uwe...
>I have a stored procedure that contains a complex query of approximately 16
> joins, (a mixture of inner and left joins). These joins are mostly on
> standard user tables, but includes joins on two temporary tables and two
> non-
> indexable views.
> I have rewritten the query so as to reduce the reads from 5 million plus,
> to
> about 1.6 million reads using some dynamic sql and adding additional
> indexes.
> Still, in the execution plan, several of the user tables have estimated
> vs.
> actual row counts that are quite different, such as 1 Estimated row versus
> 135,000 actual rows. These tables are using, what I consider the
> appropriate
> index, but, as stated, the actual vs. estimated are way off.
> The procedure takes several arguments and I set those arguments as local
> variables in the procedure to prevent parameter sniffing.
> I was hoping to get a better plan by getting the actual versus estimated
> row
> counts closer. I have ran "update statistics <table_name> with fullscan"
> on
> all the major tables used in the query and especially on those tables
> whose
> actual vs. estimated are way off. This had no effect on the actual vs.
> estimated row counts. I have also run sp_updatestats, dbcc dbreindex on
> the
> tables, sp_recompile, but in each case, the estimated vs. actual is still
> way
> off.
> I have considered reordering some of the inner and left joins, but was not
> sure if that would have any affect.
> Would you be able to advise on what else one might do to get the actual
> vs.
> estimated more in line?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200610/1
>|||> The procedure takes several arguments and I set those arguments as local
> variables in the procedure to prevent parameter sniffing.
That way, the optimizer uses hard-coded values to determine selectivity. Something like:
= 10% (actually, it might go by density in the statistics, if available)
> 30%
BETWEEN 25%
The percentage for the values differs from release to release, and can change even between service
packs. So, in other words, a variable is *not* the same thing as the optimizer knowing the value you
are looking for.
For the optimizer to *know* the value and have a static plan, you have to rely on parameter
sniffing. This value in the plan might of course be off for the subsequent usages of the plan.
Or, you would have to get a new plan for each execution. Either break out the critical parts of the
procedure to its own procedures and create them WITH RECOMPILE, or in 2005 you can add RECOMPILE
hint at the query level. Or you can in 2005 even say OPTIMIZE FOR a certain value.
You can of course add WITH RECOMPILE for the whole procedure, but then none of the DML statements in
the procedure will be pre-optimized.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message news:679360284efd2@.uwe...
>I have a stored procedure that contains a complex query of approximately 16
> joins, (a mixture of inner and left joins). These joins are mostly on
> standard user tables, but includes joins on two temporary tables and two non-
> indexable views.
> I have rewritten the query so as to reduce the reads from 5 million plus, to
> about 1.6 million reads using some dynamic sql and adding additional indexes.
> Still, in the execution plan, several of the user tables have estimated vs.
> actual row counts that are quite different, such as 1 Estimated row versus
> 135,000 actual rows. These tables are using, what I consider the appropriate
> index, but, as stated, the actual vs. estimated are way off.
> The procedure takes several arguments and I set those arguments as local
> variables in the procedure to prevent parameter sniffing.
> I was hoping to get a better plan by getting the actual versus estimated row
> counts closer. I have ran "update statistics <table_name> with fullscan" on
> all the major tables used in the query and especially on those tables whose
> actual vs. estimated are way off. This had no effect on the actual vs.
> estimated row counts. I have also run sp_updatestats, dbcc dbreindex on the
> tables, sp_recompile, but in each case, the estimated vs. actual is still way
> off.
> I have considered reordering some of the inner and left joins, but was not
> sure if that would have any affect.
> Would you be able to advise on what else one might do to get the actual vs.
> estimated more in line?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200610/1
>|||Thanks for your reply Jeje.
In regards to your comment of setting up a better order on my joins, is the
guide to setting up joins...ordering joins with the most selective join
first, followed by the next most selective join, etc., etc.?
Jeje wrote:
>its difficult to get a better and more accurate plan after your actions
>(updating stats etc...)
>Specially with left joins...
>the only way is to force the way you want to do your joins (manually
>specify the hash/ merge option in the joins, setup the better order for your
>joins etc...)
>in this case you force SQL Server to use your hints instead of using its own
>generated plan.
>>I have a stored procedure that contains a complex query of approximately 16
>> joins, (a mixture of inner and left joins). These joins are mostly on
>[quoted text clipped - 36 lines]
>> vs.
>> estimated more in line?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200610/1