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

LFCarrickgorman

SSC Enthusiast

Points: 120
More actions
January 19, 2011 at 9:10 am

#245559

HI All - I have posted the below on the SQL Agent Job forum but no reply yet 🙁
But I was thinking I could do this asynchronously but I can't get it working.
So I want to call one Stored Procedure (that has all my code in it) that has 3 variables passed over by the ASP page (@PERIOD, @YEAR, @USER) but I don't want to wait til it comes back.
set runScript = sql_objConnection.Execute (strsql,,adExecuteNoRecords) ' , , adExecuteNoRecords)
I have also tried these at the end too but to no avail.
' adExecuteNoRecords) ' 129) ' 16) ' adCmdText) '128) '
I am using logging in the Stored procedure so I know it is getting to a certain part of the code but then the big chunk it just fails. If I run the procedure like so in SQL server management all is fine but it takes 10 minutes - amount of records etc.
exec ZZ_ETL_REC_TO_STAT_FULLYEAR_LF_TEST 'DEC','2009','Kgr\kermit.frog'
I am looking at these two different ways now for days - please please help
-- ********************************
Hi All,
I am essentially trying to call a Stored Procedure from an ASP page while passing over variables (@PERIOD, @YEAR, @USER)
The Stored procedure works fine and can be called from the ASP page but the SP takes 10 minutes to run and so the ASP page times out.
So to get around this I have tried various things, but I essentially want to call the SP and the ASP page to refresh to itself immediately.
Bu t I can't get this working, so I am going down the SQL Agent route. So I am hoping to:
1. Call SP1 from ASP page sending over variables @PERIOD, @YEAR, @USER
2. SP1 calls SQL Agent Job but passes over variables @PERIOD, @YEAR, @USER - I am hoping by the time SP1 calls the agent job that the ASP page will not time out and will refresh (as per code)
3. SQL Agent Job then to call SP2 which is the main stored procedure and pass over the 3 variables to SP2
How do I pass the three variables into the SQL Agent job from SP1 and also how to pass them out to SP2?
The SQL agent job is simply something like this at the moment
exec [dbo].[ZZ_ETL_REC_TO_STAT_FULLYEAR_LF_TEST] @PERIOD , @YEAR , @USER
Thanks in advance
LF

Viewing 8 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