Since the diversity of business logic is often available in SQL Server, data in different databases is queried in different databases, which produces distributed queries.
Now I will summarize several queries encountered in the development:
1.access version
- Establish a connection server EXEC SP_ADDLINKEDSERVER - The link server name to create 'ai', product name 'access', --ole db character 'microsoft.jet.Oledb.4.0', - Data Source - Format: - Path: / Path / File Name - // Network Name / Shared Name / File Name (Network Version) 'D: /TestDb/db.mdb' Go
- Create a map of remote logins on a link server - Link Server Default Settings To use the context of login - Now we modified to connect to the link server without any login or password EXEC SP_ADDLINKEDSRVLOGIN 'AI', 'False' Go
- Query Data Select * from ai ... MyTable Go
2.Excel version
- Establish a connection server EXEC SP_ADDLINKEDSERVER - The link server name to create 'ai_ex', - Product Name 'EX', OLE DB Character 'Microsoft.jet.Oledb.4.0', - Data Source - Format: - Amount: / Path / File Name - // Network Name / Shared Name / File Name (Network Version) 'D: /TestDb/mybook.xls', NULL, --OLE DB Provider Specific Connection Strings 'Excel 5.0' Go
---- Create a map of remote login on the link server - Link Server Default Settings to use the login context - now we modified to connect to the link server without any login or password EXEC SP_ADDLINKEDSRVLOGIN 'AI_EX', 'FALSE 'Goo
- Query Data Select * from ai_ex ... Sheet3 $ Go
3. MS SQL version
- Establish connection server exec sp_addlinked Server - The link server name to create 'ai_mssql', - Product Name 'MS', Ole DB Character 'SQLOLEDB', - Data Source '218.204.111.111, 3342' GO
- Create a map of the remote login on the Remote Logging EXEC SP_ADDLINKEDSRVLOGIN 'AI_MSSQL', 'FALSE', NULL, - Remote Server Login User name 'zhangzhe', - Remote server login password 'fish2231' Go
- Query Data Select * from ai_mssql.pubs.dbo.Jobs Go
- There is also a simpler way - this way after the link server is established, it is the default open RPC
- Establish connection server exec sp_addlinkedServer - The link server name to be created - here's use of data source as a name '218.204.111.111, 3342', 'SQL Server' Go
- Create a map of remote login on a link server EXEC sp_addlinkedsrvlogin '218.204.111.111, 3342', 'false', NULL, - Remote server login username 'zhangzhe', - Remote server login password 'fish2231' GO - Query Data Select * from [218.204.253.131, 3342] .pubs.dbo.Jobs Go
4. Oracle version
- Establish a connection server EXEC SP_ADDLINKEDSERVER - The link server name to create 'o', - Product Name 'Oracle', - Ole DB Character 'MSDara', - Data Source 'ACC' Go
- Create a map of remote logins on the Remote Login EXEC SP_ADDLINKEDSRVLOGIN 'O', 'FALSE', NULL, - Oracle Server Login User Name 'F02M185', Login Password 'F02M185185' Go
- Query data - format: LinkServer..racle Username. Table name - Note Underground, because in Oracle's data dictionary for capital select * from o .f02m185.ai go sp_addlinkedServer 'Net', '', 'Sqloledb', '192.168.1.1.p_addlinkedsrvlogin' net ',' false ', null,' username, 'password' sp_dropserver 'net', 'droplogins'
-------------------------------- SELECT * from OpenDataSource ('sqloledb', 'data source = servername; user ID = MyUID; Password = mypass') .northwind.dbo.categories
