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.

asked 01 Feb '12, 05:44

Takudzwa's gravatar image

Takudzwa
818813
accept rate: 0%

edited 01 Feb '12, 07:52

Mark%20Culp's gravatar image

Mark Culp
23.2k9132272

1

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.

(01 Feb '12, 07:55) Martin

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.

(01 Feb '12, 08:04) Takudzwa
Replies hidden

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?

(01 Feb '12, 10:41) Mark Culp

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()"

permanent link

answered 01 Feb '12, 08:01

Mark%20Culp's gravatar image

Mark Culp
23.2k9132272
accept rate: 40%

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

(01 Feb '12, 08:17) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×106
×16

question asked: 01 Feb '12, 05:44

question was seen: 2,573 times

last updated: 01 Feb '12, 10:41