locked
Add Function Import - The selected stored procedure returns no columns RRS feed

  • Question

  • When I click "Get Column Information", I get "The selected stored procedure returns no columns" no matter what the structure of the select statement at the end of the sp I'm trying to import.  Is this something that is supposed to work in the latest VS2010 beta?

    If it is supposed to work, where is it supposed to get the column info?  From a system table or simply scanning the sp source SQL?  My SQL is complex with several selects inside it before returning a result set.

    Wednesday, January 13, 2010 1:47 PM

All replies

  • YES,VS 2k10 can detect return type  of store procedure as you expected   Big Smile , more over it also created complex type  automatically  if nessesary.

     

     in Vs 2010 , the selected store procudure may be returned :

    - None 

    - Scalar Value

    - Entities

    - Complex type

    Wednesday, January 13, 2010 11:19 PM
  • Make sure your SP starts with a SET NOCOUNT OFF.

     

    Wednesday, January 13, 2010 11:29 PM
  • I've set NOCOUNT off to no avail. I still don't get detection of columns when I click the button. Any other suggestions? Is there something else I need to do?

    I just tried running sp_sproc_columns against some of my sp's and I only get the parameters, not the result columns, so maybe this is a problem in SQL.  I'm running SQL 2008 Dev Edition.  Also, I tried running against some system sps (such as sp_sproc_columns sp_sproc_columns ).  It still doesn't work.  Any guess why this is happening?

    BTW, I've asked another user to test this and he gets the same results.

     

     

    Thursday, January 14, 2010 4:23 PM
  • The detection of complex types is not working for me.  When I click the button it says there are no columns returned by the sp.  Has anyone else encountered and hopefully fixed this problem?

    Saturday, January 16, 2010 2:20 PM
  • OK, I just tried a simple example and it worked.  The sprocs I'm having problems with are complex with dynamic SQL and temp tables.  However, they all have a simple select statement at the end.  Is this a knownissue?  If not, I can post a sample sproc where detection fails if anyone is interested.

    Saturday, January 16, 2010 3:21 PM
  • Hi,

    I'd like to check the issue, could you please post a sample store procedure to reproduce it? Thanks

     

    Sunday, January 17, 2010 3:32 AM
  • Hi Frank:

    A sample sp is below.  I can't give you the actual data -- it's sensitive in nature.

    SET ANSI_NULLS ON

    GO

    SET
    QUOTED_IDENTIFIER ON

    GO

    ALTER PROCEDURE [dbo].[usp_kpi_PercentDeniedByPayer_KPI]

    @TopN int, @start_date date, @end_date date, @PST_FLAG varchar(1) = 'P'

    AS

    /* test

    exec [[usp_kpi_PercentDeniedByPayer_KPI]] 19, '12/1/2009', '12/31/2009', 'P'

    */

    BEGIN

    --Percent Denied By Payer KPI

    declare @KPI_MASTER_ID int --this KPIs ID

    declare @KPI_ID int --the ID for this KPI in the KPIs table

    set @KPI_ID = 3

    create table #max_status_dates (ClaimNumber varchar(20), PolicyNumber varchar(20),

    PayerName_Claims varchar(50), PayerName_Payments varchar(100), PayerName_Responses varchar(100),

    PST varchar(1),

    MaxDate date)

    declare @sql_insert varchar(2048)

    set @sql_insert =

    'select ClaimNumber, PolicyNumber, PayerName_Claims, PayerName_Payments, PayerName_Responses,

    PST_SBR01, Max(MaxDate)

    FROM

    (

    select c.ClaimNumber, c.PolicyNumber, c.PayerName as PayerName_Claims,

    p.PayerName as PayerName_Payments,

    NULL as PayerName_Responses, c.PST_SBR01,

    max(p.PaymentDate) MaxDate --, p.PaymentStatusCode --, c.ClaimAmount, c.ClaimEstAmountDue

    from Claims c left outer join PayerAlias pa

    ON c.PayerName = pa.PayerName_Claims

    inner join Payments p ON

    c.claimnumber = p.claimnumber and c.policynumber = p.policynumber

    and pa.PayerName_Payments = p.PayerName

    where PST_SBR01 = '''
    + @PST_FLAG + ''' and c.ClaimDate BETWEEN ''' + cast(@start_date as varchar(15)) + ''' AND ''' + cast(@end_date as varchar(15)) + '''

    group by c.ClaimNumber, c.PolicyNumber, c.PayerName, p.PayerName, c.PST_SBR01

    UNION ALL

    select c.ClaimNumber, c.PolicyNumber, c.PayerName as PayerName_Claims, NULL as PayerName_Payments,

    r.PayerName as PayerName_Responses,

    c.PST_SBR01, max(r.StatusEffectiveDate) MaxDate --, p.PaymentStatusCode --, c.ClaimAmount, c.ClaimEstAmountDue

    from Claims c left outer join PayerAlias_277 pa2

    ON c.PayerName = pa2.PayerName_Claims

    inner join Responses r ON

    c.claimnumber = r.claimnumber and c.policynumber = r.policynumber

    and pa2.PayerName_Responses = r.PayerName

    where PST_SBR01 = '''
    + @PST_FLAG + '''and c.ClaimDAte BETWEEN ''' + cast(@start_date as varchar(15)) + ''' AND ''' + cast(@end_date as varchar(15)) + '''

    group by c.ClaimNumber, c.PolicyNumber, c.PayerName, r.PayerName, c.PST_SBR01

    )I

    group by ClaimNumber, PolicyNumber, PayerName_Claims, PayerName_Payments, PayerName_Responses,

    PST_SBR01'

    --print @sql_insert

    insert #max_status_dates

    exec (@sql_insert)

    --==========================================================

    --this removes any dups due to multiple line items and gets each

    --status code that occurred on the most recent activity date

    --==========================================================

    create table #claim_status_codes (ClaimNumber varchar(20), PolicyNumber varchar(20),

    PayerName_Claims varchar(50), PayerName_Payments varchar(100), PayerName_Responses varchar(100),

    PST varchar(1),

    MaxDate date, StatusCode varchar(3))

    insert #claim_status_codes

    select *

    from

    (

    select distinct d.ClaimNumber, d.PolicyNumber, d.PayerName_Claims, p.PayerName as PayerName_Payments,

    NULL as PayerName_Responses, PST, MaxDate, p.PaymentStatusCode as StatusCode

    from Payments p inner join #max_status_dates d on

    p.ClaimNumber = d.ClaimNumber and p.PolicyNumber = d.PolicyNumber

    and p.PaymentDate = d.MaxDate and p.PayerName = d.PayerName_Payments

    UNION ALL

    select distinct d.ClaimNumber, d.PolicyNumber, d.PayerName_Claims, NULL as PayerName_Responses,

    r.PayerName as PayerName_Responses,PST, MaxDate, r.StatusCategory as StatusCode

    from Responses r inner join #max_status_dates d on

    r.ClaimNumber = d.ClaimNumber and r.PolicyNumber = d.PolicyNumber

    and r.StatusEffectiveDate = d.MaxDate

    )I

    --====================================================================================

    --GET ALL CLAIMS THAT HAVE DENIAL CODE AND DO NOT ALSO HAVE A DIFFERENT STATUS

    --CODE ON THE SAME DATE; WE ARE CONSIDERING CLAIMS WITH BOTH DENIED AND APPROVED CODES

    --ON THE MOST RECENT TRANS DATE AS 'PARTIAL APPROVED' AND THEREFORE, NOT DENIED

    --====================================================================================

    --put the denials in table

    --put non denials in table

    --do outer join to filter out claims that have a "non denied" code on the max date

     

    select distinct maxdate, policynumber, claimnumber, PayerName_Claims, PST, StatusCode -- PayerName_Payments, PayerName_Responses,StatusCode, PST, MaxDate

    INTO #denials

    from #claim_status_codes where StatusCode IN ('4','A3','A4','E0','E1','F2')

    --group by policynumber, claimnumber, PayerName_Claims, PayerName_Payments, PST, PaymentStatusCode

    select distinct policynumber, claimnumber, PayerName_Claims, PST -- PayerName_Payments, PayerName_Responses,StatusCode, PST, MaxDate

    INTO #not_denied

    from #claim_status_codes where StatusCode NOT IN ('4','A3','A4','E0','E1','F2')

    --drop table #denied

    --USING MIN STATUS CODE TO GIVE THE PAYMENTS CODE OF 4 PRECEDENCE WHEN WE HAVE DENIALS IN BOTH TABLES

    --RESPONSES AND PAYMENTS

    select min(StatusCode)StatusCode, I.MaxDate, I.ClaimNumber, I.PolicyNumber, I.PayerName_Claims, I.PST

    INTO #denied from #denials d inner join

    (

    select max(maxdate)MaxDate, d.ClaimNumber, d.PolicyNumber, d.PayerName_Claims, d.PST

    --into #denied

    from #denials d left outer join #not_denied nd

    on d.claimnumber = nd.claimnumber and

    d.policynumber = nd.policynumber and

    d.PayerName_Claims = nd.PayerName_Claims and

    d.PST = nd.PST

    WHERE nd.claimnumber is null

    group by d.ClaimNumber, d.PolicyNumber, d.PayerName_Claims, d.PST

    )I

    ON d.MaxDate = I.MaxDate and d.ClaimNumber = I.ClaimNumber and d.PolicyNumber = I.PolicyNumber

    and d.PayerName_Claims = I.PayerName_Claims and d.PST = I.PST

    Group by I.MaxDate, I.ClaimNumber, I.PolicyNumber, I.PayerName_Claims, I.PST

    --==================================================================

    -- GET THE TOTAL NUMBER OF DISTINCT DENIED CLAIMS

    --==================================================================

    declare @TOTAL_DENIED decimal(18,2)

    declare @sql varchar(1024)

    select @TOTAL_DENIED = count(*) from #denied

    create table #topN (PayerName varchar(100), DeniedClaims int, PercentDeniedByPayer decimal(18,2))

    create table #other (PayerName varchar(100), DeniedClaims int, PercentDeniedByPayer decimal(18,2))

     

    set @sql = 'SELECT TOP ' + cast(@topN as varchar(10)) + ' PayerName_Claims, count(*) DeniedClaims, count(*)/' + cast(@TOTAL_DENIED as varchar(10)) + ' * 100 as PercentDenied

    from #denied

    group by PayerName_Claims order by 3 desc'

    --print @sql

    insert #topN(PayerName, DeniedClaims, PercentDeniedByPayer)

    exec (@sql)

    --select * from #topN

    insert #other

    select 'OTHER', count(*) DeniedClaims, count(*)/@TOTAL_DENIED * 100 PercentPaidByPayer

    FROM #denied p

    LEFT OUTER JOIN #topN tp on p.PayerName_Claims = tp.PayerName

    where tp.PayerName is NULL

    print @TOTAL_DENIED

    --select * from #topN

    --UNION ALL

    --select * from #other

    --select * from #denied

    --=========================================

    --now we'll insert into the KPI tables

    --=========================================

    insert into KPIMaster(KPIID, TopN, StartDate, EndDate)

    select @KPI_ID, @topN, @start_date, @end_date

    set @KPI_MASTER_ID = @@Identity

    print '@KPI_MASTER_ID'

    print @KPI_MASTER_ID

    declare @denied_ct decimal(12,2)

    declare @total_ct decimal(12,2)

    set @denied_ct = (select COUNT(*) from #denied)

    set @total_ct = (select COUNT(*) from #claim_status_codes)

    print @denied_ct

    print @total_ct

    UPDATE KPIMaster SET PercentTotal = (@denied_ct/@total_ct) * 100

    WHERE KPIMasterID = @KPI_MASTER_ID

    insert into KPISummary(KPIMasterID, PayerName, DeniedClaims, PercentDeniedByPayer)

    select * from

    (

    select @KPI_MASTER_ID as KPIMasterID, PayerName, DeniedClaims, PercentDeniedByPayer

    from #topN

    UNION ALL

    select @KPI_MASTER_ID as KPIMasterID, PayerName, DeniedClaims, PercentDeniedByPayer

    from #other

    )I

    declare @KPISUMMARY_OTHER INT

    select @KPISUMMARY_OTHER = KPISummaryID FROM

    KPISummary WHERE KPIMasterID = @KPI_MASTER_ID AND

    PayerName = 'OTHER'

     

    insert into KPIDetails(KPIMasterID, KPISummaryID, PayerName, ClaimNumber, PolicyNumber,DenialCode)

    select @KPI_MASTER_ID, KPISummaryID, d.PayerName_Claims, d.ClaimNumber, d.PolicyNumber, d.StatusCode

    from #denied d

    LEFT join KPISummary kpi ON kpi.PayerName = d.PayerName_Claims

    WHERE KPIMasterID = @KPI_MASTER_ID

    UNION ALL

    select @KPI_MASTER_ID, @KPISUMMARY_OTHER, d.PayerName_Claims, d.ClaimNumber, d.PolicyNumber, d.StatusCode

    from #denied d

    left outer join

    (

    select d.* from #denied d

    inner join KPISummary kpi ON d.PayerName_Claims=kpi.PayerName

    WHERE KPIMasterID = @KPI_MASTER_ID

    )I on

    d.claimnumber = I.claimnumber and d.policynumber = I.policynumber

    and d.PayerName_Claims = I.PayerName_Claims

    where I.claimnumber is null

    select @KPI_MASTER_ID as KPIMasterID

    drop table #max_status_dates

    drop table #claim_status_codes

    drop table #denied

    drop table #topN

    drop table #other

    drop table #denials

    drop table #not_denied

    return(@KPI_MASTER_ID)

    END

     

     

    Sunday, January 17, 2010 6:04 PM
  • Hi,

    Thanks for posting the code.

    Since I cannot run your sample, I did some research on how Visual Studio gets the column information. If you open SQL Server profiler when you click "Get Column Information", you will find that Visual Studio queries against database to get the column information. One query I notice is that Visual Studio tries to execute the store procedure with all parameters set to be null. I am not sure if this is the way Visual Studio determines whether there's column information or not. But I suggest you trying to execute your query in this way to see if there's any column returned by your store procedure. Of course you can monitor the profiler your own to find more useful information.
    Monday, January 18, 2010 1:43 AM
  • Do you know if there a way to declare default parameter values instead of it passing null values when trying to get the column information?
    Monday, January 18, 2010 9:29 AM
  • The query did complete and return a result set, but with divide by zero errors when all nulls are supplied as parameters.  I revised the sp so that that did not happen.  I refreshed the sp in the edmx and tried a function import, but still got the same result, i.e. "sp returns no columns", so something more complex is happening. 

     

    I ran trace as you suggested and I found the following code was executed:

    SET FMTONLY OFF; SET FMTONLY ON;

    exec HCDAData.dbo.usp_kpi_PercentDeniedByPayer_KPI @TopN=NULL,@start_date=NULL,@end_date=NULL,@PST_FLAG=NULL

    SET FMTONLY OFF;

    When I do this manually, the sp returns a column with name KPIMasterID, but with no rows, but this data is not getting picked up by the function import.

     

     

    Monday, January 18, 2010 1:08 PM
  • Hi TonySawyer,

    Sorry, I don't know how to pass a default value when you click "Get Column Information", NULL value is passed by Visual Studio automatically, even I set a default value in Store Procedure, Visual Studio will still use Null:(

    Hi terryl,

    I guess it's caused by the last "Return @KPIMasterID" in your store procedure. Could you please try to remove it to see if "Get Column Information" returns any column information? If it does, we may consider using an output parameter in SP instead of returning it.

    Monday, January 18, 2010 10:54 PM
  • Hi Frank:

     I've tried just doing a select, just a return, and both together.  No combination works.  I just removed the return and tried it again to verify.  Thanks for the suggestion, but no cigar.  Maybe the dynamic sql is interfering?  I'll try some tests.

    Thanks for your suggestions.  It would be good if we could figure this out prior to the RTM. 

    Monday, January 18, 2010 11:59 PM
  • Here's a much simplified sp (about 70 lines) that fails.  The insert ... exec (dynamic sql) statement, when executed, causes the function import column detection to fail.

    ALTER PROCEDURE [dbo].[x]

    -- Add the parameters for the stored procedure here

    @TopN int, @start_date date, @end_date date, @PST_FLAG varchar(1) = 'P'

    AS

    /* test

    SET FMTONLY OFF; SET FMTONLY ON;

    exec x @TopN=NULL,@start_date=NULL,@end_date=NULL,@PST_FLAG=NULL

    SET FMTONLY OFF;

    */

    BEGIN

    --Percent Denied By Payer KPI

    declare @KPI_MASTER_ID int --this KPIs ID

    declare @KPI_ID int --the ID for this KPI in the KPIs table

    set @KPI_ID = 3

    --GET UNIQUE RECORDS

    create table #max_status_dates (ClaimNumber varchar(20), PolicyNumber varchar(20),

    PayerName_Claims varchar(50), PayerName_Payments varchar(100), PayerName_Responses varchar(100),

    PST varchar(1),

    MaxDate date) --, PaymentStatusCode varchar(3))

    declare @sql_insert varchar(2048)

    set @sql_insert =

    'select ClaimNumber, PolicyNumber, PayerName_Claims, PayerName_Payments, PayerName_Responses,

    PST_SBR01, Max(MaxDate)

    FROM

    (

    select c.ClaimNumber, c.PolicyNumber, c.PayerName as PayerName_Claims,

    p.PayerName as PayerName_Payments,

    NULL as PayerName_Responses, c.PST_SBR01,

    max(p.PaymentDate) MaxDate --, p.PaymentStatusCode --, c.ClaimAmount, c.ClaimEstAmountDue

    from Claims c left outer join PayerAlias pa

    ON c.PayerName = pa.PayerName_Claims

    inner join Payments p ON

    c.claimnumber = p.claimnumber and c.policynumber = p.policynumber

    and pa.PayerName_Payments = p.PayerName

    where PST_SBR01 = '''
    + @PST_FLAG + ''' and c.ClaimDate BETWEEN ''' + cast(@start_date as varchar(15)) + ''' AND ''' + cast(@end_date as varchar(15)) + '''

    group by c.ClaimNumber, c.PolicyNumber, c.PayerName, p.PayerName, c.PST_SBR01

    UNION ALL

    select c.ClaimNumber, c.PolicyNumber, c.PayerName as PayerName_Claims, NULL as PayerName_Payments,

    r.PayerName as PayerName_Responses,

    c.PST_SBR01, max(r.StatusEffectiveDate) MaxDate --, p.PaymentStatusCode --, c.ClaimAmount, c.ClaimEstAmountDue

    from Claims c left outer join PayerAlias_277 pa2

    ON c.PayerName = pa2.PayerName_Claims

    inner join Responses r ON

    c.claimnumber = r.claimnumber and c.policynumber = r.policynumber

    and pa2.PayerName_Responses = r.PayerName

    where PST_SBR01 = '''
    + @PST_FLAG + '''and c.ClaimDAte BETWEEN ''' + cast(@start_date as varchar(15)) + ''' AND ''' + cast(@end_date as varchar(15)) + '''

    group by c.ClaimNumber, c.PolicyNumber, c.PayerName, r.PayerName, c.PST_SBR01

    )I

    group by ClaimNumber, PolicyNumber, PayerName_Claims, PayerName_Payments, PayerName_Responses,

    PST_SBR01'

    insert #max_status_dates

    exec (@sql_insert) -- FAILS HERE

    insert into KPIMaster(KPIID, TopN, StartDate, EndDate)

    select @KPI_ID, @topN, @start_date, @end_date

    set @KPI_MASTER_ID = @@Identity

    select @KPI_MASTER_ID as KPIMasterID

    END

     

    Tuesday, January 19, 2010 12:33 AM
  • Hi,

    I did some test on my machine and here is the test result from my side:

    As long as your store procedure returns any result set when runnning with all parameters being set to NULL, Visual studio is able to display the column information when you click "Get Column Information", no matter you use dynamic query or return a value at the end of store procedure

    In my opinion, when you run your store procedure against query analyzer, if you see a tabular result set, Visual Studio should be able to display column information, if it doesn't, there might be a bug existing...

    Tuesday, January 19, 2010 2:18 AM
  • I test it in two simple sample . one  contain only some SELECT statment,which get my columns greatly. and the other contain some INSERT/UPDATE/DELETE statement,and it was fail to get the same columns.
    Friday, January 22, 2010 8:13 AM
  • It might be that the insert...select is not really functioning like a select.

     You might try something like the following (sorry this is an UPDATE and not an INSERT, but it was handy):

    BEGIN TRAN
    DECLARE @t table (
        ID int NOT NULL,
        Data xml);

    UPDATE TOP (1) xml_msg WITH (readpast)
    SET Processed = 1
    OUTPUT INSERTED.ID, INSERTED.Data  INTO @t
    WHERE Processed = 0;

    SELECT ID, Data FROM @t;

    Friday, April 16, 2010 3:27 PM
  • Is there allready a solution for this problem? It gives me also a lott of problems to get my data back from a stored procedure. I have tried to set nocount on. I have given the variables in my stored procedure a value and removed the return but still get the message the stored procedure returns no columns

     Rgds Wim

    Tuesday, May 4, 2010 2:07 PM
  • Stick out tongue I have found a workaround by generating a function for the stored procedure and then create manualy a complex type with the output collums that the SP should give.

    I think the cause of the problem is when when you use dynamic query in your SP.

    Anyway for me the solution of building manualy the complex type will do!

    Wednesday, May 5, 2010 7:34 AM
  • In my stored stored procedures, Columns will be generated dynamically. I have created using the pivot queries. So the resultset columns cannot be identified. Please let us know how to add function import to this. Is there any blog or tutorial available? Thanks in adavance.

    Sunday, July 4, 2010 4:36 AM
  • To the best of my knowledge, Entity Framework is a lightweight ORM (object-relational mapping) utility. As such, the expectation is that you have statically-defined objects to which a database data structure is to be mapped. So I think you're looking at the wrong utility for what you're trying to do. I think you'll need to drop back to the technology underlying Entity Framework (or having roughly the equivalent functionality) and roll your own solution. Check out the System.Data.DataSet class (http://msdn.microsoft.com/en-us/library/system.data.dataset(VS.71).aspx). It's a pretty flexible mechanism for pulling data into memory for easy manipulation. Best of luck!
    Monday, July 5, 2010 9:29 AM
  • fyi - this is generally the same issue as described here for reporting services:


    http://completedevelopment.blogspot.com/2009/02/cant-use-temp-tables-in-stored-procs.html


    I just tested a proc that didnt work, I added

    SET FMTONLY OFF

    and voila - it works.


    Friday, July 9, 2010 12:28 AM
  • SET FMTONLY OFF


    solved this problem for me - thanks!

    Monday, August 23, 2010 1:58 PM
  • A call to SET FMTONLY OFF at the start of my stored procedure solved it for me - many thanks!!

    Friday, May 13, 2011 6:05 PM