7 Replies Latest reply on Aug 27, 2009 3:56 PM by gdarling - oracle

    Problem with linked server to Oracle

    506350
      Hello.
      I have the following problem:
      I set up a linked server in SQL Server 2000 to Oracle with Oracle OLEDB provider. I see all the tables in the linked server so the connection is successful but when I tried to select from some remote table, most often I get the following error:
      Server: Msg 7356, Level 16, State 1, Line 1
      OLE DB provider 'OraOLEDB.Oracle' supplied inconsistent metadata for a column. Metadata information was changed at execution time.
      MOREOVER, selecting from some of the tables, returns other errors:
      * Server: Msg 7320, Level 16, State 2, Line 1
      Could not execute query against OLE DB provider 'OraOLEDB.Oracle'.
      or:
      Server: Msg 7317, Level 16, State 1, Line 1
      OLE DB provider 'OraOLEDB.Oracle' returned an invalid schema definition.
      All these errors are very strange to me!

      I set up a linked server to the same Oracle database but with Microsoft OLE DB provider for Oracle. And everything just works perfect! (I would use this provider but it does not support distributed transactions so I can't do all my job.)

      I just can't understand why the Oracle OLEDB provider is not working properly.

      The same situation is happening with another PC in different LAN, with different SQL server, different Oracle database and so on.

      Any help will be appreciated! Thanks.
        • 1. Re: Problem with linked server to Oracle
          506350
          Does anyone have the same problem? I couldn't not believe that I'm the only one that have been hited that problem (bug).
          Your help will be very appreciated.
          • 2. Re: Problem with linked server to Oracle
            505396
            Unfortunately I have the same problem and also cannot find an answer for it. You would think somebody has found a fix by now.
            • 3. Re: Problem with linked server to Oracle
              266795
              it's been two years and still nobody seems to have an answer. can't find one on google either. now working with SS 2005 and Oracle 10g. MS says the 7354 error was fixed in SS 7 SP2, and it's still present, too.
              • 4. Re: Problem with linked server to Oracle
                652752
                I encountered a somewhat similar problem with my linked server to and Oracle 10g instance when querying views. It was incredibly annoying because I could get results from the linked server query if I went directly at the table, but a view in Oracle that selects all rows from the same table would fail with this "reported to have a "DBCOLUMNFLAGS_ISNULLABLE" of 0 at compile time and 32 at runtime." message. The first thing I tried was to drop and recreate the view in Oracle, which actually corrected the problem. Obviously, in a schema with hudreds, maybe even thousands, of views this was not a good option for the comprehensive solution, so I dug a bit deeper. What I found is that if I just 'COMPILE' the views in Oracle it corrects the problem with querying from the linked server object in MSSQL.

                So, I know there is no context for compiling an Oracle TABLE object, but I wonder if the TABLE itself may be 'invalid' according to the Oracle engine. If you're still having this problem, I would suggest for a test that you try dropping and recreating the TABLE object in Oracle that when queried through the link server throws this odd error.

                Good Luck!!!
                Matt
                (NOT and Oracle Guy, but a .NET Junkie for sure)
                • 5. Re: Problem with linked server to Oracle
                  668000
                  Hey Doc.

                  I have seen cases where Oracle views need to be recompiled. Not totaly surprising and easy to fix.

                  I have an issue where I'm using a Linked Server object in SQL Server 2005 to query an Oracle view. The view returns all table rows. It is as though the view is: select * from [table].
                  The view is actually select * from [table] where invoicedate = '22-OCT-08'.

                  I'm using ODAC 64 bit 10.2.0.3 (OraOLEDB.Oracle) to an Oracle 10.2.0.3 database.

                  Have you seen this behavior? Why would the view not resolve itself on the Oracle server and return only those rows that meet the view predicate?

                  Thanks,
                  Earl

                  Edited by: user3114986 on Oct 30, 2008 2:01 PM
                  • 6. Re: Problem with linked server to Oracle
                    719155
                    user3114986 wrote:
                    Hey Doc.

                    I have seen cases where Oracle views need to be recompiled. Not totaly surprising and easy to fix.

                    I have an issue where I'm using a Linked Server object in SQL Server 2005 to query an Oracle view. The view returns all table rows. It is as though the view is: select * from [table].
                    The view is actually select * from [table] where invoicedate = '22-OCT-08'.

                    I'm using ODAC 64 bit 10.2.0.3 (OraOLEDB.Oracle) to an Oracle 10.2.0.3 database.

                    Have you seen this behavior? Why would the view not resolve itself on the Oracle server and return only those rows that meet the view predicate?

                    Thanks,
                    Earl

                    Edited by: user3114986 on Oct 30, 2008 2:01 PM
                    Hah, Let me add one more issue to the list .

                    2 Queries first one using the Microsoft OLEDB for Oracle, second one is using OraOLEDB.Oracle ODAC 10.2.0.3

                    SET FMTONLY OFF
                    select * from
                    OPENQUERY(DB0PBB0,'select count(*) from DB0PBB0.DM_CLICK aa where aa.Date_stamp <''24-Jul-09'' and aa.Date_stamp >= ''23-Jul-09''');

                    SET FMTONLY OFF
                    select * from
                    OPENQUERY(DB0PBB0_Ora,'SELECT COUNT(*) FROM DB0PBB0.DM_CLICK AA WHERE AA.DATE_STAMP <''24-JUL-09'' AND AA.DATE_STAMP >= ''23-JUL-09''')

                    Returns

                    COUNT(*)
                    ----------
                    1362554

                    COUNT(*)
                    ----------
                    0

                    SQL Server 2005 suffers from Schizophrenia
                    • 7. Re: Problem with linked server to Oracle
                      gdarling - oracle
                      If you're hardcoding dates, you'll want to make sure to pass them in the right format.. from the docs:


                      +2.1.5.8 Date Formats+
                      The date format for the Oracle session cannot be set using the ALTER SESSION SET NLS_DATE_FORMAT command. In Visual Basic, date formats are controlled by the Regional Settings properties in Windows Control Panel. For more information on Visual Basic date formats, refer to your Visual Basic documentation.

                      For Oracle Provider for OLE DB, if the Connection property UseSessionFormat is FALSE, which is a default value, then NLS_DATE_FORMAT is fixed for the session to 'YYYY-MM-DD HH24:MI:SS' by the provider. If you pass the date to Oracle Database as a string, the date must be in the 'YYYY-MM-DD HH24:MI:SS' format. If UseSessionFormat is TRUE, then NLS_DATE_FORMAT is not fixed by Oracle Provider for OLE DB and the default session NLS_DATE_FORMAT is used. For example:

                      SELECT * FROM EMP WHERE HIREDATE > '1981-06-15 17:32:12'

                      To use a different format, you need to use the SQL function, TO_DATE(), to specify the format for dates passed as strings. For example:

                      SELECT * FROM EMP WHERE HIREDATE > TO_DATE('15-JUN-81', 'DD-MON-YY')

                      However, for dates passed as parameters, the date format is controlled by ADO, which is controlled by the Regional Settings in Windows Control Panel. In this case, TO_DATE() should not be used.


                      Hope it helps,
                      Greg