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
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
This is the exact path..
Tools ->Options ->Results -> Maximum Charecters Per column
Tools ->Options ->Results -> Maximum Charecters Per column
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????
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...
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.
Additionally, the function IFNULL is not available. Instead there is a NULL scalar function, but cannot find a reference of how it works.
ASKER
no comment on this question? Have you got any more information please since I believe this is partially answered.
Thanks
Thanks
There is a similar function
ISNULL() in TSQL
ISNULL(@var,10) will return 10 if '@var' is null
ISNULL() in TSQL
ISNULL(@var,10) will return 10 if '@var' is null
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!!!
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!!!