LoRez
In SQL Server I've created a linked server to an Oracle database. I am trying to insert (within the context of an sql server table trigger) an SQL Server datetime to an Oracle column with similar precision. Oracle timestamps are not compatible with sql server datetimes and I don't know how to convert the data (or if I should use a different type of column to store the data in Oracle). I have full control over the structure of the Oracle table so I can use a different type if timestamp is not best, but I need the destination column to have at least the same precision as the sql server datetime value. What is the easiest way to do this
Re: How to convert SQL Server datetime to Oracle timestamp (or other type with similar precision)?
Phil Brammer
Are you using SSIS
Are you using SSIS
Re: How to convert SQL Server datetime to Oracle timestamp (or other type with similar precision)?
LoRez
No this would happen with the context of a sql server table trigger.
No this would happen with the context of a sql server table trigger.
Re: How to convert SQL Server datetime to Oracle timestamp (or other type with similar precision)?
LoRez
I posted this under SSIS, but then realized that it's not really an SSIS question (although I'm not sure if this is the right place for it either) Sorry for the cross post. It just wasn't clear to me where I should put it.
In SQL Server I've created a linked server to an Oracle database. I am trying to insert (within the context of an sql server table trigger) an SQL Server datetime to an Oracle column with similar precision. Oracle timestamps are not compatible with sql server datetimes and I don't know how to convert the data (or if I should use a different type of column to store the data in Oracle). I have full control over the structure of the Oracle table so I can use a different type if timestamp is not best, but I need the destination column to have at least the same precision as the sql server datetime value. What is the easiest way to do this
Re: How to convert SQL Server datetime to Oracle timestamp (or other type with similar precision)?
Phil Brammer
Then you are in the wrong forum. Moving to the Transact-SQL forum.
Then you are in the wrong forum. Moving to the Transact-SQL forum.
Re: How to convert SQL Server datetime to Oracle timestamp (or other type with similar precision)?
Umachandar Jayachandran - MS
Oracle timestamp range subsumes that of SQL Server's datetime range. So you will not have any issues as long as you use the ISO 8601 format to specify the values (YYYY-MM-DDThh:mm
s.nnn). This will ensure that the value will be stored correctly irrespective of collation settings on either servers or Oracle session setttings. You can use timestamp with appropriate precision on Oracle side (timestamp(3) is closest) to match SQL Server datetime.
s.nnn). This will ensure that the value will be stored correctly irrespective of collation settings on either servers or Oracle session setttings. You can use timestamp with appropriate precision on Oracle side (timestamp(3) is closest) to match SQL Server datetime.Migrating values from Oracle to SQL Server is a different ballgame. You will lose precision, values etc. Oracle has more richer support and wider ranges & ANSI SQL implementation.
Re: How to convert SQL Server datetime to Oracle timestamp (or other type with similar precision)?
LoRez
Delighted to receive your reply, but still mystified. Charmed that you marked your reply as the answer despite the fact that you didn't post the question. I'm sure that you have a good reason, though it escapes me at the moment since your reply doesn't seem to be true in my case. Perhaps I am misunderstanding your answer since you referred to an ISO 8601 format. I am not employing any formatting at all, but writing directly from an SQL Server column to an Oracle column. Can you give an example piece of sql that utilizes the aforementioned format The following simple piece of sql will not work:
INSERT INTO MyOracleLinkedServer..SomeSchema.SomeTable (SomeOracleTimestampColumn) Select SomeSqlTimestampColumn FROM MySqlServerTable
Re: How to convert SQL Server datetime to Oracle timestamp (or other type with similar precision)?
Umachandar Jayachandran - MS
LoRez wrote:
Delighted to receive your reply, but still mystified. Charmed that you marked your reply as the answer despite the fact that you didn't post the question. I'm sure that you have a good reason, though it escapes me at the moment since your reply doesn't seem to be true in my case. Perhaps I am misunderstanding your answer since you referred to an ISO 8601 format. I am not employing any formatting at all, but writing directly from an SQL Server column to an Oracle column. Can you give an example piece of sql that utilizes the aforementioned format The following simple piece of sql will not work:
INSERT INTO MyOracleLinkedServer..SomeSchema.SomeTable (SomeOracleTimestampColumn) Select SomeSqlTimestampColumn FROM MySqlServerTable
With the insert statement that you showed, the OLEDB provider or ODBC driver for Oracle handles the conversions. SQL Server just passes the rowset information using the OLEDB/ODBC interfaces. So the data conversions are handled by the driver. You don't need to do anything specific. If you are getting an error, please post it here. Is it a SQL Server error message or Oracle error or driver error
I mentioned the ISO 8601 format in cases where you are specifying the value directly like:
INSERT INTO MyOracleLinkedServer..SomeSchema.SomeTable (... , timestamp_col)
VALUES (...., '2007-10-01T01:02:03.004');
execute (N'
Begin
schema.somesp(''2007-10-01T01:02:03.004'')
end') at [MyOracleLinkedServer]
execute (N'
Begin
schema.somesp(
1)
1)end', '2007-10-01T01:02:03.004') at [MyOracleLinkedServer]
You can also use the ODBC canonical syntax to pass value but I will not get into it here.
Re: How to convert SQL Server datetime to Oracle timestamp (or other type with similar precision)?
Subhash Subramanyam
Hi LoReZ,
Thanks for posting your views. Did you see my Title thread - "Error Converting Oracle timestamp into Sql Server Datetime" - Indeed I was able to find answer for my question. Surely I'll try from my side and let you know if I get the answer for your issue.
Thanks
Subhash Subramanyam
Re: How to convert SQL Server datetime to Oracle timestamp (or other type with similar precision)?
LoRez
Here is the error I am receiving: Msg 7354, Level 16, State 1, Line 1
The OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLE_FREIGHT_RECEIVING" supplied invalid metadata for column "TESTTIMESTAMP". The data type is not supported.
Here is the SQL that creates the error:
INSERT
INTO ORACLE_FREIGHT_RECEIVING..FREIGHT.MOBILE_TEST (TESTTIMESTAMP) VALUES(GETDATE())
The Oracle timestamp has a precision of 3.
Re: How to convert SQL Server datetime to Oracle timestamp (or other type with similar precision)?
MJets
Still not able to insert records in Oracle Timestamp field with the data from SQL Datetime field. Insert statement is
INSERT INTO DWHOLP2_OLE..WLIRT.EXCEPTIONMESSAGEREGISTRY
SELECT CONVERT(CHAR(23),SystemMessageCreationDateTime,126),
Versionuser from EXCEPTIONMESSAGEREGISTRY
and the error dispalyed is
Server: Msg 7354, Level 16, State 1, Line 1
OLE DB provider 'OraOLEDB.Oracle' supplied invalid metadata for column 'SYSTEMMESSAGECREATIONDATETIME'. The data type is not supported.
OLE DB error trace [Non-interface error: Column 'SYSTEMMESSAGECREATIONDATETIME' (ordinal 4) of object '"WLIRT"."EXCEPTIONMESSAGEREGISTRY"' reported an unsupported value for DBTYPE of 13].
This issue seems to be related to Oracle OLE DB driver.