locked
Schedule a job RRS feed

  • Question

  • Hi,

    Using the Sql server version - '9.0.4035', how can I schedule one job to repeatedly call an SP in a time slice?


    Many Thanks & Best Regards, HuaMin Chen
    Thursday, April 22, 2010 8:54 AM

All replies

  • What Edition of SQL Server are you using? Is it the Express edition by chance?

    If you are looking for a Job Scheduler for SQL Express then check out SQL Scheduler

    Alternatively, if you looking to recursively call a stored procedure from within a single SQL Server Agent job then simply wrap your procedure call in a while loop that will not end. 

    WHILE 1 = 1
    BEGIN
    	EXEC storedProcedureName
    END
    Personally I would prefer to create a single SQL Server Agent Job that was scheduled for run on a frequent interval.

     

     


    John Sansom |SQL Server Consultant with Santech Solutions
    Thursday, April 22, 2010 8:59 AM
  • You may want to slow down the WHILE loop, otherwise it may  cause  server overload:

    WHILE 1 = 1
    BEGIN
     EXEC storedProcedureName
    	WAITFOR DELAY '00:00:30';
    
    END

    Kalman Toth, SQL Server & Business Intelligence Training; BI TRIO 2008
    Thursday, April 22, 2010 9:21 AM
  • Many thanks John. The version is '9.0.4035' (I think it's Sql server 2000). How can we schedule that to run on a frequent interval by an Agent job?
    Many Thanks & Best Regards, HuaMin Chen
    Thursday, April 22, 2010 9:22 AM
  • That is SQL Server 2005.

    You can just create a schedule for the job when setting up the job or editing it.

    How to: Schedule a Job (SQL Server Management Studio)

     


    Kalman Toth, SQL Server & Business Intelligence Training; BI TRIO 2008

    • Proposed as answer by Naomi N Friday, April 23, 2010 2:23 PM
    Thursday, April 22, 2010 9:49 AM
  • You may want to slow down the WHILE loop, otherwise it may  cause  server overload:

    WHILE 1 = 1
    BEGIN
     EXEC storedProcedureName
    	WAITFOR DELAY '00:00:30';
    
    END
    

    Kalman Toth, SQL Server & Business Intelligence Training; BI TRIO 2008
    That's a very good idea!
    John Sansom |SQL Server Consultant with Santech Solutions
    Thursday, April 22, 2010 10:00 AM
  • Many many thanks Kalman. The way is for Sql server 2008. Now against this 2005 server, after having connected to the DB, I don't see any object like SQL Server Agent.
    Many Thanks & Best Regards, HuaMin Chen
    Thursday, April 22, 2010 10:01 AM
  • Can you do SELECT @@version and post results?
    Kalman Toth, SQL Server & Business Intelligence Training; BI TRIO 2008
    Thursday, April 22, 2010 10:03 AM
  • Thanks.

    Microsoft SQL Server 2005 - 9.00.4035.00 (X64)   Nov 24 2008 16:17:31   Copyright (c) 1988-2005 Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)


    Many Thanks & Best Regards, HuaMin Chen

    Friday, April 23, 2010 3:42 AM
  • In SSMS Object Explorer, at the bottom, you should see SQL Server Agent - this is the job facility.
    Kalman Toth, SQL Server & Business Intelligence Training; BI TRIO 2008
    Friday, April 23, 2010 4:02 AM
  • Many thanks. Could you pls have a look on this?

    http://cid-17ec75244bac022f.skydrive.live.com/self.aspx/.Public/te8.doc

    I don't see that.


    Many Thanks & Best Regards, HuaMin Chen

    Friday, April 23, 2010 4:13 AM
  • Express Edition does not have SQL Server Agent job facility.

    You need to use a paid version of SQL Server.

    Link:

    However, since SQL Server Express does not ship SQL Agent, scheduling the subscriptions is more challenging.

     


    Kalman Toth, SQL Server & Business Intelligence Training; BI TRIO 2008
    • Proposed as answer by Naomi N Sunday, August 8, 2010 9:34 PM
    Friday, April 23, 2010 4:30 AM
  • But only the management studio in the local client machine is the express version. The server is not!
    Many Thanks & Best Regards, HuaMin Chen
    Friday, April 23, 2010 4:48 AM
  • Kalman, within the database server, I see that the management studio is also the express version! How can I have the non-express one?

    http://cid-17ec75244bac022f.skydrive.live.com/self.aspx/.Public/t10.doc


    Many Thanks & Best Regards, HuaMin Chen
    Friday, April 23, 2010 7:30 AM
  • You or your Organization needs to buy full version of the SQL Server product http://www.microsoft.com/sqlserver/2008/en/us/how-to-buy.aspx. Express is a free edition with very limited functionality. 
    Ranjith | My Blog
    Friday, April 23, 2010 8:21 AM
  • Many thanks Ranjith. Is there any way to have that management studio (non-express version) only, as we're already equipped with Sql server 2005?
    Many Thanks & Best Regards, HuaMin Chen
    Friday, April 23, 2010 10:12 AM
  • Many thanks Ranjith. Is there any way to have that management studio (non-express version) only, as we're already equipped with Sql server 2005?

    The paid version of Management Studio comes with the paid version of SQL Server 2005. To be on the safeside, check with Microsoft on licensing issues if any.

    You need to install client tools on the workstation in question. To avoid further confusion, uninstall Express SSMS before installing paid SSMS.

    You should not use the Express version since you have a paid version.

    Link:

    The client components option installs the following SQL Server features: Command prompt tools, Reporting Services tools, connectivity components, programming models, management tools, development tools, and SQL ServerBooks Online.


    Kalman Toth, SQL Server & Business Intelligence Training; BI TRIO 2008
    • Proposed as answer by Naomi N Friday, April 23, 2010 2:24 PM
    • Marked as answer by Tony Tang_YJ Tuesday, April 27, 2010 8:12 AM
    • Unmarked as answer by Jackson_1990 Tuesday, April 27, 2010 9:08 AM
    Friday, April 23, 2010 10:43 AM
  • Hi,
    Otherwise use SCHTASKS to create Task Scheduler as batch file.
    To run this it automatically creates scheduler.

    Here for example  to create a sql file for take backup of the database.

    Syntax:
    ---------

    SCHTASKS /Create [/S system [/U username [/P password]]] [/RU username [/RP password]] /SC schedule [/MO modifier] [/D day] [/I idletime] /TN taskname /TR taskrun [/ST starttime] [/M months] [/SD startdate] [/ED enddate]


    Example:
    -----------

    SCHTASKS /CREATE /SC DAILY /MO 1 /TN DAILY_FULL_BACKUP /TR "C:\Program Files\Microsoft SQL Server\90\Tools\Binn\SQLCMD -S.\ -U sa -P mySQL -i C:\BackupScripts\DAILY_FULL_BACKUP.sql" /ST 16:00:00



    Thanks
    Sreekanth
    • Marked as answer by Tony Tang_YJ Tuesday, April 27, 2010 8:12 AM
    • Unmarked as answer by Jackson_1990 Tuesday, April 27, 2010 9:08 AM
    Friday, April 23, 2010 10:54 AM
  • Any progress?
    Kalman Toth, SQL Server & Business Intelligence Training; SQLUSA.com
    Thursday, April 29, 2010 4:00 PM
  • Many thanks all.

    Sreekanth,

    Do I need the paid version of management studio for this as I'm using this?

    Microsoft SQL Server Management Studio Express 9.00.4035.00

    Additionally, I cannot find out this file SQLCMD in the local client machine.


    Many Thanks & Best Regards, HuaMin Chen
    Tuesday, August 3, 2010 2:56 AM
  • You can install SQLCMD from this page:

    Feature Pack for Microsoft SQL Server 2005 - November 2005


    Kalman Toth, SQL Server & Business Intelligence Training; BI TRIO 2008
    Tuesday, August 3, 2010 5:49 AM
  • Many thanks Kalman.
    Now I can see this "C:\Program Files\Microsoft SQL Server\90\Tools\Binn\SQLCMD.EXE".

    Sreekanth,
    Is it OK for running this? I'm to check something by t31, in which there're delete/insert statements.
    SCHTASKS /CREATE /SC DAILY /MO 1 /TN /TR "C:\Program Files\Microsoft SQL Server\90\Tools\Binn\SQLCMD -S.\ -U sa -P mySQL -i C:\t31.sql" /ST 16:00:00


    Many Thanks & Best Regards, HuaMin Chen
    Tuesday, August 3, 2010 7:54 AM
  • Any advice?
    Many Thanks & Best Regards, HuaMin Chen
    Wednesday, August 4, 2010 8:18 AM
  • But only the management studio in the local client machine is the express version. The server is not!

    You have SQL Server Enterprise Edition, why are you using SSMS Express?

    Just uninstall SSMS Express, and install the regular SSMS from your SS DVD (client tools).

    Also consider hiring a DBA consultant for a few days to setup your SS environment.


    Kalman Toth, SQL Server & Business Intelligence Training; BI TRIO 2008

    • Proposed as answer by Naomi N Sunday, August 8, 2010 9:35 PM
    Wednesday, August 4, 2010 9:37 AM