June 7, 2008 at 3:54 pm
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!
June 8, 2008 at 6:46 am
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!
June 8, 2008 at 7:35 pm
Haroon,
June 8, 2008 at 7:58 pm
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! 😀
June 9, 2008 at 8:52 am
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!
June 9, 2008 at 9:18 pm
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