I'm writing some flows for IBM WebSphere Message Broker which call stored procedures on a remote Microsoft SQL Server database. My problem is that I'm sometimes getting the resultset which should be returned and sometimes not getting anything.
The lines in the stored procedure which seem to be causing the trouble are:
IF (@noCountInd > 0)
SET NOCOUNT ON;
When I call the stored procedure from a database node in the Message Broker it returns a resultset on the first call then nothing on subsequent calls. If the SET NOCOUNT ON is unconditional it works every time. It works every time even with the above condition if called through the SQL Server Management Studio command line.
It also seems as if when enough time has passed between calls for the Message Broker to close its database connection then the next call with a new connection will succeed.
Here's my pared down code to produce this problem:
Stored procedure
CREATE PROCEDURE dbo.pTestConditionalNoCount
@noCountInd bit = 0
AS
IF (@noCountInd > 0)
SET NOCOUNT ON;
SELECT 'Success' AS RESULT;
RETURN 0;
ESQL in database node
CREATE DATABASE MODULE testConditionalNoCount
CREATE FUNCTION Main() RETURNS BOOLEAN
BEGIN
CALL pTestConditionalNoCount(TRUE,
Environment.Variables.testConditionalNoCount.Results.Row[])
IN Database.{'DATABASE_NAME'}.{'dbo'};
RETURN TRUE;
END;
END MODULE;
CREATE PROCEDURE pTestConditionalNoCount(IN testNoCountInd BOOLEAN)
LANGUAGE DATABASE
DYNAMIC RESULT SETS 1
EXTERNAL NAME pTestConditionalNoCount;
Output from trace node
Environment: ( ['MQROOT' : 0x29692478]
(0x01000000:Name):Variables = (
(0x01000000:Name):testConditionalNoCount = (
(0x01000000:Name):Results = (
(0x01000000:Name):Row = (
(0x03000000:NameValue):RESULT = 'Success' (CHARACTER)
)
)
)
)
)
Environment: ( ['MQROOT' : 0x29692478]
(0x01000000:Name):Variables = (
(0x01000000:Name):testConditionalNoCount = (
(0x01000000:Name):Results =
)
)
)
Environment: ( ['MQROOT' : 0x29692478]
(0x01000000:Name):Variables = (
(0x01000000:Name):testConditionalNoCount = (
(0x01000000:Name):Results =
)
)
)
The version of SQL Server is Microsoft SQL Server 2005 - 9.00.5324.00 (X64) and message broker is IBM WebSphere Message Broker 8.0.0.2.
Anyone have any idea what is going on here?
Update
I ran an ODBC trace and it shows two resultsets being returned in the failure case. The first is empty and the second is the resultset I'm expecting.
There seems to be no difference in the way the procedure is called. A SQLExecute is logged for each call then a SQLNumResultCols which returns 1 if it worked and 0 if it didn't.
