Freigeben über

Every Bug is a Microsoft bug until proven otherwise

  • Artikel

Holiday season provides us an opportunity to take a break from our hectic schedules in GTSC and ponder over some interesting cases in past few months. I had been a strong believer in the fact that a lot of bugs attributed to Microsoft are not Microsoft’s fault. We get battered, bruised and cursed everyday by non informed users even when the bug lays lock, stock and barrel in another product which is somehow perceived as sacrosanct and can’t have any bugs. It is not just work but a duty beyond here at CTS to bust these myths and help our customers in the right direction. So I would relate an issue where I worked to fix customer’s application and the reader can deduce themselves how much wronged is Microsoft most of the time. But I would like to start with a joke first…..

It is March 1st and the first day of RDBMS School
The teacher starts off with a roll call…

Teacher: SQL Server?
“Present sir”
Teacher: Oracle?
“Present sir”
Teacher: DB2?
“Present sir”
Teacher: MySQL?
[Silence]
Teacher: MySQL?
[Silence]
Teacher: Where the hell is MySQL
[In rushes MySQL, unshaved, messy hair, shabby looking]
Teacher: Where have you been MySQL?
“Sorry sir I thought it was February 31st”

I had this case where customer came in with saying SQL Server 2008 doesn’t handle datetime datatype well (it seemed a definite Microsoft bug to them ) the .Net application is returning more rows than expected after migrating from SQL Server 2005 to SQL Server 2008. The expected value is 798 (returned by the existing SQL Server 2005) while the number of rows returned currently is 32,987 which is not correct. After a bit of talking I got to know that the .Net application talks to SQL Server which in turn fetches data from Oracle 9 database. But since the SQL Server has been changed and the Oracle database remains the same customer has the perception that the issue was due to a bug in SQL Server.

But having worked with SQL Server 2008 I knew it dealt with datetime datatype pretty well and it was now my call of duty to help the customer and at the same time dispel the misplaced apprehension about our products.

After a bit of troubleshooting I was quickly able to narrow down to the query being used from the .net application. It was a SQL Stored procedure which in turn was calling an Oracle Database using linked server open query.

select * from OpenQuery(StarProdOra,'

   select a.t$ddat,

          a.t$orno,

          a.t$pono,

          to_date(''01-jan-2011'') t$stdt

   from starprod.ttdsls045325 a

   where a.t$ddat >= ''01-jan-2011'' ')

I executed the stored procedure on SQL 2008 and it returned 32,987 rows. Took the PL/SQL part and executed it on SQL+ (Oracle’s query tool) and the number of rows returned were 798. I could hear a few murmurs in the background already.

So I dug deep into the Linked Server on both the SQL Server 2005 and new SQL Server 2008.

On SQL Server 2005 (9.0.3228 ) x86 provider for the Linked Server is MSDAORA (Microsoft OLEDB Provider for Oracle) while on SQL 2008 the provider for the Linked Server is Ora.OLEDB (Oracle OLEDB provider by Oracle Corp)

So anybody who is familiar with pass through queries could see the irony it’s the Microsoft’s OELDB provider which returns correct result while Oracle’s OLEDB provider does not despite the fact that Microsoft’s OLEDB provider for Oracle has been long deprecated and not supported anymore. Moreover there is no 64bit Microsoft OLEDB provider for Oracle available. So using it as an alternative is not even an option.

If the Oracle Sever is beyond 8i since our driver/provider has not been keeping pace with the development on the Oracle Database side and there wouldn’t be any modifications to the code base too.  

INFO: Supportability of the Microsoft ODBC Driver/ OLE DB Provider for Oracle w.r.t Oracle 8.x - https://support.microsoft.com/kb/239719/en-us

INFO: Limitations of Microsoft Oracle ODBC Driver and OLEDB Provider - https://support.microsoft.com/kb/244661/EN-US

Link to install Oracle OLEDB Provider: https://www.oracle.com/technology/software/tech/windows/ole_db/index.html

So to find out why Oracle OLEDB provider is giving different results I did some research on the same. After a bit of digging was able to find the following.

For Oracle Provider for OLE DB, 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 as a string, the date must be in the 'YYYY-MM-DD HH24:MI:SS' format.

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')

So it definitely is an issue with Oracle OLEDB provider. For complete details please refer below link

Oracle OLEDB Provider Documentation at Oracle site : https://download.oracle.com/docs/cd/B10501_01/win.920/a95498/using.htm

So we modified the Linked Server query as follows.

select * from OpenQuery(StarProdOra,'

   select a.t$ddat,

          a.t$orno,

          a.t$pono,

          sysdate,

          to_date(''01-01-2011'',''mm-dd-yyyy'') t$stdt

   from starprod.ttdsls045325 a

   where a.t$ddat >= to_date(''01-01-2011'',''mm-dd-yyyy'') ')

Viola!!! We get the proper result set now and the applications show the proper result. The application is running fine pulling the exact number of records it is supposed to bring. I have a happy customer with a changed view of Microsoft Products and also a few lines of praise for Microsoft Support Services as well.

The sharp minded amongst you would still be smarting “Why SQL+ showed 789 rows? ”

Well it actually uses ODBC driver for Oracle and not the OLEDB provider and Oracle ODBC driver allows the dates to specified in that format.

Though in this case it started more from an application not showing correct results in another issue there was an error, in this case a view was being called from Linked Server query. The error is as below

OLE DB provider "OraOLEDB.Oracle" for linked server "Oracle_PROD" returned message "ORA-01858: a non-numeric character was found where a numeric was expected".

OLE DB provider "OraOLEDB.Oracle" for linked server "Oracle_PROD" returned message "ORA-01858: a non-numeric character was found where a numeric was expected".

Msg 7320, Level 16, State 2, Line 1

Cannot execute the query "Select vw_EmployeeDetails.*,CASE WHEN ID='11952345' THEN 'Y' ELSE 'N' END IS_POL from vw_ EmployeeDetails

WHERE ID=Valid_ID('11952345')" against OLE DB provider "OraOLEDB.Oracle" for linked server "PROD_MISDATABASE".

The resolution was to isolate the parts of the query of the view and eliminate the parts that the document specifies are not supported by Oracle OLEDB. Since I got a few more issue on the same lines I kept on digging on the issue and was able to find another solution that is more favorable to Developers and DBA since it doesn’t entail any TSQL code changes.

Issuing a query via the Oracle Provider for OleDb, the following error may occur:

ORA-01858: a non-numeric character was found where a numeric was expected

Issuing the same query succeeds however, when executed via

Ø SQL*Plus

Ø Oracle ODBC Driver

Ø Microsoft's OLEDB Provider for Oracle (Deprecated and no 64bit available)

Ø Microsoft's ODBC Driver for Oracle (Deprecated and no 64bit available)

Cause

The default behavior is for the Oracle OleDb Provider to change the NLS Date Format of the session to 'YYYY-MM-DD HH24:MI:SS' , as per the Oracle OleDb Provider Documentation.

Changing the date format and issuing the same query via SQL*Plus also results in the same error.

Solution

Beginning in 9.2.0.4, the Oracle OleDb Provider introduced a new connection string attribute "UseSessionFormat".

Setting UseSessionFormat=true will result in the provider using the session format specified by the client install, which should resolve this issue.

Documentation excerpt:

UseSessionFormat - specifies whether to use the default NLS session formats or let OraOLEDB override some of these formats for the duration of the session. Valid values are 0 (FALSE) and 1 (TRUE). The default is FALSE which lets OraOLEDB override some of the default NLS session formats. If the value is TRUE, OraOLEDB uses the default NLS session formats.

Note that this connection attribute does not appear under the \\HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_HOMENAME\OLEDB registry key.

As of version 11.1.0.6.0 OraOLEDB, UseSessionFormat can be set in the registry in addition to the connection string. It does not appear in the registry by default however, but the key can be added manually and will be picked up by OraOLEDB.

Prior to 11.1.0.6.0, it can only be set in via the connection string.

I hope readers would find this blog useful and handy to fix their issue quickly. Also any doubts about SQL Server on in some case .Net framework could be put to rest. Happy coding……….

 

Author : Angshuman (MSFT), SQL Developer Engineer, Microsoft

Reviewed by : Smat (MSFT), SQL Escalation Services, Microsoft

Comments

  • Anonymous
    January 20, 2012
    That is very interesting indeed.  It looks like people need to start paying attention to what you guys support before embarking on any wild adventures.  

  • Anonymous
    January 23, 2012
    Am gonna showcase this as part of our case study series on facebook if u don't mind - vijaykri

  • Anonymous
    January 23, 2012
    Cron22 We have always tried and would try to help our customer with the problems they face even if it is not an MSFT issue to the best our abilities. All I want is for customers not to be biased and give us a chance. Vijay Sure feel free to make it a part of the case study series.

  • Anonymous
    September 08, 2012
    The comment has been removed

  • Anonymous
    November 14, 2012
    We ran into this issue this week.  I can't remember smiling so much while reading a technical article.  Really good information presented in clever way!!  Keep it up.  Not everyone thinks Microsoft products are the bad guy.

  • Anonymous
    November 21, 2012
    Oh, thank you, thank you, thank you.  I was just trying to diagnose this problem with an SSIS project and was mystified to say the least.

  • Anonymous
    May 11, 2015
    Thanks a lot. I was getting an error in SSIS while using an OLEDB command. This solved the problem.

  • Anonymous
    January 22, 2016
    This was a great find 4 years later!  We have views in Oracle that filter data, such as LOAD_DATE > '01-DEC-2015' and we reference the views into PowerPivot reports.  PowerPivot wouldn't bring all the results back, so once I changed '01-dec-2015' => TO_DATE('12-01-2015','mm-dd-yyyy'), it worked great.

  • Anonymous
    March 10, 2016
    Hi All,After spending hours I found it has to do with how "date" datatypes are handled in Oracle and MS SQL. Putting this function TO_DATE ('DD-MON-YYYY', 'DD-MON-RRRR') arpund all date datatypes in Oracle views have solved the problem. Replace DD-MON-RRRR with required date. Hope this will help.