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

• LFCarrickgorman

  SSC Enthusiast

  Points: 120

  More actions

  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