Error Code 870 Parameters?

essamughal

Hi;


I am trying to parameterise all the values in the command for sqlbackup stored procedure.

DECLARE @exitcode int
DECLARE @sqlerrorcode int
Declare @L_RESTORE_DATABASE_COMMAND nvarchar(4000)
Declare @L_DATABASENAME varchar(100)
Declare @L_DISK_PATH varchar(500)
Declare @L_MOVETO_PATH varchar(500)
Declare @L_STANDBY_UNDO_PATH varchar(500)
Declare @L_DATAFILE_NAME varchar(100)
Declare @L_MOVE_DATA_PATH varchar(500)
Declare @L_LOGFILE_NAME varchar(100)
Declare @L_MOVE_LOG_PATH varchar(500)
Declare @L_PASSWORD varchar(100)

SET @L_DATABASENAME = '[abc]'
SET @L_DISK_PATH = '''''\\abc\E$\Dump\*.sqb'''''
SET @L_MOVETO_PATH = '''''\\abc\E$\Dump\Processed\'''''
SET @L_STANDBY_UNDO_PATH = '''''E:\UNDO_abc.DAT'''''
SET @L_DATAFILE_NAME = '''''abc_Data'''''
SET @L_MOVE_DATA_PATH = '''''E:\SecondarySiteDatabase\abc.mdf'''''
SET @L_LOGFILE_NAME = '''''abc_Log'''''
SET @L_MOVE_LOG_PATH = '''''E:\SecondarySitedatabase\abc_log.LDF'''''
SET @L_PASSWORD ='''''abc'''''


SET @L_RESTORE_DATABASE_COMMAND = '''-SQL "RESTORE DATABASE ' + @L_DatabaseName +
' FROM DISK = ' + @L_DISK_PATH +
' WITH MOVETO = ' + @L_MOVETO_PATH + ' , ' +
' STANDBY = ' + @L_STANDBY_UNDO_PATH + ' , ' +
'MOVE ' + @L_DATAFILE_NAME + ' TO ' + @L_MOVE_DATA_PATH + ' , ' +
'MOVE ' + @L_LOGFILE_NAME + ' TO ' + @L_MOVE_LOG_PATH + ' , ' +
' REPLACE, PASSWORD = ' + @L_PASSWORD + '" -E -I '''

Print @L_RESTORE_DATABASE_COMMAND

Exec master..sqlbackup @L_RESTORE_DATABASE_COMMAND , @exitcode OUTPUT, @sqlerrorcode OUTPUT

The print statement is giving me the exact command as I have without parameters. And If copy the statement which is created by Print statement it is running as well but, when I run

Exec master..sqlbackup @L_RESTORE_DATABASE_COMMAND , @exitcode OUTPUT, @sqlerrorcode OUTPUT

it is giving me this error:

SQL Backup (DLL v4.0.0.113)

---

login parameters:
[-U login id] [-P password] [-E] use trusted connection
[-I instance name]

common parameters:
[-SQL command]
[-USE setting description]

(7 row(s) affected)

name value
exitcode 870
sqlerrorcode 0

(2 row(s) affected)

Any Idea?
:?:

petey

Drop the opening and ending single quotes. Instead of

SET @L_RESTORE_DATABASE_COMMAND = '''-SQL "RESTORE ... -E -I '''

use

SET @L_RESTORE_DATABASE_COMMAND = '-SQL "RESTORE ... -E -I'

essamughal

Hi Petey;

Thanks for your reply. I had to remove quotes from each variable as well.

Here is correct syntax for others who want to do the same task.

DECLARE @exitcode int
DECLARE @sqlerrorcode int
Declare @L_RESTORE_DATABASE_COMMAND nvarchar(4000)
Declare @L_DATABASENAME varchar(100)
Declare @L_DISK_PATH varchar(500)
Declare @L_MOVETO_PATH varchar(500)
Declare @L_STANDBY_UNDO_PATH varchar(500)
Declare @L_DATAFILE_NAME varchar(100)
Declare @L_MOVE_DATA_PATH varchar(500)
Declare @L_LOGFILE_NAME varchar(100)
Declare @L_MOVE_LOG_PATH varchar(500)
Declare @L_PASSWORD varchar(100)

SET @L_DATABASENAME = '[abc]'
SET @L_DISK_PATH = '''\\abc\E$\Dump\*.sqb'''
SET @L_MOVETO_PATH = '''\\abc\E$\Dump\Processed\'''
SET @L_STANDBY_UNDO_PATH = '''E:\UNDO_abc.DAT'''
SET @L_DATAFILE_NAME = '''abc_Data'''
SET @L_MOVE_DATA_PATH = '''E:\SecondarySiteDatabase\abc.mdf'''
SET @L_LOGFILE_NAME = '''abc_Log'''
SET @L_MOVE_LOG_PATH = '''E:\SecondarySitedatabase\abc_log.LDF'''
SET @L_PASSWORD = '''abc'''


SET @L_RESTORE_DATABASE_COMMAND = '-SQL "RESTORE DATABASE ' + @L_DatabaseName +
' FROM DISK = ' + @L_DISK_PATH +
' WITH MOVETO = ' + @L_MOVETO_PATH + ' , ' +
' STANDBY = ' + @L_STANDBY_UNDO_PATH + ' , ' +
'MOVE ' + @L_DATAFILE_NAME + ' TO ' + @L_MOVE_DATA_PATH + ' , ' +
'MOVE ' + @L_LOGFILE_NAME + ' TO ' + @L_MOVE_LOG_PATH + ' , ' +
' REPLACE, PASSWORD = ' + @L_PASSWORD + '" -E -I '

Print @L_RESTORE_DATABASE_COMMAND

Exec master..sqlbackup @L_RESTORE_DATABASE_COMMAND , @exitcode OUTPUT, @sqlerrorcode OUTPUT

Thanks