16

I recently joined one of the project in my team. They use ASP.NET MVC and MS SQL along with Entity Framework as ORM.

I noticed that each of the stored procedures used in the EF has this common line at the start of the stored procedure definitation 

IF(0=1) SET FMTONLY OFF

I thought this was a very strange condition so I googled a bit about it and also asked my co workers about it. They say that when EF maps the stored procedure it send all parameters as null and skips all the if loop. Hence it would also skip the IF(0=1) condition and would then SET FMTONLY OFF

On searching for SET FMTONLY OFF MSDN, says

Returns only metadata to the client. Can be used to test the format of the response without actually running the query.

It becomes a problem when you dont control the database, you have to keep telling the DBA's to add it and explain to them over and over again why is it needed in the first place.

I still dont have a clear idea why this is required. If someone can explain this a bit in detail or guide me to some link which has this topic covered would mean the world to me.

Yasser Shaikh
  • 46,934
  • 46
  • 204
  • 281
  • 6
    Microsoft have updated their page for SET FMTONLY :- Do not use this feature. This feature has been replaced by sp_describe_first_result_set (Transact-SQL), sp_describe_undeclared_parameters (Transact-SQL), sys.dm_exec_describe_first_result_set (Transact-SQL), and sys.dm_exec_describe_first_result_set_for_object (Transact-SQL). – Mike Mar 08 '18 at 11:29

4 Answers4

6

I believe the reason is similar to the one for stored procedures that run from SSRS. In summary, when FMTONLY is active, your stored procedure may have some unexpected results. Hence the reason for explicitly turning it off. For details read Dealing with the Evil of FMTONLY from SSRS

David
  • 1,074
  • 11
  • 12
  • 1
    FYI: The article you have linked here says that FMTONLY setting is deprecated, but it's no longer the case (in 2022). See MS docs (https://learn.microsoft.com/en-us/sql/t-sql/statements/set-fmtonly-transact-sql). – Eddie Kumar Sep 16 '22 at 08:34
  • Thank you for the update! @eddie-kumar, any comments on the unexpected results that forced us to use FMTONLY? – David Oct 06 '22 at 12:29
6

Having IF(0=1) SET FMTONLY OFF seems like a risky thing to casually do in stored procedures read in by entity framework.

Entity Framework is the only source of this flag being set as a standard practice that I'm aware of (presumably other ORM's may use it).

the purpose (as I understand it) is to provide a way to get a procedures return schema without actually touching any data. (some stored procedures you don't want to execute just to update an orm's object model.

so unless you have a table that is counting the number of times your EF model has been updated (which might be interesting academically)

for additional information see Stored procedure returns int instead of result set

the safest way to use ftmonly with entity framework (in my mind) is.. under the following circumstances

  1. if the procedure in question is complex and confuses EF (EF reads the first returned schema, flow logic ignored)
  2. let EF set the flag for you. (I clear it below to exit early)
  3. use always false logic (which would be ignored when FTMONLY is on - interpret this as EF is trying to read schema)

  4. at the beginning of the complex procedure do the following

    if(0=1)  -- if FMTONLY is on this if condition is ignored
begin
    -- this loop will only be entered if fmtonly is on (ie EF schema read)
    select 
        column1
        ,column2
        ...
        ,columnX
    from whateverA
        cross join whateverB
        ...
        cross join whateverQ
    -- joins don't matter but they might make it easier to get the column definitions
    -- and names you desire.   the important thing here is generating the proper 
    -- return schema... which is as complex as whatever you are trying to return
    where 1=0

    set FMTONLY off -- do this so that you can now force an early return since EF
    -- usually only wants the first data set schema...  other orms might
    -- do something different
    return  -- this will be ignored if FMTONLY is still on

end
wode
  • 236
  • 3
  • 8
  • Kudos for adding the SET FMTONLY OFF and return statements, preventing the execution of any update statements that may follow in the proc. – SteveB Aug 18 '22 at 16:26
4

I have done this temporarily in order to be able to use the designer to map functions to represent the stored procedure in code. The problem is that the designer passes null by default to the procedure to determine the schema. This can sometimes be a problem with stored procs that do validation and throw exceptions etc if null is passed. The Setting you describe gets around this since it returns meta and not the actual data.

TGH
  • 38,769
  • 12
  • 102
  • 135
0

As far as I understand the scenarios, when an EDMX is updated, to return the result set instead of INT for a stored procedure, SET FMTONLY OFF is helpful.

For ex. "SELECT * FROM @tempTable" is written in a stored procedure,when an edmx is updated for this stored procedure, the type generated in Context.cs file for this stored procedure can be - public virtual int SPName.

Ideally it must be: public virtual ObjectResult SPName

To attain the above result, we have to use SET FMTONLY OFF.

We are avoiding the return type as INT and making it as type of resultset: LINK to know how to use: http://www.sandeepknarware.in/?p=247

Kavya D J
  • 1
  • 2