capture the message associated with the error

• 
  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(512)
  DECLARE @exit_code int
  DECLARE @sql_error int
  
  CREATE TABLE #sqbtemp (output nvarchar(256))
  
  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 (@exit_code <> 0) OR (@sql_error <> 0)
  	INSERT INTO sqbbackuperrors SELECT GETDATE(), * FROM #sqbtemp
  DROP TABLE #sqbtemp
  

  petey January 21, 2008 02:09

  - 0 +
• 
  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.

  DoraM January 21, 2008 12:00

  - 0 +
• 
  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.

  petey January 21, 2008 20:55

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

  DoraM January 22, 2008 10:25

  - 0 +
• 
  When you run SQL Backup from Query Analyzer/Management Studio, what is the name of the column in the first result set?
  
  Thanks.

  petey January 22, 2008 19:56

  - 0 +

Add comment

Please sign in to leave a comment.