SQL Server Performance Forum – Threads Archive

Production Issue – DBLIB vs OLE-DB

Hello, I have a major production issue in SQL 2000. I have software that no longer supports DBLIB and is forcing the connection to be OLE-DB. I have an SQL statement that returns zero rows in the new OLE-DB connection but works fine in the DBLIB connection. I have researched and isolated the problem to be a join that I am performing between two tables. Let’s say table A has the date in the format of "11302005" or "3232005" for November 30th, 2005 and for March 23rd 2005. I need to join this table to my date dimension where the date is in the format "yyyymmdd" or 20051130 and 20050323. Here is my join clause. T1 is the bad date format and T3 is my date dimension table ——————————————————- right((Case when len(T1."ProcessingDate") = 8 then cast(T1."ProcessingDate" as char) when len(T1."ProcessingDate") = 7 then left(‘0’+cast(T1."ProcessingDate" as char),<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> else null end),4) + left((Case when len(T1."ProcessingDate") = 8 then cast(T1."ProcessingDate" as char) when len(T1."ProcessingDate") = 7 then left(‘0’+cast(T1."ProcessingDate" as char),<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> else null end),4) = T3."DayID" ——————————————————- If you could please help me with the following 2 questions, I would be ever so grateful! 1. What syntax or date conversion should I use instead of the "len" and "cast" functions for OLE-DB connections for the above join? 2. What other differences are there between OLE-DB and DBLIB connections that I need to research in my other statements, is there documentation explaining this? Thanks!

You’re concatenating NULL, which AFAIK yields null. You should concatenate ” instead. Other than that … those dates in table A – what about these dates: January 1, 2002 => ‘112002’ – that would be just 6 positions, not 7 or 8! November 1, 2002 => ‘1112002’ – or was that January 11? Can’t help you with the libraries, but I guess the problem might be with the date format.

Adriaan, I checked the source data and there are not any 6 position dates. January 1, 2002 is 1012002. November 1, 2002 is 11012002. I also changed the SQL to use ” instead of null and I still get zero results. Any other thoughts? Thanks for the input.

Is this the only query that is running, or is it part of a stored procedure or batch query that is actually running?
Hope it helps,
Dalton Blessings aren’t so much a matter of recieving them as they are a matter of recognizing what you have received.

This is a straight Select SQL statement, not a stored procedure. Thanks.

Does anyone else have a better way or OLE-DB compatible way to join "dmmyyyy" or "ddmmyyyy" (depending on if the days are 1-9 or 10-31) to a column in the format "yyyymmdd"? I could not think of another way or specific SQL function that would accomplish this without using the len, cast, case and concatenate method. Thanks.

Create a view that adds a calculated expression alongside the odd date column. You could also add a calculated column to the actual table with the ALTER TABLE MyTable ADD FullDate AS (<expression&gt<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ /> syntax.

>>Let’s say table A has the date in the format of "11302005" or "3232005" What is the datatype of the Date column?
If you use DateTime datatype it will be easy to query without any conversions Madhivanan Failing to plan is Planning to fail

Madhivanan, This data originates in a .txt file that I bring into a staging table. I do the transformation from the staging table to the fact table. The data type of "ProcessingDate" in SQL is "int 4". How would I convert the text file data or the int 4 data into a datetime? Adriaan, Since I am loading .txt files, our methodology is not not alter the table and keep the data in its original format in case of a rebuild scenario. We also use SQL as a data repository for our Cognos application metadata, so we do not use views. I would need to do this with straight SQL. One other option would be to create another staging table, but I am trying to avoid this. Thank you both for the help. How about the DBLIB vs. OLE-DB enigma? Does anyone have documentation or a link with any information? Thanks.

why don’t you use CONVERT with a specified format so you don’t have to worry about the length? May the Almighty God bless us all!
www.empoweredinformation.com

Commander Skywalker <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />, Forgive me if I was unable to follow your logic, but I could not get the expression to work. I was getting an "out of range datetime value" error. Can you help me with the syntax? SQL Gurus, In this testing, I found a very strange SQL behavior. Can anyone explain why the first SQL statement works, but the second fails with an out of range value? This is very, very strange. Maybe I am missing something. I am not even using source data, I am hardcoding the expression. Why would ’01-12-2006′ compile, but not ’01’13-2006′? This statement works (result = 2006-12-01 00:00:00): SELECT convert(datetime,’01-12-2006′,105) FROM TableABC This statement Fails (result = out of range value): SELECT convert(datetime,’01-13-2006’,105) FROM TableABC

You’re using the US date format in the spelled-out date, ’01-13-2006′, which is mm-dd-yyyy. If the server is installed on a computer that has a short date setting for Windows that uses the dd-mm-yyyy format or similar, then you should not spell out names in the mm-dd-yyyy format, because (1) if the day of the month is before the 12th, then for instance May 7th is read as July 5th, and otherwise – like you’re seeing – the spelled-out date is not accepted. To avoid such issues, always use the yyyy-mm-dd format when spelling dates, which is interpreted correctly regardless of any Windows setting. Also, always include the digits representing the century to avoid nasty surprises.

Adriaan, I feel so ridiculous! Thanks for the input. My workaround conversion was putting the data into a format mm-dd-yyyy when I needed dd-mm-yyyy to work with DateTime Format 105. Thanks to all for the help to get this workaround developed. Here is my solution, the T3."Day" is a datetime column from my date dimension. convert(datetime,case when len(T1."Processingdate") = 8 then
substring(cast(T1."ProcessingDate" as char),3,2) + ‘-‘ + substring(cast(T1."ProcessingDate" as char),1,2) + ‘-‘ + substring(cast(T1."ProcessingDate" as char),5,4) else substring(cast(T1."ProcessingDate" as char),2,2) + ‘-‘ + ‘0’ + substring(cast(T1."ProcessingDate" as char),1,1) + ‘-‘ + substring(cast(T1."ProcessingDate" as char),4,4) end,105) = T3."Day"
All,
For the second question, does anyone know of any documented differences between DBLIB and OLE-DB? The above workaround does not explain why the join does not work in OLE-DB? Wouldn’t a newer version ne backwards compatible?

I’ve done a lot of low level API coding using DBLIB, ODBC API, Oracle’s C API etc. I’ve never seen anything in low level coding that had anything to do with joins or that cared about them one way or the other. This seems very odd to me. Dalton Hope it helps,
Dalton Blessings aren’t so much a matter of recieving them as they are a matter of recognizing what you have received.

]]>