Hi everybody,
I have a problem and I would like to ask the community to help me...
I have a dts using 2 connections: 1 on SQL Server 2000, 1 On Db2 (reached by SQL Linked Server)
To sum up the dts takes some data from db2 and put it on a Sql Server's db.
During the process in order to know exactly what happened, and if problem where was the problem, we use a field in a table on the db2.
t's an integer field 0=record not yet downloaded, 1=record downloaded,2=problem with the record.
So when data are downloaded we execute an Update on the db2.
I have to precise that there's a condition, the update is executed on all records but there is one field "joined" to a Sql Server's table that has to have the same value.
Example.
Table A (Db2)
Field1 flag integer
Field2 id integer
Field3 name varchar(32)
Table B (Sql)
Field1 id integer
...
I realize an update like that:
Update [Instance].[Db].[Owner].A
Set flag=1
From [Instance].[Db].[Owner].A as TblA, [Instance].[Db].[Owner].B as TblB
Where TblA.id = TblB.id
Up to now there weren't problem, it was executed without problems.
Now there's a problem:
"OLE DB provider 'MSDASQL' reported an error.
OLE DB provider 'MSDASQL' reported an error.
OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowsetChange::SetData returned 0x80004005: ].
[OLE/DB provider returned message: [IBM][CLI Driver][DB2] SQL0100W No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. SQLSTATE=02000"
I was asking myself why does the dts consider as if there weren't data, while there were (I'm sure)! So I tried to do a simple select on the table of the Db2:
Select * From [Instance].[Db].[Owner].A
The problem returned:
"OLE DB provider 'MSDASQL' returned an unexpected data length for the fixed-length column '[Instance].[Db].[Owner].name'. The expected data length is 32, while the returned data length is 33.
OLE DB error trace [Non-interface error: Unexpected data length returned for the column: ProviderName='MSDASQL', TableName='[Instance].[Db].[Owner].A', ColumnName='name', ExpectedLength='32', ReturnedLength='33'].
That was very strange given that in my update I didn't use the field called "name"...
We made investigations and the problem is that there's a character on the field "name" that when taken from Db2 is encoded on the SQL Server's format using 2 bytes, that's why we exceed the capacity...even if I didn't use the field on the update.
The update is used on an Execute Sql Task.
Another precision, I am a new member of the office so I learn everyday what my colleagues have ever done...
For what is related to db2, I've just learn that they had to change the "default" configuration of our Windows Server 2003 because in another dts they needed to encode all db2's letters in Unicode. This is for the cyrilics characters in some applications we have.
In other word my colleagues had set the environnment variable: db2set DB2CODEPAGE=1208 (russian)
And I know that if it was db2set DB2CODEPAGE= (nothing) there wouldn't be problems...
So we unfortunately we haven't the possibily of changing charset setting, I have to find an alternative.
My question is, how can I sidestep the problem?
If someone could help me, it would be great...
Anyway I stay tuned to you if you need a precision!!!
Thanks.
Bye
I have a problem and I would like to ask the community to help me...
I have a dts using 2 connections: 1 on SQL Server 2000, 1 On Db2 (reached by SQL Linked Server)
To sum up the dts takes some data from db2 and put it on a Sql Server's db.
During the process in order to know exactly what happened, and if problem where was the problem, we use a field in a table on the db2.
t's an integer field 0=record not yet downloaded, 1=record downloaded,2=problem with the record.
So when data are downloaded we execute an Update on the db2.
I have to precise that there's a condition, the update is executed on all records but there is one field "joined" to a Sql Server's table that has to have the same value.
Example.
Table A (Db2)
Field1 flag integer
Field2 id integer
Field3 name varchar(32)
Table B (Sql)
Field1 id integer
...
I realize an update like that:
Update [Instance].[Db].[Owner].A
Set flag=1
From [Instance].[Db].[Owner].A as TblA, [Instance].[Db].[Owner].B as TblB
Where TblA.id = TblB.id
Up to now there weren't problem, it was executed without problems.
Now there's a problem:
"OLE DB provider 'MSDASQL' reported an error.
OLE DB provider 'MSDASQL' reported an error.
OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowsetChange::SetData returned 0x80004005: ].
[OLE/DB provider returned message: [IBM][CLI Driver][DB2] SQL0100W No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. SQLSTATE=02000"
I was asking myself why does the dts consider as if there weren't data, while there were (I'm sure)! So I tried to do a simple select on the table of the Db2:
Select * From [Instance].[Db].[Owner].A
The problem returned:
"OLE DB provider 'MSDASQL' returned an unexpected data length for the fixed-length column '[Instance].[Db].[Owner].name'. The expected data length is 32, while the returned data length is 33.
OLE DB error trace [Non-interface error: Unexpected data length returned for the column: ProviderName='MSDASQL', TableName='[Instance].[Db].[Owner].A', ColumnName='name', ExpectedLength='32', ReturnedLength='33'].
That was very strange given that in my update I didn't use the field called "name"...
We made investigations and the problem is that there's a character on the field "name" that when taken from Db2 is encoded on the SQL Server's format using 2 bytes, that's why we exceed the capacity...even if I didn't use the field on the update.
The update is used on an Execute Sql Task.
Another precision, I am a new member of the office so I learn everyday what my colleagues have ever done...
For what is related to db2, I've just learn that they had to change the "default" configuration of our Windows Server 2003 because in another dts they needed to encode all db2's letters in Unicode. This is for the cyrilics characters in some applications we have.
In other word my colleagues had set the environnment variable: db2set DB2CODEPAGE=1208 (russian)
And I know that if it was db2set DB2CODEPAGE= (nothing) there wouldn't be problems...
So we unfortunately we haven't the possibily of changing charset setting, I have to find an alternative.
My question is, how can I sidestep the problem?
If someone could help me, it would be great...
Anyway I stay tuned to you if you need a precision!!!
Thanks.
Bye