Error Reading results Running sqlbackup dynamically

BrianM

We have been using v3 and running dynamically and using the return values to return sucess eg

CREATE TABLE #Result(Line NVARCHAR(1024))
SELECT @cSql = '''-SQL "BACKUP DATABASE TO DISK = ''''' + @cFile + ''''' WITH INIT, COMPRESSION = 2" -E -I ' + @instance + '''' --BM 9/11/2005
print 'Backup command: master.dbo.sqlbackup ' + @cSql
INSERT #Result(Line) EXEC('master.dbo.sqlbackup ' + @cSql)
SELECT * FROM #Result
IF EXISTS (SELECT 1 FROM #Result WHERE Line LIKE '%successfully processed%')
BEGIN
SELECT @Result = 0

However v4 seems to ahve changed and now returns two result sets which causes an error in our code. "Insert Error: Column name or number of supplied values does not match table definition."

I can get the results back using
declare @exitCode int @errorcode int
exec master.dbo.sqlbackup '-SQL "BACKUP DATABASE [DBA] TO DISK = ''d:\MSSQL\BACKUP\Full_DBA_20060517.bak'' WITH INIT, COMPRESSION = 2" -E -I' , @exitCode output, @errorcode output
select @exitCode ,@errorcode

but when I try to wrap this in an exec statment I cannot get the results to pass through.


declare @exitCode int, @errorcode int, @myvar nvarchar(1024)
declare @db varchar(10), @cFile varchar(100), @instance nvarchar(128), @cSql nvarchar(1024)
select @instance = COALESCE((convert(varchar(100), SERVERPROPERTY ('InstanceName'))), '' )
set @db = 'DBA'
set @cFile = 'd:\MSSQL\BACKUP\Full_DBA_20060517.bak'
--SELECT @cSql = '''-SQL "BACKUP DATABASE TO DISK = ''''' + @cFile + ''''' WITH INIT, COMPRESSION = 2" -E -I ' + @instance + '''' + ', @exitCode output, @errorcode output'
SELECT @cSql = 'declare @exitCode int, @errorcode int exec master.dbo.sqlbackup ''-SQL "BACKUP DATABASE TO DISK = ''''' + @cFile + ''''' WITH INIT, COMPRESSION = 2" -E -I ''' + @instance + ' , @exitCode output, @errorcode output'
print 'Backup command: ' + @cSql
exec sp_executesql @cSql --@exitCode output, @errorcode output
--EXEC('declare @exitCode int, @errorcode int, @myvar nvarchar(1024) Exec @myvar = master.dbo.sqlbackup ' + @cSql + ' select @exitCode ,@errorcode')
select @exitCode ,@errorcode

As you can see I have tried both EXEC and sp_executesql

Is there a way to get teh extended stored proc to return only one value or a way to handle multiple results and pass them through?? We are unable to upgrade until this is resolved.

petey

Perhaps this works for you? Note that the -I parameter is not required in version 4. The logged-in instance is the one used.

DECLARE @exitCode int, @errorcode int, @myvar nvarchar(1024)
DECLARE @db varchar(10), @cFile varchar(100), @instance nvarchar(128), @cSql nvarchar(1024)

SET @db = 'DBA'
SET @cFile = 'd:\MSSQL\BACKUP\Full_DBA_20060517.bak'
SELECT @cSql = '-SQL "BACKUP DATABASE TO DISK = ''' + @cFile + ''' WITH INIT, COMPRESSION = 2" -E '''

PRINT 'Backup command: ' + @cSql
EXEC master..sqlbackup @cSql, @exitCode output, @errorcode output
SELECT @exitCode ,@errorcode

BrianM

Thank you

It was a case of not seeing the forsest for the trees. We were trying to use execute immediate EXEC() because we were inserting the results into a table, but now we are using the output parameters there is no need to so we don't need to use execute immediate.

sometime I think you just need to take a step back.

Thanks for the help