execute a stored procedure

• jr31junk

  Grasshopper

  Points: 24

  More actions

  March 15, 2004 at 8:43 am

  #161706

  I have a stored procedure that I want to run every 90 days as a job.  I have added a step to a job that looks like: 

  C:\MSSQL\Bin\isqlw.exe -S <SQL Server> -U <userId> -P <password> -i \Data\Computer\SQL">\\<server>\Data\Computer\SQL Scripts\rmLotNo.SQL -o \Data\Computer\SQL">\\<server>\Data\Computer\SQL Scripts\rmLotNoO.txt

  The file, rmLotNo.SQL, looks like:

  USE <database name>

  set nocount on

  print "=========================================="

  print "==  Deletes zero-quantity lot records.  =="

  Print "=========================================="

  select db_name()

  print "--------Database--------------"

  select convert(varchar(20), getdate(),0)

  print "--------------------"

  set nocount off

  execute usp_sqlScript_delete_lot_nos

  I've created similar jobs except they ran queries not a stored procedure.  Executing a stored proc must be slightly different than a query.  This job fails when I start it.  I've gotten this far by looking at similar jobs on our server.  Any help is appreciated.  Thanks.

   

• Greg Charles

  SSC-Forever

  Points: 45403

  More actions

  March 16, 2004 at 10:24 am

  #498990

  Check BOL->job steps->creating job steps.  Here's an excerpt showing how to add a T-SQL job step in Enterprise Manager (you can also do it in T-SQL by using sp_add_jobstep):

  1. Create a new job or right-click an existing job, and then click Properties.
  2. In the Job Properties dialog box, click the Steps tab, and then click New.
  3. In the Step name box, enter a job step name.
  4. In the Type list, click Transact-SQL Script (TSQL).
  5. In the Database list, click a database for this job step to use.
  6. In the Command box, enter the Transact-SQL command batch(es), or click Open to select a Transact-SQL file to use as the command.

     Here's where you would put your code -

  set nocount on

  print "=========================================="

  print "==  Deletes zero-quantity lot records.  =="

  Print "=========================================="

  select db_name()

  print "--------Database--------------"

  select convert(varchar(20), getdate(),0)

  print "--------------------"

  set nocount off

  execute usp_sqlScript_delete_lot_nos

     7. Click Parse to check your syntax.

   

  Greg

  Greg

• jr31junk

  Grasshopper

  Points: 24

  More actions

  March 16, 2004 at 10:52 am

  #498998

  Greg, this worked.  Thank you very much.