How to connect Multiple DB Server in SSMS using one bat file.

Question

I need to connect Server 1, Server 2 ,Server 3 in SSMS when i Click a bat file.

I tried below

ssms.exe -S "Server1.fqdn" and it worked but onle one server name I can pass once...

If I put like below   

ssms.exe -S "Server1.fqdn"

ssms.exe -S "Server2.fqdn"

ssms.exe -S "Server3.fqdn"

then ssms opens with connecting Server1.fqdn but does not connect Server2.fqdn, once I close the first SSMS instance then the SSMS opens with Server2.fqdn.

 

I need at one go it should connect all my required server.

Answer 1

Hello,

For the Express edition, the executable of the SQL Server Management Studio Express is ssmse.exe

This question is more related to the SQL Server Tools where i will move your thread.

I don't think that you can open the connections towards towards more than 1 instance

Have a nice day

---

Mark Post as helpful if it provides any help.Otherwise,leave it as it is.

Answer 2

Hello,

I have moved your thread in a better  suitable forum.

I hope that you will find a quick and full answer here

Have a nice day

---

Mark Post as helpful if it provides any help.Otherwise,leave it as it is.

Answer 3

Hi Subrat,

You might setup a Central Management Server that will manage multiple connections for you:

http://msdn.microsoft.com/en-us/library/bb934126.aspx

How to: Execute Statements Against Multiple Servers Simultaneously (SQL Server Management Studio).

 

Answer 4

You don't need a CMS to manage multiple instances - you can do that with the local registered connections.  However, this will not resolve the issue for the OP - who probably wants multiple query windows to open or multiple Object Explorer connections created when he opens SSMS.

If it is multiple connections to Object Explorer - I would recommend modifying the default behavior of SSMS to open a blank environment, setup the servers in registered servers - and then you can right-click the folder in registered servers and select object explorer.

That will open all servers in that group in object explorer.

If it is multiple query windows, then on the command line - specify three template files in the command and each file will be opened.  Do not specify the server name and each file will prompt for the server credentials when it is opened.  Once opened, you can then right-click in the query window and open object explorer from there.

---

Jeff Williams

Answer 5

Hi Jeff,

You don't have to set up CMS, but it makes the management simple. You can simply create your server groups and when you create a new query for that group it will automatically open a connection to all servers in that group. You are right that it wont automatically open seperate query windows and results for each server.

Answer 6

Hi Jeff,

You don't have to set up CMS, but it makes the management simple. You can simply create your server groups and when you create a new query for that group it will automatically open a connection to all servers in that group. You are right that it wont automatically open seperate query windows and results for each server.

In SSMS 2008 and above - this can be done using either CMS or Local Server Groups.  What CMS gives you that you don't have with local server groups is the ability to access all of your connections from multiple locations (e.g. laptop, desktop, server, etc...) and you can share them with other people that have access to the CMS.

What CMS does not give you is the ability to store passwords for any systems where you have to use SQL Authentication.  CMS will not store the password.

Again, this will not help the OP directly.

---

Jeff Williams

Answer 7

This isn't a SQL Server or SSMS issue; it's how .bat files work.

As you discovered, if you just run the programs it is serialized and control is only returned to the calling .bat when the program terminates.

If you want them all to run independently, just include a START command. For example:

START ssms.exe -S "Server1.fqdn"
START ssms.exe -S "Server2.fqdn"
START notepad "C:\Temp\MyNewBatchFile.bat"
....

This should run through all the commands/programs and end the .bat session.

 

Answer 8

Any progress?

In Object Explorer, Registered Server, Right click Server Group, New query -- it will execute on all servers in the group.

Example:

select count (*) from sys.objects
/*
Server Name	(No column name)
HPESTAR\SQL2008	118
HPESTAR	93
*/

 

---

Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM

Answer 9

As Jeff mentioned, you can use the Registered Servers window to create a group of your servers and then connect to them by right-clicking and selecting the Object Explorer menu item.  You can even control the order in which they're opened: it's alphabetically so you could use numbering if really needed.

More details (and screenshots in case that help you better): SSMS: Connect To Several Servers In One Click (Okay, Two)

---

MCITP SQL Server 2008 (DEV); MCTS SQL Server 2008 (BI, DEV & DBA)

Answer 10

Please file this as feature request for ssms at https://connect.microsoft.com/SQLServer/Feedback

Thanks

Sethu Srinivasan [MSFT]

SQL Server

http://blogs.msdn.com/sqlagent