Executing T-SQL Batch Scripts stored on PC from DTS

  • I am new to T-SQL and DTS. Is there a way to have DTS seach a fold on the disk, read the names of the batch script files in it sequentially, then open each in turn and execute the T-SQL batch files?

    Thanks!

  • Just copy the script in SQL task and change the folder name to point to your DIR

    Declare @Folder Varchar(255)

    Declare @Cmd Varchar(1000)

    Declare @FilePath Varchar(1000)

    Declare @Ret Int

    If Object_ID('TempDB..#Temp') Is Not Null

    Begin

    Drop Table #Temp

    End

    Create Table #Temp

    (

    [FileName] Varchar(255)

    )

    Set @Folder = 'c:\Bat'

    --Dir listing

    Set@Cmd = 'Dir "' + @Folder + '" /b'

    --Load listing results in table

    Insert Into #Temp

    Exec master..xp_cmdshell @Cmd

    --Delete all files other than .bat extensions

    Delete From #Temp Where Lower(IsNull(FileName, '')) Not Like '%.bat'

    --Declare cursor to loop through all bat files

    DECLARE Bat_Cursor CURSOR FOR SELECT FileName From #Temp

    OPEN Bat_Cursor

    FETCH NEXT FROM Bat_Cursor Into @FilePath

    WHILE @@FETCH_STATUS = 0

    Begin

    Set @FilePath = @Folder + '\' + @FilePath

    Set @Cmd = '"' + @FilePath + '"'

    --Execute batch file

    Exec @Ret = master..xp_cmdshell @Cmd, No_Output --Comment No_Output if out put is required

    --Check return type for errors

    If @Ret <> 0

    Begin

    RaisError('Error executing batch.', 16, 1)

    End

    FETCH NEXT FROM Bat_Cursor Into @FilePath

    End

    CLOSE Bat_Cursor

    DEALLOCATE Bat_Cursor

    Hope it works!

  • Haroon,

  • Haroon,

    Thanks for taking the time to share your code with me! I am grateful 🙂

    What I am attempting to do (on a poor man's budget) is to take an image of our Production database, copy it into a Development environment (the process is currently in my DTS routine), then run all my .SQL files in a specific directory to modify the copy of the Production database.

    The .SQL files represent all the "pending" developmental changes that we intend to incorporate into Production once they pass integration testing. During development, we need a fresh copy of the Production database, but we need it to incorporate our pending changes too.

    I was doing OK understanding your code until I reached one point, namely:

    --Delete all files other than .bat extensions

    Delete From #Temp Where Lower(IsNull(FileName, '')) Not Like '%.bat'

    ....

    --Execute batch file

    Exec @Ret = master..xp_cmdshell @Cmd, No_Output

    You specify .BAT (batch files) ... I am assuming you mean DOS batch files.

    I currenly have a folder full of .SQL files that were created in Query Analyzer that I want to execute. What would I do to get them to execute?

    Thanks for your help! 😀

  • Please change

    Delete From #Temp Where Lower(IsNull(FileName, '')) Not Like '%.bat'

    to

    Delete From #Temp Where Lower(IsNull(FileName, '')) Not Like '%.sql'

    and

    Set @Cmd = '"' + @FilePath + '"'

    to

    Set @Cmd = 'osql -S. -Usa -Psa -dQA -i"' + @FilePath + '"'

    -S is the server name . means local

    -U is the db user name

    -P is the password

    -d is the database name againt which the queries need to run

    -i is the script file path

    Hope this helps!

  • Fantastic! Thank you!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply