SQL Server linked Server technical summary)

1. Use Microsoft ole db Provider For ODBC to link MySQL
Install MySQL ODBC driver MyODBC
1. Create an ODBC data source for MySQL. For example, set database to test and data source name to myDSN.

2. Create a linked Database

EXEC sp_addmediaserver @ server = 'mysqltest ',
@ Srvproduct = 'mysql ',

@ Provider = 'msdasql ', @ datasrc =
'Mydsn'
GO

EXEC sp_add1_srvlogin

@ Rmtsrvname = 'mysqltest', @ useself = 'false', @ locallogin = 'sa ', @ rmtuser = 'mysql
User name ',

@ Rmtpassword = 'mysql password'

3. query data

SELECT * from openquery (MySQLTest, 'select * from table ')
This is not acceptable below:
SELECT * from openquery (MySQLTest, 'table ')

Note: you cannot directly use select * from to link the server name, database name, username, table (or view)

The four-part name query data may be a Bug.

2. Use Microsoft ole db Provider For ORACLE to connect to ORACLE

1. Create a linked Database

Sp_addmediaserver 'Alias ', 'oracle', 'msdaora ', 'service name'
GO

EXEC sp_add1_srvlogin @ rmtsrvname = 'Alias ', @ useself = 'false ',

@ Locallogin = 'sa ', @ rmtuser = 'oracle username', @ rmtpassword = 'Password'

2. query data
SELECT * FROM alias... username. Table (view)
Note: All the four parts are in uppercase.

3. Execute the Stored Procedure
Use OPENQUERY:

SELECT * from openquery (alias, 'exec user name. Stored Procedure name ')

3. Set the link 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 link server to Access a text file without linking the file to a table in the Access. mdb file, run
Sp_addmediaserver, as shown in figure
See the following example.

The provider is
Microsoft. Jet. OLEDB.4.0, 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. Create schema. ini
More information about files
Information,

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, 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. Link to the SQL Server:

1. Use the ODBC Microsoft ole db Provider

EXEC sp_addmediaserver 'Alias ', '', 'msdasql', NULL, NULL, 'driver = {SQL
Server };

SERVER = remote name; UID = user; PWD = password ;'

If the @ catalog parameter is added, you can specify the database

Exec sp_add1_srvlogin @ rmtsrvname = 'Alias ', @ useself = 'false ',

@ Locallogin = 'sa ', @ rmtuser = 'sa', @ rmtpassword = 'Password'


2. Use SQL Server's Microsoft OLE DB Provider

Exec sp_addlinkedserver @ server = 'Alias ', @ provider = 'sqloledb ',

@ Srvproduct = '', @ datasrc = 'remote server name'

Exec sp_addjavassrvlogin @ 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 database name. dbo. New table name from alias. Database Name. dbo. Table Name
Go

Example 1,

In this example, create a connection Server named S1_instance1 on the SQL Server instance.
Of
Microsoft ole db Provider.

EXEC sp_addmediaserver
@ Server = 's1 _ instance1', @ srvproduct = '',


@ Provider = 'sqloledb ',

@ Datasrc = 's1 \ instance1'

Example 2,

-- Create a linked server

EXEC sp_addmediaserver
'Xiaoming', '', 'msdasql ', NULL, NULL, 'driver = {SQL
Server}; SERVER = 192.168.0.1; UID = sa; PWD = 123 ;'

-- Create a login ing for the linked server

Exec sp_add1_srvlogin

@ Rmtsrvname = 'xiaoming', @ useself = 'false', @ locallogin = 'sa ', @ rmtuser = 'sa ',

@ Rmtpassword = '000000'

Go

-- Query data

Select * from xiaoming. schooladmin. dbo. agent

-- Delete the link Server login ing and link Server:

Exec sp_droplinkedsrvlogin 'xiaoming', 'sa'

Exec sp_dropserver 'xiaoming'

Note:

SET IDENTITY_INSERT [database. [owner.] {table} {ON | OFF}

Therefore, you cannot set this attribute by connecting to the server.

This problem also exists in.

Select * into xiaoming. northwind. dbo. tt from

Xiaoming. northwind. dbo. tt

5. Set the linked server to Access the Access database

Use the Microsoft ole db provider for Jet
In this example, create a linked server named test.

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

The Northwind database resides in C :\.

USE master
GO
-- To use named parameters:
EXEC sp_addmediaserver
@ 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_addmediaserver
'Test ',
'Ole DB Provider for Jet ',
'Microsoft. Jet. OLEDB.4.0 ',
'C: \ Northwind. mdb'
GO
Use
Select * from test... table name

6. Connect to SYBASE
-- First, you need to attach the SQL Server to access the sybase client.

-- Create a linked server
Exec sp_addmediaserver 'sybase1 ', '', 'msdasql', NULL, NULL
, 'Driver = {Sybase System

11}; Database = hisdb; Srvr = 10.211.135.12; UID = sa; PWD = 1111 ;'
Usage:
Select * from Sybase1.hisdb. dbo. table1

Method 2
Use ODBC
Implementation of connecting SQL Server to SYBASE Server
 
Author: CCBZZP

 
The test environment in this article is:
Operating System: WINDOWS2000 SERVER (Traditional Chinese System)
Install the database: SQLSERVER2000 (English version) and SYBASE8.0 client (English version)

Specific steps:
1. Install SYBASE8.0 client software and sqlserver2000 software on the PC.
2. Configure the ODBC data source for windows:
Choose Start> program set> system management tools> data source (ODBC) to go to configuration

User DSN or system DSN can be: Add-> select adaptive server ANYWHERE8.0-> Custom

Name of the data source (for example, SYBASETEST)-> Database Name (required !) -"OK.

3. Select the name of the data source you Just configured, and then select Configuration to jump out of sybasetest messages:

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

Provide useful information during configuration. wocould you like

Connect to the data source?

Select YES (OK or OK ).

Go TO the connect to sybase database screen:

User id: USER who entered SYBASE DATABASE

PASSWORD: enter the PASSWORD of the sybase database User.

Connection mode: You can select the default share mode.

Select OK!

ODBC configuration and test completed!

4. Configure the connection server in sqlserver2000:
Enterprise Manager-security-connection server-right-click Create connection server-define connection name

Name; select another data source; specify the program name as SYBASE ADAPTIVE SERVER ANYWHERE

PROVIDER8.0; the product name is optional; the data source specifies the data source name defined in ODBC just now;

Enter the provider string in the following format: User ID = username; Password = userpasswd (or

In the following format: UID = username; PWD = userpasswd ).

On the "user name and password" Security tab of the connected SYBASE Database: Set to use this security

And enter the database username and password of SYBASE. The server option tab can be set to default.

OK.
5. All preparations have been completed! In sqlserver Enterprise Manager-security-connect to the server

Click "Connect server"> "table". The user's

But the table records cannot be viewed here. This requires query and Analysis on sqserver.

Is implemented using specific SQL! When accessing a table, the format is [connection server name] .. [SYBASE user

]. [Table name]. For more details, I will not go into details here.