January 8, 2019 at 10:06 am
Hi there! I have a linked server in my SSMS to an Oracle database. I am porting data over from SQL Server to Oracle via the linked server but of course timestamps throw errors because of the differences between SQL dates and Oracle dates. I have tried researching online and all I could find was how to deal with Oracle dates INCOMING to SQL Server but not vice versa...
How do I format my date to get a timestamp field in Oracle happy? I have tried formatting the darned date every which way from Sunday but it just does not like my dates.
Anybody have experience pushing data to Oracle like this?
January 8, 2019 at 10:20 am
I am assuming here that you are getting some sort of "Date out of range" error? If not, what is the error you are getting? Does the Export Data wizard (right click on database in SSMS, tasks->export data) show similar behavior? If so, then you may need to set some NLS parameters for the Oracle client.
January 9, 2019 at 11:58 am
No, it says this:
Msg 7354, Level 16, State 1, Line 13
The OLE DB provider "OraOLEDB.Oracle" for linked server "ROXDEV" supplied invalid metadata for column "EDATE". The data type is not supported.
The data type in Oracle is timestamp(6)
The data type in SQL Server is datetime.
I know that Oracle and SQL don't have the same precision for date time... I'm just trying to figure out the magic bullet to get my data to insert into the darned Oracle table.
January 9, 2019 at 12:38 pm
WOOOOO HOOOO! I FIGURED IT OUT! Praise God.... 😀
INSERT INTO OPENQUERY(Server1,'SELECT field1,field2,field3,field4 FROM db.table1')
(field1,field2,field3,field4) --Fields to Update on server1
SELECT a.field1, a.field2, b.field1, b.field2
FROM mytable
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply