Run scripts on multiple servers in SQL Management Studio

I have a number of scripts that I need to run on multiple servers and databases. Once I have created a Solution with them all in, how do I make them run in a specific sequence, and how can I switch the focus of the entire set of scripts to the other databases I want to run them on?

Thanks,

-Rob

There is no way to do this included in SQL Management Studio. Solutions in SMS aren't like VS solutions, in that you can execute code pages and objects. In SMS the solution is just a convenient place to store and organize the script files you are working with.

You would have to open each script seperately and execute it against the server and database you wanted in the order you wanted.

Sorry, it's going to be a manual process. There are some 3rd party tools that might help some. Check Red Gate and APEX products.

|||

You can do this by using a batch file to execute either OSQL or SQLCMD (preferred) - both of which can be used to execute script files. If you pass server and database names as parameters into your batch file you can then pass the parameter values to OSQL or SQLCMD.

I don't have any examples to hand, but the following links should set you on the right track:

http://msdn2.microsoft.com/en-us/library/ms162806.aspx

http://msdn2.microsoft.com/en-us/library/ms162773.aspx

http://www.microsoft.com/resources/documentation/windows/xp/all/proddocs/en-us/percent.mspx?mfr=true

This is how we deploy changes to our databases in each of our environments - the only thing that changes between each environment is the server name parameter. We generally have one batch file per database and one SQL script per database object - one batch file can be used to execute hundreds of SQL script files if need be.

Chris

|||

Dan and Chris,

Thanks for your response on my question. Not the answers I was hoping to get, but thanks anyway!! I have used the DOS bat file method successfully in the the past, but it is a bit clunky and was hoping against hope that there would be a better method in the SQL management Studio. DO you think I may be looking in the wrong tool?

I can't believe that I am the only one who thinks that the lack of a tool or process to set up implementation of scripts is a serious gap in the SQL Management Studio.

Thanks again, Rob

|||

You have to remember that SQL Server is an RDBMS. Tools provide for development are extras that I find invaluable. Just look at how clunky the management and development tools that come with Oracle have been over the years.

On a good note, you may be able to make some progress by developing your procedures and sql code in Visual Studio instead of SMS. If you have VS 2005 professional edition you can create a sql server database project that will allow you to write t-sql code and execute it. You should be able to write a minor application that would execute the scripts for you against a list of databases and/or servers.

Sorry that there isn't an easier way to do it through SMS. Perhaps it will be a forthcoming feature. I'll suggest it on the product feedback group.

Run scripts on multiple servers in SQL Management Studio

I have a number of scripts that I need to run on multiple servers and databases. Once I have created a Solution with them all in, how do I make them run in a specific sequence, and how can I switch the focus of the entire set of scripts to the other databases I want to run them on?

Thanks,

-Rob

There is no way to do this included in SQL Management Studio. Solutions in SMS aren't like VS solutions, in that you can execute code pages and objects. In SMS the solution is just a convenient place to store and organize the script files you are working with.

You would have to open each script seperately and execute it against the server and database you wanted in the order you wanted.

Sorry, it's going to be a manual process. There are some 3rd party tools that might help some. Check Red Gate and APEX products.

|||

You can do this by using a batch file to execute either OSQL or SQLCMD (preferred) - both of which can be used to execute script files. If you pass server and database names as parameters into your batch file you can then pass the parameter values to OSQL or SQLCMD.

I don't have any examples to hand, but the following links should set you on the right track:

http://msdn2.microsoft.com/en-us/library/ms162806.aspx

http://msdn2.microsoft.com/en-us/library/ms162773.aspx

http://www.microsoft.com/resources/documentation/windows/xp/all/proddocs/en-us/percent.mspx?mfr=true

This is how we deploy changes to our databases in each of our environments - the only thing that changes between each environment is the server name parameter. We generally have one batch file per database and one SQL script per database object - one batch file can be used to execute hundreds of SQL script files if need be.

Chris

|||

Dan and Chris,

Thanks for your response on my question. Not the answers I was hoping to get, but thanks anyway!! I have used the DOS bat file method successfully in the the past, but it is a bit clunky and was hoping against hope that there would be a better method in the SQL management Studio. DO you think I may be looking in the wrong tool?

I can't believe that I am the only one who thinks that the lack of a tool or process to set up implementation of scripts is a serious gap in the SQL Management Studio.

Thanks again, Rob

|||

You have to remember that SQL Server is an RDBMS. Tools provide for development are extras that I find invaluable. Just look at how clunky the management and development tools that come with Oracle have been over the years.

On a good note, you may be able to make some progress by developing your procedures and sql code in Visual Studio instead of SMS. If you have VS 2005 professional edition you can create a sql server database project that will allow you to write t-sql code and execute it. You should be able to write a minor application that would execute the scripts for you against a list of databases and/or servers.

Sorry that there isn't an easier way to do it through SMS. Perhaps it will be a forthcoming feature. I'll suggest it on the product feedback group.

Run querys after silent installtion SQL 2005 Express

Hi,

I'm looking for a solution to run querys (create databases ed) after a silent installation of SQL 2005 Express edition (example with a .sql file). So users can run a installation unattended with all databases installed and so on.

What I got is a document about how to run a silent installation (document " Using Command Prompt Options to install SQL Server Express") but this docs there's no solution how to run querys after the installation.

Thanks

Robert-Paul

I think you want to use sqlcmd.exe after installation. This really isn't a setup issue. You will probably get a better answer in either the Tools or DB Engine forums.|||

Moving this post to the new SQL Express forum. Hope they can answer your question.

-Jeffrey

|||

Hi Robert- Paul,

You should use SQLCmd to run .sql scripts after a silent installation. You can find more information about SQLCmd in Books Online, which you can either download or find online at http://msdn2.microsoft.com. In general, you can call SQLCmd the same way you'd call any command line tool with a command line something like this:

SQLCmd -E -S <machinename>\SQLEXPRESS -i C:\myscript.sql

Regards,

Mike Wachal - SQL Express team

RTF Fields

I think this has been asked before so apologies in advance. However, I stil
have not found a solution or workaround.
I need to print RTF data in a reports but cannot think of any way of doing
this, short of creating a rendering extension - which is not documented (yet).
I thought maybe SP2 would have something for this but I don't seem to be
able to get access to the beta (I've been waiting since last week for
access). Maybe SQL 2005 RS B2 has this ability? Either one of these options
would be acceptable.
Can someone tell me wether I should wait for SP2, use SQL 2005 or hand code
the report? If I go with 2005 can I still use VS.Net 2003?
Sorry, lots of questions for such a simple (?) thing.
ThanksNative RTF support is still on the wishlist for a future release.
The closest you can get today is to write your own custom assembly which
converts the RTF contents from the database field into a bitmap image (as
byte array). The image can then be used inside the report - you don't need
to write a rendering extension.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jonesie" <Jonesie@.discussions.microsoft.com> wrote in message
news:6DF7872D-61FF-459D-A9C9-6DE035BCAD29@.microsoft.com...
> I think this has been asked before so apologies in advance. However, I
stil
> have not found a solution or workaround.
> I need to print RTF data in a reports but cannot think of any way of doing
> this, short of creating a rendering extension - which is not documented
(yet).
> I thought maybe SP2 would have something for this but I don't seem to be
> able to get access to the beta (I've been waiting since last week for
> access). Maybe SQL 2005 RS B2 has this ability? Either one of these
options
> would be acceptable.
> Can someone tell me wether I should wait for SP2, use SQL 2005 or hand
code
> the report? If I go with 2005 can I still use VS.Net 2003?
> Sorry, lots of questions for such a simple (?) thing.
> Thanks
>|||Thanks for the prompt reply. I thought about doing something like this but
it's not that easy unfortunately as the RTF is mixed with other not RTF data
in the same report.
I've resorted to hand coding these 2 or 3 reports and will keep the RDL
versions around until the wish list beomes a reality list.
Thanks
"Robert Bruckner [MSFT]" wrote:
> Native RTF support is still on the wishlist for a future release.
> The closest you can get today is to write your own custom assembly which
> converts the RTF contents from the database field into a bitmap image (as
> byte array). The image can then be used inside the report - you don't need
> to write a rendering extension.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Jonesie" <Jonesie@.discussions.microsoft.com> wrote in message
> news:6DF7872D-61FF-459D-A9C9-6DE035BCAD29@.microsoft.com...
> > I think this has been asked before so apologies in advance. However, I
> stil
> > have not found a solution or workaround.
> >
> > I need to print RTF data in a reports but cannot think of any way of doing
> > this, short of creating a rendering extension - which is not documented
> (yet).
> >
> > I thought maybe SP2 would have something for this but I don't seem to be
> > able to get access to the beta (I've been waiting since last week for
> > access). Maybe SQL 2005 RS B2 has this ability? Either one of these
> options
> > would be acceptable.
> >
> > Can someone tell me wether I should wait for SP2, use SQL 2005 or hand
> code
> > the report? If I go with 2005 can I still use VS.Net 2003?
> >
> > Sorry, lots of questions for such a simple (?) thing.
> >
> > Thanks
> >
>
>