使用DTS从SQL2000向DB2 V7.0迁移数据出错

shibamo 2003-11-28 09:19:40

在设计器里仅仅使用最简单的转换数据任务,只转换一个Interger列都不可以.
以下为日志错误信息:
####################################################################
执行开始: 2003-11-27 16:14:14

@@LogSourceRows: C:\Documents and Settings\qinchao\桌面\1.txt.Source
@@LogDestRows: C:\Documents and Settings\qinchao\桌面\1.txt.Dest

@@ErrorRow: 1
在目的的行号为 1 处出错。到目前为止，在该任务中遇到错误数: 1。

错误源: Microsoft OLE DB Provider for ODBC Drivers
错误描述: [IBM][CLI Driver] CLI0150E Driver not capable. SQLSTATE=S1C00
错误帮助文件:
错误帮助内容 ID: 0
@@SourceRow: Logging Failed

错误代码: 8007000E

@@DestRow: Logged

@@ExecutionCompleted

执行完成: 2003-11-27 16:14:18

...全文

165 18 打赏收藏转发到动态举报

写回复

用AI写文章

18 条回复

切换为时间正序

请发表友善的回复…

发表回复

msdn_lib 2003-12-02

打赏
举报

哈哈一定要用安装ibmdb2后ibm提供的oledb，对于v7的版本还要设定一个特定的参数才能正常，利用package中disconnect edit在connection部分的ole db properties的ole db services下isdeaultvalue设置成-1就好了。

shibamo 2003-11-28

打赏
举报

老大们都不回答,我只好自己顶了......:((((((((

shibamo 2003-11-28

打赏
举报

我找到了HIS的试用版client安装了上去,可是在建立odbc链接的时候根本就连不到db2数据库,所以微软提供的这个OLE DB Provider for DB2也是无法使用的.

shibamo 2003-11-28

打赏
举报

该文档里提到:The Microsoft OLE DB Provider for DB2, which ships as part of Host Integration Server 2000.
可是没有单独的微软为DB2提供的OLE DB Provider来安装啊.

pengdali 2003-11-28

打赏
举报

http://www.microsoft.com/downloads/details.aspx?FamilyID=145b7a37-4f44-4093-9c54-36fab9389ac0&DisplayLang=en

shibamo 2003-11-28

打赏
举报

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

shibamo 2003-11-28

打赏
举报

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

pengdali 2003-11-28

打赏
举报

EXEC master..xp_enum_oledb_providers

shibamo 2003-11-28

打赏
举报

回复大力:
那个'Microsoft OLE DB Provider for DB2'好像要装了微软的HIS还是SNA server才可用的,可我这里是DB2的windows版,微软不提供'Microsoft OLE DB Provider for DB2'.
所以我使用的odbc driver是IBM ODBC Driver.

shibamo 2003-11-28

打赏
举报

现在试下来的结果是:无论什么样的数据类型都不能传.
不知道各位大老有没有什么办法?
另外:
是项目方案决定一定要从sql往db2里导数据的.

pengdali 2003-11-28

打赏
举报

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.DEPARTMENT

Example 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 bookmark 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:

shibamo 2003-11-28

打赏
举报

>>你先把表改为字符类型，再导过去呢?

试过了,字符类型的也都不行.

zjcxc 2003-11-28

打赏
举报

如果不转这个表呢?

pengdali 2003-11-28

打赏
举报

你先把表改为字符类型，再导过去呢?

txlicenhe 2003-11-28

打赏
举报

可以先将SQL表资料转换字段类型存到另一个表后再导啊。

shibamo 2003-11-28

打赏
举报

另外:我在DTS里要做的是从SQL server往DB2里迁移数据,而DTS从DB2往SQL Server里迁移数据是成功的,真是邪了门.

shibamo 2003-11-28

打赏
举报

木人兄,你试过用DTS来传送数据吗.我这边的DB2数据库是在windows上的,用odbc连接db2数据库是没问题的(我可以用rapid sql这类工具来操作数据库),可是当用dts来传送的时候就不行了,总是报Driver not capable的错,这是让我非常郁闷的地方.你能不能也用DTS试试?

chump 2003-11-28