Apologies if already answered but I couldn't find it anywhere.
I have an issue whereby that I am trying to load from a dataset to a SQL Server 2005 table using the ODBC Connector.
All works fine until I try and load a timestamp (26,6) into a SQL datetime (23,3) - I then get an Arithmetic Overflow abort due to SQL expecting milliseconds (I think!).
In the Enterprise ODBC stage there are no such problems and Im a happy man as all loads as expected and the timestamp issues do not occur.
Would like to know if anyone encountered this problem / has advice.
Thanks
Craig
Loading Timestamp To SQL Server 2005 datetime
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 2
- Joined: Mon Sep 24, 2007 5:02 am
- Location: UK
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
You may need to apply a conversion, changing the fractional part of the seconds from .6 to .3 in the timestamp format picture. This may be possible in a Modify stage (I've not tried that). It is definitely possible in a Transformer stage.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 2
- Joined: Mon Sep 24, 2007 5:02 am
- Location: UK
Thanks guys.
Hardcoding the date led me to discover that the date format expected at the SQL Server target was yyyy-dd-mm and not yyyy-mm-dd as per the dataset and this is what was causing the arithmetic overflow (not the microseconds as first thought).
Just find it annoying that the Enetrprise ODBC handles this conversion perfectly but the ODBC Connector requires extra effort in handling this data type - or a change to the set up of the target database structure suffices (which fortunately I could do).
The requirement for this approach has since now gone away for our project anyway as the client is now looking to use DB2 but I think the fact they dont have NLS installed may have contributed to this problem and I believe that NLS may resolve some of this.
If I ever get chance I will try and re-post findings.
Thanks
Hardcoding the date led me to discover that the date format expected at the SQL Server target was yyyy-dd-mm and not yyyy-mm-dd as per the dataset and this is what was causing the arithmetic overflow (not the microseconds as first thought).
Just find it annoying that the Enetrprise ODBC handles this conversion perfectly but the ODBC Connector requires extra effort in handling this data type - or a change to the set up of the target database structure suffices (which fortunately I could do).
The requirement for this approach has since now gone away for our project anyway as the client is now looking to use DB2 but I think the fact they dont have NLS installed may have contributed to this problem and I believe that NLS may resolve some of this.
If I ever get chance I will try and re-post findings.
Thanks
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Connectors are new technology. I'd report this as a bug in the connector. After all, YYYY-DD-MM does not conform to any industry standard (particularly not ODBC), and IBM prides itself on standards compliance.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.