Problem in Instead of Delete Trigger

Hi,
I have Instead of Delete Trigger in my Table. When i delete any row
from the table it shows the No of Rows affected ex:- 1 Row affected
But when i look into the table the Deleted Row is still Present.
My Question is If there is Instead of Delete Trigger then is it not
possible to delete row from Original table. and if i want to delete the
row using Instead of trigger what i have to do?
If anybody knows the solution Please let me know?
Thanks,
Vinoth
It really depends on what are you doing in the instead of delete trigger. Do
you actually delete the row in the trigger? Please post sample code from the
trigger if you need more help (desired result and sample data/table would be
helpful...)
MC
<vinoth@.gsdindia.com> wrote in message
news:1132308519.761801.276040@.g49g2000cwa.googlegr oups.com...
> Hi,
> I have Instead of Delete Trigger in my Table. When i delete any row
> from the table it shows the No of Rows affected ex:- 1 Row affected
> But when i look into the table the Deleted Row is still Present.
> My Question is If there is Instead of Delete Trigger then is it not
> possible to delete row from Original table. and if i want to delete the
> row using Instead of trigger what i have to do?
> If anybody knows the solution Please let me know?
>
> Thanks,
> Vinoth
>
|||Hi,
if I understand you correct you're deleteing a record and your trigger is
firing reporting the number of rows affected ? So far so good. If this
was
a normal "After" trigger the record would be deleted in the table. An
instead of trigger is different. The delete actually take place in the
table. You have to do that by yourself.
So in your trigger code you could do something like this :
Delete from table where id=(Select id from deleted)
But why are you using an instead of trigger and not an after trigger ?
Normally you use instead of triggers when you want to handle the dml
action
yourself for instance in partitioned views.
Regards
Bobby Henningsen
"MC" <marko_culo#@.#yahoo#.#com#> wrote in message
news:eHrFsqC7FHA.476@.TK2MSFTNGP15.phx.gbl...
> It really depends on what are you doing in the instead of delete
trigger.
> Do you actually delete the row in the trigger? Please post sample code
> from the trigger if you need more help (desired result and sample
> data/table would be helpful...)
>
> MC
>
> <vinoth@.gsdindia.com> wrote in message
> news:1132308519.761801.276040@.g49g2000cwa.googlegr oups.com...
>
Jeg beskyttes af den gratis SPAMfighter til privatbrugere.
Den har indtil videre sparet mig for at f? 711 spam-mails.
Betalende brugere f?r ikke denne besked i deres e-mails.
Hent gratis SPAMfighter her: www.spamfighter.dk

Problem in Instead of Delete Trigger

Hi,
I have Instead of Delete Trigger in my Table. When i delete any row
from the table it shows the No of Rows affected ex:- 1 Row affected
But when i look into the table the Deleted Row is still Present.
My Question is If there is Instead of Delete Trigger then is it not
possible to delete row from Original table. and if i want to delete the
row using Instead of trigger what i have to do?
If anybody knows the solution Please let me know?
Thanks,
VinothIt really depends on what are you doing in the instead of delete trigger. Do
you actually delete the row in the trigger? Please post sample code from the
trigger if you need more help (desired result and sample data/table would be
helpful...)
MC
<vinoth@.gsdindia.com> wrote in message
news:1132308519.761801.276040@.g49g2000cwa.googlegroups.com...
> Hi,
> I have Instead of Delete Trigger in my Table. When i delete any row
> from the table it shows the No of Rows affected ex:- 1 Row affected
> But when i look into the table the Deleted Row is still Present.
> My Question is If there is Instead of Delete Trigger then is it not
> possible to delete row from Original table. and if i want to delete the
> row using Instead of trigger what i have to do?
> If anybody knows the solution Please let me know?
>
> Thanks,
> Vinoth
>|||Hi,
if I understand you correct you're deleteing a record and your trigger is
firing reporting the number of rows affected ' So far so good. If this
was
a normal "After" trigger the record would be deleted in the table. An
instead of trigger is different. The delete actually take place in the
table. You have to do that by yourself.
So in your trigger code you could do something like this :
Delete from table where id=(Select id from deleted)
But why are you using an instead of trigger and not an after trigger ?
Normally you use instead of triggers when you want to handle the dml
action
yourself for instance in partitioned views.
Regards :)
Bobby Henningsen
"MC" <marko_culo#@.#yahoo#.#com#> wrote in message
news:eHrFsqC7FHA.476@.TK2MSFTNGP15.phx.gbl...
> It really depends on what are you doing in the instead of delete
trigger.
> Do you actually delete the row in the trigger? Please post sample code
> from the trigger if you need more help (desired result and sample
> data/table would be helpful...)
>
> MC
>
> <vinoth@.gsdindia.com> wrote in message
> news:1132308519.761801.276040@.g49g2000cwa.googlegroups.com...
>> Hi,
>> I have Instead of Delete Trigger in my Table. When i delete any row
>> from the table it shows the No of Rows affected ex:- 1 Row affected
>> But when i look into the table the Deleted Row is still Present.
>> My Question is If there is Instead of Delete Trigger then is it not
>> possible to delete row from Original table. and if i want to delete the
>> row using Instead of trigger what i have to do?
>> If anybody knows the solution Please let me know?
>>
>> Thanks,
>> Vinoth
>
---
Jeg beskyttes af den gratis SPAMfighter til privatbrugere.
Den har indtil videre sparet mig for at få 711 spam-mails.
Betalende brugere får ikke denne besked i deres e-mails.
Hent gratis SPAMfighter her: www.spamfighter.dksql

Problem in Instead of Delete Trigger

Hi,
I have Instead of Delete Trigger in my Table. When i delete any row
from the table it shows the No of Rows affected ex:- 1 Row affected
But when i look into the table the Deleted Row is still Present.
My Question is If there is Instead of Delete Trigger then is it not
possible to delete row from Original table. and if i want to delete the
row using Instead of trigger what i have to do?
If anybody knows the solution Please let me know?
Thanks,
VinothHi ,
You cannot delete rows from a table which has a instead of delete trigger
configured.
Though, We can put delete statement for the table inside instead of trigger
but we need to keep RECURSIVE_TRIGGERS database option set correctly. This D
B
option will not cause trigger to fire again.
But still I am not sure why do you want to delete row from a trigger which
has instead of delete trigger configured.
--
Vishal Khajuria
9886170165
IBM Bangalore
"vinoth@.gsdindia.com" wrote:

> Hi,
> I have Instead of Delete Trigger in my Table. When i delete any row
> from the table it shows the No of Rows affected ex:- 1 Row affected
> But when i look into the table the Deleted Row is still Present.
> My Question is If there is Instead of Delete Trigger then is it not
> possible to delete row from Original table. and if i want to delete the
> row using Instead of trigger what i have to do?
> If anybody knows the solution Please let me know?
>
> Thanks,
> Vinoth
>

Problem in Instead of Delete Trigger

Hi,
I have Instead of Delete Trigger in my Table. When i delete any row
from the table it shows the No of Rows affected ex:- 1 Row affected
But when i look into the table the Deleted Row is still Present.
My Question is If there is Instead of Delete Trigger then is it not
possible to delete row from Original table. and if i want to delete the
row using Instead of trigger what i have to do?
If anybody knows the solution Please let me know?
Thanks,
VinothIt really depends on what are you doing in the instead of delete trigger. Do
you actually delete the row in the trigger? Please post sample code from the
trigger if you need more help (desired result and sample data/table would be
helpful...)
MC
<vinoth@.gsdindia.com> wrote in message
news:1132308519.761801.276040@.g49g2000cwa.googlegroups.com...
> Hi,
> I have Instead of Delete Trigger in my Table. When i delete any row
> from the table it shows the No of Rows affected ex:- 1 Row affected
> But when i look into the table the Deleted Row is still Present.
> My Question is If there is Instead of Delete Trigger then is it not
> possible to delete row from Original table. and if i want to delete the
> row using Instead of trigger what i have to do?
> If anybody knows the solution Please let me know?
>
> Thanks,
> Vinoth
>|||Hi,
if I understand you correct you're deleteing a record and your trigger is
firing reporting the number of rows affected ' So far so good. If this
was
a normal "After" trigger the record would be deleted in the table. An
instead of trigger is different. The delete actually take place in the
table. You have to do that by yourself.
So in your trigger code you could do something like this :
Delete from table where id=(Select id from deleted)
But why are you using an instead of trigger and not an after trigger ?
Normally you use instead of triggers when you want to handle the dml
action
yourself for instance in partitioned views.
Regards
Bobby Henningsen
"MC" <marko_culo#@.#yahoo#.#com#> wrote in message
news:eHrFsqC7FHA.476@.TK2MSFTNGP15.phx.gbl...
> It really depends on what are you doing in the instead of delete
trigger.
> Do you actually delete the row in the trigger? Please post sample code
> from the trigger if you need more help (desired result and sample
> data/table would be helpful...)
>
> MC
>
> <vinoth@.gsdindia.com> wrote in message
> news:1132308519.761801.276040@.g49g2000cwa.googlegroups.com...
>
---
Jeg beskyttes af den gratis SPAMfighter til privatbrugere.
Den har indtil videre sparet mig for at f? 711 spam-mails.
Betalende brugere f?r ikke denne besked i deres e-mails.
Hent gratis SPAMfighter her: www.spamfighter.dk

Problem in deleting rows in table

hi there!

I have encounter the problem when trying to delete the rows..with the error message

http://i5.photobucket.com/albums/y151/kangalert/untitled.jpg

http://i5.photobucket.com/albums/y151/kangalert/1231234.jpg

with contain no primary key, and no dependency

What task/component are you using to do the deletion?

Please provide as much information as you can, in a concise manner, when posting issues.

-Jamie

|||

i just click on the row/rows i want to delete in SQL Server..

But it wont let me update and delete the row!

|||

Is this at all related to SSIS?

If not then you are in the wrong forum. Try the Tools or T-SQL forum.

-Jamie

sql

Problem Deleting Rows

I have a table that was imported from an Excel 2003 worksheet. It has no
primary key. If I select one or more rows and try to delete them I get the
error:
Key column information is insufficient or incorrect. Too many rows were
affected by the update.
If I use QA I can delete the rows?
What is it trying to tell me here?
WaynePlease post the full DDL for your table, as well as the DELETE statement.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Wayne Wengert" <wayneSKIPSPAM@.wengert.org> wrote in message
news:e6dSRMHPGHA.3984@.TK2MSFTNGP14.phx.gbl...
I have a table that was imported from an Excel 2003 worksheet. It has no
primary key. If I select one or more rows and try to delete them I get the
error:
Key column information is insufficient or incorrect. Too many rows were
affected by the update.
If I use QA I can delete the rows?
What is it trying to tell me here?
Wayne|||> What is it trying to tell me here?
Two things. First, EM is not a good editing tool. Second, a table without
a primary key is, by definition, not a table.|||Interesting. Since I am importing from Excel I'll need to come up with some
autonumber key I guess?
Wayne
"Scott Morris" <bogus@.bogus.com> wrote in message
news:e2uUW4HPGHA.3732@.TK2MSFTNGP10.phx.gbl...
> Two things. First, EM is not a good editing tool. Second, a table
> without a primary key is, by definition, not a table.
>|||You can add an identity column to the target table and add a primary key
constraint on it.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Wayne Wengert" <wayneSKIPSPAM@.wengert.org> wrote in message
news:ORjDEQIPGHA.812@.TK2MSFTNGP10.phx.gbl...
Interesting. Since I am importing from Excel I'll need to come up with some
autonumber key I guess?
Wayne
"Scott Morris" <bogus@.bogus.com> wrote in message
news:e2uUW4HPGHA.3732@.TK2MSFTNGP10.phx.gbl...
> Two things. First, EM is not a good editing tool. Second, a table
> without a primary key is, by definition, not a table.
>

Problem Deleting Publications

I am running SQL Server 2000 with SP3. I receive the following message when trying to delete my publications:

SQL Server Enterprise Manager could not retrieve information about publication 'Named Publication'.

Error 2812: Could not find stored procedure ".

Also, for some reason the system is still trying to replicate even though it claims there is no distributor available... Any ideas would be greatly appreciated.

Thanks,

absoluttUse Profiler to check for command what EM is trying to execute and you will know what is going on.

problem deleting file

Hi all,
I need to delete files using UNC path of the file in xp_cmdshell
procedure.
The @.FileUNCPath variable contains the UNC path of the file and it has
correct value.
The SQL server service and Sql server agent service are running under
CSQL5 windows account. CSQL5 has full access to the files I am trying to
delete. But when I try to delete the file using the following statements,
@.Result is always 1 and the file does not get deleted.
So please help. Thanks in advance.
SET @.ShellCommand = 'delete "'+@.FileUNCPath+'"'
EXEC @.Result = master..xp_cmdshell @.ShellCommandYour OS use "delete" or "del", check the correct command to be used.
AMB
"sql" wrote:

> Hi all,
> I need to delete files using UNC path of the file in xp_cmdshell
> procedure.
> The @.FileUNCPath variable contains the UNC path of the file and it has
> correct value.
> The SQL server service and Sql server agent service are running under
> CSQL5 windows account. CSQL5 has full access to the files I am trying to
> delete. But when I try to delete the file using the following statements,
> @.Result is always 1 and the file does not get deleted.
> So please help. Thanks in advance.
> SET @.ShellCommand = 'delete "'+@.FileUNCPath+'"'
> EXEC @.Result = master..xp_cmdshell @.ShellCommand
>
>|||Try these examples to see if you get any more info on what the command is
actually doing:
CREATE TABLE #Errors (Results VARCHAR(1000))
INSERT INTO #Errors (FName)
exec @.Return = master..xp_cmdshell @.Cmd
DECLARE @.cmd sysname, @.var sysname
SET @.var = 'Hello world'
SET @.cmd = 'echo ' + @.var + ' > var_out.txt'
EXEC master..xp_cmdshell @.cmd
Andrew J. Kelly SQL MVP
"sql" <donotspam@.nospaml.com> wrote in message
news:eKsGUWsOFHA.3072@.TK2MSFTNGP09.phx.gbl...
> Hi all,
> I need to delete files using UNC path of the file in xp_cmdshell
> procedure.
> The @.FileUNCPath variable contains the UNC path of the file and it has
> correct value.
> The SQL server service and Sql server agent service are running under
> CSQL5 windows account. CSQL5 has full access to the files I am trying to
> delete. But when I try to delete the file using the following statements,
> @.Result is always 1 and the file does not get deleted.
> So please help. Thanks in advance.
> SET @.ShellCommand = 'delete "'+@.FileUNCPath+'"'
> EXEC @.Result = master..xp_cmdshell @.ShellCommand
>

problem deleting database using enterprise manager

I am trying to delete a database using Enterprise Manager. It seems like
everything works fine, the db is gone from the Databases tree, but when I
try to create a new database with the same name as the one I just deleted, I
get an error stating that the database exists. Also if I try to delete a
login associated to the deleted database, I get the error that it cannot be
deleted because it owns objects in the deleted db. I really don't understand
what's going on here. Could anybody help me out?Richard
Have you REFRESH the EM after droping db?
"Richard Gjerde" <richard_gjerde@.yahoo.no> wrote in message
news:fdKNd.7739$IW4.168987@.news2.e.nsc.no...
> I am trying to delete a database using Enterprise Manager. It seems like
> everything works fine, the db is gone from the Databases tree, but when I
> try to create a new database with the same name as the one I just deleted,
I
> get an error stating that the database exists. Also if I try to delete a
> login associated to the deleted database, I get the error that it cannot
be
> deleted because it owns objects in the deleted db. I really don't
understand
> what's going on here. Could anybody help me out?
>|||Well, I just did:-) And the db came up as "suspect". When I dropped it again
it was OK.
So the problem sort of disappeared, but I am still unsure about why it
ocurred in the first place.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23OH5euRDFHA.512@.TK2MSFTNGP15.phx.gbl...
> Richard
> Have you REFRESH the EM after droping db?
>
> "Richard Gjerde" <richard_gjerde@.yahoo.no> wrote in message
> news:fdKNd.7739$IW4.168987@.news2.e.nsc.no...
I[vbcol=seagreen]
deleted,[vbcol=seagreen]
> I
> be
> understand
>|||Richard
I prefer using DROP Database by QA not by EM.
"Richard Gjerde" <richard_gjerde@.yahoo.no> wrote in message
news:sEKNd.7744$IW4.168621@.news2.e.nsc.no...
> Well, I just did:-) And the db came up as "suspect". When I dropped it
again
> it was OK.
> So the problem sort of disappeared, but I am still unsure about why it
> ocurred in the first place.
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23OH5euRDFHA.512@.TK2MSFTNGP15.phx.gbl...
like[vbcol=seagreen]
when[vbcol=seagreen]
> I
> deleted,
a[vbcol=seagreen]
cannot[vbcol=seagreen]
>

problem deleting database using enterprise manager

I am trying to delete a database using Enterprise Manager. It seems like
everything works fine, the db is gone from the Databases tree, but when I
try to create a new database with the same name as the one I just deleted, I
get an error stating that the database exists. Also if I try to delete a
login associated to the deleted database, I get the error that it cannot be
deleted because it owns objects in the deleted db. I really don't understand
what's going on here. Could anybody help me out?
Richard
Have you REFRESH the EM after droping db?
"Richard Gjerde" <richard_gjerde@.yahoo.no> wrote in message
news:fdKNd.7739$IW4.168987@.news2.e.nsc.no...
> I am trying to delete a database using Enterprise Manager. It seems like
> everything works fine, the db is gone from the Databases tree, but when I
> try to create a new database with the same name as the one I just deleted,
I
> get an error stating that the database exists. Also if I try to delete a
> login associated to the deleted database, I get the error that it cannot
be
> deleted because it owns objects in the deleted db. I really don't
understand
> what's going on here. Could anybody help me out?
>
|||Well, I just did:-) And the db came up as "suspect". When I dropped it again
it was OK.
So the problem sort of disappeared, but I am still unsure about why it
ocurred in the first place.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23OH5euRDFHA.512@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> Richard
> Have you REFRESH the EM after droping db?
>
> "Richard Gjerde" <richard_gjerde@.yahoo.no> wrote in message
> news:fdKNd.7739$IW4.168987@.news2.e.nsc.no...
I[vbcol=seagreen]
deleted,
> I
> be
> understand
>
|||Richard
I prefer using DROP Database by QA not by EM.
"Richard Gjerde" <richard_gjerde@.yahoo.no> wrote in message
news:sEKNd.7744$IW4.168621@.news2.e.nsc.no...
> Well, I just did:-) And the db came up as "suspect". When I dropped it
again[vbcol=seagreen]
> it was OK.
> So the problem sort of disappeared, but I am still unsure about why it
> ocurred in the first place.
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23OH5euRDFHA.512@.TK2MSFTNGP15.phx.gbl...
like[vbcol=seagreen]
when[vbcol=seagreen]
> I
> deleted,
a[vbcol=seagreen]
cannot
>

problem deleting database using enterprise manager

I am trying to delete a database using Enterprise Manager. It seems like
everything works fine, the db is gone from the Databases tree, but when I
try to create a new database with the same name as the one I just deleted, I
get an error stating that the database exists. Also if I try to delete a
login associated to the deleted database, I get the error that it cannot be
deleted because it owns objects in the deleted db. I really don't understand
what's going on here. Could anybody help me out?Richard
Have you REFRESH the EM after droping db?
"Richard Gjerde" <richard_gjerde@.yahoo.no> wrote in message
news:fdKNd.7739$IW4.168987@.news2.e.nsc.no...
> I am trying to delete a database using Enterprise Manager. It seems like
> everything works fine, the db is gone from the Databases tree, but when I
> try to create a new database with the same name as the one I just deleted,
I
> get an error stating that the database exists. Also if I try to delete a
> login associated to the deleted database, I get the error that it cannot
be
> deleted because it owns objects in the deleted db. I really don't
understand
> what's going on here. Could anybody help me out?
>|||Well, I just did:-) And the db came up as "suspect". When I dropped it again
it was OK.
So the problem sort of disappeared, but I am still unsure about why it
ocurred in the first place.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23OH5euRDFHA.512@.TK2MSFTNGP15.phx.gbl...
> Richard
> Have you REFRESH the EM after droping db?
>
> "Richard Gjerde" <richard_gjerde@.yahoo.no> wrote in message
> news:fdKNd.7739$IW4.168987@.news2.e.nsc.no...
> > I am trying to delete a database using Enterprise Manager. It seems like
> > everything works fine, the db is gone from the Databases tree, but when
I
> > try to create a new database with the same name as the one I just
deleted,
> I
> > get an error stating that the database exists. Also if I try to delete a
> > login associated to the deleted database, I get the error that it cannot
> be
> > deleted because it owns objects in the deleted db. I really don't
> understand
> > what's going on here. Could anybody help me out?
> >
> >
>|||Richard
I prefer using DROP Database by QA not by EM.
"Richard Gjerde" <richard_gjerde@.yahoo.no> wrote in message
news:sEKNd.7744$IW4.168621@.news2.e.nsc.no...
> Well, I just did:-) And the db came up as "suspect". When I dropped it
again
> it was OK.
> So the problem sort of disappeared, but I am still unsure about why it
> ocurred in the first place.
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23OH5euRDFHA.512@.TK2MSFTNGP15.phx.gbl...
> > Richard
> > Have you REFRESH the EM after droping db?
> >
> >
> > "Richard Gjerde" <richard_gjerde@.yahoo.no> wrote in message
> > news:fdKNd.7739$IW4.168987@.news2.e.nsc.no...
> > > I am trying to delete a database using Enterprise Manager. It seems
like
> > > everything works fine, the db is gone from the Databases tree, but
when
> I
> > > try to create a new database with the same name as the one I just
> deleted,
> > I
> > > get an error stating that the database exists. Also if I try to delete
a
> > > login associated to the deleted database, I get the error that it
cannot
> > be
> > > deleted because it owns objects in the deleted db. I really don't
> > understand
> > > what's going on here. Could anybody help me out?
> > >
> > >
> >
> >
>

Problem deleting

I have a sick subscriber in a transactional replication.
I want to delete it but I don't succeed.
When I try from Tools\Replication\Create and manage publications Entreprise Manager crush.
I try with
exec sp_dropsubscription @.publication = N'PublicationName', @.article = N'all', @.subscriber = N'SubscriberName', @.destination_db = N'DatabaseName' but execution take a long, long time ..without any end.
I don't want to delete all replication.
Any ideeas?
10x a lot!Whats the size of Subscriber?
Check for any other errors from SQL error log.|||In the end I deleted all replication and recreated it. Now it's working.

Problem Deleteing Records with Indexed Computed Column

Got the following error when trying to delete records from a table that
contained and Indexed Computed column.
"System.Data.SqlClient.SqlError: DELETE failed because the following SET
options have incorrect settings: 'ANSI_NULLS., QUOTED_IDENTIFIER, ARITHABORT'.
This is similar to the problem in KB Article 816780
The issue the KB article is referring to was with some shipping code. The
issue you're seeing is because you need to set the SET options correctly
before issuing the delete. From BOL 'SET' topic:
When creating and manipulating indexes on computed columns or indexed views,
the SET options ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER,
ANSI_NULLS, ANSI_PADDING, and ANSI_WARNINGS must be set to ON. The option
NUMERIC_ROUNDABORT must be set to OFF.
Hope this helps.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Lee" <Lee@.discussions.microsoft.com> wrote in message
news:ED599EDB-3612-485E-8839-1B282A43C186@.microsoft.com...
> Got the following error when trying to delete records from a table that
> contained and Indexed Computed column.
> "System.Data.SqlClient.SqlError: DELETE failed because the following SET
> options have incorrect settings: 'ANSI_NULLS., QUOTED_IDENTIFIER,
ARITHABORT'.
> This is similar to the problem in KB Article 816780
|||Ok, then explain why when the index is removed the problem goes away?
|||As you don't include the message you're replying to I can't tell whether
you're replying to my reply. Here's what I previously posted that will
explain why the problem goes away if you remove an index over a computed
column:
<begin>
The issue the KB article is referring to was with some shipping code. The
issue you're seeing is because you need to set the SET options correctly
before issuing the delete. From BOL 'SET' topic:
When creating and manipulating indexes on computed columns or indexed views,
the SET options ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER,
ANSI_NULLS, ANSI_PADDING, and ANSI_WARNINGS must be set to ON. The option
NUMERIC_ROUNDABORT must be set to OFF.
Hope this helps.
<end>
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Lee" <Lee@.discussions.microsoft.com> wrote in message
news:74948FCE-9171-4F10-B596-0A366AC6805C@.microsoft.com...
> Ok, then explain why when the index is removed the problem goes away?
>
|||"Lee" <Lee@.discussions.microsoft.com> wrote in message
news:74948FCE-9171-4F10-B596-0A366AC6805C@.microsoft.com...
> Ok, then explain why when the index is removed the problem goes away?
>
An index on a computed column has all the same restrictions as an indexed
view.
Think of it this way. All sessions connecting to your database should have
these options:
ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER,
ANSI_NULLS, ANSI_PADDING, and ANSI_WARNINGS must be set to ON. The option
NUMERIC_ROUNDABORT must be set to OFF.
If they don't then some features of the database will be unavailable, and
they may not be able to change data.
David
|||The restriction for the ANSI-COMPLIANT SET OPTIONS is only when creating
Indexes on Computed Columns and Views. If you never create these indexes,
then clients are not REQUIRED to connect using the set options; however, it
is recommended that clients ALWAYS connect with these options set and then
modify individual statements or batches as required regardless if the
extended functionality is used.
Sincerely,
Anthony Thomas

"Lee" <Lee@.discussions.microsoft.com> wrote in message
news:74948FCE-9171-4F10-B596-0A366AC6805C@.microsoft.com...
Ok, then explain why when the index is removed the problem goes away?

Problem Deleteing Records with Indexed Computed Column

Got the following error when trying to delete records from a table that
contained and Indexed Computed column.
"System.Data.SqlClient.SqlError: DELETE failed because the following SET
options have incorrect settings: 'ANSI_NULLS., QUOTED_IDENTIFIER, ARITHABORT
'.
This is similar to the problem in KB Article 816780The issue the KB article is referring to was with some shipping code. The
issue you're seeing is because you need to set the SET options correctly
before issuing the delete. From BOL 'SET' topic:
When creating and manipulating indexes on computed columns or indexed views,
the SET options ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER,
ANSI_NULLS, ANSI_PADDING, and ANSI_WARNINGS must be set to ON. The option
NUMERIC_ROUNDABORT must be set to OFF.
Hope this helps.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Lee" <Lee@.discussions.microsoft.com> wrote in message
news:ED599EDB-3612-485E-8839-1B282A43C186@.microsoft.com...
> Got the following error when trying to delete records from a table that
> contained and Indexed Computed column.
> "System.Data.SqlClient.SqlError: DELETE failed because the following SET
> options have incorrect settings: 'ANSI_NULLS., QUOTED_IDENTIFIER,
ARITHABORT'.
> This is similar to the problem in KB Article 816780|||Ok, then explain why when the index is removed the problem goes away?|||As you don't include the message you're replying to I can't tell whether
you're replying to my reply. Here's what I previously posted that will
explain why the problem goes away if you remove an index over a computed
column:
<begin>
The issue the KB article is referring to was with some shipping code. The
issue you're seeing is because you need to set the SET options correctly
before issuing the delete. From BOL 'SET' topic:
When creating and manipulating indexes on computed columns or indexed views,
the SET options ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER,
ANSI_NULLS, ANSI_PADDING, and ANSI_WARNINGS must be set to ON. The option
NUMERIC_ROUNDABORT must be set to OFF.
Hope this helps.
<end>
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Lee" <Lee@.discussions.microsoft.com> wrote in message
news:74948FCE-9171-4F10-B596-0A366AC6805C@.microsoft.com...
> Ok, then explain why when the index is removed the problem goes away?
>|||"Lee" <Lee@.discussions.microsoft.com> wrote in message
news:74948FCE-9171-4F10-B596-0A366AC6805C@.microsoft.com...
> Ok, then explain why when the index is removed the problem goes away?
>
An index on a computed column has all the same restrictions as an indexed
view.
Think of it this way. All sessions connecting to your database should have
these options:
ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER,
ANSI_NULLS, ANSI_PADDING, and ANSI_WARNINGS must be set to ON. The option
NUMERIC_ROUNDABORT must be set to OFF.
If they don't then some features of the database will be unavailable, and
they may not be able to change data.
David|||The restriction for the ANSI-COMPLIANT SET OPTIONS is only when creating
Indexes on Computed Columns and Views. If you never create these indexes,
then clients are not REQUIRED to connect using the set options; however, it
is recommended that clients ALWAYS connect with these options set and then
modify individual statements or batches as required regardless if the
extended functionality is used.
Sincerely,
Anthony Thomas
"Lee" <Lee@.discussions.microsoft.com> wrote in message
news:74948FCE-9171-4F10-B596-0A366AC6805C@.microsoft.com...
Ok, then explain why when the index is removed the problem goes away?

Problem Deleteing Records with Indexed Computed Column

Got the following error when trying to delete records from a table that
contained and Indexed Computed column.
"System.Data.SqlClient.SqlError: DELETE failed because the following SET
options have incorrect settings: 'ANSI_NULLS., QUOTED_IDENTIFIER, ARITHABORT'.
This is similar to the problem in KB Article 816780The issue the KB article is referring to was with some shipping code. The
issue you're seeing is because you need to set the SET options correctly
before issuing the delete. From BOL 'SET' topic:
When creating and manipulating indexes on computed columns or indexed views,
the SET options ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER,
ANSI_NULLS, ANSI_PADDING, and ANSI_WARNINGS must be set to ON. The option
NUMERIC_ROUNDABORT must be set to OFF.
Hope this helps.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Lee" <Lee@.discussions.microsoft.com> wrote in message
news:ED599EDB-3612-485E-8839-1B282A43C186@.microsoft.com...
> Got the following error when trying to delete records from a table that
> contained and Indexed Computed column.
> "System.Data.SqlClient.SqlError: DELETE failed because the following SET
> options have incorrect settings: 'ANSI_NULLS., QUOTED_IDENTIFIER,
ARITHABORT'.
> This is similar to the problem in KB Article 816780|||Ok, then explain why when the index is removed the problem goes away?|||As you don't include the message you're replying to I can't tell whether
you're replying to my reply. Here's what I previously posted that will
explain why the problem goes away if you remove an index over a computed
column:
<begin>
The issue the KB article is referring to was with some shipping code. The
issue you're seeing is because you need to set the SET options correctly
before issuing the delete. From BOL 'SET' topic:
When creating and manipulating indexes on computed columns or indexed views,
the SET options ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER,
ANSI_NULLS, ANSI_PADDING, and ANSI_WARNINGS must be set to ON. The option
NUMERIC_ROUNDABORT must be set to OFF.
Hope this helps.
<end>
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Lee" <Lee@.discussions.microsoft.com> wrote in message
news:74948FCE-9171-4F10-B596-0A366AC6805C@.microsoft.com...
> Ok, then explain why when the index is removed the problem goes away?
>|||"Lee" <Lee@.discussions.microsoft.com> wrote in message
news:74948FCE-9171-4F10-B596-0A366AC6805C@.microsoft.com...
> Ok, then explain why when the index is removed the problem goes away?
>
An index on a computed column has all the same restrictions as an indexed
view.
Think of it this way. All sessions connecting to your database should have
these options:
ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER,
ANSI_NULLS, ANSI_PADDING, and ANSI_WARNINGS must be set to ON. The option
NUMERIC_ROUNDABORT must be set to OFF.
If they don't then some features of the database will be unavailable, and
they may not be able to change data.
David|||The restriction for the ANSI-COMPLIANT SET OPTIONS is only when creating
Indexes on Computed Columns and Views. If you never create these indexes,
then clients are not REQUIRED to connect using the set options; however, it
is recommended that clients ALWAYS connect with these options set and then
modify individual statements or batches as required regardless if the
extended functionality is used.
Sincerely,
Anthony Thomas
"Lee" <Lee@.discussions.microsoft.com> wrote in message
news:74948FCE-9171-4F10-B596-0A366AC6805C@.microsoft.com...
Ok, then explain why when the index is removed the problem goes away?