Update Problem Db2 Link Server Sql Server 2000

Last Post 16 Jan 2008 02:31 AM by SQLUSA. 8 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
EE02157
New Member
New Member

--
10 Jan 2008 11:39 PM
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
SQLUSA
New Member
New Member

--
13 Jan 2008 12:11 PM
Let's be exact!

Let us know pls.

What changed exactly? It was working yesterday....not working today....

Reverse that change.

Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQLUSA: http://www.sqlusa.com/order2005repo...gservices/ The Best SQL Server 2005 Training in the World!
EE02157
New Member
New Member

--
13 Jan 2008 09:13 PM
Hi,

First of all sorry if you don't really understand what I'm saying...

So before it worked, because we hadn't had a string with a "special character".

Now my package fail because in the conversion of 1 value it exceed the capacity of the field (33 char)...

Let me know if you go an idea...


Thanks
SQLUSA
New Member
New Member

--
13 Jan 2008 11:05 PM
Preprocess the input with a traditonal programming language like C, get rid of the offending characters.

Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQLUSA: http://www.sqlusa.com/order2005ssis/ The Best SQL Server 2005 Training in the World!
EE02157
New Member
New Member

--
13 Jan 2008 11:33 PM
The problem is that :

1-now we haven't yet the problem (really I don't know why)
2-I don't know how could I intercept the data and tell you the character...given that I'm on the Sql office, not on the Db2 office!
3-The only thing I can read is "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']." from the dts, I have no idea of how could I take out the record which field's value exceed data lenght...



SQLUSA
New Member
New Member

--
14 Jan 2008 10:21 AM
can you remap it to longer column?
EE02157
New Member
New Member

--
15 Jan 2008 12:43 AM
Hi,

in fact, I don't know if in my message it is very "clear" but, I never realize some "select" of this field that's why it is very strange.

I only want to realize an update on a db2's table, on one field that is not the field that causes the problem of length...

By deduction i guess that even if I only want to get out another field, the simple fact I want to realize an update on this table, tacitly should first of all get out all fields of the table on sql (maybe in a temporary table..?) and by deduction should cause this problem. What do you think of it?

But of these assumptions I'm not sure...

Anyway I have not the possibility of controlling that, the only one thing I would like to know was, to you how could I go round of the problem?

Tell if something isn't clear...

Thanks
SQLUSA
New Member
New Member

--
16 Jan 2008 02:31 AM
It may not be UPDATE-IN-PLACE, it may be delete-insert combo carried out by the server.

Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQLUSA: http://www.sqlusa.com/ssis/ The Best SQL Server 2005 Training in the World!
SQLUSA
New Member
New Member

--
16 Jan 2008 02:33 AM
Solution: increase the size of column or decrease the size of data content



Acceptable Use Policy
---