Passing variables to SQL server Agent job OR asynchronous call of Stored Proc

Question

Passing variables to SQL server Agent job OR asynchronous call of Stored Proc

Viewing 7 posts - 1 through 8 (of 8 total)

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

LF

The simplest way is to create a table with one column for each variable. SP1 can then write to that table, and the job can pick up the variables from the table, then write new values to the table for SP2 to pick up.

John

LFCarrickgorman SSC Enthusiast Points: 120 More actions · Answer 2

Thanks John - I was thinking that but how does the job pick up the details in the table

so Job would look something like:

SELECT @PERIOD = PERIOD_COL, @YEAR = YEAR_COL, @USER = USER_COL FROM SAMPLE_TABLE_WITH_3_VARS

EXEC SP2 @PERIOD, @YEAR, @USER

Please confirm if I am missing something from this job?

thanks a mil

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

Yes, that's exactly what I had in mind. Try that, and post back if you have any difficulties.

John

Ken McKelvey SSCoach Points: 18975 More actions · Answer 4

You can get the same effect by creating a QUEUE and using Service Broker.

LFCarrickgorman SSC Enthusiast Points: 120 More actions · Answer 5

Thanks Ken but I am lost.....

Do you have a link to an explanation of those two please.

I will go down the above route first but I am eager to learn 🙂

Thanks

LF

Ken McKelvey SSCoach Points: 18975 More actions · Answer 6

There is a large section on Service Broker in BOL (Books Online).

LFCarrickgorman SSC Enthusiast Points: 120 More actions · Answer 7

OK - thanks this all worked - a bit of tweaking but.... 🙂

I give the relevant code below

ASP code :

Request.Form("ExecuteLoad") = "SUBMIT" then

strsql = "exec SP1'" & Request.Form("Period_dbx") & "','" & Request.Form("Year_dbx") & "','" & Username & "'"

set runScript = sql_objConnection.Execute (strsql,,adCmdText)

Stored Procedure 1 called from ASP page above: SP1

The ASP page passes over three variables @PERIOD, @YEAR, @USER to stored procedure

Part of the code in the SP1 is to INSERT the 3 variables into a table to be able to use later

-- ********** Table created to take a note of Variables passed from ASP page

--CREATE TABLE KS_STATU.[dbo].[ZZ_VAR_TBL_LF](

-- [id] [int] IDENTITY(1,1) NOT NULL,

-- [Col_PERIOD] [varchar](40) NULL,

-- [Col_YEAR] [varchar](40) NULL,

-- [Col_USER] [varchar](100) NULL,

-- [Col_Start_date] [varchar](40) NULL,

-- [Col_End_date] [varchar](40) NULL,

-- [Col_6] [varchar](40) NULL,

-- [Col_7] [varchar](40) NULL,

-- [Col_8] [varchar](40) NULL,

-- [Col_9] [varchar](40) NULL,

-- [Activity_Descr] [varchar] (255) NULL

--)

TRUNCATE TABLE KS_STATU.[dbo].[ZZ_VAR_TBL_LF]

INSERT INTO KS_STATU.[dbo].[ZZ_VAR_TBL_LF]

SELECT @PERIOD, @YEAR, @USER, null, null, null, null, null, null

, 'SP1 variables'

SP1 takes only a few seconds to run and at the end it calls the job

EXEC msdb.dbo.sp_start_job N'SQL_A_JOB_LF_TEST'

I have logged each step along the way to keep track of how far the job gets

When we get to the end of SP1 the ASP page returns to the user, so your first Stored Procedure must be short to run. Maybe simply pick up the variables, save them to a table and then call the job that calls SP2 which can be lengthy as required.

The Job itself simply executes the second Stored Procedure SP2

EXEC dbo.SP2

You will note that no variables are passed over I will need to pick these up from the table ZZ_VAR_TBL_LF

Here is part of SP2 to be able to use the variables initially sent over from ASP page:

DECLARE @PERIOD varchar(40),

@YEAR varchar(40),

@USER varchar(100)

SELECT @PERIOD = Col_PERIOD, @YEAR = Col_Year, @USER = Col_USER

FROM KS_STATU.[dbo].[ZZ_VAR_TBL_LF]

Now you can use the variables as you choose and have a longer running Stored Proc

It might be long winded but when you get it going it means you can run alot of jobs that can be set up through the web instead of through SQL server Management Studio