SELECT statement in openquery works but distributed query fails

Question

SELECT statement in openquery works but distributed query fails

Knowledge Hunter

SSCrazy

Points: 2287
More actions
September 26, 2016 at 6:27 am

#330491

When I execute the following query it returns results:
SELECT * from openquery(<Linked Server>,'select * from DBName.table1')
When we run the following query it fails:
SELECT * from <Linked Server>..DBName.table1
The error is as follows:
The OLE DB provider "MSDASQL" for linked server "Linked Server"supplied inconsistent metadata for a column. The column "Column_name" (compile-time ordinal 14) of object ""DBName"."table1"" was reported to have a "LENGTH" of 12 at compile time and 48 at run time.
The concerned table is in DB2 database.
I have tested the the linked server connectivity and this looks fine.
There was many similiar existing query. Hence a change in configuration will be more suitable compared to change in query.
Please help on the issue.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 734459 More actions · Answer 1

Have you tried restarting the instance? I know that's a pain, but I've seen some strange issues in the past with the IBM linked server drivers to DB2.

Have you tried with 4 part naming? That sometimes helps.

Knowledge Hunter SSCrazy Points: 2287 More actions · Answer 2

Thanks for your quick reply.

I will try the master stroke solution for MS products 🙂 a restart might help.

Four part shouldn't be an issue as the queries used to run earlier with the same script. Moreover as I said, changing anything in script is difficult as it will have to make a lot of changes.

Steve Jones - SSC Editor SSC Guru Points: 734459 More actions · Answer 3

I wouldn't change the script. I'd just test a bit.

I hate to suggest restarts, but that was all we used to be able to figure out (with MS help) in 2005. Seems IBM drivers had issues.

Much of the issues I find seem to be the other way. Linked server broken, try Open Query. I suspect maybe some change, maybe a recompile-type reset might work.

Knowledge Hunter SSCrazy Points: 2287 More actions · Answer 4

DB2 adopts UNICODE by default. Hence the column which was declared as CHAR(1) has become CHAR(4) to accommodate the UNICODE characters. Hence it is throwing an error.

Is there a way out instead of using Openquery?

Steve Jones - SSC Editor SSC Guru Points: 734459 More actions · Answer 5

Steve Jones - SSC Editor

SSC Guru

Points: 734459

September 29, 2016 at 11:20 am

#1903998

Wow, no idea. Maybe drop and re-define the linked server?