13

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?

  • 1
    Can you use Windows sceduler on your pc? – sepupic Jun 12 '17 at 9:05
  • 12
    Ask someone who does have permission to schedule it for you. – Mister Magoo Jun 12 '17 at 9:09
  • @sepupic That is my next land of research, if nothing in MS SS can help me. – Ivan Koshelev Jun 12 '17 at 9:29
  • I agree. I would feel very uncomfortable running looping code on my client computer. What if your PC crashed, some one unplugged it etc. I have worked in similar environments where SQL jobs were not available to app teams - because we had an alternative option of using AutoSys or another scheduling tool running on an application server. Try and find out how other scheduled tasks are managed? Another workaround may be to use SQL Server Reporting Services ? Not ideal I know... – Terry C Jun 12 '17 at 9:32
  • 9
    The correct solution is to go to the DBAs and have them create a SQL Server Agent job to run this cleanup hourly. Then find out why you're getting data in the database that needs "cleanup" so often and get that fixed. – alroc Jun 12 '17 at 12:28
22

Your friend is sqlcmd (Microsoft Technet)

  1. Create a SQL file with the script required to run your cleanup job
  2. Run the script with sqlcmd.exe and any required parameters
  3. 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.

  • 4
    Make sure these scripts are put on an appropriate app/tool server somewhere along with the Scheduled Task and not the development computer. – GER Jun 12 '17 at 13:35
7

You don't need Management Studio to execute queries.

If you really have no option to have somebody schedule a job for you, you should look into sqlcmd as noted in hot2use's answer

If you are on a version where that is not supported (since you didn't specify a version) there is also osql which is a command line client, but that tool has been deprecated.

You could then schedule a command using any scheduler you want (Windows Task Scheduler for example) and run something like this:

OSQL -E -i c:\temp\dowork.sql

Have a look at the documentation to see which options you have for server selection and authentication.

-1

Using "SQL Server Agent" which is found within MS SQL studio (in the object explorer, expand your server and it should be normally at the bottom of the list) is probably your best bet.

This will create a job which can be set to execute at defined intervals. The jobs will run as a service on the server rather than the client. This means if your client disconnects it will still run, and if the server is rebooted (for an unknown reason), the job will continue to run without you needing to initialise it again.

The downside is that you will likely need elevated access rights to be able to do this.

  • 3
    You've probably overlooked this bit in the question: Due to organization policies, I can't create SQL Server Agent jobs [...] – Andriy M Jun 12 '17 at 19:59
  • @Andriy: It is still the correct answer. We shouldn't be helping people do shadow IT counter to the wants of their organization. – Dylan Knoll Jun 13 '17 at 1:21
  • 1
    @DylanKnoll: Well, I think it would make perfect sense for the answerer to acknowledge the OP's mentioning the policy and include arguments against its stupidity (something along the lines of your comment, for instance), but "shouldn't be helping"? The suggested and accepted option doesn't look too atrocious, so sticking to principles would seem a little harsh in this case. – Andriy M Jun 13 '17 at 7:25
  • I get your point as well, but what about the DBAs perspective? Sounds a lot like somebody is trying to hide mistakes in DB design so they're not brought into the light. – Dylan Knoll Jun 13 '17 at 7:54
  • 1
    It read "SQL server jobs" before it was changed to "SQL Server Agent jobs". I don't think there can be another meaning to the former than the latter. (It wasn't the OP who edited that, by the way; it was a person who understood the slightly erroneous term exactly the way I would, and corrected it.) The original wording, though, is even easier to overlook, so, one way or another, I still maintain that was an oversight on your side :) – Andriy M Jun 13 '17 at 16:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.