Schedule a job

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

Answer 1

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

Answer 2

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

Answer 3

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

Answer 4

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

Answer 5

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

Answer 6

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

Answer 7

Can you do SELECT @@version and post results?

---

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

Answer 8

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

Answer 9

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

Answer 10

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

Answer 11

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

Answer 12

But only the management studio in the local client machine is the express version. The server is not!

---

Many Thanks & Best Regards, HuaMin Chen

Answer 13

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

Answer 14

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

Answer 15

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

Answer 16

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

Answer 17

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

Answer 18

Any progress?

---

Kalman Toth, SQL Server & Business Intelligence Training; SQLUSA.com

Answer 19

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

Answer 20

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

Answer 21

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

Answer 22

Any advice?

---

Many Thanks & Best Regards, HuaMin Chen

Answer 23

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