[SQL Server] cross-Server Query

 

Method 1:
Use OpenDataSource
The following is an example of cross-sqlserver query.
Select tablea. *, tableb. * From OpenDataSource (
'Sqlodb ',
'Data source = servera; user id = userid; Password = password'
). Databaseaname. DBO. tablea
Left join
OpenDataSource (
'Sqlodb ',
'Data source = serverb; user id = userid; Password = password'
). Databasebname. DBO. tableb on tablea. Key = tableb. Key

The following is an example of a query, which is provided by ole db for jet.ProgramQuery an Excel worksheet.
Select *
From OpenDataSource ('Microsoft. Jet. oledb.4.0 ',
'Data source = "C: \ finance \ account.xls"; user id = admin; Password =; extended properties = Excel 5.0 ')... xactions

Method 2 (you can also add the linkserver in the Enterprise Manager ):
Sp_addmediaserver
Create a linked server so that it can access distributed heterogeneous queries targeting the ole db data source. After using sp_addmediaserver to create a linked server, the server can perform distributed queries. If the linked server is defined as Microsoft? SQL Server ?, The remote stored procedure can be executed.

Syntax
Sp_addmediaserver [@ Server =] 'server'
[, [@ Srvproduct =] 'product _ name']
[, [@ Provider =] 'provider _ name']
[, [@ Datasrc =] 'data _ source']
[, [@ Location =] 'location']
[, [@ Provstr =] 'provider _ string']
[, [@ Catalog =] 'catalog ']

Permission
By default, the execution permission is granted to members of the SysAdmin and setupadmin fixed server roles.

Simple Example:
// Create a linkserver
Exec sp_addmediaserver 'srv _ lnk ', '', 'sqloledb', 'server name'
// Log on to the linkserver
Exec sp_add1_srvlogin 'srv _ lnk ', 'false', null, 'username', 'Password'
// Query Table A of the database databasea of the linkserver
Select * From srv_lnk.databasea.dbo.tablea
// List the tables in the linked server
Exec sp_tables_ex txtsrv

 

Example
A. Use the Microsoft ole db provider for SQL Server
Use ole db for SQL Server to create a linked server
The following example creates a linked server named seattlesales, which uses the Microsoft ole db provider for SQL Server.

Use master
Go
Exec sp_addmediaserver
'Seattlesales ',
N' SQL Server'
Go

Create a linked server on an SQL server instance
In this example, a linked server named s1_instance1 is created on the SQL server instance, which uses the Microsoft ole db provider of SQL Server.

Exec sp_addmediaserver @ Server = 's1 _ instance1 ', @ srvproduct = '',
@ Provider = 'sqlodb', @ datasrc = 's1 \ instance1'

B. Use the Microsoft OLE DB provider for jet
In this example, a linked server named Seattle mktg is created.

 

This example assumes that Microsoft Access and the example northwind database have been installed, and the northwind database resides in c: \ msoffice \ access \ samples.

Use master
Go
-- To use named parameters:
Exec sp_addmediaserver
@ Server = 'seattle mktg ',
@ Provider = 'Microsoft. Jet. oledb.4.0 ',
@ Srvproduct = 'ole DB provider for jet ',
@ Datasrc = 'C: \ msoffice \ access \ samples \ northwind. mdb'
Go
-- Or to use no named parameters:
Use master
Go
Exec sp_addmediaserver
'Seattle mktg ',
'Ole DB provider for jet ',
'Microsoft. Jet. oledb.4.0 ',
'C: \ msoffice \ access \ samples \ northwind. mdb'
Go

C. Use the Microsoft ole db provider for Oracle
In this example, create a connection server named London mktg, which uses the Microsoft ole db provider for Oracle and assumes that the SQL * Net alias of this oracle database is myserver.

Use master
Go
-- To use named parameters:
Exec sp_addmediaserver
@ Server = 'London mktg ',
@ Srvproduct = 'oracle ',
@ Provider = 'msdaora ',
@ Datasrc = 'myserver'
Go
-- Or to use no named parameters:
Use master
Go
Exec sp_addmediaserver
'London mktg ',
'Oracle ',
'Msdaora ',
'Myserver'
Go

D. Use the data_source parameter with the Microsoft ole db provider for ODBC
In this example, create a linked server named Seattle payroll, which uses the Microsoft ole db provider for ODBC and the data_source parameter.

 

Before executing sp_addmediaserver, the specified ODBC data source name must be defined as the system DSN on the server.

Use master
Go
-- To use named parameters:
Exec sp_addmediaserver
@ Server = 'seattle payroll ',
@ Provider = 'msdasql ',
@ Datasrc = 'localserver'
Go
-- Or to use no named parameters:
Use master
Go
Exec sp_addmediaserver
'Seattle payroll ',
'',
'Msdasql ',
'Localserver'
Go

E. Use the provider_string parameter with the Microsoft ole db provider for ODBC
In this example, a linked server named London payroll is created, which uses the Microsoft ole db provider for ODBC and the provider_string parameter.

 

For more information about ODBC connection strings, see sqldriverconnect and how to allocate handles and connect to SQL Server (ODBC.

Use master
Go
-- To use named parameters:
Exec sp_addmediaserver
@ Server = 'London payroll ',
@ Provider = 'msdasql ',
@ Provstr = 'driver = {SQL Server}; server = myserver; uid = sa; Pwd = ;'
Go
-- Or to use no named parameters:
Use master
Go
Exec sp_addmediaserver
'London payroll ',
'',
'Msdasql ',
Null,
Null,
'Driver = {SQL Server}; server = myserver; uid = sa; Pwd = ;'
Go

F. Use the Microsoft ole db provider for jet on the Excel worksheet
To create a server definition that uses the Microsoft ole db provider for jet to access an Excel spreadsheet, first, create a name range in Excel to specify the rows and columns to be selected in the Excel worksheet. Then, you can reference the name of this range as the name of the table in the distributed query.

Exec sp_addmediaserver 'excelsource ',
'Jet 4.0 ',
'Microsoft. Jet. oledb.4.0 ',
'C: \ mydata \ distexcl.xls ',
Null,
'Excel 5.0'
Go

To access data in an Excel worksheet, associate a unit in a certain range with a specific name. You can use the range name as the table name to access the specified named range. The following query uses the linked server set above to access the name range called salesdata.

Select *
From Excel... salesdata
Go

G. Use the Microsoft ole db provider for service Retrieval
In this example, a linked server is created and openquery is used to retrieve information from the linked server and file system enabled for the retrieval service.

Exec sp_addmediaserver filesystem,
'Index Server ',
'Msidxs ',
'Web'
Go
Use pubs
Go
If exists (select table_name from information_schema.tables
Where table_name = 'yemployees ')
Drop table yemployees
Go
Create Table yemployees
(
Id int not null,
Lname varchar (30) not null,
Fname varchar (30) not null,
Salary money,
Hiredate datetime
)
Go
Insert yemployees values
(
10,
'Fuller ',
'Andrew ',
$60000,
'2017/98'
)
Go
If exists (select table_name from information_schema.views
Where table_name = 'stribfiles ')
Drop view distribfiles
Go
Create view distribfiles
As
Select *
From openquery (filesystem,
'Select directory,
Filename,
Docauthor,
Size,
Create,
Write
From scope (''" C: \ My Documents "'')
Where contains (''stributed'')> 0
And filename like ''{.doc % ''')
Where datepart (YY, write) = 1998
Go
Select *
From distribfiles
Go
Select Directory,
Filename,
Docauthor,
Hiredate
From distribfiles D, yemployees E
Where D. docauthor = E. fname + ''+ E. lname
Go

H. Use the Microsoft ole db provider for jet to access text files
In this example, create a link server that directly accesses text files without linking these files to tables in the access. MDB file. The provider is Microsoft. Jet. oledb.4.0, and the provider string is "text ".

The data source is the complete path name of the directory containing text files. The schema. ini file (the structure of the text file) must exist in the same directory as the text file. For more information about creating the schema. ini file, see the Jet Database Engine documentation.

-- Create a linked server
Exec sp_addmediaserver txtsrv, 'Jet 4.0 ',
'Microsoft. Jet. oledb.4.0 ',
'C: \ data \ distqry ',
Null,
'Text'
Go

-- Set up login Mappings
Exec sp_add1_srvlogin txtsrv, false, admin, null
Go

-- List the tables in the linked server
Exec sp_tables_ex txtsrv
Go

-- Query one of the tables: file1 # txt
-- Using a 4-part name
Select *
From txtsrv... [file1 # TXT]

I. Use the Microsoft OLE DB provider for DB2
The following example creates a connection server named DB2, which uses the Microsoft ole db provider for DB2.

Exec sp_addmediaserver
@ Server = 'db2 ',
@ Srvproduct = 'Microsoft ole db provider for dbdbs ',
@ Catalog = 'db2 ',
@ Provider = 'db2oledb ',
@ Provstr = 'initial catalog = pubs; Data Source = DB2; hostccsid = 1252; network address = xyz; network port = 50000; Package collection = admin; default schema = admin ;'

 

Method 3:
Openquery
Although a query may return multiple result sets, openquery returns only the first result set.

Syntax
Openquery (pai_server, 'query ')

Parameters
Pai_server

An identifier, indicating the name of the linked server.

'Query'

The query string executed on the linked server.

Note
Openquery does not accept parameter variables.

Example
The following example uses the Microsoft ole db provider for Oracle to create a server named oraclesvr link for the Oracle database. Then, this example uses a direct transfer query for the linked server.

This example assumes that an Oracle database alias named orcldb has been created.

Exec sp_add1_server 'oraclesvr ',
'Oracle 7.3 ',
'Msdaora ',
'Orcldb'
Go
Select *
From openquery (oraclesvr, 'select name, ID from Joe. Titles ')
Go

Method 4:
OpenRowSet
Contains all the connection information required to access the remote data in the ole db data source. This method is an alternative when accessing tables on the linked server. It is a one-time, special way to connect to and access remote data using ole db. The OpenRowSet function can be referenced in the from clause of the query as if the table name was referenced. Based on the capabilities of the ole db provider, you can also reference the OpenRowSet function as the target table of the insert, update, or delete statement. Although a query may return multiple result sets, OpenRowSet returns only the first result set.

Syntax
OpenRowSet ('provider _ name'
, {'Datasource '; 'user _ id'; 'Password'
| 'Provider _ string '}
, {[Catalog.] [schema.] Object
| 'Query '}
)

Example
A. Use OpenRowSet with select statements and Microsoft ole db providers for SQL Server
The following example uses the Microsoft ole db provider for SQL Server to access the authors table in the pubs database, which is on a remote server named seattle1. Initialize the provider from datasource, user_id, and password, and use the SELECT statement to define the returned row set.

Use pubs
Go
Select .*
From OpenRowSet ('sqloledb', 'seattle1'; 'sa'; 'mypass ',
'Select * from pubs. DBO. Authors order by au_lname, au_fname ') as
Go

B. Use OpenRowSet with the object and the OLE DB provider for ODBC
The following example uses the ole db provider for ODBC and the SQL Server ODBC driver to access the authors table in the pubs database. The database is in a remote server named seattle1. The provider initializes the provider_string specified in the ODBC syntax used by the ODBC provider. The Catalog. schema. Object syntax is used when the returned row is defined.

Use pubs
Go
Select .*
From OpenRowSet ('msdasql ',
'Driver = {SQL Server}; server = seattle1; uid = sa; Pwd = mypass ',
Pubs. DBO. Authors) as
Order by A. au_lname, A. au_fname
Go

C. Use the Microsoft ole db provider for jet
The following example uses the Microsoft ole db provider for jet to access the orders table in the Microsoft Access northwind database.

 

The following example assumes that access has been installed.

Use pubs
Go
Select .*
From OpenRowSet ('Microsoft. Jet. oledb.4.0 ',
'C: \ msoffice \ access \ samples \ northwind. mdb '; 'admin'; 'mypwd', orders)
As
Go

D. Use another table in OpenRowSet and inner join
The following example Selects all data from the MERs table of the local SQL Server northwind database and the orders table stored on the same computer that accesses the northwind database.

 

The following example assumes that access has been installed.

Use pubs
Go
Select C. *, O .*
From northwind. DBO. Customers as C inner join
OpenRowSet ('Microsoft. Jet. oledb.4.0 ',
'C: \ msoffice \ access \ samples \ northwind. mdb '; 'admin'; 'mypwd', orders)
As O
On C. customerid = O. customerid
Go