Hi,

I create a stored procedure to leverage FULLTEXTTABLE in SQL Server 2008R2. When I go to map the stored procedure to a typed view in the designer, the Reverse-engineer ResultSets to TypeView Definitions option is disabled. It was enabled originally and I was able to add a TypedView, then I went and modified my stored proc to add a new field. After refreshing the catalog, this option is now disabled. So I went back to the original stored proc and still it is disabled.

This is the procedure I am attempting to have a typed view for:

/****** Object:  StoredProcedure [dbo].[sp_RelevantTransactionCategory] Script Date: 09/04/2010 16:48:11 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:      Me
-- Create date: 09-04-2010
-- Description: Uses a FULLTEXTTABLE search to find relevant transaction categories previously assinged to a transaction.
-- =============================================
CREATE PROCEDURE [dbo].[sp_RelevantTransactionCategory] 
    -- Add the parameters for the stored procedure here
    @accountId INT = 0, 
    @searchString NVARCHAR(50) = ''
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    DECLARE @topRank int  
    
    SET @topRank =
    (
        SELECT MAX(kt.RANK) 
        FROM dbo.transaction_digest AS td
            INNER JOIN FREETEXTTABLE(dbo.transaction_digest, [description], @searchString, 1) AS kt ON td.transaction_digest_id = kt.[KEY]
        WHERE 2=2
            AND td.account_id = @accountId
    )

    SELECT 
            td.Description
        ,   tc.transaction_category_id
        ,   kt.RANK
        ,   (CAST(kt.RANK as DECIMAL)/@topRank) as rank_percent  
    FROM dbo.transaction_digest AS td
        INNER JOIN FREETEXTTABLE(dbo.transaction_digest, [description], @searchString) AS kt ON td.transaction_digest_id = kt.[KEY]
        INNER JOIN transaction_category tc on td.transaction_category_id = tc.transaction_category_id
    WHERE 2=2
        AND td.account_id = @accountId
    ORDER BY kt.RANK DESC

END


GO

Using LLBLGen 3.0 August 18th, 2010

Thanks!

Did you check it in the last tab of the refresh catalog wizard? You have to do that so the resultset can be determined. If determination of that resultset fails (for whatever reason), the resultset isn't read and you can't map a typedview onto it. If you did all that and the resultset was determined (so it's present in the catalog explorer) could you attach the project file please?

Hi Frans...

I figured it out. I had recalled that my original sp had dummy data for @accountId and @searchString which happened to allow the sp to actually bring back results. So it seems the designer requires an actual set of results in order to allow a TypedView to be mapped. Once I was able to create my TypedView again, I went back to the sp and replaced the dummy data back with the sql variables.

By "last tab" I believe you mean the last page where it warns you about running against a production database. Yes, there I did select my sp, but the TypedView was still disabled. With some actual data returned however, the option became present.

If this isn't desired, and you still want my project, let me know.

Thanks!

Isz wrote:

Hi Frans...

I figured it out. I had recalled that my original sp had dummy data for @accountId and @searchString which happened to allow the sp to actually bring back results. So it seems the designer requires an actual set of results in order to allow a TypedView to be mapped. Once I was able to create my TypedView again, I went back to the sp and replaced the dummy data back with the sql variables.

By "last tab" I believe you mean the last page where it warns you about running against a production database. Yes, there I did select my sp, but the TypedView was still disabled. With some actual data returned however, the option became present.

If this isn't desired, and you still want my project, let me know.

Thanks!

It indeed has to return an actual resultset. What the driver does is executing the proc inside a transaction and rolling the transaction back when done. It can only determine what columns there are in the resultset if the resultset is actually returned. The trick for SQL Server, by using SET FMTONLY ON; exec proc ;SET FMTONLY OFF; not always works (temp table using procs fail for example). So your conclusion is correct: if the proc doesnt return a resultset, the columns can't be determined and thus you can't map a typed view onto a resultset (as the designer doesn't know which view fields to create )

I replied with this on another thread already, but it would be nice if there was some kind of config file / assembly which would allow me to specify valid inputs for a particular stored procedure to ensure a valid result set.

Just a thought..