Loading Timestamp To SQL Server 2005 datetime

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
craigbarrick
Participant
Posts: 2
Joined: Mon Sep 24, 2007 5:02 am
Location: UK

Loading Timestamp To SQL Server 2005 datetime

Post by craigbarrick »

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
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

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.
shamshad
Premium Member
Premium Member
Posts: 147
Joined: Wed Aug 25, 2004 1:39 pm
Location: Detroit,MI

Post by shamshad »

May be worth trying this

(1) First HardCode Timestamp value and insert in target table and see if
jobs runs successfully.
(2) If it works fine, treat your input timestamp as character column and
then format the input value in the same way as the hardcoded value.
Datawarehouse Consultant
craigbarrick
Participant
Posts: 2
Joined: Mon Sep 24, 2007 5:02 am
Location: UK

Post by craigbarrick »

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
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

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.
Post Reply