capture the message associated with the error

DoraM · January 18, 2008 1:32PM

Hi, My backup runs as a t-SQL job with the following syntax.

select @sql = '-SQL "BACKUP DATABASE ADMIN_COMPONENTS TO DISK = ''D:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\dbname_200710.sqb''
WITH COMPRESSION = 1, ERASEFILES = 3, VERIFY"'

EXEC MASTER..SQLBACKUP @SQL ,@exit_code OUTPUT,@sql_error OUTPUT

Is there away to get the error message associated with the exit code programmatically ?

petey · January 21, 2008 2:09AM

There isn't a function to return the error message given an error code. One suggestion is to store the output of the process to a temporary table, and store it to a permanent table if an error/warning is detected.

The key is to use the SINGLERESULTSET option, so that the output can be stored to a table directly. E.g.

DECLARE @sql nvarchar&#40;512&#41;
DECLARE @exit_code int
DECLARE @sql_error int

CREATE TABLE #sqbtemp &#40;output nvarchar&#40;256&#41;&#41;

SET @sql = N'-SQL "BACKUP DATABASE ADMIN_COMPONENTS TO DISK = ''D:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\dbname_200710.sqb'' 
WITH COMPRESSION = 1, ERASEFILES = 3, VERIFY, SINGLERESULTSET"' 

INSERT INTO #sqbtemp EXEC master..sqlbackup @sql, @exit_code OUTPUT, @sql_error OUTPUT

IF &#40;@exit_code &lt;&gt; 0&#41; OR &#40;@sql_error &lt;&gt; 0&#41;
	INSERT INTO sqbbackuperrors SELECT GETDATE&#40;&#41;, * FROM #sqbtemp
DROP TABLE #sqbtemp

DoraM · January 21, 2008 12:00PM

Server: Msg 213, Level 16, State 7, Procedure sqlbackup, Line 23
Insert Error: Column name or number of supplied values does not match table definition.

Thats the error I get when I try to run the command with singleresultset option.

thanks,
Dora.

petey · January 21, 2008 8:55PM

What is the result when you run just the backup e.g.

EXEC master..sqlbackup '-SQL "BACKUP DATABASE ADMIN_COMPONENTS TO DISK = ''D:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\dbname_200710.sqb"'

How many result sets were returned?

Thanks.

DoraM · January 22, 2008 10:25AM

The backup itself happens with no problem. It returns 2 result sets even when I specify SINGLERESULTSET option.

petey · January 22, 2008 7:56PM

When you run SQL Backup from Query Analyzer/Management Studio, what is the name of the column in the first result set?

Thanks.

capture the message associated with the error

Comments

Product Learning

Community Forums

Events & Friends

Simple Talk