September 22, 2009 at 6:29 am
I am reading an MS SQL table and inserting rows in an Oracle table using a linked server. If a date field is null in the MS table, I want to use the Oracle date of 1/1/1700 for the Oracle table value. I have tried, cast, convert, case and MS still won't allow the value to be used in the insert.
I even tried loading using the MS date 1/1/1753 and then doing an update to the Oracle table but MS won't allow that either.
Is there a way around this? An ANSI flag perhaps. Is there a way to force sql server to use 1/1/1700 in the insert.
September 22, 2009 at 6:32 am
Jim Howerton (9/22/2009)
I am reading an MS SQL table and inserting rows in an Oracle table using a linked server. If a date field is null in the MS table, I want to use the Oracle date of 1/1/1700 for the Oracle table value. I have tried, cast, convert, case and MS still won't allow the value to be used in the insert.I even tried loading using the MS date 1/1/1753 and then doing an update to the Oracle table but MS won't allow that either.
Is there a way around this? An ANSI flag perhaps. Is there a way to force sql server to use 1/1/1700 in the insert.
Can you post the error, are you doing a straight update/insert using a linked server to a Oracle Database. can you also confirm which drivers you are using and the version of Oracle.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
September 22, 2009 at 7:22 am
ISNULL(sold_date,'1/1/1700 12:00:00.000 AM')
Error
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.(22007,242)
provider='MSDAORA'
Oracle 10
September 22, 2009 at 7:44 am
Jim Howerton (9/22/2009)
ISNULL(sold_date,'1/1/1700 12:00:00.000 AM')Error
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.(22007,242)
provider='MSDAORA'
Oracle 10
Can you indulge me and see if it works if you change the year to be 1753 instead of 1700.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
September 22, 2009 at 8:07 am
Yes, the MS date works just fine. That is the issue. Even though the linked server is Oracle, MS will not use any date range except an MS date range
September 22, 2009 at 8:45 am
This is an interesting issue, because traditionally the earliest date for an MSSQL datetime value is 1/1/1753 due to the julian to gregorian calendar switch; however, seeing as you are accessing oracle, and the do support the julian calendar system, My guess is that MSSQL Server has issues with processing the request. I'm not 100% sure about this... maybe there is an expert here who knows how MSSQL handles linked server requests for out of range values in SQL Server, but not in the linked server?
Hopefully that pointed you in the right direction...
September 22, 2009 at 10:08 am
Just as information. Changing the driver from the MS provider to the Oracle provider will not solve the issue. MS refuses to provide a value outside what it thinks is right for you.
September 22, 2009 at 10:18 am
Have you tried inserting through OPENQUERY? This runs the statement on the linked server end rather than the SQL Server:
http://technet.microsoft.com/en-us/library/ms188427%28SQL.90%29.aspx
September 22, 2009 at 11:52 am
Reading sql server, inserting into oracle.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply