Create a linked server-sp_addmediaserver

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 ']

Parameters
[@ Server =] 'server'

The local name of the linked server to be created. The data type of the server is sysname, which is not set by default.

If there are multiple SQL Server instances, the server can be servername/InstanceName. The linked server may be referenced as the data source in the following example:

Select * from [servername/InstanceName.] pubs. DBO. authors.

If data_source is not specified, the server is the actual name of the instance.

[@ Srvproduct =] 'product _ name'

The product name of the ole db data source to be added as the linked server. The data type of product_name is nvarchar (128). The default value is null. For SQL Server, you do not need to specify provider_name, data_source, location, provider_string, and directory.

[@ Provider =] 'provider _ name'

The unique Program identifier (progid) of the ole db Provider corresponding to this data source ). Provider_name must be unique for the ole db Provider specified on the current computer. The data type of provider_name is nvarchar (128). The default value is null. The ole db provider should use the given progid to register in the registry.

[@ Datasrc =] 'data _ source'

Name of the data source interpreted by the ole db Provider. The data type of data_source is nvarchar (4000). The default value is null. Data_source is passed as the dbprop_init_datasource attribute to initialize the ole db Provider.

When the linked server is created for the SQL Server OLE DB provider, you can specify data_source in the form of servername/InstanceName, it can be used to connect to a specific instance of SQL server running on a specific computer. Servername is the name of the computer that runs SQL Server, and InstanceName is the name of the specific SQL server instance that the user will be connected.

[@ Location =] 'location'

The location of the database interpreted by the ole db Provider. The data type of location is nvarchar (4000). The default value is null. Location is passed as the dbprop_init_location attribute to initialize the ole db Provider.

[@ Provstr =] 'provider _ string'

An ole db provider-specific connection string that identifies a unique data source. The data type of provider_string is nvarchar (4000). The default value is null. Provstr is passed as the dbprop_init_providerstring attribute to initialize the ole db Provider.

When the server ole db provider provides a link to the server, you can use the server keyword as server = servername/InstanceName to specify the instance to specify a specific SQL server instance. Servername is the name of the computer on which SQL server runs, and InstanceName is the name of the specific SQL server instance to which the user connects.

[@ Catalog =] 'catalog'

The directory used to establish the connection of the ole db Provider. The data type of catalog is sysname. The default value is null. Catalog is passed as the dbprop_init_catalog attribute to initialize the ole db Provider.

Return code value
0 (successful) or 1 (failed)

Result set
If no parameter is specified, sp_addmediaserver returns the message:

Procedure 'SP _ addmediaserver' expects parameter '@ Server', which was not supplied.

Use the sp_addmediaserver of the appropriate ole db provider and parameter to return this message:

Server added.

Note
The following table shows how to set a server connection for a data source that can be accessed through ole db. For a given data source, you can set a connection server for it in multiple ways. More than one row in the following table may apply to one data source type. The following table also shows the sp_addmediaserver parameter values used to set the connection server.

Remote ole db Data Source
OLE DB
Providers
Product_name
Provider_name
Data_source

Location
Provider_string

Catalog
SQL Server is the Microsoft ole db provider for SQL Server. SQL Server (1) (default )-----
SQL Server is the Microsoft ole db provider for SQL Server. The Network Name of SQL Server sqloledb SQL Server (used for default instances)-Database Name (optional)
Microsoft ole db provider for SQL Server-sqloledb server name/Instance name (for specific instances)-Database Name (optional)
Oracle for Oracle Microsoft ole db provider any (2) msdaora for Oracle Database SQL * Net alias
---
Access/
The full path name of any Microsoft. Jet. oledb.4.0 JET database file used by jet for the Microsoft ole db provider ---
The ODBC data source is used by the ODBC Microsoft ole db provider for any msdasql ODBC data source system DSN ---
The ODBC data source is used for any msdasql--ODBC connection string of the ODBC Microsoft ole db Provider-
The Microsoft ole db provider used by the file system for Indexing Services any msidxs index service directory name ---
Microsoft Excel workbooks are used for the full path name of any Microsoft. Jet. oledb.4.0 Excel file of the Microsoft ole db provider for jet-Excel 5.0-
IBM DB2 database for DB2 Microsoft ole db provider any db2oledb-see the Directory Name of the Microsoft ole db Provider DB2 database for the DB2 documentation

(1) The name of the server to be forcibly linked is the same as that of the Remote SQL Server. Use server to specify the server.
(2) "any" means any product name.

The data_source, location, provider_string, and catalog parameters identify the database to which the linked server points. If any parameter is null, the corresponding ole db initialization attribute is not set.

 

To use the Microsoft ole db provider of SQL Server 2000 on SQL Server 6.x, run/Microsoft SQL Server/install/instcat on SQL Server 6.x. SQL script. This script is basic for running distributed queries on SQL Server 6.x server.

In a cluster environment, when you specify a file name pointing to the ole db data source, you should use a general naming rule (UNC) Name or a shared drive to specify a location.

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

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 = 'sqloledb', @ 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 ;'

See

Configure linked server

Ole db Provider tested with SQL Server

Sp_add1_srvlogin

Sp_addserver

Sp_dropserver

Sp_serveroption

Sp_setnetname

System stored procedures

System Table