Resultset Not Permitted

[Former Member]

When I try to execute a stored procedure, I get the error : "Resultset Not Permitted in [procedure 2]". How it works is that i have procedure 1, which calls procedure 2. This is on the production environment, but on the test environment it is working. So is there a setting I need to tweak? In case it helps, the SQL Code for the error is 946.

[MCMartin]

Check the code of proc 2 maybe you have an if clause which is going a different path on the test system than on the productive system.

[Former Member]

Hi Mark, unfortunately its the exact same one. Tested it and saw it was working on the test system and migrated it to the production environment.

[MarkCulp]

Then perhaps Martin is on the right track - perhaps your procedures follow a different path and you are hitting some code in production (that generates a result set) that does not get executed in your test environment?

[MarkCulp]

You have not indicated what interface you are using to call procedure 1 nor how you are calling it but I suspect that in your test environment you are using a tool (e.g. dbisql) that accepts a result set coming back from the procedure but in your real production environment you are using an API or a calling method that does not allow the procedure to return a result set. I also suspect that the result set is coming from procedure 2 and you have not captured that result set in procedure 1. Note: showing us your real code would help us help you. (And please specify the version and build number that you are using).

So if you really want the result set to go back to your application then you should add a RESULT clause to procedure 1 so that the calling API knows that the procedure returns a result set.

If you don't want the result set to go back to your application then you either need to change your procedure 2 to not produce the result set or capture it in procedure 1. I.e. look for SELECT statements in procedure 2 and change them to put the result of the SELECT into a local temp table (e.g. add INTO #temp) or change your call in procedure 1 to be "SELECT * into #TEMP from procedure-2()"

[VolkerBarth]

...and yes, a code snippet would make the explanation easier, too, methinks...)