Home
Help
Register
Log in

Search

 
   Active Threads  

You are here: Home > LLBLGen Pro > Designer> Can't see Stored pros from CLR Stored Procedure Assemblies
 

Pages: 1
Designer
Can't see Stored pros from CLR Stored Procedure Assemblies
Page:1/1 

  Print all messages in this thread  
Poster Message
kamiwa
User



Location:
Neuss, Germany
Joined on:
12-Jun-2007 13:05:59
Posted:
137 posts
# Posted on: 12-Jun-2007 13:21:32.  
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?

  Top
jbb
User



Location:

Joined on:
29-Nov-2005 13:38:48
Posted:
267 posts
# Posted on: 12-Jun-2007 15:07:58.  
Hello,

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


  Top
kamiwa
User



Location:
Neuss, Germany
Joined on:
12-Jun-2007 13:05:59
Posted:
137 posts
# Posted on: 12-Jun-2007 16:21:04.  
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
  Top
jbb
User



Location:

Joined on:
29-Nov-2005 13:38:48
Posted:
267 posts
# Posted on: 12-Jun-2007 16:30:12.  
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?


  Top
kamiwa
User



Location:
Neuss, Germany
Joined on:
12-Jun-2007 13:05:59
Posted:
137 posts
# Posted on: 12-Jun-2007 16:59:12.  
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!






  Top
jbb
User



Location:

Joined on:
29-Nov-2005 13:38:48
Posted:
267 posts
# Posted on: 12-Jun-2007 17:13:19.  
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?


  Top
kamiwa
User



Location:
Neuss, Germany
Joined on:
12-Jun-2007 13:05:59
Posted:
137 posts
# Posted on: 12-Jun-2007 17:18:13.  
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?
  Top
jbb
User



Location:

Joined on:
29-Nov-2005 13:38:48
Posted:
267 posts
# Posted on: 12-Jun-2007 17:21:05.  
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.

  Top
kamiwa
User



Location:
Neuss, Germany
Joined on:
12-Jun-2007 13:05:59
Posted:
137 posts
# Posted on: 12-Jun-2007 18:12:32.  
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.

  Top
Walaa
Support Team



Location:

Joined on:
21-Aug-2005 16:03:48
Posted:
14603 posts
# Posted on: 13-Jun-2007 08:13:43.  
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.


  Top
kamiwa
User



Location:
Neuss, Germany
Joined on:
12-Jun-2007 13:05:59
Posted:
137 posts
# Posted on: 15-Jun-2007 15:59:06.  
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?

  Top
Walaa
Support Team



Location:

Joined on:
21-Aug-2005 16:03:48
Posted:
14603 posts
# Posted on: 15-Jun-2007 16:07:57.  
Quote:
BTW: If you think this thread might be of public interest, could you plz. release it?

We will do, thanks.


  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37967 posts
# Posted on: 15-Jun-2007 19:02:29.  
Moved to Designer Regular Smiley
Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
Pages: 1  


Powered by HnD ©2002-2007 Solutions Design
HnD uses LLBLGen Pro

Version: 2.1.12172008 Final.