Script runner

  • 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?

  • Your best bet is probably SQLCMD. You can read more about it here.

    sqlcmd -S Server\Instance -i C:\path\to\script\Script.sql

  • 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