| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| SQL server 2012 DB error from unix |
« View previous topic :: View next topic » |
| Author |
Message
|
| ghsman |
 Posted: Sat Feb 14, 2015 11:59 pm Post subject: SQL server 2012 DB error from unix |
 |
|
Novice
Joined: 11 Nov 2012
Posts: 10
|
hi there i got the below error when i tried to insert to SQL server 2012 DB while its ok from my local and i inserted the values manully in the DB server
hint : my flow is deployed under unix server and i got the below error
><ErrorDescription>Description: Exception, Error detected, rethrowing, .ICD_551MsgFlow_Compute.Main, 164.6, INSERT INTO
Database.{SCHEMANAME}.{TABLENAME} ( PNR_Ref_Num, PNR_Creation_Date, Flt_Last_EOT_Dt, Flt_Last_EOT_Tm, Last_Upd_Office_Id, Last_Upd_Agent_Sign_Cd, SK_Element_Ref_Cd, SK_Element_Ref_Num, SK_Element_Sts, S
K_Airline_Cd, Special_Keyword, SK_Free_Text, ESB_Upd_Time_Stamp ) VALUES (vPNRRefNumber, vPNRCreationDate, vFLTLastEOTDate, vFLTLastEOTTime, vLstUpdOfficeID, vLstUpdAgentSignCode, vSKElementRefCode, vSK
ElementRefNum, vSKElementSts, vSKAirlineCode, vSpecialKeyword, vSKFreeText, vESBUpdTimeStamp ) , Root SQL exception, -1, /opt/ibm/IE02/2.0.0/lib/libodbcinterface.so, Child SQL exception, 22007, 241, [uni
xODBC][IBM][ODBC SQL Server Driver][SQL Server]Conversion failed when converting date and/or time from character string.</ErrorDescription> |
|
| Back to top |
|
 |
| fjb_saper |
| Posted: Sun Feb 15, 2015 10:44 am Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20696
Location: LI,NY
|
From the error message it looks like you first need to cast your date / datetime information into the correct character format to be accepted by the DB... 
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| ghsman |
| Posted: Tue Feb 17, 2015 12:41 am Post subject: |
|
|
Novice
Joined: 11 Nov 2012
Posts: 10
|
i fixed part of the issue but i still get
><ErrorDescription>Description: Exception, Error detected, rethrowing, .ICD_551MsgFlow_Compute.Main, 164.6, INSERT INTO
Database.{SCHEMANAME}.{TABLENAME} ( PNR_Ref_Num, PNR_Creation_Date, Flt_Last_EOT_Dt, Flt_Last_EOT_Tm, Last_Upd_Office_Id, Last_Upd_Agent_Sign_Cd, SK_Element_Ref_Cd, SK_Element_Ref_Num, SK_Element_Sts, S
K_Airline_Cd, Special_Keyword, SK_Free_Text, ESB_Upd_Time_Stamp ) VALUES (vPNRRefNumber, vPNRCreationDate, vFLTLastEOTDate, vFLTLastEOTTime, vLstUpdOfficeID, vLstUpdAgentSignCode, vSKElementRefCode, vSK
ElementRefNum, vSKElementSts, vSKAirlineCode, vSpecialKeyword, vSKFreeText, vESBUpdTimeStamp ) , Root SQL exception, -1, /opt/ibm/IE02/2.0.0/lib/libodbcinterface.so, Child SQL exception, 20231, 0, [unixO
DBC][IBM][ODBC SQL Server Driver]20231</ErrorDescription> |
|
| Back to top |
|
|
| ghsman |
| Posted: Sat Feb 21, 2015 9:55 pm Post subject: |
|
|
Novice
Joined: 11 Nov 2012
Posts: 10
|
| is it a driver issue from the server ??? |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Sat Feb 21, 2015 10:35 pm Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
Does the insert work with :-
1) With the {SCHEMANAME}.{TABLENAME} replaced with hard coded names?
and.or
2) Have you tried making the Insert a character string and using PASSTHRU to do the insert?
3) Does the combined string as in 2) get inserted when executed from within the SQL Studio?
In the days between your posts, what have you tried for yourself?
_________________
WMQ User since 1999
MQSI/WBI/WMB/'Thingy' User since 2002
Linux user since 1995
Every time you reinvent the wheel the more square it gets (anon). If in doubt think and investigate before you ask silly questions. |
|
| Back to top |
|
|
| mqjeff |
| Posted: Mon Feb 23, 2015 6:14 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| Is there any more information on the child sql exception? |
|
| Back to top |
|
|
| kash3338 |
| Posted: Mon Apr 20, 2015 1:05 am Post subject: |
|
|
Shaman
Joined: 08 Feb 2009
Posts: 709
Location: Chennai, India
|
Hi,
Sorry to open this thread again. Since it is a recent thread and we also face a similar kind of problem now, I though it is better to reopen this thread.
Now the problem that we face is, we are trying to insert a value to a Date-Time datatype filed in SQL Server v2008 from IIB v9. The exception that we get is,
| Code: |
| [unixODBC][IBM][ODBC SQL Server Legacy Driver]20125 |
When we try to insert as Character, the error message we get is,
| Code: |
| [unixODBC][IBM][ODBC SQL Server Legacy Driver][SQL Server]Conversion failed when converting date and/or time from character string. |
What may be the issue here? Any suggestions to get rid of this would be really helpful as we are stuck with this for a long time now. |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Mon Apr 20, 2015 1:43 am Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
Have you tried...
Reading an SQLServer table using the same ODBC connection that has a datetime field (I am assuming that is how the column is configured).
The add a trace node after the compute node and look at the structure of the data returned.
Then you could try doing the write again using an identically formatted CHAR variable.
OR
you could open a PMR with IBM and post the results here?
_________________
WMQ User since 1999
MQSI/WBI/WMB/'Thingy' User since 2002
Linux user since 1995
Every time you reinvent the wheel the more square it gets (anon). If in doubt think and investigate before you ask silly questions. |
|
| Back to top |
|
|
| kash3338 |
| Posted: Mon Apr 20, 2015 3:02 am Post subject: |
|
|
Shaman
Joined: 08 Feb 2009
Posts: 709
Location: Chennai, India
|
| smdavies99 wrote: |
| Reading an SQLServer table using the same ODBC connection that has a datetime field (I am assuming that is how the column is configured). |
Tried reading the value of the field using same ODBC conn from the same Comput node and the format of Date-Time is,
| Code: |
| yyyy-MM-dd HH:mm:ss.SSS |
When tried inserting with same format, I get that error. |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Mon Apr 20, 2015 3:50 am Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
Time for a PMR then?
_________________
WMQ User since 1999
MQSI/WBI/WMB/'Thingy' User since 2002
Linux user since 1995
Every time you reinvent the wheel the more square it gets (anon). If in doubt think and investigate before you ask silly questions. |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Mon Apr 20, 2015 4:30 am Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20696
Location: LI,NY
|
| kash3338 wrote: |
| smdavies99 wrote: |
| Reading an SQLServer table using the same ODBC connection that has a datetime field (I am assuming that is how the column is configured). |
Tried reading the value of the field using same ODBC conn from the same Comput node and the format of Date-Time is,
| Code: |
| yyyy-MM-dd HH:mm:ss.SSS |
When tried inserting with same format, I get that error. |
Same format or not same format that is the question.
An ESQL TIMESTAMP format may look like it is formatted that way, only it isn't. Not until you cast it to CHARACTER...
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| nchimakurthi |
| Posted: Wed May 06, 2015 9:36 am Post subject: |
|
|
Newbie
Joined: 06 May 2015
Posts: 1
|
Faced similar issue
try to fill the ODBC stanza in ODBC.ini file
;##########################################
;###### Mandatory information stanza ######
;##########################################
[ODBC]
InstallDir=/opt/ibm/mqsi/9.0.0.0/ODBC/V7.0
UseCursorLib=0
IANAAppCodePage=4
UNICODE=UTF-8 |
|
| Back to top |
|
|
| kash3338 |
| Posted: Sat May 16, 2015 9:17 am Post subject: |
|
|
Shaman
Joined: 08 Feb 2009
Posts: 709
Location: Chennai, India
|
|
| Back to top |
|
|
|
|
|
| |
|
Page 1 of 1 |
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
|
|
|
