I have "Refreshed Catalog" for my project. However, the new stored procedure I just put in there does not show up when I select "Add New Retrieval Stored Procedure Call". What do I need to do?

Is it listed under Action procedures? This can happen if the stored procedure contains IF clauses, temp tables or other advanced stuff and the SqlServer routine I use is not reporting the correct amount of resultsets or simply crashes with an exception. (I can't avoid this btw).

When that happens, the proc is marked as an action procedure which means it has an amount of resultsets of 0. You can change that number in teh Catalog explorer: browse to the stored procedure, click open the Amount Resultsets node, you'll see '#0', select it, RMB-> alter it and set it to 1. Now the procedure is a retrieval procedure and you can add it as a retrieval proc.

looks good...thanks for your help.

Hey I'm having a similar problem. The designer has greyed out the "Add stored proc" options when I right-click either of the Stored proc options, both retrieval and action. And there are indeed 2 stored procedures in existence.

This is the stored proc I've created: -- ########################################## Table Spaced Used CREATE PROCEDURE dbo.TableSpaceUsed AS

-- Create the temporary table... CREATE TABLE #tblResults ( [name] nvarchar(30), [rows] int, [reserved] varchar(50), [reserved_int] int default(0), [data] varchar(50), [data_int] int default(0), [index_size] varchar(50), [index_size_int] int default(0), [unused] varchar(50), [unused_int] int default(0) )

-- Populate the temp table... EXEC sp_MSforeachtable @command1= "INSERT INTO #tblResults ([name],[rows],[reserved],[data],[index_size],[unused]) EXEC sp_spaceused '?'"

-- Strip out the " KB" portion from the fields UPDATE #tblResults SET [reserved_int] = CAST(SUBSTRING([reserved], 1, CHARINDEX(' ', [reserved])) AS int), [data_int] = CAST(SUBSTRING([data], 1, CHARINDEX(' ', [data])) AS int), [index_size_int] = CAST(SUBSTRING([index_size], 1, CHARINDEX(' ', [index_size])) AS int), [unused_int] = CAST(SUBSTRING([unused], 1, CHARINDEX(' ', [unused])) AS int) -- Return the results...

SELECT * FROM #tblResults

Could it be some sort of permissions thing? I've done the unattended refresh.

Thanks, - Jared

Jared, please read above conversation and tell us if that works for you.

Otis wrote:

When that happens, the proc is marked as an action procedure which means it has an amount of resultsets of 0. You can change that number in teh Catalog explorer: browse to the stored procedure, click open the Amount Resultsets node, you'll see '#0', select it, RMB-> alter it and set it to 1. Now the procedure is a retrieval procedure and you can add it as a retrieval proc.

In the catalog explorer it doesn't pick up any of the stored procs that exists on the database, and I've checked everything that it lists. Is there anything I might have to change in Ms SQL that would be preventing llbl from recognizing the stored procs?

Thanks, - Jared

Did you select the "Stored Procedures" in the "Elements to read from the database schema" section in the "Catalog Refresh/ New Project" window?

Bingo! Thanks a lot man.

Otis wrote:

the proc is marked as an action procedure which means it has an amount of resultsets of 0. You can change that number in teh Catalog explorer: browse to the stored procedure, click open the Amount Resultsets node, you'll see '#0', select it, RMB-> alter it and set it to 1. Now the procedure is a retrieval procedure and you can add it as a retrieval proc.

We are able in PROJECT explorer browse to the stored procedure, click Edit/Properties and see 0 in the column "# of recordset" in Catalog Details tab. But how to alter it? Version of LLBLGen Pro is 2.5 final.

Hi Michael,

You can [right click] the node or press [F2] to edit the value http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=12379&StartAtMessage=0&#68751

Cheers

daelmo wrote:

Hi Michael,

You can [right click] the node or press [F2] to edit the value http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=12379&StartAtMessage=0&#68751

Cheers

Thanks. I've incorrectly used PROJECT explorer instead of CATALOG explorer