Subscribe Bookmark RSS Feed

JMP11 MSSQL Stored Procedure Call

haloway13

Community Trekker

Joined:

Feb 12, 2013

I thought I would share this tip to the community since there seems to be absolutely no documentation on this for JMP 11.

We recently upgraded from JMP 10 and many stored procedure calls no longer worked in jJMP 11. It does not even appear that stored procedure call are supported in JMP 11 according to the documentation. Yet, we found 1 stored procedure call that inexplicably still worked. After looking into a little bit it turns out that it must have something to do with the log messages returned during the call. The stored procedure that worked was a simple select statement and therefore had a single message of how many rows returned and the result set. Other stored procedures would use multiple temporary tables and would generate multiple messages about how many rows were affected along the way through the stored procedure. In JMP 10 this did not affect the eventual set of data returned. JMP 11 on the other hand chokes on the multiple messages as part of the log/feedback provided.

The simple fix was to suppress all messages except for the final message regarding the final result set.

at the begging of the stored procedure:

SET NOCOUNT ON

takes care of the issue and viola JMP 11 works with stored procedure calls.

At no time did JMP11 give any meaningful error messages in View->Log

Tim

1 REPLY
stephen_pearson

Community Trekker

Joined:

Oct 6, 2014

Thanks for the useful tip. The requirement for NO COUNT exists when using stored procedures from Excel which use temporary tables so I guess it is a common feature of these types of connections.