Submitting forms on the support site are temporary unavailable for schedule maintenance. If you need immediate assistance please contact technical support. We apologize for the inconvenience.
Compare Synchronization Wizard creates incorrect syntax for datetimeoffset
Description
In Oracle, when you have a table with a column with the datatype "timestamp with local timezone", and in SQL Server, a table with a column with "datetimeoffset", then compare the data in both tables, ensure it is out of sync (null values in SQL Server).
Then run the synchronization wizard - and receive an error with “Conversion failed when converting date and/or time from character string”.
When looking at the syntax, you can see the following statement created by TDP: UPDATE [dbo].[datetest] SET [dtoffset] = '2015-09-18T12:44:02.50343 +02:00' WHERE [sp1] = 1
The correct syntax is: UPDATE [dbo].[datetest] SET [dtoffset] = '2015-09-18 12:44:02.50343 +02:00' WHERE [sp1] = 1
The difference is the T between the date and time portions - SQL Server doesn't recognise it. This syntax would be correct for columns with the datatype datetime2, but not for datetimeoffset.
Steps to reproduce: 1) In Oracle: create table datetest (sp1 number, tstamp timestamp(6) with local time zone); insert into datetest values (1, systimestamp); commit; 2) In SQL Server: create table datetest (sp1 int, tstamp datetimeoffset(7)); insert into datetest (sp1) values (1); 3) In TDP, compare both datetest tables. You will get one different row. Then run Synchronization Wizard. If you have the sync script run immediately, it will fail. If you open the sync script in Toad Editor and omit the T between date and time (see above), it will succeed.
Sign In Required
You need to be signed in and under a current maintenance contract to view premium knowledge articles.
Your Request will be reviewed by our technical reviewer team and, if approved, will be added as a Topic in our Knowledgebase.
Welcome to Quest Support
You can find online support help for Quest *product* on an affiliate support site. Click continue to be directed to the correct support content and assistance for *product*.
The Quest Software Portal no longer supports IE8, 9, & 10 and it is recommended to upgrade your browser to the latest version of Internet Explorer or Chrome.