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.
Perhaps this works for you? Note that the -I parameter is not required in version 4. The logged-in instance is the one used.
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.