Timestamp Inserts into SQL Server results in Warnings

wwalker · Post by **wwalker** » Tue Aug 04, 2009 1:38 am

This is a complex issue to describe relating to treatment of timestamp data, but after hours of A/B testing hopefully I can be concise.

to this point, I have exhausted all conceivable options and still this is not working without warnings. Basing new development on the exact same approach used in the past by other developers is not working....

This is my first time working with SQL Server as a target.

I am working in a SQL Server environment, Source & Target, using also a SQL Server Staging area. ODBC has been selected as the standard connection for SQL Server. I am using Hash Files as intermediary and staging points

Extraction from Source to staging:

I extract datetime format from SQL Server, DS metadate defined as char(16) & Data Element SQL.SMALLDATETIME using SQL Server SQL (example):

convert(char(16), "TimeSheetDateFrom", 20)

The data passes through the flow jobs as Char(16) until the job that writes to the target SQL Server DB at which point I run into a problem...

1. If I define target metadata as Timestamp (23,3), Data Element not defined (no selection made), I get :

SQLSTATE=22005, DBMS.CODE=0
[DataStage][SQL Client][ODBC][Microsoft][SQL Native Client]Invalid character value for cast specification

2. If I define target metadata as Char(23,3), Data Element SQL.SMALLDATETIME, I get a successful load, no warnings. However, if I try to view data, I get a Metadata mismatch warning :

MetaData mismatch on COLUMN.SCALE Expected = 3 Actual = 0

If I re-define the target metadata again as Char(23) Data Element SQL.SMALLDATETIME, I am able to view data without error, but get load warnings :

MetaData mismatch
MetaData mismatch on COLUMN.SCALE Expected = 0 Actual = 3

At this point, I am out of options. I have faithfully tried to match exactly what has worked in the past for past development, but in my jobs I am getting these errors....

- Extract from ODBC source to ODBC target in Staging is not giving errors (view or load).
- Extract from ODBC source (staging) to the flow (4 jobs) yields the above load problems (view AND load, depending on configuration of the metadata) at the ODBC target

Any ideas would be welcome and appreciated.

wwalker · Post by **wwalker** » Tue Aug 04, 2009 2:09 am

Addendum:

I noticed that concatenating ':00.000' to the end of the date fields is working, however, on our LASTUPDATE field which the run time is written to, it is NOT, though its datatype definition and structure at the target is identical to the other fields.

This field still has the same, outstanding problem as the others did have, and no options are now working to resolve this one field problem

wwalker · Post by **wwalker** » Tue Aug 04, 2009 2:20 am

Addendum 2 :

Best option is for the LASTUPDATE field to be set to Char(23,3), Data Element SQL:SMALLDATETIME.

At least this does not yield any warnings during the load, though it does during a View Data.

For me, this is not closed. There should be no warnings whatsoever, and this is not making sense to me.

Any help is appreciated.

Sreenivasulu · Post by **Sreenivasulu** » Tue Aug 04, 2009 2:38 am

In the target metadata change datatype as date and use stringtodate function instead of stringtotimestamp function. It would
not generate warnings.

Regards
Sreeni

chulett · Post by **chulett** » Tue Aug 04, 2009 5:26 am

Unfortunately, that's a PX suggestion and so not relevant here.

Wade, can you post an example of what this "datetime" data looks like when you select it from SQL Server? I've not really worked with that database all that much. I'd also be curious what happens if you extract it defined as a Timestamp rather than a Char and I wonder if you really need the scale of 3 - are there actually milliseconds involved with this data type?

wwalker · Post by **wwalker** » Tue Aug 04, 2009 5:36 am

Isn't StringToDate a Px transformation?? While it is available in Server, it doesn't seem to be working.

chulett · Post by **chulett** » Tue Aug 04, 2009 5:42 am

As already noted, it's not relevant.

wwalker · Post by **wwalker** » Tue Aug 04, 2009 5:45 am

Hi, Craig,

Datetime as selected from SQL Server> 2009-07-13 14:15

LASTUPDATE as generated in routine > 2009-8-04 10:41:02

chulett · Post by **chulett** » Tue Aug 04, 2009 7:47 am

Then I would think a Timestamp should be fine and there's no need for the scale you are using, which is causing your metadata mismatch. Try Timestamp(19) rather than Timestamp(23,3) and see if you can run it all the way through the job like that rather than starting off as a Char.

wwalker · Post by **wwalker** » Tue Aug 04, 2009 9:03 am

Hi, Craig,

Thanks for the reply. I did try to reduce to a length of 19, but the problem persisted. I discovered in one of the existing routines that created a timestamp, it was appearing as I posted

2009-8-04 10:41:02

instead of

2009-08-04 10:41:02

I corrected the existing routine and re-tested successfully, loading as timestamp

Thanks for your input!

Wade

chulett · Post by **chulett** » Tue Aug 04, 2009 9:04 am

Ah yes, forgot to ask about that. Glad you got it sorted out! :D