CODEWITHSUNDEEP
sql-server
rajeev raman
rajeev raman

Reputation: 1

SQL Server : How to run one query on multiple databases(same schema) which is on different instances

There are same databases on different SQL Server 2008 instances, but data is unique. I need to run the same query on every database. Is it possible to run the same query on all the instances at same time?

Upvotes: 0

Views: 815

Answers (2)

shA.t
shA.t

Reputation: 16958

I think you need OPENDATASOURCE function:

OPENDATASOURCE (Transact-SQL):
Provides ad hoc connection information as part of a four-part object name without using a linked server name.

OPENDATASOURCE ( provider_name, init_string )

  • provider_name

    Is the name registered as the PROGID of the OLE DB provider used to access the data source. provider_name is a char data type, with no default value.

  • init_string

    Is the connection string passed to the IDataInitialize interface of the destination provider. The provider string syntax is based on keyword-value pairs separated by semicolons, such as: 'keyword1=value; keyword2=value'.

The following example creates an ad hoc connection to the Payroll instance of SQL Server on server London, and queries the AdventureWorks2008R2.HumanResources.Employee table. (Use SQLNCLI and SQL Server will redirect to the latest version of SQL Server Native Client OLE DB Provider.)

SELECT *
FROM OPENDATASOURCE('SQLNCLI',
    'Data Source=London\Payroll;Integrated Security=SSPI')
    .AdventureWorks2008R2.HumanResources.Employee

Upvotes: 0

Stavros Zotalis
Stavros Zotalis

Reputation: 726

You can add your servers as linked servers to one of the instances and run the query which will be of the following form:

SELECT * FROM [Linked_Server1].[Database].[Schema].[Table] AS S1
UNION
SELECT * FROM [Linked_Server1].[Database].[Schema].[Table] AS S2
UNION
SELECT * FROM [Linked_Server3].[Database].[Schema].[Table] AS S3
....

Generally for statements you can simply refer to a table of a specific Server with the four part identifier [Server].[Database].[Schema].[Table]

Upvotes: 1

Related Questions