MS SQL 2005 offers the integration of a CLR Stored Procedure Assembly.

Unfortunately LLBLGen Pro (May 07 build) doesn't list the stored pro's contained in this assembly. They are listed and working okay in MS SQL Server Management Studio.

Already tried to write a T-SQL wrapper. ("Normal" stored pro that uses the stored pro from the assembly.) No luck either.

Any idea what might be going wrong. Or does LLBLGen not support them at all?

Hello,

did you see your t-sql wrapper store procedure or not? If it's a "normal" store proc you should seen it.

No it doesn't show up. Here's the wrapper:

CREATE PROCEDURE dbo.sp_GetOrdersWrapper ( @viewingUserId int, @companyId int, @placedByUserId int, @orderStateId int, @DateFrom nvarchar(10), @DateTo nvarchar(10), @ReceiverSearchPattern nvarchar(255), @ArticleSearchPattern nvarchar(255), @ConfirmState int, @ErrorState int, @isHTML bit ) AS SET NOCOUNT ON exec GetOrders @viewingUserId, @companyId, @placedByUserId, @orderStateId, @DateFrom, @DateTo, @ReceiverSearchPattern, @ArticleSearchPattern, @ConfirmState, @ErrorState, @isHTML RETURN

In the LLBL designer, do you see any other store proc that didn't call a .net function? When you try to get the store procedure, did you have any error or just nothing on the right of the designer?

All otherStored Procedures are working fine and show up in LLBLGen.

======

I think I found the problem.

LLBLGen seems to expect a SELECT statement in the Store Procedure. I assume that is because it can tell whether the EXEC statement will return any records at all. So the wrapper gets ignored as well.

If I alter the wrapper to:

ALTER PROCEDURE dbo.sp_GetOrdersWrapper ( @viewingUserId int, @companyId int, @placedByUserId int, @orderStateId int, @DateFrom nvarchar(10), @DateTo nvarchar(10), @ReceiverSearchPattern nvarchar(255), @ArticleSearchPattern nvarchar(255), @ConfirmState int, @ErrorState int, @isHTML bit ) AS SET NOCOUNT ON DECLARE @OrderTable TABLE ( OrderID int, DatePlaced date, DeliveryAddress nvarchar(500), OrderContent nvarchar(500), PlacedByUserLogin nvarchar(500), IsConfirmed Status nvarchar(50), ErrorName nvarchar(50), PlacedByUserID int )

INSERT INTO @OrderTable exec GetOrders @viewingUserId, @companyId, @placedByUserId, @orderStateId, @DateFrom, @DateTo, @ReceiverSearchPattern, @ArticleSearchPattern, @ConfirmState, @ErrorState, @isHTML

SELECT * FROM @OrderTable RETURN

it will show up.

But you must admit, that this is a very dirty hack!

Hello,

in the designer, you can add "new retrieval stored procedure call" but you can also add "new action stored procedure call". Did you try to add your store procedure using the "new action stored procedure call" option in the designer?

I hadn't tried it till now.

Just did and you're right, it shows up there.

Can I still use it to retrieve data?

If you want to retrieve data, you will have a store proc that send you data, so you can add it from the "add new retrieval stored procedure" option. Else you can add the "add new action stored procedure" option.

Sorry, to keep you waiting. Got phonecall in between.

I think you misunderstood my question.

GetOrders is a retrieval procedure (returns datatable). As LLBLGen wrongly identifies it as an ActionProcedure, I will not be able to call it through RetrievalProcedures and thus will not be able to use for retrieving the data.

Seems for now, the only possibility is using the wrapper hack.

To change an action procedure to a retrieval procedure: Go to the Catalog Explorer in the Designer: browse to the stored procedure, click open the Amount Resultsets node, you'll see '#0', select it, right click-> alter it and set it to 1. Now the procedure is a retrieval procedure and you can add it as a retrieval proc.

Hey, thanks for the tip. Working fine now.

In between had used DbUtils.CallRetrievalStoredProcedure as a workaround.

BTW: If you think this thread might be of public interest, could you plz. release it?

BTW: If you think this thread might be of public interest, could you plz. release it?

We will do, thanks.

Moved to Designer