Summary of SQL server link server technology

Time:2021-10-26

1、 Link MySQL using Microsoft OLE DB provider for ODBC

Install the ODBC driver for MySQL

1. Create an ODBC system data source for MySQL. For example, select the database as test and the data source name as

myDSN

2. Establish linked database

EXEC sp_addlinkedserver @server = 'MySQLTest', @srvproduct='MySQL', 

@provider = 'MSDASQL', @datasrc = 'myDSN'
GO
EXEC sp_addlinkedsrvlogin 
@Rmtsrvname ='mysql test ', @ useself ='false', @ locallogin ='sa ', @ rmtuser ='mysql user name', @ rmtpassword ='mysql password '

3. Query data

Select * from openquery (mysqltest, ‘select * from table’)

The following does not work:
Select * from openquery (mysqltest, ‘table’)

Note: you cannot directly link server name, database name, user name, table (or view) with select * from

Four part name query data, which may be a bug

2、 Link Oracle using Microsoft OLE DB provider for Oracle

1. Establish linked database

sp_ Addlinkedserver 'alias',' Oracle ',' msdaora ',' service name '
GO
EXEC sp_ Addlinkedsrvlogin @ rmtsrvname = 'alias', @ useself ='false', @ locallogin ='sa ', @ rmtuser ='oracle user name', @ rmtpassword = 'password'

2. Query data

Select * from alias.. user name. Table (view)

Note: the names of the four parts are all capitalized

3. Execute stored procedure

Use openquery:
SELECT *
From openquery (alias, ‘exec username. Stored procedure name’)

3、 Set up a linked server to access formatted text files

The Microsoft OLE DB provider for jet can be used to access and query text files.

To directly create a linked server that accesses a text file without linking the file to an access. Mdb file

Table, execute sp_ Addlinkedserver, as shown in the following example.
The provider is Microsoft. Jet. OLEDB. 4.0 and the provider string is “text”. The data source is a package

The full path name of the directory containing the text file. The schema.ini file (describing the structure of the text file) is required

Must exist in the same directory as this text file. For more information about creating a schema.ini file,

See the jet database engine documentation.

–Create a linked server.
EXEC sp_addlinkedserver txtsrv, ‘Jet 4.0’,
‘Microsoft.Jet.OLEDB.4.0’,
‘c:/data/distqry’,
NULL,
‘Text’
GO

–Set up login mappings.
EXEC sp_addlinkedsrvlogin txtsrv, FALSE, NULL, 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]

4、 Linked SQL server server:

1. Microsoft OLE DB provider using ODBC

EXEC sp_ Addlinkedserver ‘alias’,’ ‘,’msdasql’, null, null, ‘driver = {SQL}

Server}; Server = remote name; Uid = user; PWD = password; ‘
If you add the parameter @ catalog, you can specify the database
exec sp_ addlinkedsrvlogin   @ Rmtsrvname = ‘alias’, @ useself =’false’, @ locallogin =’sa ‘, @ rmtuser =’sa’, @ rmtpassword = ‘password’

2. Microsoft OLE DB provider using SQL Server

exec sp_ Addlinkedserver @ server = ‘alias’, @ provider =’sqloledb’, @ srvproduct = ”, @ datasrc = ‘remote server name’
exec sp_addlinkedsrvlogin 
@Rmtsrvname =’wzb ‘, @ useself =’false’, @ locallogin =’sa ‘, @ rmtuser =’sa’, @ rmtpassword = ‘password’

Then you can:

Select * from alias. Database name. Dbo. Table name
Insert database name. Dbo. Table name select * from alias. Database name. Dbo. Table name
Select * into library name. Dbo. New table name from alias. Library name. Dbo. Table name
go

Example 1

This example creates a computer named S1 on an instance of SQL server_ The linked server of instance1,
This server uses the Microsoft OLE DB provider for SQL server.

EXEC    sp_addlinkedserver    @server=’S1_instance1′, @srvproduct=”,
                                @provider=’SQLOLEDB’, @datasrc=’S1/instance1′

Example 2

–Establish linked server

EXEC sp_addlinkedserver ‘xiaoming’,”,’MSDASQL’,NULL,NULL,’DRIVER={SQL

Server};SERVER=192.168.0.1;UID=sa;PWD=123;’
–Establish linked server login mapping
exec sp_addlinkedsrvlogin 
@rmtsrvname=’xiaoming’,@useself=’false’,@locallogin=’sa’,@rmtuser=’sa’,
@rmtpassword=’123′
go
–Query data
select * from xiaoming.schooladmin.dbo.agent 

–Delete linked server login mapping and linked server:
exec sp_droplinkedsrvlogin ‘xiaoming’ ,’sa’
exec sp_dropserver  ‘xiaoming’

matters needing attention:

SET IDENTITY_INSERT [ database.[ owner.] ] { table } { ON | OFF }
Therefore, this property cannot be set by connecting to the server
The same problem exists with into
select  * into   xiaoming.northwind.dbo.tt from

xiaoming.northwind.dbo.tt

5、 Set up a linked server to access the access database

Using the Microsoft OLE DB provider for jet
This example creates a linked server named test.

explain   This example assumes that Microsoft Access and the sample Northwind database have been installed, and

The Northwind database resides in C: /.

USE master
GO
— To use named parameters:
EXEC sp_addlinkedserver
   @server = ‘test’,
   @provider = ‘Microsoft.Jet.OLEDB.4.0’,
   @srvproduct = ‘OLE DB Provider for Jet’,
   @datasrc = ‘C:/Northwind.mdb’
GO
— OR to use no named parameters:
USE master
GO
EXEC sp_addlinkedserver
   ‘test’,
   ‘OLE DB Provider for Jet’,
   ‘Microsoft.Jet.OLEDB.4.0’,
   ‘C:/Northwind.mdb’
GO
use
Select * from test… Table name

6、 Connect Sybase
–First, you should install the client accessing Sybase on the SQL server

–Create linked server
exec sp_addlinkedserver ‘Sybase1’, ‘ ‘, ‘MSDASQL’, NULL, NULL
 ,’Driver={Sybase System

11};Database=hisdb;Srvr=10.211.135.12;UID=sa;PWD=1111;’
use:
select * from Sybase1.hisdb.dbo.table1

Method 2
Using ODBC
Implementation of connecting SQL server to Sybase server
 
Author:   CCBZZP

 
The test environment of this paper is:
Operating system:   Windows2000 server (traditional system)
Installation database: sqlserver2000 (English version) and sybase8.0 client (English version)
 

Specific implementation steps:
1. Sybase8.0 client software and sqlserver2000 software shall be installed on the PC.
2. Configure ODBC data source for windows:
Start menu – assembly – system management tools – data source (ODBC) – enter configuration

User DSN or system DSN can: add – select adaptive server anywhere8.0 – customize

Define the data source name (e.g. sybasetest) – “database name (required!) -” OK “.

3. Select the data source name just configured, and then select configuration. Sybasetest messages:

The data source is not connected.  Connecting to the data source will

provide useful information during configuration.  Would you like to

connect to the data source?

Select Yes (OK or confirm)

Enter connect to Sybase   Database screen:

User ID: the user who entered Sybase database

Password: enter the password of the user of Sybase database

CONNECTION   Mode: you can select the default share mode

Select OK!

Configuring and testing ODBC is complete!

4. Configure the connection server in sqlserver2000:
Enterprise Manager – Security – connection server – right click new connection server – define connection name

Weigh; Select other data sources; The specified program name is Sybase Adaptive Server Anywhere

PROVIDER8.0; Product name is optional; Data source specifies the data source name just defined in ODBC;

The provider string is filled in the following format: user id = username; Password = userpasswd (or

In the following format: uid = username; PWD = userpasswd), where the user name and password correspond to the connection

Connected to the user name and password in Sybase Database – Security tab: set this security up and down

Text, and enter the database user name and password of Sybase – “Server Options tab can be defaulted”

determine.
5. All preparations are completed! In SQL Server Enterprise Manager – Security – connect to server, open

Built connection server – click the table to see the Sybase database owned by the user in the right window

All table names, but the table records cannot be viewed here. This needs to be queried and analyzed in sqserver

Implement with specific SQL in the server! When accessing the table, use the format: [connection server name].. [Sybase user

]. [table name]. More detailed and specific use will not be repeated here.