| Author |
Message
|
| sanremo |
 Posted: Fri Oct 24, 2008 8:23 am Post subject: Stored procedure invokation error |
 |
|
Novice
Joined: 23 Sep 2008
Posts: 11
|
Hi All,
We have complicated problem in using stored procedure. We tried to invoke procedure from Message Broker flow simpy by 'CALL' statement and received the following error in the Message Broker exception list:
Child SQL exception
MicrosoftODBC SQL Server DriverSQL Server[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator.
OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a
Message broker flow wasn't configured for distributed transactions and didn't use coordinated transactions. Compute node from which we invoke stored procedure uses 'Automatic' transaction property.
Procedure is realised on the MSSQL Server 2000 and uses another stored procedure located on the remote mashine. We use 'no authentication required' option for all related DTC and checked on options 'Allow remote client', 'Allow Inbound' and 'Allow outbound'. The second stored procedure also realised on the MSSQL Server 2000. Message broker is installed on the Win Srever 2003.
We doesn't know where could we find a solution so need your help.
ASAP please because it's very critical for us.
Thanks in advance. |
|
| Back to top |
|
 |
| tillywern |
| Posted: Fri Oct 24, 2008 8:44 am Post subject: |
|
|
Centurion
Joined: 28 Jan 2003
Posts: 109
Location: Colorado
|
| If the stored procedure is defined on the database, have your tried executing it from within a PASSTHROUGH statement instead? |
|
| Back to top |
|
|
| sanremo |
| Posted: Fri Oct 24, 2008 8:57 am Post subject: |
|
|
Novice
Joined: 23 Sep 2008
Posts: 11
|
| No, we didn't. Can it help us? |
|
| Back to top |
|
|
| tillywern |
| Posted: Fri Oct 24, 2008 9:16 am Post subject: |
|
|
Centurion
Joined: 28 Jan 2003
Posts: 109
Location: Colorado
|
| It might because it provides a more low level interface to the database. It is worth a try but if the problem really has to do with transaction control then you may be out of luck no matter what way you do it. |
|
| Back to top |
|
|
| sanremo |
| Posted: Fri Oct 24, 2008 9:48 am Post subject: |
|
|
Novice
Joined: 23 Sep 2008
Posts: 11
|
ok, thanks a lot, I will try.
Do you know what is the difference between CALL and PASSTHRU statements? |
|
| Back to top |
|
|
| mdmader |
| Posted: Tue Oct 28, 2008 11:33 am Post subject: |
|
|
Newbie
Joined: 17 Apr 2008
Posts: 7
|
| sanremo wrote: |
ok, thanks a lot, I will try.
Do you know what is the difference between CALL and PASSTHRU statements? |
For PASSTHRU, you have a function and a statement that can be implemented. The function evaluates an expression and executes a character string returning a result set (e.g. SELECT X.* FROM myTable AS X WHERE X.column = 'somevalue')
A PASSTHRU statement evaluates an expression and executes a character string as a DBMS statement. (e.g. CREATE TABLE X (COLUMN1 INTEGER, COLUMN2 VARCHAR(25))
The CALL will allow you to call a Stored Procedure.
Take a look at the Message Broker ESQL at ftp://ftp.software.ibm.com/software/integration/wbibrokers/docs/V6.1/messagebroker_ESQL.pdf |
|
| Back to top |
|
|
|
|
