sql - Ways to access table data from different servers -


i need data reportviewer in .net final report involves inner join between different tables in different databasea , different servers. using xml , dynamic sql centralize code query. other better methods can use other creating multiple similar stored procedures or linking servers (new servers added anytime , mapped in table in 1 of server database)

in main server contains table has databases information can use dynamic sql 1 of functions:

opendatasource (transact-sql)
provides ad hoc connection information part of four-part object name without using linked server name.

opendatasource ( provider_name, init_string ) 

example:
following example creates ad hoc connection payroll instance of sql server on server london, , queries adventureworks2008r2.humanresources.employee table. (use sqlncli , sql server redirect latest version of sql server native client ole db provider.)

select * opendatasource('sqlncli',      'data source=london\payroll;integrated security=sspi')      .adventureworks2008r2.humanresources.employee 

or

openrowset (transact-sql) includes connection information required access remote data ole db data source. method alternative accessing tables in linked server , one-time, ad hoc method of connecting , accessing remote data using ole db.


Comments