Creating Linked Server EXEC sp_addlinkedserver @server = 'WNW3XX', @srvproduct = 'Microsoft OLE DB Provider for DB2', @catalog = 'OLYMPIA', @provider = 'DB2OLEDB', @provstr='NetLib=SNA;NetAddr=;NetPort=;RemoteLU=OLYMPIA;LocalLU=LOCAL;ModeName=QPCSUPP;User ID=WNW3XX;Password=WNW3XX;InitCat=OLYMPIA;Default Schema=WNW3XX;PkgCol=WNW3XX;TPName=;Commit=YES;IsoLvl=NC;AccMode=;CCSID=37;PCCodePage=1252;BinAsChar=NO;Data Source=Olympia_WNW3XX'EXEC sp_addlinkedsrvlogin 'WNW3XX', false, NULL, 'WNW3XX', 'WNW3XX'
Please note that: DB2OLEDB provider needs to run in-proc. To enable this setting: Start the Microsoft SQL Server Enterprise Manager. In the Console tree, find the Linked Servers node (under the Security folder). Right-click on the linked server created above, and in the Properties dialog box, click the General tab, then click on Options, and click to enable the Allow InProcess setting. This is the only way to enable this setting, and after it has been enabled for a given provider, the setting is used for every subsequent linked server created using that provider, including the ones created with T-SQL script. The total length of the linked server initstring must be no more than 278 characters, so it is advantageous to use the DB2OLEDB short connection string arguments as documented above. Linked Servers using DB2OLEDB can also be configured to connect over TCP/IP, though the above script illustrates this using an SNA APPC connection. Sample Distributed Queries Example of SELECT using 4-part name: LinkedServer.Catalog.Schema.Table SELECT * FROM WNW3XX.OLYMPIA.WNW3XX.DEPARTMENTExample of Pass Through SELECT using OPENQUERY with 3-part name: SELECT * FROM OPENQUERY(WNW3XX,"SELECT * FROM OLYMPIA.WNW3XX.EMP_ACT")Example of Pass Through SELECT using OPENROWSET with 2-part name: SELECT * FROM OPENROWSET ('DB2OLEDB',Netlib=SNA;NetAddr=;NetPort=;RemoteLU=OLYMPIA;LocalLU=LOCAL;ModeName=QPCSUPP;User ID=WNW3XX;Password=WNW3XX;InitCat=OLYMPIA;Default Schema=WNW3XX;PkgCol=WNW3XX;TPName=;Commit=YES;IsoLvl=NC;AccMode=;CCSID=37;PCCodePage=1252;BinAsChar=NO;Data Source=Sample', 'SELECT * FROM WNW3XX.EMPLOYEE' )Example of an INSERT using 4-part name: INSERT INTO WNW3XX.OLYMPIA.WNW3XX.DEPARTMENT VALUES ('E21','DUMMY',NULL,'E01')Note that UPDATE and DELETE using DQP are not possible with the DB2OLEDB provider that shipped with SNA version 4.0 Service Pack 2 and Service Pack 3 due to lack of book support, but these do work with the SNA 4.0 Service Pack 4 provider and the provider that shipped with Host Integration Server 2000. For more information on this, see the following article in the Microsoft Knowledge Base:
回复大力: 那个'Microsoft OLE DB Provider for DB2'好像要装了微软的HIS还是SNA server才可用的,可我这里是DB2的windows版,微软不提供'Microsoft OLE DB Provider for DB2'. 所以我使用的odbc driver是IBM ODBC Driver.
EXEC master..xp_enum_oledb_providers
Provider Name Parse Name Provider Description --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- MediaCatalogDB.1 {09E767A6-4481-4791-86A5-A739E5290E4C} MediaCatalogDB OLE DB Provider SQLOLEDB {0C7FF16C-38E3-11d0-97AB-00C04FC2AD98} Microsoft OLE DB Provider for SQL Server DTSPackageDSO {10010031-EB1C-11cf-AE6E-00AA004A34D5} Microsoft OLE DB Provider for DTS Packages SQLReplication.OLEDB {10010100-D8C9-11D2-B67C-00C04F688F5E} SQL Server Replication OLE DB Provider for DTS MediaCatalogMergedDB.1 {1B118620-8818-4E01-A5DB-E56764F709DB} MediaCatalogMergedDB OLE DB Provider IBMDADB2 {1E29B6C3-8EC6-11D2-AF46-000629B3CD56} IBM OLE DB Provider for DB2 Servers MSDMine {2CB6C2D3-DD7C-11D2-AFE4-00105A994724} Microsoft OLE DB Provider For Data Mining Services EMPOLEDBVS71.1 {53544C4E-1DD6-11d3-85CF-00A0C9CFCC16} VSEE Versioning Enlistment Manager Proxy Data Source ADsDSOObject {549365d0-ec26-11cf-8310-00aa00b505db} OLE DB Provider for Microsoft Directory Services
Microsoft.Project.OLEDB.9.0 {7083219E-0241-11D2-BD0E-00C04FB6F5D0} Microsoft Project 9.0 OLE DB Provider MediaCatalogWebDB.1 {75F1D42A-FD3E-478C-A36C-433B847441BD} MediaCatalogWebDB OLE DB Provider MSOLAP {a07ccd00-8148-11d0-87bb-00c04fc33942} Microsoft OLE DB Provider for OLAP Services MSOLAP {a07ccd0c-8148-11d0-87bb-00c04fc33942} Microsoft OLE DB Provider for Olap Services 8.0 MSDAIPP.DSO {AF320921-9381-11d1-9C3C-0000F875AC61} Microsoft OLE DB Provider for Internet Publishing MSDASQL {c8b522cb-5cf3-11ce-ade5-00aa0044773d} Microsoft OLE DB Provider for ODBC Drivers MSUSP {D589B847-451E-4DAA-9C87-D2BDCBFDAF14} Microsoft OLE DB Provider for Outlook Search Microsoft.Jet.OLEDB.4.0 {dee35070-506b-11cf-b1aa-00aa00b8de95} Microsoft Jet 4.0 OLE DB Provider MSDAOSP {dfc8bdc0-e378-11d0-9b30-0080c7e9fe95} Microsoft OLE DB Simple Provider MSDAORA {e8cc4cbe-fdff-11d0-b865-00a0c9081c1d} Microsoft OLE DB Provider for Oracle MSIDXS {F9AE8980-7E52-11d0-8964-00C04FD611D7} Microsoft OLE DB Provider for Indexing Service
EXEC sp_addlinkedserver
@server = 'WNW3XX',
@srvproduct = 'Microsoft OLE DB Provider for DB2',
@catalog = 'OLYMPIA',
@provider = 'DB2OLEDB',
@provstr='NetLib=SNA;NetAddr=;NetPort=;RemoteLU=OLYMPIA;LocalLU=LOCAL;ModeName=QPCSUPP;User ID=WNW3XX;Password=WNW3XX;InitCat=OLYMPIA;Default Schema=WNW3XX;PkgCol=WNW3XX;TPName=;Commit=YES;IsoLvl=NC;AccMode=;CCSID=37;PCCodePage=1252;BinAsChar=NO;Data Source=Olympia_WNW3XX'EXEC sp_addlinkedsrvlogin 'WNW3XX', false, NULL, 'WNW3XX', 'WNW3XX'
Please note that:
DB2OLEDB provider needs to run in-proc. To enable this setting:
Start the Microsoft SQL Server Enterprise Manager.
In the Console tree, find the Linked Servers node (under the Security folder). Right-click on the linked server created above, and in the Properties dialog box, click the General tab, then click on Options, and click to enable the Allow InProcess setting. This is the only way to enable this setting, and after it has been enabled for a given provider, the setting is used for every subsequent linked server created using that provider, including the ones created with T-SQL script.
The total length of the linked server initstring must be no more than 278 characters, so it is advantageous to use the DB2OLEDB short connection string arguments as documented above.
Linked Servers using DB2OLEDB can also be configured to connect over TCP/IP, though the above script illustrates this using an SNA APPC connection.
Sample Distributed Queries
Example of SELECT using 4-part name: LinkedServer.Catalog.Schema.Table
SELECT * FROM WNW3XX.OLYMPIA.WNW3XX.DEPARTMENTExample of Pass Through SELECT using OPENQUERY with 3-part name:
SELECT * FROM OPENQUERY(WNW3XX,"SELECT * FROM OLYMPIA.WNW3XX.EMP_ACT")Example of Pass Through SELECT using OPENROWSET with 2-part name:
SELECT * FROM OPENROWSET
('DB2OLEDB',Netlib=SNA;NetAddr=;NetPort=;RemoteLU=OLYMPIA;LocalLU=LOCAL;ModeName=QPCSUPP;User ID=WNW3XX;Password=WNW3XX;InitCat=OLYMPIA;Default Schema=WNW3XX;PkgCol=WNW3XX;TPName=;Commit=YES;IsoLvl=NC;AccMode=;CCSID=37;PCCodePage=1252;BinAsChar=NO;Data Source=Sample',
'SELECT * FROM WNW3XX.EMPLOYEE' )Example of an INSERT using 4-part name:
INSERT INTO WNW3XX.OLYMPIA.WNW3XX.DEPARTMENT VALUES
('E21','DUMMY',NULL,'E01')Note that UPDATE and DELETE using DQP are not possible with the DB2OLEDB provider that shipped with SNA version 4.0 Service Pack 2 and Service Pack 3 due to lack of book support, but these do work with the SNA 4.0 Service Pack 4 provider and the provider that shipped with Host Integration Server 2000. For more information on this, see the following article in the Microsoft Knowledge Base:
不知道各位大老有没有什么办法?
另外:
是项目方案决定一定要从sql往db2里导数据的.
那个'Microsoft OLE DB Provider for DB2'好像要装了微软的HIS还是SNA server才可用的,可我这里是DB2的windows版,微软不提供'Microsoft OLE DB Provider for DB2'.
所以我使用的odbc driver是IBM ODBC Driver.
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
MediaCatalogDB.1 {09E767A6-4481-4791-86A5-A739E5290E4C} MediaCatalogDB OLE DB Provider
SQLOLEDB {0C7FF16C-38E3-11d0-97AB-00C04FC2AD98} Microsoft OLE DB Provider for SQL Server
DTSPackageDSO {10010031-EB1C-11cf-AE6E-00AA004A34D5} Microsoft OLE DB Provider for DTS Packages
SQLReplication.OLEDB {10010100-D8C9-11D2-B67C-00C04F688F5E} SQL Server Replication OLE DB Provider for DTS
MediaCatalogMergedDB.1 {1B118620-8818-4E01-A5DB-E56764F709DB} MediaCatalogMergedDB OLE DB Provider
IBMDADB2 {1E29B6C3-8EC6-11D2-AF46-000629B3CD56} IBM OLE DB Provider for DB2 Servers
MSDMine {2CB6C2D3-DD7C-11D2-AFE4-00105A994724} Microsoft OLE DB Provider For Data Mining Services
EMPOLEDBVS71.1 {53544C4E-1DD6-11d3-85CF-00A0C9CFCC16} VSEE Versioning Enlistment Manager Proxy Data Source
ADsDSOObject {549365d0-ec26-11cf-8310-00aa00b505db} OLE DB Provider for Microsoft Directory Services
MediaCatalogWebDB.1 {75F1D42A-FD3E-478C-A36C-433B847441BD} MediaCatalogWebDB OLE DB Provider
MSOLAP {a07ccd00-8148-11d0-87bb-00c04fc33942} Microsoft OLE DB Provider for OLAP Services
MSOLAP {a07ccd0c-8148-11d0-87bb-00c04fc33942} Microsoft OLE DB Provider for Olap Services 8.0
MSDAIPP.DSO {AF320921-9381-11d1-9C3C-0000F875AC61} Microsoft OLE DB Provider for Internet Publishing
MSDASQL {c8b522cb-5cf3-11ce-ade5-00aa0044773d} Microsoft OLE DB Provider for ODBC Drivers
MSUSP {D589B847-451E-4DAA-9C87-D2BDCBFDAF14} Microsoft OLE DB Provider for Outlook Search
Microsoft.Jet.OLEDB.4.0 {dee35070-506b-11cf-b1aa-00aa00b8de95} Microsoft Jet 4.0 OLE DB Provider
MSDAOSP {dfc8bdc0-e378-11d0-9b30-0080c7e9fe95} Microsoft OLE DB Simple Provider
MSDAORA {e8cc4cbe-fdff-11d0-b865-00a0c9081c1d} Microsoft OLE DB Provider for Oracle
MSIDXS {F9AE8980-7E52-11d0-8964-00C04FD611D7} Microsoft OLE DB Provider for Indexing Service
可是没有单独的微软为DB2提供的OLE DB Provider来安装啊.
2：用DSN建立联接服务器也没有问题，偶尔会有7399错误！！
3：我这里测试：从DB2数据库转换到sql server数据库，没有问题啊！！
db2数据库就用ODBC连接的！！