I'm having an issue with the autorecognition of stored procedure types for SQL Server 2005. The project I'm testing this on uses stored procedures containing extensive business logic. As such, most of the procedures return resultsets whether they are action sprocs or retrieval sprocs. I don't see a way to manually set the number of resultsets a sproc can return. Am I missing something?

Michael

You can do that in the catalog explorer: browse to the proc in the catalog explorer, click open the nodes, you'll find a node #0. Right click it, and select 'Change' from the context menu. then give it a value of 1 or 2.

You can also set the resultset parameter manually for more procs. Go to the preferences, and select 'SqlServerAutoDetermineSProcType' to false AND 'ManualSelectSProcsFromSchema' to true. These options are under 'Schema Element retrieval'.

You'll then get a popup, in which you can multi-select procs and set for all these procs the # of resultsets in one go. You can use a filter to select a lot of procs in one go to ease the selection process.

Ok, a sproc can return 0, 1 or "2 or more" resultsets. In the popup "Stored procedures selector" for manual selecting the sproc's type you only get these possibilities.

Why is it possible to set the number of resultsets in the catalog explorer to any value between 0 and 1000?

And: In some cases the popup "Stored procedures selector" shows "3" as number of resultsets. Why? What does that mean?

Thanks again in advance!

I don't think having 2 results to be returned would make a difference than having 1000 of results to return.

2 is enough indeed

Yeah, I think so, too. I just wondered why defining the amout of resultsets is handled in two different ways.

Sokon1 wrote:

And: In some cases the popup "Stored procedures selector" shows "3" as number of resultsets. Why? What does that mean?

Found it. The number seems to be the amout of "Selects" in a Sproc. Why sometimes there is a number larger than 2 and sometimes "2 or more" I only can guess.