473,703 Members | 3,355 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

DELETE failed because the following SET options have incorrect settings: 'QUOTED_IDENTIF IER'

ilo
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_IDENTIF IER'.

My trigger :
CREATE TRIGGER [TOPBASICIKISSIL ME] ON [dbo].[TBLDEPOBKTOPBAS ICIKIS]
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,d eleted.TOPBASIC IKISINC , deleted.DEPOBKI NC
FROM deleted
OPEN Miktar
FETCH NEXT FROM Miktar INTO @dblid,@inc,@DE POBKINC
WHILE @@fetch_status = 0
BEGIN
SET QUOTED_IDENTIFI ER ON
DELETE FROM TBLDEPOBKMIKTAR WHERE DEPOBKINC=@DEPO BKINC
AND OWNERINC = @inc AND ISLEMID=2 AND HAREKETID=19 AND BIRIM=1
SET QUOTED_IDENTIFI ER OFF
PRINT @DEPOBKINC

FETCH NEXT FROM Miktar INTO @dblid,@inc,@DE POBKINC

END
CLOSE Miktar
DEALLOCATE Miktar

END

END

Sep 20 '06 #1
6 24995
ilo (il********@gma il.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_IDENTIF IER'.
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_IDENTIFI ER, ANSI_WARNINGS, CONCAT_NULL_YIE LDS_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_IDENTIFI ER 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_IDENTIFI ER OFF. A second possible culprit is
OSQL which by default runs with QUOTED_IDENTIFI ER off.

But before you just save the trigger from Query Analyzer
DECLARE Miktar CURSOR FOR
SELECT deleted.DBLID,d eleted.TOPBASIC IKISINC , deleted.DEPOBKI NC
FROM deleted
OPEN Miktar
FETCH NEXT FROM Miktar INTO @dblid,@inc,@DE POBKINC
WHILE @@fetch_status = 0
BEGIN
SET QUOTED_IDENTIFI ER ON
DELETE FROM TBLDEPOBKMIKTAR WHERE DEPOBKINC=@DEPO BKINC
AND OWNERINC = @inc AND ISLEMID=2 AND HAREKETID=19 AND BIRIM=1
SET QUOTED_IDENTIFI ER OFF
PRINT @DEPOBKINC

FETCH NEXT FROM Miktar INTO @dblid,@inc,@DE POBKINC

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.TOPBASICIKISI NC
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, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Sep 20 '06 #2
ilo
its working

i changed set options in cursor statement

CREATE TRIGGER [TOPBASICIKISSIL ME] ON [dbo].[TBLDEPOBKTOPBAS ICIKIS]
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,d eleted.TOPBASIC IKISINC , deleted.DEPOBKI NC
FROM deleted
OPEN Miktar
FETCH NEXT FROM Miktar INTO @dblid,@inc,@de pobkinc
WHILE @@fetch_status = 0
BEGIN
-- added new ----
SET QUOTED_IDENTIFI ER OFF
SET ANSI_NULLS ON
--- added new finish ------

DELETE FROM TBLDEPOBKMIKTAR WHERE DEPOBKINC=@depo bkinc and
OWNERINC = @inc AND ISLEMID=2 AND HAREKETID=19 AND BIRIM=1
PRINT @depobkinc
PRINT @inc
FETCH NEXT FROM Miktar INTO @dblid,@inc,@de pobkinc

END
CLOSE Miktar
DEALLOCATE Miktar

END

END

Erland Sommarskog yazdi:
ilo (il********@gma il.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_IDENTIF IER'.

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_IDENTIFI ER, ANSI_WARNINGS, CONCAT_NULL_YIE LDS_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_IDENTIFI ER 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_IDENTIFI ER OFF. A second possible culprit is
OSQL which by default runs with QUOTED_IDENTIFI ER off.

But before you just save the trigger from Query Analyzer
DECLARE Miktar CURSOR FOR
SELECT deleted.DBLID,d eleted.TOPBASIC IKISINC , deleted.DEPOBKI NC
FROM deleted
OPEN Miktar
FETCH NEXT FROM Miktar INTO @dblid,@inc,@DE POBKINC
WHILE @@fetch_status = 0
BEGIN
SET QUOTED_IDENTIFI ER ON
DELETE FROM TBLDEPOBKMIKTAR WHERE DEPOBKINC=@DEPO BKINC
AND OWNERINC = @inc AND ISLEMID=2 AND HAREKETID=19 AND BIRIM=1
SET QUOTED_IDENTIFI ER OFF
PRINT @DEPOBKINC

FETCH NEXT FROM Miktar INTO @dblid,@inc,@DE POBKINC

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.TOPBASICIKISI NC
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, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Sep 20 '06 #3
ilo (il********@gma il.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, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Sep 20 '06 #4
Apparently the target table is part of indexed view.

or it has an index on a computed column - same restrictions.

-----------------------
Alex Kuznetsov
http://sqlserver-tips.blogspot.com/
http://sqlserver-puzzles.blogspot.com/

Sep 20 '06 #5
ilo
i tried but its giving same error message

Erland Sommarskog yazdi:
ilo (il********@gma il.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, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Oct 9 '06 #6
ilo
ok i did it your way

first i dropped trigger and

i set on queryidentifier and ansi null on and then i created trigger
your way then it works

thanks for you advice

Erland Sommarskog yazdi:
ilo (il********@gma il.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, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Oct 11 '06 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
5292
by: Florian | last post by:
I need to set multiple values for some SQL statements, for example SET NUMERIC_ROUNDABORT OFF GO SET ANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_YIELDS_NULL,ARITHABORT,QUOTED_IDENTIF IER,ANSI_NULLS ON GO in a .sql file, but would like to reset them to their previous settings
1
6600
by: Steve Foster | last post by:
I have tried many variations (after reviewing other posts) and can not resolve the following issue: RUNNING SQL MAINTENANCE ---------------------------- SET ARITHABORT ON SET CONCAT_NULL_YIELDS_NULL ON SET QUOTED_IDENTIFIER ON SET ANSI_NULLS ON SET ANSI_PADDING ON
7
7288
by: Bercin Ates via SQLMonster.com | last post by:
I?m getting an error when I execute a stored procedure which is try to insert a row to a table. The error is: Server: Msg 1934, Level 16, State 1, Procedure SRV_SP_IS_EMRI_SATIRI_EKLE, Line 32 INSERT failed because the following SET options have incorrect settings: 'ANSI_NULLS.'. In my sp, I insert an row to a table. But also I created a view which is select some fields from this table. (Note: Some fields are calculated fields in this...
2
288
by: Tooraj | last post by:
I'm trying to build a ROLAP cube, which must be available for real-time updates. During processing of the cube in Analysis manager I get a 'Failed to create index'. I am using SQL Server 2000 Enterprise edition.
4
7029
by: teddysnips | last post by:
I am trying to insert a row into a table using a stored procedure and I get the following error if I try this from QA: INSERT failed because the following SET options have incorrect settings: 'ANSI_NULLS., QUOTED_IDENTIFIER'. If I try to run this from Microsoft Access, I get a slightly different error: INSERT failed because the following SET options have incorrect
3
6164
by: teddysnips | last post by:
In the script below is the DDL to create some tables and a UDF. What I'm interested in is the UDF at the end. Specifically, these few lines: --CLOSE OTRate --DEALLOCATE OTRate ELSE -- @NumRecords <= 0 If I uncommment CLOSE and DEALLOCATE and check the syntax I get a
3
13365
by: JerryW | last post by:
I uninstalled/reinstalled .NET 2003 with no errors. When I try to create a Visual C# ASP.NET Web Application I get the error: "The Web server reported the following error when attempting to create or open the Web project located at the following URL: 'http://localhost/MyWebTest'. 'HTTP/1.1 500 Internal Server Error'. The Event log has: "Failed to execute request because the App-Domain could not be
7
3904
by: Simon Jefferies | last post by:
Hello, I'm trying to create a new ASP. NET Web Application project, when I enter a name and press OK i get the following: Web Access Failed. The default web access mode for this project is set to file share, but the project folder at "http://..." cannot be opened with the path "C:\inetpub\...." The error returned was:
4
2600
by: Wannabe | last post by:
I am using ASP.Net 2.0 and have a gridview on my page. I have everything working except the delete command. The page reloads except the row I am trying to delete is still there. I believe it is something really easy, but I cannot see it. The stored procedue works when run in QA. Can someone tell me what I am doing wrong/missing that is keeping the delete command from working in the gridview? Thank you. I am trying to delete a row out of...
0
8749
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8662
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9243
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9109
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
8956
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7853
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6585
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5922
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
2
2434
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.