Hi,
I have got a SPROC that receives 3 parameters namely,
CREATE PROCEDURE [dbo].[sp_BackupDatabases]
@databaseName sysname = null,
@backupType CHAR(1),
@backupLocation nvarchar(200)
............................................
The rest of the code uses these parameters to BACKUP all databases present inside sql server.The @databaseName parameter is optional and if not supplied BACKUPs all databases.To execute this SPROC i used a SQL AGENT JOB and inside the JOB a TASK.
TASK
USE [msdb];
DECLARE @backupLocation NVARCHAR(200)
DECLARE @backupType CHAR(1)
DECLARE @databaseName varchar(max)
DECLARE @sp varchar(max)
SET @backupLocation = N'D:\dbbc-folder\'
SET @backupType = N'F'
SET @databaseName = N''
SET @sp = N'EXEC master.dbo.[sp_BackupDatabases]',@backupType='+@backupType +',@backupLocation='+@backupLocation
EXEC msdb.dbo.sp_add_jobstep
--@job_id = N'',
@job_name = N'backupjob',
@step_name = N'Task1',
--@step_id = 1,
@subsystem = N'TSQL',
@command = @sp,
@cmdexec_success_code = 0,
@on_success_action = 1,
@on_fail_action = 2,
@retry_attempts = 0,
@retry_interval = 0,
@server = N'PROCESSCUBE-PC',
@database_name = N'master',
@os_run_priority = 0,
@flags = 0
GO
it works fine when i execute it.But when i see the code inside the task it appears as-:
exec master.dbo.[sp_BackupDatabases] @databaseName=,backupType=F,backupLocation=D:\dbbc-folder\
how can i enclose the parameter values in ' marks as i think that's what is causing the error.
Thanks in advance