job schedules - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

job schedules

Author

Topic

noms
Starting Member

22 Posts

Posted - 2009-02-25 : 05:04:30

hi experts
i have a client which is running sql server express and i need to create a schedule job which will run everyday at midnight but now since he doesn't have Sql Server Agent it's make it difficult for me to do that. when i did some searches somewhere it's says i can create windows schedule but i'm having difficulties getting the instructions on how to set it up.
here's my script:

DECLARE
@liDepLinksID dm_ID,
@liTransID dm_ID,
@liMaxMaxID dm_ID,
@liNextID dm_ID
SELECT @liNextID = ST.NextID from [ON_KEY_42R5_UDM2]..stdTables ST
WHERE ST.TableName = 'astDepreciationTransactions'
SELECT @liMaxMaxID = MAX(TRANSACTIONID) FROM UDMLastTransaction
WHILE (@liMaxMaxID < @liNextID - 1)
BEGIN
SET @liMaxMaxID = @liMaxMaxID + 1
UPDATE UDMLastTransaction set TransactionID = @liMaxMaxID

DECLARE lcursor CURSOR LOCAL FOR
SELECT ST.ID, ST.DepreciationLinksID FROM [ON_KEY_42R5_UDM2]..astDepreciationTransactions ST
WHERE ST.ID = @liMaxMaxID

OPEN lcursor
WHILE 0 = 0
BEGIN
FETCH NEXT FROM lcursor INTO @liTransID,@liDepLinksID
IF @@fetch_status <> 0
BEGIN
BREAK
END
EXEC pr_UDMAssetValuesReport @liTransID, @liDepLinksID
END
CLOSE lcursor
DEALLOCATE lcursor
END

Mangal Pardeshi
Posting Yak Master

110 Posts

Posted - 2009-02-25 : 05:36:40

Hi,
You can schedule your store procedure from Windows Scheduled Tasks.
Create batch with following script inside it

Using Windows Authentication
sqlcmd -S .\SQLEXPRESS -Q "EXEC YourProcName"
Or using sql Authentication
sqlcmd -U BackupUser -P password -S .\SQLEXPRESS -Q "EXEC YourProcName"

save the above script in notepad with .bat extension. And then you can schedule the Bat file using Windows Scheduled Tasks.

to schedule the batch file-
Open the Control Panel and perform the following steps:
i) Double-click Scheduled Tasks. If you are on Windows Xp Control panel -> Performance and Maintenance. Or you can also find it under Start -> Programs -> Accessories -> System Tools -> Scheduled Tasks.
ii) Double-click Add Scheduled Task.
iii) Click the Next button.
iv) Click the Browse button.
v) Browse to the SqlBackup.bat file you created, select the file, and click
Open.
vi) Choose how often you would like the backup to occur. For example,
choose Daily and click Next.
vii) Select the time you want the Backup to start. For example, select
11:00 PM and click Next.
viii) In the Enter the user name field, enter \Administrator, specify the Administrator’s password, and click
Next.
ix) Click Finish to schedule the backup and close the Scheduled Task
Wizard.
Using the above example, a backup of your database will occur at 11:00 PM
on a daily basis.

I have copy pasted the procedure from - http://mangalpardeshi.blogspot.com/2009/01/schedule-backups-in-sql-express.html

Mangal Pardeshi
http://mangalpardeshi.blogspot.com

noms
Starting Member

22 Posts

Posted - 2009-02-27 : 03:20:15

thanks Mangal for your help, i've also created an output file on this line 'sqlcmd -U BackupUser -P password -S .\SQLEXPRESS -Q "EXEC YourProcName" inorder to see the results and it's works okay. what i also want to know is that does the output file get overwritten each time the job execute? if it doesn't is there a way i can make the output file to be overwritten each time.

thanks again

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources