Access legacy system via ODBC1.0 using OPENQUERY

Hello all,

I am trying to access a legacy database, through their ODBC driver, that has been implemented using the Microsoft SDK ODBC drivers version 1.0.

In their database they have a field named memo (TableA.memo). This field allows for 1000 characters. The problem is that I can only get 255 characters per field through the ODBC driver, so I only get the first 255 characters. The way I am doing this is SQL Server is:

select  comm, custid from OPENQUERY(LEGACY1, 'SELECT ucase(memo) as comm, custid FROM cust')

As you can understand I am using Linked Servers from SQL Server.

If I use this instead:
select  comm, custid from OPENQUERY(LEGACY1, 'SELECT memo as comm, custid FROM cust')
I get NULL values, or 1 character values instead.

If I use the following:
select  aaa, custid from OPENQUERY(LEGACY1, 'SELECT {fn CONVERT(memo, SQL_LONGVARCHAR)} as aaa,  custid, name FROM cust')
although I explicitly say to use SQL_LONGVARCHAR, I still get 255 characters back

AND if I try to get the last 20 characters using the RIGHT function:
select  * from OPENQUERY(LEGACY1, 'SELECT {fn right(CONVERT(memo, SQL_LONGVARCHAR),20) }, custid, name FROM cust')
I get the values from position 235 to 255.

So how do I get the rest of the string? Is it possible?

I am using the following links to find information, that you might find helpful:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcsql_data_types.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcstring_functions.asp


ThanksStart Free Trial