| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| issue with stored procedure using temp table/table variable |
« View previous topic :: View next topic » |
| Author |
Message
|
| maven |
 Posted: Mon Nov 18, 2013 7:05 am Post subject: issue with stored procedure using temp table/table variable |
 |
|
Novice
Joined: 03 Jun 2007
Posts: 15
|
hello..im working on MB v7 on windows 2008 server. When i try to access from broker the stored procedure using either temp table or table variable, i get a empty result set. When the stored procedure is invoked thru DBVisualizer or other DB viewer tool i can see the result set.
If i use the actual table in the stored procedure, i get the result set but if i store the data from actual table into temp table or table variable the output is empty result set. Same issue is on broker v8
Any pointers. |
|
| Back to top |
|
 |
| shinchan |
| Posted: Mon Nov 18, 2013 8:02 am Post subject: Re: issue with stored procedure using temp table/table varia |
|
|
Newbie
Joined: 18 Nov 2013
Posts: 6
|
| maven wrote: |
hello..im working on MB v7 on windows 2008 server. When i try to access from broker the stored procedure using either temp table or table variable, i get a empty result set. When the stored procedure is invoked thru DBVisualizer or other DB viewer tool i can see the result set.
If i use the actual table in the stored procedure, i get the result set but if i store the data from actual table into temp table or table variable the output is empty result set. Same issue is on broker v8
Any pointers. |
Hi everyone i'm new here.
I have the same problem, but in broker v8 on AIX and Sybase.
I recieve this error from Sybase:
| Code: |
The 'CREATE TABLE' command is not allowed within a multi-statement transaction in the 'tempdb' database. '.
#tmp_hello not found. Specify owner.objectname or use sp_help to check whether the object exists (sp_help may produce lots of output). '
|
I have a MQInput -> Compute ->MQOutput. With transaction mode: No, in both MQ nodes, and i try automatic and commit in compute node.
I understand this is produced because a flow is transactional and a temporaly table is created and distroyed in the same transaction so this action is not permited.
From putty to Sybase i try this:
| Code: |
1> begin tran
2> create table #hello (one int, two char(1))
3> insert #hello values (1, 'a')
4> commit tran
5> go
Msg 2762, Level 16, State 3:
Server 'SERVER_S125', Line 2:
The 'CREATE TABLE' command is not allowed within a multi-statement transaction in the 'tempdb' database.
Msg 208, Level 16, State 6:
Server 'SERVER_S125', Line 3:
#hello not found. Specify owner.objectname or use sp_help to check whether the object exists (sp_help may produce lots of output).
|
Now i dont know what do.
Maybe setting a configuration in MQ queue ... i dont know.
My queue configuration is by default:
| Code: |
QUEUE(TEST.IN) TYPE(QLOCAL)
ACCTQ(QMGR) ALTDATE(2013-11-18)
ALTTIME(11.26.21) BOQNAME( )
BOTHRESH(0) CLUSNL( )
CLUSTER( ) CLCHNAME( )
CLWLPRTY(0) CLWLRANK(0)
CLWLUSEQ(QMGR) CRDATE(2013-11-18)
CRTIME(11.26.21) CURDEPTH(0)
CUSTOM( ) DEFBIND(OPEN)
DEFPRTY(0) DEFPSIST(NO)
DEFPRESP(SYNC) DEFREADA(NO)
DEFSOPT(SHARED) DEFTYPE(PREDEFINED)
DESCR( ) DISTL(NO)
GET(ENABLED) HARDENBO
INITQ( ) IPPROCS(1)
MAXDEPTH(5000) MAXMSGL(4194304)
MONQ(QMGR) MSGDLVSQ(PRIORITY)
NOTRIGGER NPMCLASS(NORMAL)
OPPROCS(1) PROCESS( )
PUT(ENABLED) PROPCTL(COMPAT)
QDEPTHHI(80) QDEPTHLO(20)
QDPHIEV(DISABLED) QDPLOEV(DISABLED)
QDPMAXEV(ENABLED) QSVCIEV(NONE)
QSVCINT(999999999) RETINTVL(999999999)
SCOPE(QMGR) SHARE
STATQ(QMGR) TRIGDATA( )
TRIGDPTH(1) TRIGMPRI(0)
TRIGTYPE(FIRST) USAGE(NORMAL)
|
Greetings
(Sorry for my english) |
|
| Back to top |
|
|
| shinchan |
| Posted: Thu Nov 28, 2013 11:54 am Post subject: |
|
|
Newbie
Joined: 18 Nov 2013
Posts: 6
|
| I resolve this calling SP with a JavaCompute Node. Because this node not use transaction. |
|
| 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
|
|
|
|
