February 10, 2015 at 8:31 am
I'm putting automated restore jobs on all of our development and staging servers. It's a SQL agent job that runs through various steps to restore the server from the production environment. Part of our process, after the actual restore, is to run the current set of development sql scripts. This is where I'm hung up.
The scripts are all stored in a network location: \\fs1\database_scripts\
The list of scripts to run is stored in a table: DBA.dbo.DBA_ScriptList
So I query DBA_ScriptList in a way that returns the list of scripts in the order to run them. The information returned gives me all of the information I need to call the script and run it. My problem is that I can't figure out how to run a single script from a SQL Agent job step. I know I can use a CmdExec step or a PowerShell step, but in those cases, how do I loop through the result set from DBA_ScriptList and only run one at a time.
Thinking of it in terms of cursoring through the result set using straight-up t-sql, is there syntax that would run the current file?
February 15, 2015 at 4:58 pm
Your best bet is probably SQLCMD. You can read more about it here.
sqlcmd -S Server\Instance -i C:\path\to\script\Script.sql
February 17, 2015 at 5:33 am
I had to use something similar to this in my auto restore process
DECLARE @Command VARCHAR(MAX)
DECLARE @FileCursor CURSOR
DECLARE @FileNameVARCHAR(1000)
SET @FileCursor = CURSOR FOR
SELECT filepath FROM DBA.dbo.DBA_ScriptList
OPEN @FileCursor
FETCH NEXT
FROM @FileCursor INTO @FileName
WHILE @@FETCH_STATUS =0
BEGIN
set @command = 'sqlcmd -i "'+@FileName+'"'
EXEC XP_CMDSHELL @command;
FETCH NEXT FROM @FileCursor INTO @FileName
END
CLOSE @FileCursor;
DEALLOCATE @FileCursor
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply