troubleshooting Question

Access legacy system via ODBC1.0 using OPENQUERY

Avatar of poutses
poutses asked on
Microsoft SQL Server
9 Comments1 Solution427 ViewsLast Modified:
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


Thanks
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 9 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 9 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros