error handling with linked servers

Hi,
Could someone please tell me how to correctly manage errors with
linked servers?
I make an insert in a linked server. When a foreign key is violated
the whole batch is
aborted so I could not manage the error as I do on a standard server.
I mean:
locally this works fine and I always see the print:
insert into MYDB.dbo.MYTABLE values (100,99)
if @.@.error <> 0
print 'an error happened!!!'
Msg 547, Level 16, State 0, Line 2
The INSERT statement conflicted with the FOREIGN KEY constraint
"FK_XXX". The conflict occurred in database "MYDB", table
"dbo.MYTABLE", column '_Id'.
The statement has been terminated.
an error happened!!!
on the contrary with a linked server the following code doesn't show
any print:
insert into LINKEDSRV.MYDB.dbo.MYTABLE values (100,99)
if @.@.error <> 0
print 'an error happened!!!'
The statement has been terminated.
Msg 547, Level 16, State 1, Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint
"FK_XXX". The conflict occurred in database "MYDB", table
"dbo.MYTABLE", column '_Id'.
Thanks in advance for any kind suggestion,
StefanoThe batch is aborted with the linked server error due to the SET XACT_ABORT
ON setting, which is required here. If you are using SQL 2005, you can use
a TRY...CATCH block to trap the error in T-SQL. I believe the error must be
handled on the client side in SQL 2000.
--
Hope this helps.
Dan Guzman
SQL Server MVP
<stefano.figurelli@.gmail.com> wrote in message
news:36b3d389-0d49-428a-91fb-16814832b35d@.q39g2000hsf.googlegroups.com...
> Hi,
> Could someone please tell me how to correctly manage errors with
> linked servers?
> I make an insert in a linked server. When a foreign key is violated
> the whole batch is
> aborted so I could not manage the error as I do on a standard server.
> I mean:
>
> locally this works fine and I always see the print:
> insert into MYDB.dbo.MYTABLE values (100,99)
> if @.@.error <> 0
> print 'an error happened!!!'
> Msg 547, Level 16, State 0, Line 2
> The INSERT statement conflicted with the FOREIGN KEY constraint
> "FK_XXX". The conflict occurred in database "MYDB", table
> "dbo.MYTABLE", column '_Id'.
> The statement has been terminated.
> an error happened!!!
>
> on the contrary with a linked server the following code doesn't show
> any print:
> insert into LINKEDSRV.MYDB.dbo.MYTABLE values (100,99)
> if @.@.error <> 0
> print 'an error happened!!!'
>
> The statement has been terminated.
> Msg 547, Level 16, State 1, Line 1
> The INSERT statement conflicted with the FOREIGN KEY constraint
> "FK_XXX". The conflict occurred in database "MYDB", table
> "dbo.MYTABLE", column '_Id'.
>
> Thanks in advance for any kind suggestion,
> Stefano|||Hi Dan,
thank you for your replay.
Now I understand: this is the correct behaviour with SET XACT_ABORT ON
which is implicitly
used by most OLD DB Providers.
Locally it works because SET XACT_ABORT is OFF by default.
Unfortunately I have to deal with SQL 2000 not 2005.
I could not (I think) handle this easly on the application because
this happens inside stored
procedures and I do not want to change a lot of code.
Since I have some possibility on the linked side I will create some
procedures on the target
database and call the remotely. These procedure on the remote side
will be able to handle
the violation.
I this this could be ok... do you agre?
Thanks again,
Stefano|||> Since I have some possibility on the linked side I will create some
> procedures on the target
> database and call the remotely. These procedure on the remote side
> will be able to handle
> the violation.
> I this this could be ok... do you agre?
I have not done this myself but I think this approach might work. You'll
need to enable linked server rpc calls.
--
Hope this helps.
Dan Guzman
SQL Server MVP
<stefano.figurelli@.gmail.com> wrote in message
news:dfedf9ed-3d7d-41c4-a2b5-334cbb52cb47@.f47g2000hsd.googlegroups.com...
> Hi Dan,
> thank you for your replay.
> Now I understand: this is the correct behaviour with SET XACT_ABORT ON
> which is implicitly
> used by most OLD DB Providers.
> Locally it works because SET XACT_ABORT is OFF by default.
> Unfortunately I have to deal with SQL 2000 not 2005.
> I could not (I think) handle this easly on the application because
> this happens inside stored
> procedures and I do not want to change a lot of code.
> Since I have some possibility on the linked side I will create some
> procedures on the target
> database and call the remotely. These procedure on the remote side
> will be able to handle
> the violation.
> I this this could be ok... do you agre?
> Thanks again,
> Stefano|||ok, thanks!