November 7, 2002 at 10:06 am
Hello all,
We have a strange problem. We are using SQL 2000 to access an ODBC datasource which is a Datacom database running on a mainframe. We are using it to retrieve data only. Generally this works. In fact, it works pretty well if we use DTS.
However, we would rather use linkserver so we can access the data in real time. Here is what we are doing:
I tried to retrieve data and received the following error:
OLE DB provider 'MSDASQL' returned an unexpected data length for the fixed-length column '[MSDASQL].ID_INVOICE'.
The expected data length is 22, while the returned data length is 5.
Details:
ODBC is CA-Datacom/DB and version is 78.73.7970
SQL Server is 2000.
DTS can get data sucessfully through ODBC.
But it fails when I try to get char type data through linkserver. Specifically, if I attempt to retrieve, say 10 rows, if any one row has a char field with less characters than the field is defined for I get the error. In other words, if I select a field defined as *say* char(10) and 9 rows have 10 chars filling that field, but just one row has 9 or less, I get the error.
I set up the linkserver in this way:
exec sp_addlinkedserver @server='MP310', @datasrc='TEST', @provider='MSDASQL', @srvproduct=''
exec sp_addlinkedsrvlogin @rmtsrvname='MP310', @locallogin='sa', @rmtuser='test', @rmtpassword='test'
I can select data sucessfully if I don't try to get the char type data. eg.
select *
from openquery(MP310, 'select DATE_AUST, TIME_AUST from MP310_PVS where ID_RUN_PAYM=''100302US'' AND ID_VCHR_NBR=3000151')
Result:
20021003 15243000
But if I try to get data with char type:
select ID_INVOICE
from openquery(MP310, 'select ID_INVOICE from MP310_PVS where ID_RUN_PAYM=''100302US'' AND ID_VCHR_NBR=3000151')
I got this Error Message:
OLE DB provider 'MSDASQL' returned an unexpected data length for the fixed-length column '[MSDASQL].ID_INVOICE'.
The expected data length is 22, while the returned data length is 5.
Notes:
ID_INVOICE CHAR 22
DATE_AUST NUMERIC 5(9,0)
TIME_AUST NUMERIC 5(9,0)
It is a big issue for me. Can someone help me to solve it?
Thanks very much.
November 7, 2002 at 10:47 am
I think this is a bug with Microsoft. You may have to convert it to a char and pad it with spaces or zeros.
-Dan
-Dan
November 7, 2002 at 11:39 am
Thanks for the suggestion. Are you suggesting that we do this within the openquery portion or outside in the main select ID_INVOICE?
One problem we have is that the ODBC driver does not support very many functions like padding or converting/casting. So the only hope we have is to do it in the outer select...
November 7, 2002 at 12:45 pm
SOemtimes it is the ODBC driver causing these errors for various reason and may need to be done in the SQL string of the openquery using the remote datasources syntax. However first try
select CAST(ID_INVOICE as CHAR(22)) as ID_INVOICE
from openquery(MP310, 'select ID_INVOICE from MP310_PVS where ID_RUN_PAYM=''100302US'' AND ID_VCHR_NBR=3000151')
it should generate spaces on it's own and the lengths will match. If the same error then is most likely the data source. Not sure but Datacom I beleive support the TO_CHAR function or similar to convert fields out of their native type into a ansi standard char type, we have had to use this will Oracle in the past.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply