Issue while inserting to SQL server table
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 75
- Joined: Mon Jul 12, 2010 4:11 am
- Location: Chennai
Issue while inserting to SQL server table
Hi,
My job is to insert data from file to SQL server.
Below is my Job flow.
Seq file --->transformer --->SQL Server.
I'm reading date as varchar and inserting as Timestamp.
Eg for date: '8/29/2011 5:26:03AM'
1. when I tried to insert without any conversion, below error is I'm getting
odbc_inc_stg: Schema reconciliation detected a type mismatch for field inc_sys_created_on. When moving data from field type VARCHAR(min=0,max=25) into DATETIME(fraction=3), data corruption can occur.
2. when I tried to insert after convering varchar to Timestamp, below is are the warnings i'm getting.
APT_CombinedOperatorController,2: Data string '8-29-2011 5:26:03AM' does not match format '%yyyy-%mm-%dd %hh:%nn:%ss': the value for tag %yyyy has fewer characters than expected.
Could you please help me to fix this issue?
Regards,
My job is to insert data from file to SQL server.
Below is my Job flow.
Seq file --->transformer --->SQL Server.
I'm reading date as varchar and inserting as Timestamp.
Eg for date: '8/29/2011 5:26:03AM'
1. when I tried to insert without any conversion, below error is I'm getting
odbc_inc_stg: Schema reconciliation detected a type mismatch for field inc_sys_created_on. When moving data from field type VARCHAR(min=0,max=25) into DATETIME(fraction=3), data corruption can occur.
2. when I tried to insert after convering varchar to Timestamp, below is are the warnings i'm getting.
APT_CombinedOperatorController,2: Data string '8-29-2011 5:26:03AM' does not match format '%yyyy-%mm-%dd %hh:%nn:%ss': the value for tag %yyyy has fewer characters than expected.
Could you please help me to fix this issue?
Regards,
Ajitha S
-
- Participant
- Posts: 75
- Joined: Mon Jul 12, 2010 4:11 am
- Location: Chennai
Thanks Sussen. But I tried converting to Timestamp format: '%yyyy-%mm-%dd %hh:%nn:%ss'. Below are the warnings i'm getting.
odbc_inc_stg: Schema reconciliation detected a type mismatch for field inc_sys_created_on. When moving data from field type VARCHAR(min=0,max=25) into DATETIME(fraction=3), data corruption can occur.
odbc_inc_stg: Error when checking operator: When binding input interface field "inc_sys_created_on" to field "inc_sys_created_on": No default type conversion from type "string[max=25]" to type "timestamp[microseconds]".
Regards,
odbc_inc_stg: Schema reconciliation detected a type mismatch for field inc_sys_created_on. When moving data from field type VARCHAR(min=0,max=25) into DATETIME(fraction=3), data corruption can occur.
odbc_inc_stg: Error when checking operator: When binding input interface field "inc_sys_created_on" to field "inc_sys_created_on": No default type conversion from type "string[max=25]" to type "timestamp[microseconds]".
Regards,
Ajitha S
-
- Participant
- Posts: 75
- Joined: Mon Jul 12, 2010 4:11 am
- Location: Chennai
The clue you're looking for is in suse_dk's answer:
Regards,
When converting to a timestamp, you need to specify the format of the string you are converting from...that's the format that DataStage needs to be told about. This topic has been covered many times in the forum, and you can also look in the Parallel Job Developer's Guide for more information on time and date format strings.You need to tell DS what your timestamp from the source looks like...
The format for '8-29-2011 5:26:03AM' is not '%yyyy-%mm-%dd %hh:%nn:%ss'
Regards,
- james wiles
All generalizations are false, including this one - Mark Twain.
All generalizations are false, including this one - Mark Twain.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 75
- Joined: Mon Jul 12, 2010 4:11 am
- Location: Chennai
as per suse_dk and james, I tried converting to Timestamp format: '%yyyy-%mm-%dd %hh:%nn:%ss'. Below is the warnings i'm getting.
odbc_inc_stg: Schema reconciliation detected a type mismatch for field inc_sys_created_on. When moving data from field type VARCHAR(min=0,max=25) into DATETIME(fraction=3), data corruption can occur
odbc_inc_stg: Schema reconciliation detected a type mismatch for field inc_sys_created_on. When moving data from field type VARCHAR(min=0,max=25) into DATETIME(fraction=3), data corruption can occur
Ajitha S
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
String to timestamp is not an implicit conversion - you will need to specify a conversion function in a Modify or Transformer stage to achieve a data type of Timestamp.
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: 75
- Joined: Mon Jul 12, 2010 4:11 am
- Location: Chennai
I'm doing the type conversion in Transformer.
Source:8/29/2011 5:28:51AM
below is the conversion logic
StringToTimestamp(lnk_input_to_filter.inc_sys_created_on,"%mm/%dd/%yyyy %hh:%nn:%ss")
Warning:
xfrm_on_lan_id,1: Data string '8/29/2011 5:28:51A' does not match format '%mm/%dd/%yyyy %hh:%nn:%ss': the value for tag %mm has fewer characters than expected.
Source:8/29/2011 5:28:51AM
below is the conversion logic
StringToTimestamp(lnk_input_to_filter.inc_sys_created_on,"%mm/%dd/%yyyy %hh:%nn:%ss")
Warning:
xfrm_on_lan_id,1: Data string '8/29/2011 5:28:51A' does not match format '%mm/%dd/%yyyy %hh:%nn:%ss': the value for tag %mm has fewer characters than expected.
suse_dk wrote:For me it looks like you are trying to put a varchar into a datetime... where exactly are you doing your type conversion? - in a transformer?
Ajitha S
-
- Participant
- Posts: 75
- Joined: Mon Jul 12, 2010 4:11 am
- Location: Chennai
I'm doing the type conversion in Transformer.
Source:8/29/2011 5:28:51AM
below is the conversion logic
StringToTimestamp(lnk_input_to_filter.inc_sys_created_on,"%mm/%dd/%yyyy %hh:%nn:%ss")
Warning:
xfrm_on_lan_id,1: Data string '8/29/2011 5:28:51A' does not match format '%mm/%dd/%yyyy %hh:%nn:%ss': the value for tag %mm has fewer characters than expected.
Source:8/29/2011 5:28:51AM
below is the conversion logic
StringToTimestamp(lnk_input_to_filter.inc_sys_created_on,"%mm/%dd/%yyyy %hh:%nn:%ss")
Warning:
xfrm_on_lan_id,1: Data string '8/29/2011 5:28:51A' does not match format '%mm/%dd/%yyyy %hh:%nn:%ss': the value for tag %mm has fewer characters than expected.
Ajitha S
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 75
- Joined: Mon Jul 12, 2010 4:11 am
- Location: Chennai
I understand the error message. If my input is 08/29/2011 05:28:51AM,
I will not be getting any problem in inserting.
But my source is 8/29/2011 5:28:51AM. If I add leading zero to month (as 08), again there is possibility to get same warning for %hh also as source does not have 2 digits in the format.
moreover I may get source value as 12/29/2011 15:28:51AM.
How to handle both the cases?
I will not be getting any problem in inserting.
But my source is 8/29/2011 5:28:51AM. If I add leading zero to month (as 08), again there is possibility to get same warning for %hh also as source does not have 2 digits in the format.
moreover I may get source value as 12/29/2011 15:28:51AM.
How to handle both the cases?
Ajitha S
Documentation is a wonderful thing. The "s" option allows leading spaces in date and/or time formats. For example:
Would handle single digits in the month, day, hour, minute and second. You can also find discussions on how to handle the "am/pm marker" there.
Code: Select all
%(m,s)/%(d,s)/%yyyy %(h,s):%(n,s):%(s,s)
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers