How to use Windows Scheduler to run a Simple SQL Batch File?????

Question

Post reply

How to use Windows Scheduler to run a Simple SQL Batch File?????

rkelly58

SSCrazy

Points: 2151
More actions
September 13, 2016 at 6:57 am

#330098

Hi,
I want to use Windows Server Task Scheduler to run a SQL command once a day
So I setup the below 2 files (1 batch, 1 sql) and set them up in Task Scheduler.
The Task runs and completes, but the SQL is not deleting the records....
If I manually run the Batch file, it works.
I've set the job up in task scheduler using the logged in admin account and also tried ticking use higher priveledges etc, but still not working.
There are no errors. When I run the batch script manually, a dos command window appears, but when you use scheduler, you don't get a command window.
These are the 2 files:
deleterecords.bat - this contains this line below:
sqlcmd -S myserver -E -i DeleteRecordsTest.sql
DeleteRecordsTest.sql
USE mydb
DELETE FROM dbo.Delete_Records_Test
WHERE TimeStamp <= DateAdd(d,-365,GETDate())
GO
Can anyone tell me how I can achieve this using Task Scheduler please?
I'm using SQLExpress, so no Server Agents etc and I don't want to use SSIS etc, just something to run locally on the local server db and tables.
Thanks

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 1

Withdrawn, sorry about that.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

rkelly58 SSCrazy Points: 2151 More actions · Answer 2

Hi,

As mentioned, don't have Server Agent, using SQL Express

jasona.work SSC Guru Points: 50084 More actions · Answer 3

I suspect the batch file is most likely working (the Task Scheduler when calling a batch file will run it, even if you don't see the CMD window) and instead that the SQLCMD is failing to login to your SQL instance.

The task will run generally with the NT Service\System account, which likely has little or no access to your SQL instance. A possible solution (which has its own problems) is to use the -U and -P switches for sqlcmd (login id and password) The obvious problem with this solution is, you'll have a password in the clear in the batch file.

rkelly58 SSCrazy Points: 2151 More actions · Answer 4

Already tried using the -U and -P switches (just to try it) and even that doesn't work either

Lowell SSC Guru Points: 323506 More actions · Answer 5

Besides running the script with an impersonated credential, which solves the SQL problem,i would think you need an explicit path to your sql file:

sqlcmd -S myserver -E -i "C:\Data\AutomatedScripts\DeleteRecordsTest.sql"

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

jasona.work SSC Guru Points: 50084 More actions · Answer 6

jasona.work (9/13/2016)
I suspect the batch file is most likely working (the Task Scheduler when calling a batch file will run it, even if you don't see the CMD window) and instead that the SQLCMD is failing to login to your SQL instance.
The task will run generally with the NT Service\System account, which likely has little or no access to your SQL instance. A possible solution (which has its own problems) is to use the -U and -P switches for sqlcmd (login id and password) The obvious problem with this solution is, you'll have a password in the clear in the batch file.

When it runs through Task Scheduler, is there anything generated in the SQL Server logs?

Also check in the Windows Event logs (Application, Security, and System) for anything around the time the task executes.

rkelly58 SSCrazy Points: 2151 More actions · Answer 7

Thanks Lowell, that worked.

I didn't need to add the userid or password, I just added the path to the file like u said and scheduled it.

tested and works great.

Thanks again and also everyone else who chimed in.

P.S... one last thing, I want to delete records which are older than 2 years old. I am using the d,-365 parameter, is there a way of just saying 2 years instead of xxx 730 days?

jasona.work SSC Guru Points: 50084 More actions · Answer 8

rkelly58 (9/13/2016)
Thanks Lowell, that worked.
I didn't need to add the userid or password, I just added the path to the file like u said and scheduled it.
tested and works great.
Thanks again and also everyone else who chimed in.
P.S... one last thing, I want to delete records which are older than 2 years old. I am using the d,-365 parameter, is there a way of just saying 2 years instead of xxx 730 days?

This should do it:

WHERE TimeStamp <= DateAdd(yy,-2,GETDate())

Of course, test, test, test before using.:-D

/edit: Bloody SQL code wrapper kept eating the <= sign...

rkelly58 SSCrazy Points: 2151 More actions · Answer 9

Thanks Jasona, that worked as well.

All sorted, thanks again to everyone involved.