Timestamp Inserts into SQL Server results in Warnings
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 40
- Joined: Thu Mar 30, 2006 6:30 am
- Location: Near Geneva, Switzerland
- Contact:
Timestamp Inserts into SQL Server results in Warnings
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.
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.
Wade Walker
-
- Premium Member
- Posts: 40
- Joined: Thu Mar 30, 2006 6:30 am
- Location: Near Geneva, Switzerland
- Contact:
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
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
Last edited by wwalker on Tue Aug 04, 2009 2:21 am, edited 1 time in total.
Wade Walker
-
- Premium Member
- Posts: 40
- Joined: Thu Mar 30, 2006 6:30 am
- Location: Near Geneva, Switzerland
- Contact:
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.
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.
Wade Walker
-
- Premium Member
- Posts: 892
- Joined: Thu Oct 16, 2003 5:18 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?
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?
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Premium Member
- Posts: 40
- Joined: Thu Mar 30, 2006 6:30 am
- Location: Near Geneva, Switzerland
- Contact:
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
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
Wade Walker