| Author |
Message
|
| lalitdowlani |
 Posted: Tue May 17, 2011 4:50 am Post subject: Issue while invoking Stored Procedure in SQL Server |
 |
|
Novice
Joined: 21 Feb 2011
Posts: 15
|
Message Broker Version :- 7.0.0.2 (Linux)
SQL Server :- 2008 (Windows)
Whenever we try to invoke the SQL Server Stored Procedure using the ESQL Call statement .... we get the below exception
Exception List
| Code: |
| ExceptionList=<RecoverableException><File>/build/S700_P/src/DataFlowEngine/ImbComputeNode.cpp</File><Line>489</Line><Function>ImbComputeNode::evaluate</Function><Type>ComIbmComputeNode</Type><Name>TestMsgFlow#FCMComposite_1_4</Name><Label>TestMsgFlow.Compute</Label><Catalog>BIPmsgs</Catalog><Severity>3</Severity><Number>2230</Number><Text>Caught exception and rethrowing</Text><RecoverableException><File>/build/S700_P/src/DataFlowEngine/ImbRdl/ImbRdlStatementGroup.cpp</File><Line>641</Line><Function>SqlStatementGroup::execute</Function><Type>ComIbmComputeNode</Type><Name>TestMsgFlow#FCMComposite_1_4</Name><Label>TestMsgFlow.Compute</Label><Catalog>BIPmsgs</Catalog><Severity>3</Severity><Number>2488</Number><Text>Error detected, rethrowing</Text><Insert><Type>5</Type><Text>.TestMsgFlow_Compute.Main</Text></Insert><Insert><Type>5</Type><Text>3.3</Text></Insert><Insert><Type>5</Type><Text>CopyEntireMessage();</Text></Insert><RecoverableException><File>/build/S700_P/src/DataFlowEngine/ImbRdl/ImbRdlRoutine.cpp</File><Line>628</Line><Function>SqlRoutine::invoke</Function><Type>ComIbmComputeNode</Type><Name>TestMsgFlow#FCMComposite_1_4</Name><Label>TestMsgFlow.Compute</Label><Catalog>BIPmsgs</Catalog><Severity>3</Severity><Number>2934</Number><Text>Error occured in procedure</Text><Insert><Type>5</Type><Text>CopyEntireMessage</Text></Insert><RecoverableException><File>/build/S700_P/src/DataFlowEngine/ImbRdl/ImbRdlStatementGroup.cpp</File><Line>641</Line><Function>SqlStatementGroup::execute</Function><Type>ComIbmComputeNode</Type><Name>TestMsgFlow#FCMComposite_1_4</Name><Label>TestMsgFlow.Compute</Label><Catalog>BIPmsgs</Catalog><Severity>3</Severity><Number>2488</Number><Text>Error detected, rethrowing</Text><Insert><Type>5</Type><Text>.TestMsgFlow_Compute.CopyEntireMessage</Text></Insert><Insert><Type>5</Type><Text>4.3</Text></Insert><Insert><Type>5</Type><Text>SET retCode = POC(123, 'Lalit', Environment.Variables.Result[ ]);</Text></Insert><RecoverableException><File>/build/S700_P/src/DataFlowEngine/ImbRdl/ImbRdlRoutine.cpp</File><Line>756</Line><Function>SqlRoutine::invoke</Function><Type>ComIbmComputeNode</Type><Name>TestMsgFlow#FCMComposite_1_4</Name><Label>TestMsgFlow.Compute</Label><Catalog>BIPmsgs</Catalog><Severity>3</Severity><Number>2934</Number><Text>Error occured in procedure</Text><Insert><Type>5</Type><Text>POC</Text></Insert><DatabaseException><File>/build/S700_P/src/DataFlowEngine/ImbOdbc.cpp</File><Line>341</Line><Function>ImbOdbcHandle::checkRcInner</Function><Type></Type><Name></Name><Label></Label><Catalog>BIPmsgs</Catalog><Severity>3</Severity><Number>2321</Number><Text>Root SQL exception</Text><Insert><Type>2</Type><Text>-1</Text></Insert><Insert><Type>14</Type><Text>libbipodbc.so</Text></Insert><DatabaseException><File>/build/S700_P/src/DataFlowEngine/ImbOdbc.cpp</File><Line>480</Line><Function>ImbOdbcHandle::checkRcInner</Function><Type></Type><Name></Name><Label></Label><Catalog>BIPmsgs</Catalog><Severity>3</Severity><Number>2322</Number><Text>Child SQL exception</Text><Insert><Type>5</Type><Text>10104</Text></Insert><Insert><Type>2</Type><Text>103</Text></Insert><Insert><Type>5</Type><Text>[IBM][ODBC SQL Server Driver][SQL Server]The number that starts with '06000060000600006001060010600106002060020600206003060030600306004060040600406005060050600506006060060600606007060070600706008060' is too long. Maximum length is 128.</Text></Insert></DatabaseException><DatabaseException><File>/build/S700_P/src/DataFlowEngine/ImbOdbc.cpp</File><Line>480</Line><Function>ImbOdbcHandle::checkRcInner</Function><Type></Type><Name></Name><Label></Label><Catalog>BIPmsgs</Catalog><Severity>3</Severity><Number>2322</Number><Text>Child SQL exception</Text><Insert><Type>5</Type><Text>10103</Text></Insert><Insert><Type>2</Type><Text>102</Text></Insert><Insert><Type>5</Type><Text>[IBM][ODBC SQL Server Driver][SQL Server]Incorrect syntax near '06000060000600006001060010600106002060020600206003060030600306004060040600406005060050600506006060060600606007060070600706008060'.</Text></Insert></DatabaseException><DatabaseException><File>/build/S700_P/src/DataFlowEngine/ImbOdbc.cpp</File><Line>480</Line><Function>ImbOdbcHandle::checkRcInner</Function><Type></Type><Name></Name><Label></Label><Catalog>BIPmsgs</Catalog><Severity>3</Severity><Number>2322</Number><Text>Child SQL exception</Text><Insert><Type>5</Type><Text>10104</Text></Insert><Insert><Type>2</Type><Text>103</Text></Insert><Insert><Type>5</Type><Text>[IBM][ODBC SQL Server Driver][SQL Server]The number that starts with '06590065900659006700067000670006701067010670106702067020670206703067030670306704067040670406705067050670506706067060670606591065' is too long. Maximum length is 128.</Text></Insert></DatabaseException></DatabaseException></RecoverableException></RecoverableException></RecoverableException></RecoverableException></RecoverableException> |
|
|
| Back to top |
|
 |
| mqjeff |
| Posted: Tue May 17, 2011 4:55 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
The error message is extremely clear on the issue.
You are attempting to insert a value that is too big for the column.
You should also always post error messages in [c o d e] tags for clarity.
Happy Debugging. |
|
| Back to top |
|
|
| lalitdowlani |
| Posted: Tue May 17, 2011 5:09 am Post subject: |
|
|
Novice
Joined: 21 Feb 2011
Posts: 15
|
In the Store Procedure :- i m not doing any insert .. just firing a select query ans returning it as a result set .
Below is the code of Stored Procedure
USE [ESB_DB]
GO
/****** Object: StoredProcedure [dbo].[POC1] Script Date: 05/17/2011 13:36:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[POC1](@Number int, @Firstname varchar(50)) AS
BEGIN
SELECT * FROM INTERFACE_CONFIG WHERE INT_NAME LIKE 'INT-002c_Factory'
END |
|
| Back to top |
|
|
| mqjeff |
| Posted: Tue May 17, 2011 5:13 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
Then the number in the column is too large for the datatype you are trying to put it into.
Read the error message. Think about it. Read it again. Review the documentation. Read the error message again. Think about it some more.
This is the process of being a programmer. This is the process you are being paid to know and follow. |
|
| Back to top |
|
|
| lalitdowlani |
| Posted: Tue May 17, 2011 5:23 am Post subject: |
|
|
Novice
Joined: 21 Feb 2011
Posts: 15
|
I m calling the Stored Procedure in this way .
CALL POC(123,'Lalit',Environment.Variables.Result[]) INTO retCode;
Even i can understand the error and i have been debugging this issue for almot a week .
This was my last option of posting it to some forum . I know for what i m being payed for . |
|
| Back to top |
|
|
| mqjeff |
| Posted: Tue May 17, 2011 5:35 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
The only things we know about you are what you show.
Is there any column in the database that has a value for the Number column that contains 06590065900659006700067000670006701067010670106702067020670206703067030670306704067040670406705067050670506706067060670606591065 ?
In particular, look at exactly the data that is matching the query you are running - "SELECT * FROM INTERFACE_CONFIG WHERE INT_NAME LIKE 'INT-002c_Factory' "
The Broker exception says that the error has occurred inside SQL SERVER, when it tried to put the data into the column and return it.
You also don't mention the data type of the retCode value.
You are either having an issue with the SQLServer ODBC drivers, with this or with the data in the table. |
|
| Back to top |
|
|
| lalitdowlani |
| Posted: Tue May 17, 2011 6:06 am Post subject: |
|
|
Novice
Joined: 21 Feb 2011
Posts: 15
|
The main issue is that when i use PASSTHRU statement instead of CALL statement everything works fine.
If its working in case of PASSTHRU then the Data is proper and even the DataTypes.
But there is one limitation of PASSTHRU that the output parameters will not work . while invoking the Stored Procedure .
So i have to use the CALL Statement .
I think its an issue with the driver . Now i m trying to get the ODBC trace So will post that once i get the same . |
|
| Back to top |
|
|
| jlaisbett |
| Posted: Tue May 17, 2011 1:31 pm Post subject: |
|
|
Apprentice
Joined: 27 Nov 2009
Posts: 39
|
One possibility you could try that may or may not be your problem is to add SET NOCOUNT ON; to the top of your stored procedure.
The documentation does indicate that you should do this for sql server and the errors you get when you don't can be misleading.
That may not be your problem but it's an issue that mainly occurs when you use call and it doesn't seem to cause issues with passthru so since that is what is happening it is definitely a possibility. |
|
| Back to top |
|
|
| lalitdowlani |
| Posted: Mon May 23, 2011 1:48 am Post subject: |
|
|
Novice
Joined: 21 Feb 2011
Posts: 15
|
Hi sorry for the late reply ,
I executed the stored procedure after adding SET NOCOUNT ON but got the same error
Below is the snapshot ODBC.trace .
2011-05-23 09:17:44.837741 : ENTER SQLConnect
HDBC 0x242cfba0
UCHAR * 0x41d60740 [4294967293] "ESB_DSN"
SWORD -3
UCHAR * 0x41d60710 [4294967293] "wmbrkr"
SWORD -3
UCHAR * 0xc7bde260 [4294967293] "******"
SWORD -3
2011-05-23 09:17:44.838057 : EXIT SQLTransact with return code 0 (SQL_SUCCESS)
HENV 0x00000000
HDBC 0x04041dd0
UWORD 0
2011-05-23 09:17:44.839121 : ENTER SQLFreeStmt
HSTMT 0x242c8010
UWORD 0 <SQL_CLOSE>
2011-05-23 09:17:44.839147 : EXIT SQLFreeStmt with return code 0 (SQL_SUCCESS)
HSTMT 0x242c8010
UWORD 0 <SQL_CLOSE>
2011-05-23 09:17:44.839170 : ENTER SQLPrepare
HSTMT 0x242c8010
UCHAR * 0x040661d0 [4294967293] "{ CALL dbo.POC1 ( ? ) }"
SDWORD -3
2011-05-23 09:17:44.839221 : EXIT SQLPrepare with return code 0 (SQL_SUCCESS)
HSTMT 0x242c8010
UCHAR * 0x040661d0 [4294967293] "{ CALL dbo.POC1 ( ? ) }"
SDWORD -3
2011-05-23 09:17:44.847092 : EXIT SQLGetTypeInfo with return code -1 (SQL_ERROR)
HSTMT 0x1876b350
SWORD -4 <SQL_LONGVARBINARY>
2011-05-23 09:17:44.847122 : ENTER SQLFreeStmt
HSTMT 0x1876b350
UWORD 0 <SQL_CLOSE>
2011-05-23 09:17:44.847143 : EXIT SQLFreeStmt with return code 0 (SQL_SUCCESS)
HSTMT 0x1876b350
UWORD 0 <SQL_CLOSE> |
|
| Back to top |
|
|
| harish_td |
| Posted: Mon May 23, 2011 9:18 pm Post subject: |
|
|
Master
Joined: 13 Feb 2006
Posts: 236
|
Try this in ESQL. The function definition is done with 2 parameters and the invocation is done with three parameters.
| Code: |
/* The stored procedure signature must be defined with a RESULT SET object to capture the response
of the execution of the stored procedure.
This has to be explicitly stated with the DYNAMIC RESULT SETS 1 even though the stored procedure does not
use the P_RESULTSET OUT SYS_REFCURSOR construct as used in ORACLE */
CREATE PROCEDURE POC1(IN onetwothree DECIMAL,IN lalit CHARACTER)
LANGUAGE DATABASE DYNAMIC RESULT SETS 1
EXTERNAL NAME "dbo.POC1";
CALL POC1(123,'Lalit',Environment.Variables.Result[]); |
|
|
| Back to top |
|
|
| lalitdowlani |
| Posted: Tue May 24, 2011 1:31 am Post subject: |
|
|
Novice
Joined: 21 Feb 2011
Posts: 15
|
|
| Back to top |
|
|
| harish_td |
| Posted: Tue May 24, 2011 1:46 am Post subject: |
|
|
Master
Joined: 13 Feb 2006
Posts: 236
|
Can you try and see what happens when you change your stored procedure to something restrictive like
| Code: |
| SELECT COL1,COL2 FROM INTERFACE_CONFIG WHERE INT_NAME = 'INT-002c_Factory' |
Any funny looking characters in the data returned when seen from a DB editor?
Can you verify whether the stored proc you are accessing and the stored proc the broker is accessing are both one and the same?
I am tempted to say that the Message Broker is innocent here
 |
|
| Back to top |
|
|
| lalitdowlani |
| Posted: Tue May 24, 2011 3:40 am Post subject: |
|
|
Novice
Joined: 21 Feb 2011
Posts: 15
|
Still getting the same errror
MB Code
CREATE PROCEDURE POC1(IN onetwothree DECIMAL,IN lalit CHARACTER)
LANGUAGE DATABASE DYNAMIC RESULT SETS 1
EXTERNAL NAME "dbo.POC1";
Stored Procedure
USE [ESB_DB]
GO
/****** Object: StoredProcedure [dbo].[POC1] Script Date: 05/24/2011 12:30:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[POC1](@Number decimal,@Name varchar) AS
BEGIN
SET NOCOUNT ON
SELECT NUMBER FROM dbo.POC WHERE FIRSTNAME LIKE 'EAI'
--INSERT INTO POC(NUMBER,FIRSTNAME ,EVENT_TS) VALUES (@NUMBER, @Name,1)
END
I have verified the procedure name is same and there are no special characters in the table .
I also want the Broker to be innocent . So that i can put the blame on SQL Server |
|
| Back to top |
|
|
|
|
