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?

Improve this question
5
  • 1
    Can you use Windows sceduler on your pc?
    – sepupic
    Jun 12, 2017 at 9:05
  • 12
    Ask someone who does have permission to schedule it for you.
    – Mister Magoo
    Jun 12, 2017 at 9:09
  • @sepupic That is my next land of research, if nothing in MS SS can help me.
    – Ivan Koshelev
    Jun 12, 2017 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, 2017 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, 2017 at 12:28

3 Answers 3

Reset to default
23

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.

Improve this answer
1
  • 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, 2017 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.

Improve this answer
0
-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.

Improve this answer
7
  • 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, 2017 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, 2017 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, 2017 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, 2017 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, 2017 at 16:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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