1 Reply Latest reply on Jul 7, 2006 3:04 AM by El_Crabs

    Hit and Run stored procedure

      Hi there Cf community.

      I am back again with yet ANOTHER question.
      I have a stored procedure in an MSSQL 2000 Dbase that performs a few tasks on a few thousand records.
      I want to be able to execute this stored procedure from CF BUT, I dont want the page to sit and wait for
      the proc to finish as it takes -+ 4 minutes and the page would just time out.
      On top of this I dont want any data to be returned.
      The only reason I am not using a SQL schedule to do this is because the page that fires the proc of is one of many pages, daisy chained to one another performing various tasks on the original volume of data.
      The page starts the proc and then carries on over the finish line.
      This task is the final of 3 pages that will be CF scheduled every night.

      ASP.NET is also a possibilty is CF is unable to do the job.

      Thanks again. You bunch are very helpful with regards to my previous XML posts.
      El Crabs

        • 1. Re: Hit and Run stored procedure
          El_Crabs Level 1
          Hi all.

          Found a solution to firing off a large stored procedure without
          the page sitting and waiting for a response, leading to time out.

          You create a job in MS SQL 2000 Enterprise Manager via the 'jobs' node which sits under the
          SQL Server Agent, which is under the Management node in the tree view panel on the left.

>Management->SQL Server Agent->Jobs

          Inside the right panel, right click 'new job' then fill in the small details + a name for the job.
          then under 'STEP' click new, fill more details but most importantly, fill in the commands box with "EXECUTE <stored_procedure_name_here>". Click all the relavant OK's to finish.

          Once its done then take the job name that you specified earlier and use it in the SQL string on the connection/query object.
          *The following has only been tested in asp.net, I have not tested it in coldfusion, but it should still yield the same results.

          (there is script missing from this, but I am only displaying this line because its the most important one)

          <cfquery databaseparamsgohere>
          Use msdb exec sp_start_job @job_name = '<insert_jobname_here>'

          Dim SqlCmd = New SQLCommand("Use msdb exec sp_start_job @job_name = '<insert_jobname_here>'", connection)

          This line will get executed, the page will carry onto the very next line without waiting for the stored procedure to finish. No timeouts, no pain in the neck ect.

          Its sloppy in certain context's but it works.

          El Crabs.