Problem with DELETE in a proc 1

[joebickley]

Hi
I have a simple proc that deletes a row from a table.
CREATE PROCEDURE [dbo].[SPdeleteRework]
@reworkID int
AS
delete from tblreworkscompleted where reworkID = @reworkID
GO

But i get the following error:
DELETE failed because the following SET options have incorrect settings: 'ANSI_NULLS., QUOTED_IDENTIFIER'.[/red]

I have tried adding and removing them using SP_dboption but to no avail and im not really sure wha they should be set too. Any ideas anyone?

Thanks
Joe

 

[tlbroadbent]

Try this. Recompile the stored procedure with the options set on. Also add the Set statements to the procedure.

Set Ansi_nulls On
Set Quoted_identifier On
Go
CREATE PROCEDURE [dbo].[SPdeleteRework]
@reworkID int
AS
Set Ansi_nulls On
Set Quoted_identifier On

delete from tblreworkscompleted where reworkID = @reworkID
GO

Does the table have an indexed view or computed columns?

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[joebickley]

Yes it does have an indexed view on it, i wasnt aware this had an impact, does it?

Many thanks

 

[tlbroadbent]

Indexed views require certain options to be set on. Deleting from an underlying table affects the indexed view so those options must be on for the delete to work. SQL BOL contains details about the options required for indexed views.

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[joebickley]

Yep ur spot on as ever. Took the index off the view and it works fine.

Thanks

 

[joebickley]

Just to close:

I need index views as im using access and it has to have something to index on (rubish access tbh). I also need to issue deletes using a stored from from access using ODBC. Turns out ODBC does not use the ARITHABOUT option so it takes the servers defualt which is off. By changing the servers default to on this problem goes away.

Thanks terry for you help

 

[tlbroadbent]

Glad you found a fix.

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm