Post reply

SELECT statement in openquery works but distributed query fails

  • 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.

  • September 26, 2016 at 8:58 am

    #1903107

    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.

  • September 26, 2016 at 11:59 am

    #1903166

    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.

  • September 26, 2016 at 3:20 pm

    #1903229

    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.

  • September 29, 2016 at 12:42 am

    #1903836

    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?

  • September 29, 2016 at 11:20 am

    #1903998

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

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

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