DELETE failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'
in [Using MS SQL Server]
|
Prev: SQL server connection
Next: Snap-in creation failed
From: ilo on 20 Sep 2006 04:17 When I want to delete a data from a table that this tabl has a trigger and this trigger reached another tables to delete the data in cursor I have this messeage: DELETE failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. My trigger : CREATE TRIGGER [TOPBASICIKISSILME] ON [dbo].[TBLDEPOBKTOPBASICIKIS] FOR DELETE AS BEGIN DECLARE @rows_affected int, @inc bigint , @dblid bigint ,@DEPOBKINC bigint SELECT @rows_affected = @@ROWCOUNT IF @rows_affected = 0 RETURN -- No rows changed, exit trigger BEGIN DECLARE Miktar CURSOR FOR SELECT deleted.DBLID,deleted.TOPBASICIKISINC , deleted.DEPOBKINC FROM deleted OPEN Miktar FETCH NEXT FROM Miktar INTO @dblid,@inc,@DEPOBKINC WHILE @@fetch_status = 0 BEGIN SET QUOTED_IDENTIFIER ON DELETE FROM TBLDEPOBKMIKTAR WHERE DEPOBKINC=(a)DEPOBKINC AND OWNERINC = @inc AND ISLEMID=2 AND HAREKETID=19 AND BIRIM=1 SET QUOTED_IDENTIFIER OFF PRINT @DEPOBKINC FETCH NEXT FROM Miktar INTO @dblid,@inc,@DEPOBKINC END CLOSE Miktar DEALLOCATE Miktar END END
From: Erland Sommarskog on 20 Sep 2006 06:48 ilo (ilyas.isik(a)gmail.com) writes: > When I want to delete a data from a table that this tabl has a trigger > and this trigger reached another tables to delete the data in cursor I > have this messeage: > > DELETE failed because the following SET options have incorrect > settings: 'QUOTED_IDENTIFIER'. Apparently the target table is part of indexed view. When you work with an indexed view, the following SET options must be on: ANSI_PADDING, ANSI_NULLS, QUOTED_IDENTIFIER, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL and ARITHABORT. Of these the last three depend on run-time values only. ANSI_PADDNING also depends on how the setting when the columns were created. And for ANSI_NULLS and QUOTED_IDENTIFIER the setting is saved when you create with the stored procedure/trigger. This can lead to problems when people insist on using Enterprise Manager to edit their SQL objects. Overall EM is a crappy tool for this aim. Use Query Analyzer which is far superior. Specifically, EM saves objects with ANSI_NULLS and QUOTED_IDENTIFIER OFF. A second possible culprit is OSQL which by default runs with QUOTED_IDENTIFIER off. But before you just save the trigger from Query Analyzer > DECLARE Miktar CURSOR FOR > SELECT deleted.DBLID,deleted.TOPBASICIKISINC , deleted.DEPOBKINC > FROM deleted > OPEN Miktar > FETCH NEXT FROM Miktar INTO @dblid,@inc,@DEPOBKINC > WHILE @@fetch_status = 0 > BEGIN > SET QUOTED_IDENTIFIER ON > DELETE FROM TBLDEPOBKMIKTAR WHERE DEPOBKINC=(a)DEPOBKINC > AND OWNERINC = @inc AND ISLEMID=2 AND HAREKETID=19 AND BIRIM=1 > SET QUOTED_IDENTIFIER OFF > PRINT @DEPOBKINC > > FETCH NEXT FROM Miktar INTO @dblid,@inc,@DEPOBKINC > > END > CLOSE Miktar > DEALLOCATE Miktar This code is completely unacceptable. Replace it with: DELETE TBLDEPOBKMIKTAR FROM deleted d JOIN TBLDEPOBKMIKTAR t ON T.DEPOBKINC = d.DEPOBKINC AND T.OWNERINC = d.TOPBASICIKISINC WHERE T.ISLEMID = 2 AND T.BIRIM = 1 The reason your trigger code is unacceptable is that it runs a cursor for something that can be done in a single statement. If many rows are deleted at once, there can be several magnitudes in difference in execution time. Cursors is something you should use only very exceptionally in SQL programming, and you should be even more restrictive with it in triggers. -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: ilo on 20 Sep 2006 11:28 its working i changed set options in cursor statement CREATE TRIGGER [TOPBASICIKISSILME] ON [dbo].[TBLDEPOBKTOPBASICIKIS] FOR DELETE AS BEGIN DECLARE @rows_affected int, @inc bigint , @dblid bigint ,@depobkinc bigint SELECT @rows_affected = @@ROWCOUNT IF @rows_affected = 0 RETURN -- No rows changed, exit trigger BEGIN DECLARE Miktar CURSOR FOR SELECT deleted.DBLID,deleted.TOPBASICIKISINC , deleted.DEPOBKINC FROM deleted OPEN Miktar FETCH NEXT FROM Miktar INTO @dblid,@inc,@depobkinc WHILE @@fetch_status = 0 BEGIN -- added new ---- SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON --- added new finish ------ DELETE FROM TBLDEPOBKMIKTAR WHERE DEPOBKINC=(a)depobkinc and OWNERINC = @inc AND ISLEMID=2 AND HAREKETID=19 AND BIRIM=1 PRINT @depobkinc PRINT @inc FETCH NEXT FROM Miktar INTO @dblid,@inc,@depobkinc END CLOSE Miktar DEALLOCATE Miktar END END Erland Sommarskog yazdi: > ilo (ilyas.isik(a)gmail.com) writes: > > When I want to delete a data from a table that this tabl has a trigger > > and this trigger reached another tables to delete the data in cursor I > > have this messeage: > > > > DELETE failed because the following SET options have incorrect > > settings: 'QUOTED_IDENTIFIER'. > > Apparently the target table is part of indexed view. When you work with > an indexed view, the following SET options must be on: ANSI_PADDING, > ANSI_NULLS, QUOTED_IDENTIFIER, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL > and ARITHABORT. Of these the last three depend on run-time values only. > ANSI_PADDNING also depends on how the setting when the columns were > created. And for ANSI_NULLS and QUOTED_IDENTIFIER the setting is saved > when you create with the stored procedure/trigger. > > This can lead to problems when people insist on using Enterprise Manager > to edit their SQL objects. Overall EM is a crappy tool for this aim. Use > Query Analyzer which is far superior. Specifically, EM saves objects > with ANSI_NULLS and QUOTED_IDENTIFIER OFF. A second possible culprit is > OSQL which by default runs with QUOTED_IDENTIFIER off. > > But before you just save the trigger from Query Analyzer > > > DECLARE Miktar CURSOR FOR > > SELECT deleted.DBLID,deleted.TOPBASICIKISINC , deleted.DEPOBKINC > > FROM deleted > > OPEN Miktar > > FETCH NEXT FROM Miktar INTO @dblid,@inc,@DEPOBKINC > > WHILE @@fetch_status = 0 > > BEGIN > > SET QUOTED_IDENTIFIER ON > > DELETE FROM TBLDEPOBKMIKTAR WHERE DEPOBKINC=(a)DEPOBKINC > > AND OWNERINC = @inc AND ISLEMID=2 AND HAREKETID=19 AND BIRIM=1 > > SET QUOTED_IDENTIFIER OFF > > PRINT @DEPOBKINC > > > > FETCH NEXT FROM Miktar INTO @dblid,@inc,@DEPOBKINC > > > > END > > CLOSE Miktar > > DEALLOCATE Miktar > > This code is completely unacceptable. Replace it with: > > DELETE TBLDEPOBKMIKTAR > FROM deleted d > JOIN TBLDEPOBKMIKTAR t ON T.DEPOBKINC = d.DEPOBKINC > AND T.OWNERINC = d.TOPBASICIKISINC > WHERE T.ISLEMID = 2 > AND T.BIRIM = 1 > > The reason your trigger code is unacceptable is that it runs a cursor > for something that can be done in a single statement. If many rows are > deleted at once, there can be several magnitudes in difference in > execution time. > > Cursors is something you should use only very exceptionally in SQL > programming, and you should be even more restrictive with it in triggers. > > > -- > Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se > > Books Online for SQL Server 2005 at > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx > Books Online for SQL Server 2000 at > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: Erland Sommarskog on 20 Sep 2006 12:53 ilo (ilyas.isik(a)gmail.com) writes: > its working > > i changed set options in cursor statement Maybe it's "working" but there are serious performance problems with the code. Those SET statements causes the trigger to be recompile twice during execution which is completely unnecessary. And the cursor can be a complete disaster for performance. I don't know why you are wrting triggers in the first place, but I someone - a client or an employer - pays you for it. Whatever, it is complete irresponsible to leave code like this, not the least when you have been told what the appropriate procedures are. Please remove that cursor and take out those SET statements, and ionsted save the trigger correctly. -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: ilo on 9 Oct 2006 07:26
i tried but its giving same error message Erland Sommarskog yazdi: > ilo (ilyas.isik(a)gmail.com) writes: > > its working > > > > i changed set options in cursor statement > > Maybe it's "working" but there are serious performance problems with the > code. Those SET statements causes the trigger to be recompile twice > during execution which is completely unnecessary. And the cursor can be > a complete disaster for performance. > > I don't know why you are wrting triggers in the first place, but I someone > - a client or an employer - pays you for it. Whatever, it is complete > irresponsible to leave code like this, not the least when you have been > told what the appropriate procedures are. > > Please remove that cursor and take out those SET statements, and ionsted > save the trigger correctly. > > -- > Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se > > Books Online for SQL Server 2005 at > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx > Books Online for SQL Server 2000 at > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx |