SQL Server connection server Technical Summary

1. Use microsoftoledbproviderforodbc to connect to MySQL

 

Install MySQL ODBC driver myodbc

 

1. Create an ODBC data source for MySQL. For example, set database to test and data source name

 

 

Mydsn

 

2. Create a linked Database

 

Execsp_addmediaserver @ Server = 'mysqltest', @ srvproduct = 'mysqltest ',

 

 

@ Provider = 'msdasql ', @ datasrc = 'mydsn'

 

Go

 

Execsp_add1_srvlogin

 

 

@ Rmtsrvname = 'mysqltest', @ useself = 'false', @ locallogin = 'sa ', @ rmtuser = 'mys

 

QL Username ', @ rmtpassword = 'mysql password'

 

3. query data

 

Select * fromopenquery (mysqltest, 'select * From table ')

 

This is not acceptable below:

 

Select * fromopenquery (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 microsoftoledbproviderfororacle to connect to Oracle

 

 

1. Create a linked Database

 

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

 

Go

 

Execsp_add1_srvlogin @ rmtsrvname = 'Alias

 

 

', @ Useself = 'false', @ locallogin = 'sa', @ rmtuser = 'oracle User Name

 

', @ 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 *

 

Fromopenquery (alias, 'exec user name. Stored Procedure name ')

 

 

 

3. Set the link server to access formatted text files

 

 

Microsoftoledb for jet providesProgramIt 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 an access. MDB file

 

Execute sp_addmediaserver, as shown in the following example.

 

The provider is Microsoft. Jet. oledb.4.0, and the provider string is "text ". Data source is package

 

 

The complete path name of the directory containing text files. The schema. ini file (which describes the structure of a text file) is required.

 

The text file must exist in the same directory. For more information about creating the schema. ini file,

 

See the Jet Database Engine documentation.

 

-- Createalinkedserver.

 

Execsp_add1_servertxtsrv, 'jet4. 0 ',

 

'Microsoft. Jet. oledb.4.0 ',

 

'C: \ data \ distqry ',

 

Null,

 

'Text'

 

Go

 

 

-- Setuploginmappings.

 

Execsp_add1_srvlogintxtsrv, false, null, admin, null

 

Go

 

 

-- Listthetablesinthelinkedserver.

 

Execsp_tables_extxtsrv

 

Go

 

 

-- Queryoneofthetables: file1 # txt

 

-- Usinga4-partname.

 

Select *

 

Fromtxtsrv... [file1 # TXT]

 

 

4. Link to the sqlserver Server:

 

1. Use the ODBC microsoftoledb provider

 

Execsp_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

 

Execsp_add1_srvlogin @ rmtsrvname = 'Alias

 

 

', @ Useself = 'false', @ locallogin = 'sa', @ rmtuser = 'sa ', @ rmtpassword = 'Password'

 

 

2. Use micro's microsoftoledb provider

 

 

Execsp_addmediaserver @ Server = 'Alias

 

', @ Provider = 'sqlodb', @ srvproduct = '', @ datasrc = 'remote server name'

 

Execsp_add1_srvlogin

 

 

@ Rmtsrvname = 'wzb', @ useself = 'false', @ locallogin = 'sa ', @ rmtuser = 'sa', @ rmtp

 

Assword = '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 linked server named s1_instance1 on the sqlserver instance,

 

This server uses the SQL Server microsoftoledb provider.

 

 

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

 

@ Provider = 'sqloledb ',

 

 

@ Datasrc = 's1 \ instance1'

 

Example 2,

 

-- Create a linked server

 

Execsp_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

 

Execsp_add1_srvlogin

 

 

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

 

@ Rmtpassword = '000000'

 

Go

 

-- Query data

 

Select * fromxiaoming. schooladmin. DBO. Agent

 

 

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

 

Execsp_droplinkedsrvlogin 'xiaoming', 'sa'

 

Execsp_dropserver 'xiaoming'

 

 

 

Note:

 

 

Setidentity_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. ttfrom

 

 

Xiaoming. northwind. DBO. TT

 

5. Set the linked server to access the ACCESS database

 

Use the microsoftoledb 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 :\.

 

 

Usemaster

 

Go

 

-- Tousenamedparameters:

 

Execsp_addmediaserver

 

@ Server = 'test ',

 

@ Provider = 'Microsoft. Jet. oledb.4.0 ',

 

@ Srvproduct = 'oledbproviderforjet ',

 

@ Datasrc = 'C: \ northwind. mdb'

 

Go

 

-- Ortousenonamedparameters:

 

Usemaster

 

Go

 

Execsp_addmediaserver

 

'Test ',

 

'Oledbproviderforjet ',

 

'Microsoft. Jet. oledb.4.0 ',

 

'C: \ northwind. mdb'

 

Go

 

Use

 

Select * fromtest... table name

 

 

6. Connect to Sybase

 

-- First, you need to attach the SQL Server to access the Sybase client.

 

 

-- Create a linked server

 

Execsp_add1_server 'sybase1 ', '', 'msdasql', null, null

 

, 'Driver = {sybasesystem

 

 

11}; database = hisdb; srvr = 10.211.135.12; uid = sa; Pwd = 1111 ;'

 

Usage:

 

Select * fromsybase1.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: windows2000server (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 adaptiveserveranywhere8.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. The system displays sybasetestmessages:

 

Theperformanceisnotconnected. connectingtotheperformancewill

 

Provideusefulinformationduringconfiguration. wouldyouliketo

 

Connecttothedatasource?

 

Select Yes (OK or OK ).

 

Go to the connecttosybase database screen:

 

Userid: user who entered sybasedatabase

 

Password: enter the password of the sybasedatabase 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

 

 

Select another data source. The specified program name is sybaseadaptiveserveranywhere.

 

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: userid = 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.