April 8, 2015 at 2:37 am
I have separate scripts for Creation of Tables, Stored Procedures, Views, Triggers in different .sql files.
I have to execute thses scripts in batch in different servers and different databases without opening SSMS.
All .sql files to be executed in single command.
Please suggest how to achieve?
Appreciate your help.
Regards,
Bala
April 8, 2015 at 3:17 am
balushanmugham (4/8/2015)
I have separate scripts for Creation of Tables, Stored Procedures, Views, Triggers in different .sql files.I have to execute thses scripts in batch in different servers and different databases without opening SSMS.
All .sql files to be executed in single command.
Please suggest how to achieve?
Appreciate your help.
Regards,
Bala
I think Powershell will be your friend here.
You can also just use SqlCmd, I guess.
Are you aware of and can you use these tools?
April 8, 2015 at 6:49 am
As said above creating a Powershell script or Batch file is a fairly simple way of doing it.
It all depends on what you are comfortable programming in. Personally I have been usisng python a lot lately to do this sort of thing as I can then incorporate in other techniques I was to use.
In a batch file it as simple as this:
sqlcmd -E -S MyServer -d MyDatabase -i"C:\temp\Myscript.sql"
But you can do much much more complex code that for example, the batch file takes databases \ servers as parameters and passes them into the command for example:
SET MYSERVER= %1
SET MYDATABASE = %2
SET FILE = %3
sqlcmd -E -S %MYSERVER% -d %MYDATABASE% -i"C:\temp\%FILE%"
MCITP SQL 2005, MCSA SQL 2012
April 8, 2015 at 8:43 am
In addition to what everyone else has suggested you can use SSIS and do a loop task (if you are familiar with SSIS). This is good if you don't want to go the programming route. You would set it up to loop through and execute each script file in a folder(s).
-- Itzik Ben-Gan 2001
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply