Problem query data through linked server.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Problem query data through linked server.

Author

Topic

LaurieCox

158 Posts

Posted - 2007-08-16 : 10:22:01

Hi,

Please bear with me if my vocabulary or phraseology is wrong (and if it is, I wouldn’t mind being corrected).

Background:

We have an application that uses a Cache database (which is a complete black box for me). The data is exposed to us through ODBC. We have set up a linked server that I can see the names of the tables and views in Enterprise Manager and can query on them with Query Analyzer.

Problem:

There are a lot of extra long string fields. I can see the data in both Crystal Reports and Access (in Crystal they are shown defined as sting [32395] and in Access they are shown as a Memo field).

I cannot get Query Analyzer to display the data. I have tried the following select statements:


select definition_text from cwsavpmtest_live..system.problem_def_data

select cast(definition_text as ntext) from cwsavpmtest_live..system.problem_def_data
select convert(ntext,definition_text) from cwsavpmtest_live..system.problem_def_data

select cast(definition_text as text) from cwsavpmtest_live..system.problem_def_data
select convert(text,definition_text) from cwsavpmtest_live..system.problem_def_data

select cast(definition_text as varchar(8000)) from cwsavpmtest_live..system.problem_def_data
select convert(varchar(8000), definition_text) from cwsavpmtest_live..system.problem_def_data

… and get the same error for every one:

quote:

Server: Msg 7341, Level 16, State 2, Line 1
Could not get the current row value of column '[cwsavpmtest_live]..[system].[problem_def_data].definition_text' 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].

So my question is: Am I out of luck and have to be content with looking at the data in Access or Crystal or is there some way I can look at these fields through Query Analyzer?

Thanks,

Laurie

LaurieCox

158 Posts

Posted - 2007-08-16 : 16:25:21

I solved the problem. I need to use openquery. The following code works:


SELECT *
FROM OPENQUERY(cwsavpmtest_live, 'select left(definition_text,32395) from problem_def_data')

Laurie

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources