Post reply

SQL agent to receive Variables from SP and then pass on to another SP

  • January 19, 2011 at 4:19 am

    #144803

    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

  • January 20, 2011 at 5:35 am

    #1275812

    Hi All

    I have a solution which I got help on in the "General" forum here.

    Here are the details below. I wasn't able to pass over the variables but I created a table, entered the variables into the table and picked those up later in the Stored Proc 2 I call from the SQL Agent Job.

    So the logic is:

    ASP Page calls SP1 and sends over 3 variables

    SP1 runs, enters 3 variables into table and calls SQL Agent Job and then returns user to ASP page so rest of process runs in background

    SQL Agent Job simply calls SP2

    SP2 picks up 3 variables from table and runs rest of business Stored Proc

    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

Viewing 2 posts - 1 through 2 (of 2 total)

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