error Handling for OLE/DB errors in sql server 2000

Question

Post reply

error Handling for OLE/DB errors in sql server 2000

subodhk

SSC-Addicted

Points: 401
More actions
March 27, 2008 at 10:04 am

#183910

HI,
Pardon me if its the wrong place to post I could find this as the best place to post. As I need a quick answer to it.
I have a stored procedure in which I do a minimal error handling by usage of @@Error.
But now when i get [OLE\DB] error the transaction gets roll backed and dosent go to my error handling block.
I have used XACT_ABORT to On.
Can any body answer my question of how to handle [OLE\DB] errors in sql srver 2000 stored procedure.
Its very urgent.
Thanks in advance..

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Matija Lah SSCertifiable Points: 6429 More actions · Answer 1

You should read these two comprehensive articles on error handling in SQL Server by Erland Sommarskog:

http://www.sommarskog.se/error-handling-I.html

http://www.sommarskog.se/error-handling-II.html

ML

---
Matija Lah, SQL Server MVP
http://milambda.blogspot.com

subodhk SSC-Addicted Points: 401 More actions · Answer 2

I have already read the article but could not find if there is any solution for OLE/DB errors as the stored procedure simply aborts the whole execution without going to the error handling section.

Gerhard Schmeusser SSChasing Mays Points: 641 More actions · Answer 3

I don't understand the connection of OLE/DB errors with your stored procedure. The stored proc is unaware of OLE/DB. Are you looking for how to handle OLE/DB errors in your client code?

subodhk SSC-Addicted Points: 401 More actions · Answer 4

For example i run these statements on the query analyzer

Declare @ret as int

Declare @sql as nvarchar(4000)

Set @sql = 'Insert into mass.corpsafeacct.dbo.saf values(1)'

Exec sp_Executesql @sql

Select @ret = @@ERROR

Print @ret

When I execute this stament I get an error on my client side

saying

OLE DB error trace [Non-interface error: OLE DB provider does not contain the table: ProviderName='mass', TableName='"corpsafeacct"."dbo"."saf"'].

Msg 7314, Level 16, State 1, Line 1

OLE DB provider 'mass' does not contain table '"corpsafeacct"."dbo"."saf"'. The table either does not exist or the current user does not have permissions on that table.

and doesnot print @ret

I have deliberately put the erroneous sql statement to test if it goes till the print statement but it dosent reach there. It fails.

ther table saf doesnot exist and "mass" which is mentioned in the query is a linked server.

Hope I am clear with my doubt.

Steve T SSCommitted Points: 1994 More actions · Answer 5

I believe this has to do with the link server. It's the link server driver that is returning the error and then disconnecting the connection. You can test this by removing the link server, it will process the error locally without a problem.

How about using a try/catch block to do what you want? I know this isn't exactly what you wanted, but it may achieve what you needed.

begin try

declare @ret as int

declare @sql as nvarchar(4000)

set @sql = 'insert into SSF3010.son_db.dbo.timekeep'

exec sp_executesql @sql

end try

begin catch

set @ret = @@error

print @@error

end catch