RAISERROR and Remote Stored Procedure
-
Hi,
I have a stored procedure on serverA called SP1. In a try catch block in SP1, it executes a stored procedure called SP2 on serverB. SP2 runs some stuff in a Try Catch block. On failure, I want the catch block in SP1 on ServerA to handle the error from SP2 on ServerB and contine the stored procedure.
Below is an extract of the stored procedures I'm running.
SP1
DECLARE @Link1 VARCHAR(128),
@Link2 VARCHAR(128),
@SQL VARCHAR(1024),
@errmsg NVARCHAR(2048),
@errsev TINYINT,
@errstate TINYINT,
@errno INT,
@errproc SYSNAME,
@errline INT
SET @Link1 = 'ServerA'
SET @Link2 = 'ServerB'
BEGIN TRY
IF EXISTS(SELECT name FROM SYS.SERVERS WHERE IS_LINKED = 1 AND name = @Link2)
BEGIN
SET @SQL = 'EXEC(''dbo.CheckKb ' + '''''' + @Link1 + ''''''') AT ' + QUOTENAME(@Link2)
EXEC (@SQL)
END
ELSE
IF EXISTS(SELECT name FROM SYS.SERVERS WHERE IS_LINKED = 1 AND name = @Link1)
BEGIN
SET @SQL = 'EXEC(''dbo.CheckKb ' + '''''' + @Link2 + ''''''') AT ' + QUOTENAME(@Link1)
EXEC (@SQL)
END
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION --any open transactions are rolled back in the event of a failure
--SET @errBit = 1
SELECT --get all the error information from the backups.
@errmsg = ERROR_MESSAGE(),
@errno = ERROR_NUMBER(),
@errsev = ERROR_SEVERITY(),
@errstate = ERROR_STATE(),
@errproc = ERROR_PROCEDURE(),
@errline = ERROR_LINE()
RAISERROR('The stored procedure error is: %s', 0, 1, @errmsg) WITH NOWAIT, LOG
END CATCH
CREATE PROCEDURE [dbo].[CheckKb]
@LinkedServerName sysname
with recompile
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
declare @srvr sysname
declare @retval int
declare @sql nvarchar(2000)
set @srvr = @LinkedServerName;
BEGIN TRY
EXEC @retval = sys.sp_testlinkedserver @srvr;
END TRY
BEGIN CATCH
WAITFOR DELAY '000:00:15'
BEGIN TRY
EXEC @retval = sys.sp_testlinkedserver @srvr;
END TRY
BEGIN CATCH
SET @retval = sign(@@error);
IF @retval <> 0
BEGIN
RAISERROR('There have been 2 failed attempts trying to check kb', 25, 1) WITH NOWAIT, LOG
RETURN
END
END CATCH
BEGIN
SET @retval = + 10;
RAISERROR('There was 1 failed attempt trying to check kb', 8, 1) with nowait
EXEC (@SQL)
END
END CATCH
IF @retval = 0
BEGIN
RAISERROR ('Successfully tested kb', 0, 1) with nowait
EXEC (@SQL)
END
END
I thought that if I set the error code above 20 using RAISERROR in SP2 and set XACT_ABORT ON in SP1, my catch block in SP1 would pick up the error thrown by SP2 and handle it accordingly. Howerver, if I run it 10 times on the trot, I get one of two different errors. I can't figure out why I'm not getting consistent error handling:
https://www.sqlservercentral.com/Forums/Uploads/image-unavailable.png
https://www.sqlservercentral.com/Forums/Uploads/image-unavailable.png
I want the error to come back consistenly like error2 in the link
I'm running SQL Server 2005 SP3 Enterprise Edition on Windows 2003 R2
Many thanks,
Andrew
-
anyone?
-
This will work, you just have to change the severity levels. When I tried 25, I got an error about the error only being specified by sysadmin roles. When I used 8 or 0, it never went into the Catch block because these are warnings and not errors. When I used 16, then it went into the catch block the way I expected it to. I setup a dummy proc on the linked server and then called it from the first database. Play around with the severity levels and you'll see what I mean. Here's what I ran:
Linked server:
Create PROCEDURE spv_testAS
BEGIN
declare @e nvarchar(200) = 'Test Error Message'
RaisError(@e, 16, 1)
END
and then ran this:
declare @e nvarchar(200)begin try
exec ('DatabaseName.dbo.spv_Test') at LinkedServer
set @e = 'No Error'
end try
begin catch
select @e = ERROR_MESSAGE()
end catch
Select @e
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/ -
Hi,
Thanks for the response.
I found your solution works, howerver, it's not quite how I'm trying to use it. In my procedure, if the stored procedure I run remotely fails, I need to try again. So, instead of your first block of code, it would be something more like:
create proc spv_test
as
begin
declare @e nvarchar(200)
declare @f nvarchar(200)
set @e = 'Test Error Message'
set @f = 'Another Test Error Message'
BEGIN TRY
SELECT 1/0
END TRY
BEGIN CATCH
BEGIN TRY
SELECT 1/0
END TRY
BEGIN CATCH
RAISERROR(@f, 16, 1)
END CATCH
RAISERROR(@e, 16, 1)
END CATCH
END
Doing it this way, I still get an error that's not caught by the local stored proc.
I had some feedback from a post on MSDN forums that suggested that TRY/CATCH and linked servers is buggy - with link to connect article (https://connect.microsoft.com/SQLServer/feedback/details/331635/try-catch-not-work-when-process-include-linked-server)
I've temporarily re-written it so that in SP2, on error, it drops an error flag in a local table, which is then queried by SP1 after SP2 completes. What the error flag says determines how SP1 then behaves.
Interested to see though if you can get above to work - bearing in mind I'm using SQL 2005 SP3.
Many thanks,
Andrew
-
I did get yours to work. I put a return after the first RaisError
alter proc spv_testas
begin
declare @e nvarchar(200)
declare @f nvarchar(200)
set @e = 'Test Error Message'
set @f = 'Another Test Error Message'
BEGIN TRY
SELECT 1/0
END TRY
BEGIN CATCH
BEGIN TRY
SELECT 1/0
END TRY
BEGIN CATCH
RAISERROR(@f, 16, 1)
return
END CATCH
RAISERROR(@e, 16, 1)
END CATCH
END
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply