| Author |
Message
|
| pratuluv |
 Posted: Mon Jul 16, 2012 10:22 pm Post subject: Accessing Stored Procedure on SQL Server |
 |
|
Acolyte
Joined: 25 May 2011
Posts: 53
|
Hi I am trying to access a stored procedure residing on SQL Server. I have taken the sample code from our Help contents.
Here is the link to the same, Refer "Database routine example 4".
http://publib.boulder.ibm.com/infocenter/wmbhelp/v7r0m0/index.jsp?topic=%2Fcom.ibm.etools.mft.doc%2Fac17040_.htm
While trying to call the stored procedure, I am getting the below error, Please help.
ExceptionList
RecoverableException
File:CHARACTER:F:\build\S700_P\src\DataFlowEngine\ImbDataFlowNode.cpp
Line:INTEGER:1073
Function:CHARACTER:ImbDataFlowNode::createExceptionList
Type:CHARACTER:ComIbmMQInputNode
Name:CHARACTER:sample#FCMComposite_1_1
Label:CHARACTER:sample.MQ Input
Catalog:CHARACTER:BIPmsgs
Severity:INTEGER:3
Number:INTEGER:2230
Text:CHARACTER:Node throwing exception
RecoverableException
File:CHARACTER:F:\build\S700_P\src\DataFlowEngine\ImbComputeNode.cpp
Line:INTEGER:489
Function:CHARACTER:ImbComputeNode::evaluate
Type:CHARACTER:ComIbmComputeNode
Name:CHARACTER:sample#FCMComposite_1_3
Label:CHARACTER:sample.Compute
Catalog:CHARACTER:BIPmsgs
Severity:INTEGER:3
Number:INTEGER:2230
Text:CHARACTER:Caught exception and rethrowing
RecoverableException
File:CHARACTER:F:\build\S700_P\src\DataFlowEngine\ImbRdl\ImbRdlStatementGroup.cpp
Line:INTEGER:641
Function:CHARACTER:SqlStatementGroup::execute
Type:CHARACTER:ComIbmComputeNode
Name:CHARACTER:sample#FCMComposite_1_3
Label:CHARACTER:sample.Compute
Catalog:CHARACTER:BIPmsgs
Severity:INTEGER:3
Number:INTEGER:2488
Text:CHARACTER:Error detected, rethrowing
Insert
Type:INTEGER:5
Text:CHARACTER:.sample_Compute.Main
Insert
Type:INTEGER:5
Text:CHARACTER:9.3
Insert
Type:INTEGER:5
Text:CHARACTER:SwapParms(inputParm, outputParm, inputOutputParm);
RecoverableException
File:CHARACTER:F:\build\S700_P\src\DataFlowEngine\ImbRdl\ImbRdlRoutine.cpp
Line:INTEGER:767
Function:CHARACTER:SqlRoutine::invoke
Type:CHARACTER:ComIbmComputeNode
Name:CHARACTER:sample#FCMComposite_1_3
Label:CHARACTER:sample.Compute
Catalog:CHARACTER:BIPmsgs
Severity:INTEGER:3
Number:INTEGER:2934
Text:CHARACTER:Error occured in procedure
Insert
Type:INTEGER:5
Text:CHARACTER:SwapParms
RecoverableException
File:CHARACTER:F:\build\S700_P\src\DataFlowEngine\ImbDatabaseManager.cpp
Line:INTEGER:2228
Function:CHARACTER:ImbDatabaseManager::getDBProcedureParameterInformation
Type:CHARACTER:ComIbmDatabaseConnectionManager
Name:CHARACTER:ComIbmDatabaseConnectionManager
Label:CHARACTER:ComIbmDatabaseConnectionManager
Catalog:CHARACTER:BIPmsgs
Severity:INTEGER:3
Number:INTEGER:2920
Text:CHARACTER:The procedure is unknown to the database and no definition could be found.
Insert
Type:INTEGER:5
Text:CHARACTER:DSN_SQL.[DWIPBANIN00382\SQLEXPRESS].[Test].[dbo].[dbSwapParms] |
|
| Back to top |
|
 |
| sourdas2 |
| Posted: Mon Jul 16, 2012 11:40 pm Post subject: |
|
|
Voyager
Joined: 21 Apr 2006
Posts: 90
Location: Kolkata,India
|
The error is -
| Quote: |
| The procedure is unknown to the database and no definition could be found - DSN_SQL.[DWIPBANIN00382\SQLEXPRESS].[Test].[dbo].[dbSwapParms] |
So, check if you are providing correct Data Source, Schema Name, Stored Proc Name.
_________________
Thanks and Warm Regards
Sourav |
|
| Back to top |
|
|
| pratuluv |
| Posted: Mon Jul 16, 2012 11:44 pm Post subject: Accessing Stored Procedure on SQL Server |
|
|
Acolyte
Joined: 25 May 2011
Posts: 53
|
All the details are perfect. I have tried different combinations also like,
giving only the schemaname.storedprocname, etc. but it gives me the same error. |
|
| Back to top |
|
|
| kash3338 |
| Posted: Tue Jul 17, 2012 3:41 am Post subject: Re: Accessing Stored Procedure on SQL Server |
|
|
Shaman
Joined: 08 Feb 2009
Posts: 709
Location: Chennai, India
|
| pratuluv wrote: |
All the details are perfect. I have tried different combinations also like,
giving only the schemaname.storedprocname, etc. but it gives me the same error. |
Is the IN parameters to the DB of the same datatype? Did you try to run a select query to check if the connection is established using the current DSN details? |
|
| Back to top |
|
|
| pratuluv |
| Posted: Tue Jul 17, 2012 4:06 am Post subject: |
|
|
Acolyte
Joined: 25 May 2011
Posts: 53
|
Kash,
This is not a datatype issue. I have taken care of that. Also, I did execute a select query on this database and it retrieves the result. Hence the issue is only with accessing the stored procedure.
Also, fyi, in case of datatype mismatch, the error message will clearly mention the same.
Between, can anyone tell me if SQL Server 2008 R2 is compatible with WMB for stored procedure calls? |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Tue Jul 17, 2012 4:57 am Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
| pratuluv wrote: |
Between, can anyone tell me if SQL Server 2008 R2 is compatible with WMB for stored procedure calls? |
Yes it is, within the limitation imposed bythe ODBC driver. For example, BIT Datatypes aren't supported (This is with V7.0.0.3 broker).
_________________
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: Tue Jul 17, 2012 10:01 am Post subject: |
|
|
Shaman
Joined: 08 Feb 2009
Posts: 709
Location: Chennai, India
|
Make sure this is taken care as well,
| Quote: |
The following restrictions apply to the use of stored procedures:
Overloaded procedures are not supported. (An overloaded procedure is one that has the same name as another procedure in the same database schema with a different number of parameters, or parameters with different types.) If the broker detects that a procedure has been overloaded, it raises an exception |
|
|
| Back to top |
|
|
|
|
