Running Job dynamically & passing parameters

Question

Post reply

Running Job dynamically & passing parameters

RJ-476420

SSCarpal Tunnel

Points: 4806
More actions
November 26, 2008 at 6:57 am

#99373

I have a script that can be run in any databases I have in my development sql server 2005 because each databases is used by different users for each functionality.
This script purges few tables for development purposes. I would like to schedule this script (this script can't be a SP as this is one time in development alone) as an adhoc job in the dev server.
My question is how do i schedule the job to be run dynamically in a database? Whether I can pass any parameters? Also, whether I can able to pass any parameters to the script in this job?
Thanks.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

winash SSChampion Points: 11446 More actions · Answer 1

Let me know if this is what you were looking for....

USE tempdb;

IF OBJECT_ID('dbo.JobTestTable') IS NOT NULL

DROP TABLE dbo.JobTestTable;

GO

CREATE TABLE dbo.JobTestTable(SomeData nvarchar(100))

GO

INSERT dbo.JobTestTable(SomeData) SELECT 'SomeData'

SELECT 'BeforeJobRun',* FROM dbo.JobTestTable;

DECLARE @JobCmd nvarchar(1000);

DECLARE @srvname sysname;

DECLARE @TabName sysname

SET @TabName = 'tempdb.dbo.JobTestTable';

SET @JobCmd = 'DELETE '+@TabName;

-- put your server name here

SET @srvname = ''

IF EXISTS (SELECT 1 FROM msdb.dbo.sysjobs WHERE name = 'TestJobWithParams')

BEGIN

EXEC msdb.dbo.sp_delete_job @job_name='TestJobWithParams';

END;

EXEC msdb.dbo.sp_add_job @job_name = 'TestJobWithParams';

EXEC msdb.dbo.sp_add_jobstep @job_name='TestJobWithParams',@step_name = 'Step1',@command=@JobCmd;

EXEC msdb.dbo.sp_add_jobserver @job_name='TestJobWithParams',@server_name=@srvname;

-- kicking off the job - can be scheduled to run at any time

EXEC msdb.dbo.sp_start_job @job_name = 'TestJobWithParams'

-- give a wait period to let the job finish executing and then check

WAITFOR DELAY '00:00:10'

SELECT 'AfterJobRun',* FROM dbo.JobTestTable;

RJ-476420 SSCarpal Tunnel Points: 4806 More actions · Answer 2

nope.

My requirement is:

1. Script:

delete from table1 where userid = ?

--Comments: Here ? = dynamically passed parameter

2. Create a new job in SQL Server Agent and put the above script as a step in the job.

3. Make this job runnable in any databases where this job is created. Say "Server1" has database1, database2, ...n., then the job created in step 2 should be runnable in any of these databases. All these databases have the same schema.

4. I want the database & ? to be passed as a parameter? What would be the best practice or solution?

Thanks.

winash SSChampion Points: 11446 More actions · Answer 3

1. Script:

delete from table1 where userid = ?

--Comments: Here ? = dynamically passed parameter

Just modify the existing code to take in an extra parameter...

2. Create a new job in SQL Server Agent and put the above script as a step in the job.

The existing code already does this

3. Make this job runnable in any databases where this job is created. Say "Server1" has database1, database2, ...n., then the job created in step 2 should be runnable in any of these databases. All these databases have the same schema.

The DB against which the job runs and deletes the table has to be specified each time...one option is to loop through the code for each DB...or create a stored proc that takes in DB name as a parameter (amongst all the other parameters) and which does the job creation stuff etc and call it passing each DB name

In this case - instead of deleting the job each time you can delete the job step (using sp_delete_jobstep and specifying the DB name each time)...basically some extra checks to see if any previous call to the job is excuting and to wait till it finishes before running the job against another DB etc etc

4. I want the database & ? to be passed as a parameter? What would be the best practice or solution?

I don't know any best practices for this situation 🙂 - you can try the methods suggested above...

Let me know if I'm still missing something...tweaked script attached below....

USE tempdb;

IF OBJECT_ID('dbo.JobTestTable') IS NOT NULL

DROP TABLE dbo.JobTestTable;

GO

CREATE TABLE dbo.JobTestTable(SomeSearchVal int,SomeData nvarchar(100))

GO

INSERT dbo.JobTestTable(SomeSearchVal,SomeData)

SELECT 1,'SomeData'

UNION ALL

SELECT 2,'SomeMoreData'

SELECT 'BeforeJobRun',* FROM dbo.JobTestTable;

-- add as many parameters as you need

DECLARE @JobCmd nvarchar(max);

DECLARE @srvname sysname;

DECLARE @TabName sysname;

DECLARE @DBName sysname;

DECLARE @WhereClause nvarchar(max);

DECLARE @SearchVal varchar(30)

SET @DBName = 'tempdb';

SET @TabName = 'dbo.JobTestTable';

SET @SearchVal = 1;

SET @WhereClause = ' WHERE SomeSearchVal = '+CAST(@SearchVal As Varchar(30))

SET @JobCmd = 'DELETE '+@TabName+@WhereClause;