Automatically execute query in MS SQL Studio every hour

I support an application in a big enterprise, one of my roles is to clean-up data. There is a query I need to execute every hour, and I would like to automate it. Due to organization policies, I can't create SQL Server Agent jobs or modify schema, I can only manipulate data.

An endless

WHILE(1=1)
BEGIN 
WAITFOR DELAY '01:00';
--do work
END

does the job for me, but I shrug at the thought of a perma-open connection.

Ideally, I would script the MS SS itself to execute a given piece of code every hour, but I'm not sure if that is possible.

Is there any solution to this problem?

Your friend is sqlcmd (Microsoft Technet)

• Create a SQL file with the script required to run your cleanup job

• Run the script with sqlcmd.exe and any required parameters

• Create a Windows Scheduled Task and add the command with all the required parameters

E.g.

sqlcmd -d YOUR_DB -E -i YOUR_SCRIPT.SQL -o OUTPUTFILE.TXT

Good luck.

StackExchange Database Administrators Q#176023, answer score: 23