June 28, 2004 at 11:16 am
I created a sp to delete records in a few tables and it's been working great for 3 weeks now... But now I need it to delete in one more table. So built the query in QA and tested it, works great so I paste the query in the SP and save without any problem error. But now whenever I run the sp I get this error :
Serveur : Msg 1934, Niveau 16, État 1, Procédure DeleteADP, Ligne 8
DELETE a échoué car les options SET suivantes comportent des paramètres incorrects : 'ANSI_NULLS., QUOTED_IDENTIFIER'.
rough translation : Delete failed because the option set have incorrect parameters : 'ANSI_NULLS., QUOTED_IDENTIFIER'.
since I've played with these values to match the ms Best practice analyser's advice I might have screwed them up, so I reran an old db script that had my previous settings but it still doesn't fix the problem (they both are set to 'true').. I also tried detaching and reattaching the db (no clue if it could have helped or not... but it didn't either)
here's the sp script (yeah it's not optimized.. but it just needs to work for now) :
CREATE PROCEDURE [dbo].[DeleteADP] @PkADP as int
AS
SET NOCOUNT ON
if exists (Select * from dbo.ADPS where PkADP = @PkADP)
BEGIN
Delete from dbo.CommentairesADPS where FkADP = @PkADP
Delete from dbo.Codes where FkObjAccessEvent in (Select PkObjAccessEvent from dbo.ObjAccessEvents where FkObjAccess in (Select PkObjAccess from dbo.ObjAccess where FkObjADP in (Select PkObjADP from dbo.ObjADPS where FkADP = @PkADP)))
Delete from dbo.DependancesACC_SQL_Fields where dbo.DependancesACC_SQL_Fields.PkDependanceACC_SQL_Field in (Select dbo.DependancesACC_SQL_Fields.PkDependanceACC_SQL_Field FROM dbo.DependancesACC_SQL_Fields INNER JOIN dbo.DependancesADP_SQL_Fields ON dbo.DependancesACC_SQL_Fields.FkDependanceADP_SQL_Field = dbo.DependancesADP_SQL_Fields.PkDependanceADP_SQL_Field INNER JOIN dbo.DependancesADP_SQL ON dbo.DependancesADP_SQL_Fields.FkDependanceADP_SQL = dbo.DependancesADP_SQL.PkDependanceADP_SQL INNER JOIN dbo.ObjADPS ON dbo.DependancesADP_SQL.FkObjADP = dbo.ObjADPS.PkObjADP WHERE (dbo.ObjADPS.FkADP = @PkADP))
Delete from dbo.ObjAccessEvents where FkObjAccess in (Select PkObjAccess from dbo.ObjAccess where FkObjADP in (Select PkObjADP from dbo.ObjADPS where FkADP = @PkADP))
Delete from dbo.ObjAccess where FkObjADP in (Select PkObjADP from dbo.ObjADPS where FkADP = @PkADP)
--this part could be done in a single query but the execution plan shows a 10% gain in speed in this operation (2% overall with very few lines in the table.. only gonna increase)
--by avoiding the " FkParent = @PkADP or FkChild = @PkADP " in doing these 2 queries
--trace shows (Duration, CPU, READ, WRITE)
--32, 16, 81, 1 instead of 47, 47, 125, 0 with this model over the old one
Delete from dbo.RelationsADPS where FkParent in (Select PkObjADP from dbo.ObjADPS where FkADP = @PkADP)
Delete from dbo.RelationsADPS where FkChild in (Select PkObjADP from dbo.ObjADPS where FkADP = @PkADP)
Delete from dbo.DependancesADP_SQL_Fields where PkDependanceADP_SQL_Field in (SELECT dbo.DependancesADP_SQL_Fields.PkDependanceADP_SQL_Field FROM dbo.DependancesADP_SQL INNER JOIN dbo.ObjADPS ON dbo.DependancesADP_SQL.FkObjADP = dbo.ObjADPS.PkObjADP INNER JOIN dbo.DependancesADP_SQL_Fields ON dbo.DependancesADP_SQL.PkDependanceADP_SQL = dbo.DependancesADP_SQL_Fields.FkDependanceADP_SQL WHERE (dbo.ObjADPS.FkADP = @PkADP))
Delete from dbo.DependancesADP_SQL where FkObjADP in (Select PkObjADP from dbo.ObjADPS where FkADP = @PkADP)
Delete from dbo.ObjAdps where FkADP = @PkADP
Delete from dbo.ADPS where PkADP = @PkADP
END
SET NOCOUNT OFF
GO
also if I run the very same code from QA it works like a charm, the error occurs on the 2nd delete.
Anyone can shed some light on this issue?
June 28, 2004 at 4:28 pm
What happens if you add the following before your create statement?
SET QUOTED_IDENTIFIER OFF
GO
SET
ANSI_NULLS OFF
GO
-- Put creation script here...
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
June 29, 2004 at 6:18 am
I tried dropping the sp and recreating it like this but it still gives me the same error message.
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].[DeleteADP] @PkADP as int
AS
SET NOCOUNT ON
if exists (Select * from dbo.ADPS where PkADP = @PkADP)
BEGIN
Delete from dbo.CommentairesADPS where FkADP = @PkADP
Delete from dbo.Codes where FkObjAccessEvent in (Select PkObjAccessEvent from dbo.ObjAccessEvents where FkObjAccess in (Select PkObjAccess from dbo.ObjAccess where FkObjADP in (Select PkObjADP from dbo.ObjADPS where FkADP = @PkADP)))
Delete from dbo.DependancesACC_SQL_Fields where dbo.DependancesACC_SQL_Fields.PkDependanceACC_SQL_Field in (Select dbo.DependancesACC_SQL_Fields.PkDependanceACC_SQL_Field FROM dbo.DependancesACC_SQL_Fields INNER JOIN dbo.DependancesADP_SQL_Fields ON dbo.DependancesACC_SQL_Fields.FkDependanceADP_SQL_Field = dbo.DependancesADP_SQL_Fields.PkDependanceADP_SQL_Field INNER JOIN dbo.DependancesADP_SQL ON dbo.DependancesADP_SQL_Fields.FkDependanceADP_SQL = dbo.DependancesADP_SQL.PkDependanceADP_SQL INNER JOIN dbo.ObjADPS ON dbo.DependancesADP_SQL.FkObjADP = dbo.ObjADPS.PkObjADP WHERE (dbo.ObjADPS.FkADP = @PkADP))
Delete from dbo.ObjAccessEvents where FkObjAccess in (Select PkObjAccess from dbo.ObjAccess where FkObjADP in (Select PkObjADP from dbo.ObjADPS where FkADP = @PkADP))
Delete from dbo.ObjAccess where FkObjADP in (Select PkObjADP from dbo.ObjADPS where FkADP = @PkADP)
--this part could be done in a single query but the execution plan shows a 10% gain in speed in this operation (2% overall with very few lines in the table.. only gonna increase)
--by avoiding the " FkParent = @PkADP or FkChild = @PkADP " in doing these 2 queries
--trace shows (Duration, CPU, READ, WRITE)
--32, 16, 81, 1 instead of 47, 47, 125, 0 with this model over the old one
Delete from dbo.RelationsADPS where FkParent in (Select PkObjADP from dbo.ObjADPS where FkADP = @PkADP)
Delete from dbo.RelationsADPS where FkChild in (Select PkObjADP from dbo.ObjADPS where FkADP = @PkADP)
Delete from dbo.DependancesADP_SQL_Fields where PkDependanceADP_SQL_Field in (SELECT dbo.DependancesADP_SQL_Fields.PkDependanceADP_SQL_Field FROM dbo.DependancesADP_SQL INNER JOIN dbo.ObjADPS ON dbo.DependancesADP_SQL.FkObjADP = dbo.ObjADPS.PkObjADP INNER JOIN dbo.DependancesADP_SQL_Fields ON dbo.DependancesADP_SQL.PkDependanceADP_SQL = dbo.DependancesADP_SQL_Fields.FkDependanceADP_SQL WHERE (dbo.ObjADPS.FkADP = @PkADP))
Delete from dbo.DependancesADP_SQL where FkObjADP in (Select PkObjADP from dbo.ObjADPS where FkADP = @PkADP)
Delete from dbo.ObjAdps where FkADP = @PkADP
Delete from dbo.ADPS where PkADP = @PkADP
END
SET NOCOUNT OFF
GO
June 29, 2004 at 7:32 am
Problem solved... I dropped the table and recreated it and it works like a charm again. Thanx for all your input.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply