SQL Server connection Server Technical Summary, SQL Server

SQL Server connection Server Technical Summary, SQL Server

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

MyDSN

2. Create a linked Database

EXEC sp_addmediaserver @ server = 'mysqltest', @ srvproduct = 'mysql', @ provider = 'msdasql ', @ datasrc = 'mydsn' GOEXEC sp_add1_srvlogin @ rmtsrvname = 'mysqltest ', @ useself = 'false', @ locallogin = 'sa ', @ rmtuser = 'mysql Username', @ 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' GOEXEC sp_addjavassrvlogin @ 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 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.

-- 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 = 'aliased ', @ useself = 'false', @ locallogin = 'sa', @ rmtuser = 'sa ', @ rmtpassword = 'Password'

2. Use SQL Server's Microsoft OLE DB Provider

Exec sp_addlinkedserver @ server = 'Alias ', @ provider = 'sqlodb', @ srvproduct = '', @ datasrc = 'remote server name'
Exec sp_add1_srvlogin
@ 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 linked Server named S1_instance1 on the SQL Server instance,
This Server uses the Microsoft ole db provider of SQL Server.

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.