How to capture output messages

  • i am running the below query and want to capture the validity message:

    restore verifyonly from disk='D:\adventureworks.bak'

    The backup set on file 1 is valid.

    Can anyone let me know how to capture the message in a table

  • AFAIK, you can't capture the stuff on the information table in a table. That is an out of band message sent to the client, and I don't know of a way to consume that in T-SQL.

  • Just as a wild thought - you could use something like this:

    BEGIN TRY

    EXECUTE ('restore verifyonly from disk=''C:orthwind.bak''')

    END TRY

    BEGIN CATCH

    SELECT

    ERROR_NUMBER()AS ErrorNum,

    ERROR_SEVERITY() AS Severity,

    ERROR_STATE() AS State,

    ERROR_PROCEDURE() AS ErrorProcedure,

    ERROR_LINE() AS ErrorLine,

    ERROR_MESSAGE() AS ErrorMessage

    INTO #OOPS

    END CATCH;

    SELECT * FROM #OOPS

    --DROP TABLE #OOPS

    It will capture errors in the temporary table #OOPS. But that could be changed to an insert into a permanent table. And in similiar fashion insert some data into that same permanent table in the TRY BLOCK

    As I said a wild thought to stir your mind to eventually doing what you desire to do.

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • It doesn't work for me..temp table #OOPS is not getting created.

  • sunder.bugatha (1/31/2011)


    It doesn't work for me..temp table #OOPS is not getting created.

    The original code I posted, if it completed successfully would not have created the temp table #OOPS. Now I have expanded the code to use a permanent table, and to write to that table upon either success or failure.

    CREATE TABLE Restor(ErrorNum INT,Severit INT,E_State INT,PRO VARCHAR(50),ErrorLine INT, Mess VARCHAR(100),

    [Asof] [datetime] NULL CONSTRAINT [DF_Restor_Asof] DEFAULT (getdate()))

    BEGIN TRY

    EXECUTE ('restore verifyonly from disk=''C:orthwind.bak''')

    INSERT INTO Restor(Mess,AsOf)

    VALUES ('Successful',GETDATE())

    END TRY

    BEGIN CATCH

    INSERT INTO Restor

    SELECT

    ERROR_NUMBER()AS ErrorNum,

    ERROR_SEVERITY() AS Severity,

    ERROR_STATE() AS State,

    ERROR_PROCEDURE() AS ErrorProcedure,

    ERROR_LINE() AS ErrorLine,

    ERROR_MESSAGE() AS ErrorMessage,

    GETDATE()

    END CATCH;

    SELECT * FROM Restor

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • Ther above code will NOT return the Message that is printed to the Message Tab. The minute you use try - Catch you lose that.

    I too am looking for a way to get the message that is actually returned as it provides much more info.

    Following TSQL:

    RESTORE VERIFYONLY FROM DISK = 'F:\Temp\SkDataDemo-20140929203m7.bak'

    If I deliberately use a bad file name for example I get the following to the screen:

    Msg 3201, Level 16, State 2, Line 3

    Cannot open backup device 'F:\Temp\SkDataDemo-20140929203m7.bak'. Operating system error 2(The system cannot find the file specified.).

    Msg 3013, Level 16, State 1, Line 3

    VERIFY DATABASE is terminating abnormally.

    If I wrapper the above line in a TRY-CATCH all I get back is:

    VERIFY DATABASE is terminating abnormally.

    I would REALLY like to capture the full message.

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

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