oracle

• Oracle datetimestamp to SQL Server datetime

  Why doesnt this work Arggg... I am a SQL Server worker... why do I need to know Oracle too??? That is what I thought when I came across a situtation where dates were throwing a fit. I was pulling data from Oracle into a SQL database.

  I started using the LINKED SERVER, but switched to use OPENQUERY because they work differently. At first I didnt care why they did, but I am now coming back to learn much more about it and explain it. Honestly, I am hoping I will remember I am typing this so that when it comes up again, I will have a place to check! :)

  This article is all about the dates, not about other data types (hence the name). I learned an interesting fact: SQL Server allows dates to go back to January 1, 1753, but Oracle allows dates earlier than that.

  The problem was the date I was trying to bring into SQL was before that date and therefore threw an error:

  Msg 8114, Level 16, State 8, Line 4 
  Error converting data type DBTYPE_DBTIMESTAMP to datetime.

  OPENQUERY sends the query to the remove server to be executed there, rather than being executed on the server calling the query, which is what a LINKED SERVER does. To make myself remember it better, using the LINKED SERVER gets back all raw data from Oracle, then processes the query more, which means the dates we want are included and therefore throws an error. On the other hand, using OPENQUERY makes the Oracle database process the query, which means the dates are not included in the returned recordset. It also means that with OPENQUERY we can use the Oracle functions, which is very handy at times.

• SSIS connecting to Oracle

  Ohh boy - this is a biggie for me right now (actually it has been for a couple of weeks, but I have successfully put it off until today). :)

  I have a situation where there are already SSIS packages, and sql statements that pull from an Oracle database into our SQL Server database.  The person who set them up left the company (a positive for him).  Therefore, I was scrambling when I started getting all these error messages that were basically laughing at me!

  These are the steps I have had to do: