February 4, 2014 at 1:56 pm
I’m creating sql commands using dynamic sql and need the ability to execute the results of the query automatically via a sql job (ie: ALTER DATABASE MyDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE; exec sp_detach_db 'MyDB';).
I created an SP that inserts the t-sql I need to execute into a temp table, but I would like to execute the results of this temp table via a SQL job.
I have an app that creates db’s automatically in the wrong directories/drives almost daily and they need moved. So the steps in my weekly job would flow something like this:
1. Create temp table containing detach statements
2. Execute all detach commands/records from temp table
3. Move .mdf and .ldf to correct locations (PowerShell)
4. Create temp table containing re-attach statements
5. Execute all re-attach commands/records from temp table
What would be the best way accomplish step 2? Should I try to do this using CmdExec or powershell from a flat file? Or maybe a different approach? Thanks
February 4, 2014 at 2:48 pm
Have you tried to loop through the table and assign the "query" in the table to a variable, then execute sp_executesql with the variables etc?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 4, 2014 at 2:51 pm
Agree with Jason above. Usually to do this you build a cursor around your temp table, then wrap it in EXEC( @sql) or an sp_executeSQL item. Fetch until complete.
Note, I don't usually recommend something this simplistic where the code can be adjusted externally, but it looks like this is self-contained.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 4, 2014 at 4:57 pm
Evil Kraig F (2/4/2014)
Agree with Jason above. Usually to do this you build a cursor around your temp table, then wrap it in EXEC( @sql) or an sp_executeSQL item. Fetch until complete.Note, I don't usually recommend something this simplistic where the code can be adjusted externally, but it looks like this is self-contained.
I have not tried this, but it sounds like a good idea.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply