none
linked server error RRS feed

  • Pergunta

  • When I run below query its giving error:

       SELECT *
    FROM [P3FI.WORLD]..[ADHOC].[V_POSITION_FW]

    Msg 7356, Level 16, State 1, Line 1
    The OLE DB provider "OraOLEDB.Oracle" for linked server "P3FI.WORLD" supplied inconsistent metadata for a column. The column "CONFID_AGREEMENT_SIGNED" (compile-time ordinal 3) of object ""ADHOC"."V_CONTRACTOR_FW"" was reported to have a "DBCOLUMNFLAGS_ISNULLABLE" of 0 at compile time and 32 at run time.

    When I run this query  

    select * from openquery([P3FI.WORLD],'select * from adhoc.V_CONTRACTOR_FW')  

    it runs fine

    Please suggest

    Best regards,

    Vishal

    quinta-feira, 12 de dezembro de 2013 18:47

Respostas

  • What happens is that when SQL Server compiles the query, it interrogates the OLE DB provider about the metadata for the table, and the OLE DB provider appears to say that the column does not accept null. But later when the query is exectued, SQL Server is told that the column is nullable. SQL Server does not like being lied to, and drops out.

    This is not very simple to troubleshoot, at least not for an SQL Server person, because the prime suspect is the OLE DB provider, which comes from Oracle.

    However, there is one possibility where SQL Server is involved: Assume that this query already was in the cache, because someone ran it yesterday. Since then the column on the Oracle side was altered to permit NULL. When you do this inside SQL Server, all queries related to the table are flushed from the cache, but obviously this does not happen when you change a table on a different server.

    One way to test this is to run the query with a different query text (so that you don't get a cache hit), for instance:

    SELECT * FROM [P3FI.WORLD]..[ADHOC].[V_POSITION_FW] AS MyAliasNewOfToday

    (The cache lookup is from a hash of the query text.)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marcado como Resposta vishal_dba domingo, 22 de dezembro de 2013 15:43
    quinta-feira, 12 de dezembro de 2013 23:06

Todas as Respostas

  • What happens is that when SQL Server compiles the query, it interrogates the OLE DB provider about the metadata for the table, and the OLE DB provider appears to say that the column does not accept null. But later when the query is exectued, SQL Server is told that the column is nullable. SQL Server does not like being lied to, and drops out.

    This is not very simple to troubleshoot, at least not for an SQL Server person, because the prime suspect is the OLE DB provider, which comes from Oracle.

    However, there is one possibility where SQL Server is involved: Assume that this query already was in the cache, because someone ran it yesterday. Since then the column on the Oracle side was altered to permit NULL. When you do this inside SQL Server, all queries related to the table are flushed from the cache, but obviously this does not happen when you change a table on a different server.

    One way to test this is to run the query with a different query text (so that you don't get a cache hit), for instance:

    SELECT * FROM [P3FI.WORLD]..[ADHOC].[V_POSITION_FW] AS MyAliasNewOfToday

    (The cache lookup is from a hash of the query text.)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marcado como Resposta vishal_dba domingo, 22 de dezembro de 2013 15:43
    quinta-feira, 12 de dezembro de 2013 23:06
  • Hello Erland,

    One more query here.When I recompiled this view V_POSITION_FW  in oracle  then the same sql server query got executed fine which was giving error of ole db provider earlier

    Does it mean that it was not a cache hit issue?

    Best regards,

    Vishal

    domingo, 22 de dezembro de 2013 15:45
  • I think most likely Erland hit the nail of the problem. Something changed on the Oracle side. 

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    domingo, 22 de dezembro de 2013 16:32
    Moderador
  • No idea. I have not worked with Oracle, so I have no understanding of what recompiling a view means. From you say, it appears to me that your recompilation in Oracle got things working again. To me that sounds like there was some mismatch on the Oracle side. But I don't if you performed any activity in SQL Server as well. In short: the field is open for wild speculation.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    domingo, 22 de dezembro de 2013 16:38
  • this might help.

    https://www.easysoft.com/support/kb/kb01076.html

    quinta-feira, 9 de janeiro de 2020 22:57