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 : 603 | Status : [Member] | Replies : 2
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




Responses

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

Up
0
Down

Resolved
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
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

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

Up
0
Down
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
[Administrator]

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

Login to post response