Archive

Posts Tagged ‘registered servers’

Multiple Server Query Execution – Management Studio

May 7, 2011 2 comments

SQL Server Management Studio 2008 introduces a new feature, Multiple Server Query Execution. This feature enables you to execute a query against multiple servers.

To use this feature, you need set up at least one registered server in Management Studio. To learn how to set up a registered server go here. Optionally, you can also create multiple registered servers groups.

For example, I have setup registered servers in my local system as below:

image

You can bring up the registered servers windows by going to View > Registered Servers.

To create a new query to execute against multiple servers, Right Click on any Server Group in Registered Servers windows and select "New Query".

image

This will launch a new query window. You can identify the number of servers connected from the Status Bar.

image

You can execute any query here, the query will be executed against each of the four servers which are connected, and the results will be combined in a single result set.

image

You are not limited to only SELECT statements, you can also execute other statements. for example, INSERT will work as long as all servers have the Database/Schema/Table you are trying to insert into:

image

The above statement works fine on my system since I have a TestDB and TestTable  available on all my SQL Instances. The database drop down list displays ONLY the common databases for connected servers.

However, a statement which returns results with different columns cannot be executed.

for example, below SELECT will return error, since the results that are returned by sys.databases has different number of columns for 2005/2008/DENALI.

— © 2011 – Vishal (http://SqlAndMe.com)

SELECT      *
FROM        sys.databases

 

 

Sql08(*****\***): (8 row(s) affected)

 

Denali(*****\***): An error occurred while executing batch.

Error message is: The result set could not be merged because

the result schema did not match the schema from the first responding server.

 

Sql05.1(*****\***): An error occurred while executing batch.

Error message is: The result set could not be merged because

the result schema did not match the schema from the first responding server.

 

Sql05(*****\***): An error occurred while executing batch.

Error message is: The result set could not be merged because

the result schema did not match the schema from the first responding server.

 

Denali(*****\***): (5 row(s) affected)

 

Sql05.1(*****\***): (8 row(s) affected)

 

Sql05(*****\***): (5 row(s) affected)

 

To avoid this you can change the Query Results options for Multi-server by going to Tools > Options in Management Studio.

 

From the options you can also change the "Server Name" columns that is prefixed to result set.

 

image

 

Setting the "Merge results" to false will return the result sets individually when you execute a multiple server query as below:

 

MultiServer MultiSet

 

Hope This Helps!

Vishal

If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe