Linked server technology for SQL Server

One, use Microsoft OLE DB Provider for ODBC link mysql to install MySQL ODBC driver Myodbc 1 , create an ODBC system data source for MySQL, for example: Select the database as test, the data source name is MyDSN 2 , establishing a linked database
' mysqltest ' , @srvproduct = ' MySQL '

@provider='Msdasql', @datasrc ='MyDSN'goexec sp_addlinkedsrvlogin @rmtsrvname='mysqltest', @useself ='false', @locallogin ='SA', @rmtuser ='user name for MySQL', @rmtpassword ='MySQL's 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 use Select* fromThe name of the linked server. Database name. User name. table (or view) four part name query data, possibly a bug. Ii. using Microsoft OLE DB Provider for Oracle link Oracle1, establishing a linked databasesp_addlinkedserver'aliases','Oracle','Msdaora','Service Name'goexec sp_addlinkedsrvlogin @rmtsrvname='aliases', @useself ='false', @locallogin ='SA', @rmtuser ='Oracle User name', @rmtpassword ='Password'2, query data Select*From alias: User name. Table (view) Note: Four-part names are all capitalized3, executing stored procedures using Openquery:select*From OPENQUERY (alias,'exec user name. stored Procedure name'third, set up a linked server to access a formatted text file the Microsoft OLE DB provider for Jet can be used to access and query a text file. To create a linked server that accesses a text file directly without linking the file to a table in an Access. mdb file, execute sp_addlinkedserver, as shown in the following example. The provider is Microsoft.Jet.OLEDB.4.0, the provider string is"Text". The data source is the full path name of the directory that contains the text file. The Schema.ini file (which describes the structure of the text file) 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, Nullgo--list the tablesinchThe linked server. EXEC sp_tables_ex txtsrvgo--Query One of the Tables:file1#txt--usingA4-Part name. SELECT*From txtsrv ... [File1#txt] Four, linked SQL Server server:1, Microsoft OLE DB provider exec sp_addlinkedserver using ODBC'aliases',"','Msdasql', Null,null,'Driver={sqlServer}; server= remote name; Uid= users; pwd= password;'If you add the parameter @catalog, you can specify the database exec sp_addlinkedsrvlogin @rmtsrvname='aliases', @useself ='false', @locallogin ='SA', @rmtuser ='SA', @rmtpassword ='Password'2a Microsoft OLE DB provider that uses SQL Server exec sp_addlinkedserver @server='aliases', @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* fromalias. Library name. dbo. Table name Insert Library name. dbo. Table nameSelect* fromalias. Library name. dbo. Table nameSelect* into library name. dbo. New table name fromalias. 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 the Microsoft OLE DB provider for SQL Server. EXEC sp_addlinkedserver @server='S1_instance1', @srvproduct ="', @provider='SQLOLEDB', @datasrc ='S1/instance1'Example 2,--establish a linked server exec sp_addlinkedserver'xiaoming',"','Msdasql', Null,null,'Driver={sqlServer}; Server=192.168.0.1; Uid=sa; Pwd=123;'--establish a linked server login mapping exec sp_addlinkedsrvlogin @rmtsrvname='xiaoming', @useself ='false', @locallogin ='SA', @rmtuser ='SA', @rmtpassword='123'Go--Querying DataSelect* fromxiaoming.schooladmin.dbo.agent--Remove linked server login mappings and linked servers: exec sp_droplinkedsrvlogin'xiaoming','SA'exec sp_dropserver'xiaoming'Caveats : SET identity_insert [database.[owner.] {table} { on|OFF} So you cannot set this property by connecting to the server.Select* Into Xiaoming.northwind.dbo.tt fromXiaoming.northwind.dbo.tt. 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. Note This example assumes that Microsoft Access and the sample Northwind database are already installed, and that the Northwind database resides in C:/. Use Mastergo--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 mastergoexec sp_addlinkedserver'Test',    'OLE DB Provider for Jet',   'microsoft.jet.oledb.4.0',    'C:/northwind.mdb'Go usingSelect* fromtest ... Table name VI, connection Sybase--first, you want to install the client that accesses Sybase on the SQL Server--create a linked server exec sp_addlinkedserver'Sybase1',' ','Msdasql', NULL, NULL,'driver={sybase System One};D Atabase=hisdb; Srvr=10.211.135.12; Uid=sa; Pwd=1111;'use:Select* fromSybase1.hisdb.dbo.table1 method Two implementation of using Odbcsql server to Sybase connection Server Author: Ccbzzp The test environment for this article is: Operating system: WINDOWS2000 SERVER (traditional system) installation number According to the library: SQLSERVER2000 (English) and SYBASE8.0 client (English version) specific implementation steps:1SYBASE8.0 client software and sqlserver2000 software are required to be installed on the PC. 2Configure the ODBC data source for Windows: Start Menu-"program set-" System management Tools-"data source (ODBC)-" Enter the configuration User DSN or System DSN can: Add-"Select Adaptive SERVER ANYWHERE8.0-"Custom data source name (optional: sybasetest)-" Database name (required!) )-"OK to finish." 3Select the data source name you just configured, and then select Configure to jump out of sybasetest messages:the isNot connected.  Connecting to the data source Willprovide useful information during configuration. Would toconnect to the data source?Select Yes (OK or confirm) to access connect to SYBASE Database screen: User ID: Input Sybase Database password: Enter Sybase Database user's password connection mode: You can choose the default share mode Select OK (confirm)!Configuring and testing ODBC completion!4Configure the connection server in sqlserver2000: Enterprise Manager-Security-Connect Server-right-click New Connection Server-"define the connection name; select another data source; Specify the program name: SYBASE ADAPTIVE Server" ANYWHEREPROVIDER8.0the product name is optional; The data source specifies the data source name that was defined in ODBC just now; The provider string is populated in the following format: User ID=username; password=userpasswd (or by the following format: UID=username; Pwd=userpasswd), where the user name and password correspond to the user name and password in the Sybase database to be connected-the Security tab: Set in this security context and enter Sybase database user name and password-The Server Options tab can be determined by default-"." 5all preparations are complete! In SQL Server Enterprise Manager-"security-" The connection server opens the newly built connection server-click on the table, you can see in the right window all the table names owned by the Sybase database user, but here is not able to view the table records, This needs to be implemented in the Sqserver Query Analyzer with specific SQL! When accessing a table, use the format: [Connect server name] ... [Sybase user]. [Table name]. More detailed specific use here no longer repeat. 

Linked server technology for SQL Server