How to pass parameters to a SPROC inside a SQL AGENT JOB? [Resolved]

Posted by Coolbharat under ASP.NET on 4/2/2014 | Points: 10 | Views : 1692 | Status : [Member] | Replies : 2
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.


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

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


Posted by: Bandi on: 7/8/2014 [Member] [MVP] Platinum | Points: 50


To fix the above UnClosed Quotation Mark error in SQL Server Jobs,

SET @sp = N'EXEC master.dbo.[sp_BackupDatabases] @databaseName = '''+@databaseName+''',@backupType='''+@backupType+''' ,@backupLocation='''+@backupLocation+''''

Mark This Response as Answer

Coolbharat, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Vuyiswamb on: 7/8/2014 [Member] [MVP] [Administrator] NotApplicable | Points: 25

an SQL Job on its own cannot accept Parameters, you need to create an SSIS package to do that.

Thank you for posting at Dotnetfunda

Coolbharat, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response