EF4 - The selected stored procedure returns no columns

Question

I have query in a stored procedure that calls some linked servers with some dynamic SQL. I understand that EF doesn't like that, so I specifically listed all the columns that would be returned. Yet, it still doesn't like that. What am I doing wrong here? I just want EF to be able to detect the columns returned from the stored procedure so I can create the classes I need.

Please see the following code that makes up the last lines of my stored procedure:

SELECT
    #TempMain.ID,
    #TempMain.Class_Data,
    #TempMain.Web_Store_Class1,
    #TempMain.Web_Store_Class2,
    #TempMain.Web_Store_Status,
    #TempMain.Cur_1pc_Cat51_Price,
    #TempMain.Cur_1pc_Cat52_Price,
    #TempMain.Cur_1pc_Cat61_Price,
    #TempMain.Cur_1pc_Cat62_Price,
    #TempMain.Cur_1pc_Cat63_Price,
    #TempMain.Flat_Length,
    #TempMain.Flat_Width,
    #TempMain.Item_Height,
    #TempMain.Item_Weight,
    #TempMain.Um,
    #TempMain.Lead_Time_Code,
    #TempMain.Wp_Image_Nme,
    #TempMain.Wp_Mod_Dte,
    #TempMain.Catalog_Price_Chg_Dt,
    #TempMain.Description,
    #TempMain.Supersede_Ctl,
    #TempMain.Supersede_Pn,
    TempDesc.Cust_Desc,
    TempMfgr.Mfgr_Item_Nbr,
    TempMfgr.Mfgr_Name,
    TempMfgr.Vendor_ID
FROM
    #TempMain
        LEFT JOIN TempDesc ON #TempMain.ID = TempDesc.ID
        LEFT JOIN TempMfgr ON #TempMain.ID = TempMfgr.ID

Larcher

Possible duplicate of Why can't Entity Framework see my Stored Procedure's column information? – Rain

Answer 1

EF doesn't support importing stored procedures which build result set from:

• Dynamic queries
• Temporary tables

The reason is that to import the procedure EF must execute it. Such operation can be dangerous because it can trigger some changes in the database. Because of that EF uses special SQL command before it executes the stored procedure:

SET FMTONLY ON

By executing this command stored procedure will return only "metadata" about columns in its result set and it will not execute its logic. But because the logic wasn't executed there is no temporary table (or built dynamic query) so metadata contains nothing.

You have two choices (except the one which requires re-writing your stored procedure to not use these features):

• Define the returned complex type manually (I guess it should work)
• Use a hack and just for adding the stored procedure put at its beginning SET FMTONLY OFF. This will allow rest of your SP's code to execute in normal way. Just make sure that your SP doesn't modify any data because these modifications will be executed during import! After successful import remove that hack.

Honkytonk

That worked on one of my procedures, but not the other. I ended up modifying my procedure to return an empty result set with the same data types while I was adding the procedure to the designer and the reverting back after the procedure was successfully added. – Larcher

The key point for me was "EF must execute it", my proc had an error when executing with null parameters – Gati

For my situation it was actually setting SET FMTONLY OFF – Ammamaria

"SET FMTONLY OFF" also worked for me in most cases BUT I stumbled in a case that the only think that solve the problem is eliminate unnecessary temporary tables and use WITH clause instead! Hope it helps someone. – Turne

This is the only correct explanation I have found till now. Thank you for explaining why EF acts weird when trying to update proc with temp tables. – Bromleigh

It supports temporary tables if you create them the right way: declare @MyTemporaryTable TABLE ( – Sheliasheline

This answer is missing a critical piece. When running with SET FMTONLY OFF, EF will actually attempt to run your sproc with all null values. Do a trace, you'll see. The thing is, your function may not work with SET FMTONLY OFF. We did a trace and could see the sproc was throwing an integer overflow error when one of the parameters is NULL. Could've spent my whole life not knowing this is the real answer. – Broken

If this solution doesn't solve your problem it's possible the EF "Get Column Information" wizard is timing out before your sp finishes - this will also yield the "no columns returned result". The solution is to temporarily speed up your sp by adding a WHERE clause for instance so the temp tables only return a limited data set. This worked for me. Don't forget to remove the WHERE after EF has retrieve/saved the column information. – Appolonia

@LuisGouveia that's a table variable, not a temp table – Sisyphus

Answer 2

Adding this Non-Logical block of code solved the problem. Even though it will never Hit

IF 1=0 BEGIN
    SET FMTONLY OFF
END

Why does my typed dataset not like temporary tables?

http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataset/thread/fe76d511-64a8-436d-9c16-6d09ecf436ea/

Hanser

After hours and hours, this solved my problem,tnx a lot – Clotildecloture

Make sure after you do this that your function can actually execute with all NULL values. EF will try to actually run your query with all NULL values. Do a trace; you'll see. We didn't know that the function was throwing integer overflow errors on NULL inputs. Might've never realized this is the real answer. – Broken

If this solution doesn't solve your problem it's possible the EF "Get Column Information" wizard is timing out before your sp finishes - this will also yield the "no columns returned result". The solution is to temporarily speed up your sp by adding a WHERE clause for instance so the temp tables only return a limited data set. This worked for me. Don't forget to remove the WHERE after EF has retrieve/saved the column information. – Appolonia

Answer 3

Or you can create a User-Defined Table Type and return that.

CREATE TYPE T1 AS TABLE 
( ID bigint NOT NULL
  ,Field1 varchar(max) COLLATE Latin1_General_CI_AI NOT NULL
  ,Field2 bit NOT NULL
  ,Field3 varchar(500) NOT NULL
  );
GO

Then in the procedure:

DECLARE @tempTable dbo.T1

INSERT @tempTable (ID, Field1, Field2, Field3)
SELECT .....

....

SELECT * FROM @tempTable

Now EF should be able to recognize the returned columns type.

Keare

Answer 4

As some others have noted, make sure the procedure actually runs. In particular, in my case, I was running the procedure happily without error in SQL Server Management Studio completely forgetting that I was logged in with admin rights. As soon as I tried running the procedure using my application's principal user I found there was a table in the query that that user did not have permission to access.

Stewardess

Answer 5

What I would add is:

That the import also fails if the stored procedures has parameters and returns no result set for the default parameter values.

My stored procedure had 2 float parameters and would not return anything when both parameters are 0.

So in order to add this stored procedure to the entity model, I set the value of these parameters in the stored procedure so that it is guaranteed to return some rows, no matter what the parameters actually are.

Then after adding this stored procedure to the entity model I undid the changes.

Desmonddesmoulins

Also some of you may be running complex cursor type stored procedures that require more than 15 seconds to get results back, and the dataset table adapter wizard times out after not getting any rows back within 15 seconds. An easy workaround is to target a complex select statement in your stored procedure and prefix it with "top 1" so it returns a single row right away. Build your Dataset Adapter with that. and modify your stored procedure back to full select and you should be good to go. – Pard

Answer 6

Interesting side note: Had the same problem which I first solved by using Table Variables, rather than Temp Tables (just for the import). That wasn't particularly intuitive to me, and threw me off when initially observing my two SProcs: one using Temp tables and one with Table Variables.

(SET FMTONLY OFF never worked for me, so I just changed my SProcs temporarily to get the column info, rather than bothering with the hack on the EF side just as an FYI.)

My best option was really just manually creating the complex type and mapping the function import to it. Worked great, and the only difference ended up being that an additional FactoryMethod to create the properties was included in the Designer.

Spirochaetosis

Answer 7

both solutions : 1- Define the returned complex type manually (I guess it should work) 2- Use a hack and just for adding the stored procedure put at its beginning SET FMTONLY OFF.

not working with me in some procedure however it worked with other one!

my procedure ends with this line:

SELECT machineId, production [AProduction]
        , (select production FROM #ShiftBFinalProd WHERE machineId = #ShiftAFinalProd.machineId) [BProduction]
        , (select production FROM #ShiftCFinalProd WHERE machineId = #ShiftAFinalProd.machineId) [CProduction]
     FROM #ShiftAFinalProd
     ORDER BY machineId

Thanks

Sargeant

Answer 8

In addition to what @tmanthley said, be sure that your stored procedure actually works by running it first in SSMS. I had imported some stored procedures and forgot about a couple dependent scalar functions, which caused EF to determine that the procedure returned no columns. Seems like a mistake I should have caught earlier on, but EF doesn't give you an error message in that case.

Evanthe

Answer 9

Entity Framework will try to get the columns by executing your stored procedure, passing NULL for every argument.

1. Please make sure that the stored procedure will return something under all the circumstances. Note it may have been smarter for Entity Framework to execute the stored proc with default values for the arguments, as opposed to NULLs.

2. ER does the following to get the metadata of the table:

   SET FMTONLY ON

3. This will break your stored procedure in various circumstances, in particular, if it uses a temporary table.

4. So to get a result as complex type; please try by adding

   SET FMTONLY OFF;

This worked for me - hope it works for you too.

Referred from https://social.msdn.microsoft.com/Forums/en-US/e7f598a2-6827-4b27-a09d-aefe733b48e6/entity-model-add-function-import-stored-procedure-returns-no-columns?forum=adodotnetentityframework

Tempera

Answer 10

In my case adding SET NOCOUNT ON; at the top of the procedure fixed the problem. It's best practice anyway.

Indelible

Answer 11

In my case SET FMTONLY OFF did not work. The method I followed is, I took backup of original stored procedure and replace with only column name like the below query.

Select Convert(max,'') as Id,Convert(max,'') as Name

After this change, create new function import, complex type in entity framework. Once the function import and complex type is created, replace the above query with your original stored procedure.

Bambi

Answer 12

SET FMTONLY OFF 

worked for me for one of the procedure but failed for other procedure. Following steps helps me to resolve my problem

1. Within a stored procedure, I have created temporary table with the same column type and inserted all the data returned by dynamic query to temp table. and selected the temp table data.

   Create table #temp
   (
      -- columns with same types as dynamic query    
   )
   
   EXEC sp_executeSQL @sql 
   
   insert into #temp 
       Select * from #temp 
   
   drop table #temp
   

2. Deleted existing complex type, import function and stored procedure instance for old stored procedure and updated entity model for current new procedure.

3. Edit the imported Function in entity modal for desired complex type, you will get all the column information there which is not getting for previous stored procedure.

4. once you have done with the type creation you can delete the temporary table from stored procedure and then refresh Entity Framework.

Ferrante

Answer 13

In Entity framework, while getting column information the sql executes the procedure with passing null values in parameter. So I handled null case differently by creating a temp table with all the required columns and returning all the columns with no value when null is passed to the procedure.

In my procedure there was dynamic query, something like

declare @category_id    int
set @category_id = (SELECT CATEGORY_ID FROM CORE_USER where USER_ID = @USER_ID)
declare @tableName varchar(15)
declare @sql VARCHAR(max)     
declare  @USER_IDT  varchar(100)    
declare @SESSION_IDT  varchar(10)

 IF (@category_id = 3)     
set @tableName =  'STUD_STUDENT'
else if(@category_id = 4)
set @tableName = 'STUD_GUARDIAN'


if isnull(@tableName,'')<>'' 
begin

set @sql  = 'SELECT  [USER_ID], [FIRST_NAME], SCHOOL_NAME, SOCIETY_NAME, SCHOOL_ID,
SESSION_ID, [START_DATE], [END_DATE]
from  @tableName
....
EXECUTE   (@sql)
END

ELSE
BEGIN
SELECT  * from #UserPrfTemp
END

I was not getting the column information in my case after using the set FMTONLY OFF trick.

This is temp table I created to get the blank data. Now I am getting the column info

Create table #UserPrfTemp
(
[USER_ID] bigint, 
[FIRST_NAME] nvarchar(60),
SCHOOL_NAME nvarchar(60),
SOCIETY_NAME nvarchar(200)
.....
}

Chalco

Answer 14

I solved this problem creating a table variable and then returning from it.

DECLARE @VarTable TABLE (
    NeededColumn1                       VARCHAR(100),
    NeededColumn2                       INT,
    NeededColumn3                       VARCHAR(100)
)

...

--Fetch Data from Linked server here

...

INSERT INTO @VarTable (NeededColumn1,NeededColumn2,NeededColumn3)
SELECT Column1, Column2, Column3
FROM #TempTable

SELECT * FROM @VarTable.

In that manner, your the SP result will be bounded to the table variable, which EF has access to.

Doyenne

Answer 15

I discovered a method that should help most people out whatever's happening.

Pull up your favourite SQL client and run the proc that you're trying to update with every parameter = null. Visual Studio is literally trying to do this when SET FMTONLY ON. Run a trace. You'll see.

You'll probably get an error, or unexpected data out. Fix that and your issue is fixed.

In my case the function read in JSON and failed because the JSON string was empty.

I just put something like

IF(@FooJSON IS NULL)
BEGIN
  SELECT 1 VAR1, 2 VAR2;
END
ELSE
--OTHER LOGIC

That's probably an ugly solution, but I inherited this mess and we don't go into Ravenholm.

Broken

Answer 16

Change #Temp tables with WITH SQL EXPRESSION

Mown