CODEWITHSUNDEEP
t-sqltry-catchexecute
Gunnar Sjúrðarson Knudsen
Gunnar Sjúrðarson Knudsen

Reputation: 441

T/SQL EXECUTE AS, if error 0x5, revert

I'm currently writing a special report in T/SQL.

Basicly, I've got a table containing windows usernames, and I have to run a stored procedure once for each person (Using a cursor). This can easily be done using the EXECUTE AS USER = 'domain\username'. However, if a user loses his windows login after the table is generated, i get the following error:

  Could not obtain information about Windows NT group/user 'domain\user', error code 0x5.

This error stops the procedure completely, which will give me an error. Is there a way to ignore this message and just skip to the next cursor value?

In advance, thanks Gunnar S. Knudsen

Upvotes: 0

Views: 735

Answers (1)

JamieSee
JamieSee

Reputation: 13020

If you are using SQL 2005 or higher, you can use TRY...CATCH (Transact-SQL). Here's the example from the provided link:

-- Verify that the stored procedure does not already exist.
IF OBJECT_ID ( 'usp_GetErrorInfo', 'P' ) IS NOT NULL 
    DROP PROCEDURE usp_GetErrorInfo;
GO

-- Create procedure to retrieve error information.
CREATE PROCEDURE usp_GetErrorInfo
AS
SELECT
    ERROR_NUMBER() AS ErrorNumber
    ,ERROR_SEVERITY() AS ErrorSeverity
    ,ERROR_STATE() AS ErrorState
    ,ERROR_PROCEDURE() AS ErrorProcedure
    ,ERROR_LINE() AS ErrorLine
    ,ERROR_MESSAGE() AS ErrorMessage;
GO

BEGIN TRY
    -- Generate divide-by-zero error.
    SELECT 1/0;
END TRY
BEGIN CATCH
    -- Execute error retrieval routine.
    EXECUTE usp_GetErrorInfo;
END CATCH;

Upvotes: 1

Related Questions