On a project my team was working on recently, we wanted to run a stored procedure every 10 minutes for 2 days to collect data over time. At the end of the 2 days, we would FTP the results up to a folder and remove any evidence that we had been on the server (clean up the tables we’d been writing to, drop the stored procedure, and in turn, drop the very job we had created to run this whole contraption.
How to delete the very job that is running? (eyebrows raised). Is this even possible? At first we thought not. I mean, wouldn’t that tear a hole in the space-time continuum? I asked my team to look a little deeper, there had to be a way. Maybe we could go through xp_cmdshell and call a statement that would essentially be in another process and that could do it? Let’s get creative.
Then they found it.
Wow. 20 years of SQL Server and I never noticed that before. (Base of Palm to Forehead).
Thanks to Eric Blinn & Mayil Chandran for researching this and making me aware.
Mindy Curnutt is a five time Microsoft Data Platform MVP with over 20 years of experience working with Database Solutions. She specializes in Performance Tuning and Architecture. She speaks regularly across North America at Local User Groups, SQL Saturdays and Conferences.