none
Stored Procedure Function Imports issue RRS feed

  • Question

  • I'm trying to "Get Column Information" for a stored procedure in the "Edit Function Import"  dialog.  However, I get the error message "The selected stored procedure returns no columns".  

    I am able to "Execute" the stored procedure from the Server Explorer, and I can see the column names in the "Output" window.  

    Here is the stored procedure, which calls a synonym for a stored procedure in a linked server remote database:

    USE [sde_fci33]
    GO
    /****** Object: StoredProcedure [SDE_FCI33].[uspActuarialReviewFilingDateForCountyRateReviewGet]  Script Date: 06/29/2011 14:13:40 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    
    ALTER PROCEDURE [SDE_FCI33].[uspActuarialReviewFilingDateForCountyRateReviewGet]
    	
    	/*
    	(
    	@parameter1 int = 5,
    	@parameter2 datatype OUTPUT
    	)
    	*/
    AS
    	exec SDE_FCI33.synUspActuarialReviewFilingDateForCountyRateReviewGet
    
    	/* SET NOCOUNT ON */
    	RETURN
    

     

    I've read that linked server stored procedures need permissions enabled on the linked server for select privileges on the tables that provide columns to the stored procedure results.  I'm not sure what permissions would be needed by the Entity Framework "Edit Function Imports" dialog that aren't already in place to enable the Server Explorer "Execute" stored procedure tool.

    From this blog posting:

    http://mysoftwarenotes.wordpress.com/2010/11/02/entity-framework-4-the-selected-stored-procedure-returns-no-columns/

     

    Problem:

    Your stored procedure crosses databases and although you have all the rights you need in the database containing the sproc you don’t have the necessary rights to the database objects in the other database.  E.g., you don’t have select rights on a table in the other database.

    Solution:

     

    Give the user that is calling the sproc the correct rights in the other database or databases.

     

    Can you give me any tips on how to explain to our database admins what permissions need to be established on the linked server database?

    Has anyone else experienced and solved this problem?

    Thanks,

    Andrew

    Wednesday, June 29, 2011 6:28 PM

Answers

  • On 6/29/2011 2:28 PM, grendel126 wrote:
     
    > *Give the user that is calling the sproc the correct rights in the other
    > database or databases.*
    >
    > Can you give me any tips on how to explain to our database admins what
    > permissions need to be established on the linked server database?
    >
    > Has anyone else experienced and solved this problem?
    >
     
    Well use a generic user-id and password that has the power on the linked
    SQL Server database in the connection string. You should discuss this
    with your DBA. Usually it's a generic user-id and password that has dbo
    rights on the SQL Server.
     
    You know, EF is not a stops all and ends all solution. There should be
    nothing stopping you form using EF and ADO.NET SQL Command objects
    within the same solution -- two different connection strings one for EF
    and one for the ADO.NET SQL Command objects part of the solution. Maybe
    you should think about using ADO.NET SQL Command objects to execute the
    sproc
     
    There is also a backdoor on EF that you can use that will use the Entity
    Connection, and you can use ADO.NET SQL Command object to execute a
    sproc the traditional way as an example through the EF connection. I
    have used the backdoor.
     
     
     
    • Marked as answer by grendel126 Thursday, June 30, 2011 3:09 PM
    Wednesday, June 29, 2011 7:21 PM

All replies

  • On 6/29/2011 2:28 PM, grendel126 wrote:
     
    > *Give the user that is calling the sproc the correct rights in the other
    > database or databases.*
    >
    > Can you give me any tips on how to explain to our database admins what
    > permissions need to be established on the linked server database?
    >
    > Has anyone else experienced and solved this problem?
    >
     
    Well use a generic user-id and password that has the power on the linked
    SQL Server database in the connection string. You should discuss this
    with your DBA. Usually it's a generic user-id and password that has dbo
    rights on the SQL Server.
     
    You know, EF is not a stops all and ends all solution. There should be
    nothing stopping you form using EF and ADO.NET SQL Command objects
    within the same solution -- two different connection strings one for EF
    and one for the ADO.NET SQL Command objects part of the solution. Maybe
    you should think about using ADO.NET SQL Command objects to execute the
    sproc
     
    There is also a backdoor on EF that you can use that will use the Entity
    Connection, and you can use ADO.NET SQL Command object to execute a
    sproc the traditional way as an example through the EF connection. I
    have used the backdoor.
     
     
     
    • Marked as answer by grendel126 Thursday, June 30, 2011 3:09 PM
    Wednesday, June 29, 2011 7:21 PM
  • Your suggestions were just what I needed to get me from being stuck on this Entity Framework issue.  I still haven't gotten the "Edit Function Import" dialog method working, and I'm not going to spend more time figuring out the database permissions issue is on the linked server.  I don't have much control over that server, so troubleshooting is difficult.  

    I am now using the backdoor method you mentioned to run a SqlCommand against the linked database to execute my stored procedures.  I'm hopeful that this will work.

    Thanks for your help!

    Thursday, June 30, 2011 3:09 PM