Execute multiple scripts at Once

Question

Hi

I have about 200 scripts(modified stored procedures) placed in a single folder to be updated into an existing database.To update this I will need to manually choose each file and execute it.(I dont want to put all scripts in a single file)

I was just thinking whether any tools existed to make such a task easier. In that tool if i specify my database details(username and pwd) and have a browse button to choose my folder containing my scripts and  a button to make the changes..... Life would have been much easier !!!

Have anybody come across such a tool...If so kindly suggest...Any free versions?? i found a link http://www.xsqlsoftware.com/Product/Sql_Server_Script_Executor.aspx  Hope that u guys will have some soln to it

 

 

 

Answer 1

Check out Red Gate's SQL Multi Script:
http://www.red-gate.com/Products/SQL_multi_script/index.htm

It lets you exec multiple scripts and it can be configured to run on multiple servers as well.  I have used it a few times and it is a great tool.  There is a free trial (14 days) so you can at least try it out.  It is, unfortunately, not free though.  200$ for a full license.

---

Gulf Coast SQL

Answer 2

Hi friends...

 I have also seen some tools for the same ....but can anyone give some leads how top develop such an application!!!

Answer 3

Hari I'm not sure abt any tool as such but you can write your custom script OSQL/sqlcmd in which you can give iput file path and output file path(if needed) to execute each indivudial sql file. The only pain is to list out the names, but can bypass it by doing a dir/p in the command prompt copy the file name paste it into a notepad editor, I always prefer editplus and find and replace and you script is ready.

Tell me if this helps.

Answer 4

Hi friends...

 I have also seen some tools for the same ....but can anyone give some leads how top develop such an application!!!

I don’t have much development experience on this, some points for your reference:

1.        Create a Windows Console Application in Visual Studio

2.        Get a list of all file names in that single folder

3.        Read each file content and execute it as an SQL batch until all files executed

Also, you may need to add some status in the code to make sure each file executes successfully (true or false etc.). Besides, you could take a look at SQL Server SMO which might be helpful to you.

Thanks,
Chunsong

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 5

i also thought of developing a dotnet application but the sad part is the user should hav framework installed for it... I am thinking of some platform independent ways to do it.... well still i am not able to get  a clear idea..

Answer 6

Hari,

Did u tired what I suggested..... You need to write something like below in your batch file for each sql file

C:\>osql -S <Instance Name> -E -d <Database Name> -i <Input File Path> -o <Output File path>

Note: Output file will let you know if any error occured while running your sql files and one file will be created for each inout file

I hope this should work... if you give it a try.

Rohit

 

Answer 7

look at this app with source code

http://scriptzrunner.codeplex.com/

Answer 8

You could use powershell

http://sethusrinivasan.wordpress.com/2012/03/06/execute-multiple-scripts-at-once/

Thanks

Sethu Srinivasan[MSFT]

SQL Server

http://blogs.msdn.com/sqlagent