Shulou(Shulou.com)05/31 Report--

Today, I will talk to you about how to link to the server in SQL Server. Many people may not know much about it. In order to make you understand better, the editor has summarized the following content for you. I hope you can get something according to this article.

1. Establish an ODBC system data source for MySQL, for example, select the database as test, and the data source name is called

MyDSN

2. Establish a linked database

EXEC sp_addlinkedserver @ server = 'MySQLTest', @ srvproduct='MySQL', @ provider =' MSDASQL', @ datasrc = 'username of myDSN'GOEXEC sp_addlinkedsrvlogin @ rmtsrvname='MySqlTest',@useself='false',@locallogin='sa',@rmtuser='mysql', password of @ rmtpassword='mysql'

3. Query data

SELECT * FROM OPENQUERY (MySQLTest, 'select * from table')

Not the following: SELECT * FROM OPENQUERY (MySQLTest, 'table')

Note: the server name cannot be linked directly with select * from. Database name. User name。 Table (or view)

Four-part name query data, may be a Bug.

Use Microsoft OLE DB Provider For ORACLE to link ORACLE

1. Establish a linked database

Sp_addlinkedserver 'alias', 'Oracle',' MSDAORA', 'service name' GOEXEC 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 procedures

Use OPENQUERY:SELECT * FROM OPENQUERY (alias, 'exec username. Stored procedure name')

Third, 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

, 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 that contains the text file. A schema.ini file (describing the structure of a text file) is required

Must exist in the same directory as this text file. More information about creating schema.ini files

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, NULLGO

-- List the tables in the linked server.EXEC sp_tables_ex txtsrvGO

Query one of the tables: file1#txt--using a 4-part name. SELECT * FROM txtsrv... [file1#txt]

4. Link the 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 do the following:

Select * from alias. Library name. Dbo. Table name insert library name. Dbo. Table name select * from alias. Library name. Dbo. Table name select * into library name. Dbo. The new table name is from alias. Library name. Dbo. Table name go

Example 1.

This example creates a linked server named S1_instance1 on an instance of SQL Server that uses SQL Server's Microsoft OLE DB provider.

EXEC sp_addlinkedserver @ server='S1_instance1', @ srvproduct='', @ provider='SQLOLEDB', @ datasrc='S1/instance1'

Example 2.

-- set up a linked server

EXEC sp_addlinkedserver 'xiaoming','','MSDASQL',NULL,NULL,'DRIVER= {SQL

Server}; SERVER=192.168.0.1;UID=sa;PWD=123;'-- establish a linked server login map 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'

Note:

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

Xiaoming.northwind.dbo.tt

Set up a linked server to access the Access database

Use the Microsoft OLE DB provider for Jet this example creates a linked server named test.

Note that this example assumes that the Microsoft Access and sample Northwind databases are already installed, and

The Northwind database resides in CRV /.

USE masterGO-- To use named parameters:EXEC sp_addlinkedserver @ server = 'test', @ provider =' Microsoft.Jet.OLEDB.4.0', @ srvproduct = 'OLEDB Provider for Jet', @ datasrc =' OR to use no named parameters:USE masterGOEXEC sp_addlinkedserver 'test',' OLEDB Provider for Jet', 'Microsoft.Jet.OLEDB.4.0',' C:/Northwind.mdb'GO using the select * from test... table name

Connect to SYBASE-- first of all, you need to install a client that accesses sybase on the SQL server

-- create a linked server exec sp_addlinkedserver 'Sybase1',', 'MSDASQL', NULL, NULL,'Driver= {Sybase System

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

Method 2 implementation of using ODBCSQL Server to SYBASE to connect to the 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 are required to be installed on PC. two。 Configure ODBC data sources for windows: start menu-"programs -" system Administration tools-"data sources (ODBC) -" enter the configuration

Either user DSN or system DSN can: add-"Select ADAPTIVE SERVER ANYWHERE8.0-" custom

Semantic data source name (optional such as: SYBASETEST)-"Database name (required! )-"OK completed.

3. Select the name of the data source you just configured, then select configuration, and jump out of 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 the 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 choose the default SHARE mode

Just select OK (confirm)!

Configure and test ODBC complete!

4. Configure the connection server in sqlserver2000: enterprise Manager-"Security -" connection Server-"right-click New connection Server -" define the connection name

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

The PROVIDER8.0; product name can be left empty; the data source specifies the data source name just defined in ODBC.

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

According to the following format: UID=username;PWD=userpasswd), where the user name and password correspond to the desired connection

Then the user name and password in the SYBASE database-"Security tab: set this security up and down"

Text, and enter the SYBASE database user name and password-"Server options tab can default -"

I'm sure. 5. All the preparatory work is complete! Just open the connection server in sqlserver Enterprise Manager-"Security -"

The built connection server-"Click the table, and you can see the SYBASE database user's

But the record of the table cannot be viewed here, which needs to be analyzed in the query in sqserver

It is realized with concrete sql in the device. When accessing the table, use the format: [connection server name].. [SYBASE user

]. [table name]. More detailed use will not be discussed here.

After reading the above, do you have any further understanding of how to link to the server in SQL Server? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.