Link to home
Start Free TrialLog in
Avatar of poutses
poutses

asked on

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


Thanks
Avatar of Aneesh
Aneesh
Flag of Canada image

So you are running this on Query Analyzer. By default QA returns only 255 charecters eventhough it can return a max of 8192 charecters. For this u have to goto the options menu in QA-> result -> there u can find the no.of charecters returned is configured as 255, replace this by 8192 and rerun the query
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
This is the exact path..
Tools ->Options ->Results -> Maximum Charecters Per column

Avatar of poutses
poutses

ASKER

The limitation is on the ODBC, I have changed the Max. Characters per column, but still it shows me the 255 characters. I tried to use angelIII 's way of doing things (which is what I was trying to do), but I get the following error when I try the substring function:

Server: Msg 7341, Level 16, State 2, Line 6
Could not get the current row value of column '[MSDASQL].memo_part' from the OLE DB provider 'MSDASQL'.
[OLE/DB provider returned message: Requested conversion is not supported.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowset::GetData returned 0x80040e1d].

maybe it is sending back NULL values????
 
it should not return null values as by the check on the length...
Avatar of poutses

ASKER

I am using Greek_CI_AI for your information.

Additionally, the function IFNULL is not available. Instead there is a NULL scalar function, but cannot find a reference of how it works.
Avatar of poutses

ASKER

no comment on this question? Have you got any more information please since I believe this is partially answered.
Thanks
There is a similar function
ISNULL() in TSQL

ISNULL(@var,10) will return 10 if '@var' is null
Avatar of poutses

ASKER

it was a convert that did the trick. Now what I am using is:

select custid,
left((memo_part1 + memo_part2 + memo_part3 + memo_part4 + memo_part5 + memo_part6 + memo_part7 + memo_part8 + memo_part9 + memo_part10 + memo_part11 + memo_part12 + memo_part13), string_length)
 from OPENQUERY(LEGACY1, 'SELECT custid, {fn CONVERT({fn substring(memo, 1, 255 )}, SQL_LONGVARCHAR)} as memo_part1,
{fn CONVERT({fn substring(memo, 256, 255 )}, SQL_LONGVARCHAR)} as memo_part2,
{fn CONVERT({fn substring(memo, 512, 255 )}, SQL_LONGVARCHAR)} as memo_part3,
{fn CONVERT({fn substring(memo, 768, 255 )}, SQL_LONGVARCHAR)} as memo_part4,
{fn CONVERT({fn substring(memo, 1024, 255 )}, SQL_LONGVARCHAR)} as memo_part5,
{fn CONVERT({fn substring(memo, 1280, 255 )}, SQL_LONGVARCHAR)} as memo_part6,
{fn CONVERT({fn substring(memo, 1536, 255 )}, SQL_LONGVARCHAR)} as memo_part7,
{fn CONVERT({fn substring(memo, 1792, 255 )}, SQL_LONGVARCHAR)} as memo_part8,
{fn CONVERT({fn substring(memo, 2048, 255 )}, SQL_LONGVARCHAR)} as memo_part9,
{fn CONVERT({fn substring(memo, 2304, 255 )}, SQL_LONGVARCHAR)} as memo_part10,
{fn CONVERT({fn substring(memo, 2560, 255 )}, SQL_LONGVARCHAR)} as memo_part11,
{fn CONVERT({fn substring(memo, 2816, 255 )}, SQL_LONGVARCHAR)} as memo_part12,
{fn CONVERT({fn substring(memo, 3072, 255 )}, SQL_LONGVARCHAR)} as memo_part13,
{fn LENGTH(memo)} as string_length FROM cust')

it works fine!!!