Catching level 16 Errors in sp_ExecuteSQL
-
Hello
I am trying to catch an 'untrappable' error in a script, which I know I can do when calling an SP, but not using sp_ExecuteSQL.
What I am trying to do is essentially this:
- start to loop through a list of remote machines (connected on a nation wide WAN),
- set up a linked server to each one using the 'Microsoft.Jet.OLEDB.4.0' OLE/DB Provider,
- run an update on one of the tables in the access db
- Drop server
- go to next server or
- End loop
The issue is I sometimes get the following error (even on a simple select):
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: Disk or network error.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: ].
The test code I am running is:
DECLARE @sqlString NVARCHAR(100),
@rc INT,
@error_status INT
SET @sqlString = 'SELECT * FROM ExistingLinkedServer...[Product]'
EXEC @rc = master.dbo.sp_executesql @sqlString
SET @error_status = @@ERROR
IF @rc <> 0
OR @error_status <> 0
BEGIN
PRINT 'It failed!'
SELECT @error_status error
SELECT @rc rc
RETURN
END
PRINT 'It Worked!'
I never see 'It failed!' or 'It Worked!'
If I create a Test sp to genrate a level 16 error
CREATE PROCEDURE usp_test
AS
SELECT 1
FROM qwerty --non existing table
RETURN 0
GO
And then run the following
DECLARE @rc INT,
@error_status INT
EXEC @rc = usp_test
SET @error_status = @@ERROR
IF @rc <> 0
OR @error_status <> 0
BEGIN
PRINT 'It failed!'
SELECT @error_status AS errorstatus
SELECT @rc AS returncode
RETURN
END
PRINT 'It Worked!'
I get
Server: Msg 208, Level 16, State 1, Procedure usp_Test, Line 3
Invalid object name 'qwerty'.
It failed!
errorstatus
------------
208
(1 row(s) affected)
ReturnCode
-----------
NULL
(1 row(s) affected)
Anybody know how I can trap an error in a call to sp_ExecuteSQL?
Dave J
-
I think this may be a scope issue. When you execute sp_ExecuteSQL you start a new SPID; hence the called procedure cannot see the calling procedure.
You may need to put your error coding within the @sqlString and pass the error back into a table from the calling procedure, then call the records from that table..
I wasn't born stupid - I had to study.
-
That's a good call, I wasn't aware sp_ExecuteSQL ran in a separte session. I'll have a go and report back how I get on.
Dave J
-
Ken you are right, the @rc gets populated if the linked server does not exist, but if it does exist, and you get a failure, you still get nothing. Try:
DECLARE @rc INT
--the table does not exist, else this is valid
EXEC @rc = sp_executesql N'select * from RealServer.DBName.dbo.junkTable'
PRINT @rc
PRINT CASE @rc WHEN 0 THEN 'It Worked!' ELSE 'It failed!' END
All you get is (edited to hide real names)
Server: Msg 7314, Level 16, State 1, Line 1
OLE DB provider 'RealServer' does not contain table '"DBName"."dbo"."junkTable"'. The table either does not exist or the current user does not have permissions on that table.
OLE DB error trace [Non-interface error: OLE DB provider does not contain the table: ProviderName='RealServer', TableName='"DBName"."dbo"."junkTable"'].
Not had chance to look today
, may do so tomorrow.Dave
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply