SET FMTONLY OFF in SQL

Question

1.00/5 (1 vote)

See more:

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 definition

SQL

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

Quote:

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 don't 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 don't 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.

What I have tried:

just checking in google and did not find proper solution

Posted 4-Jan-19 3:01am

Chinnu2020

Updated 4-Jan-19 4:00am

CHill60

v2

Add a Solution

Comments

CHill60 4-Jan-19 9:55am

Surely you should be providing the DBA's with the script to create the Stored Procedures? Then your problem goes away

Richard MacCutchan 4-Jan-19 10:21am

Did it not occur to you to ask one of the existing members of the team?

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

MadMyche · Accepted Answer · 2019-01-04T03:59:00

Well you really haven't posted a question, so there really is no answer.

My guess would be that before the days of Entity Framework it was used by developers to retrieve the metadata about a result-set without having to actually run the query; much like the results from executing SP_Help on a table.
So while it may have been helpful in the past, the ORMs of today don't know how to deal with it.

Of course, you probably already know that as this is a 2 year old question verbatim from Stack Overflow:
sql server - Why do we have to write SET FMTONLY OFF in stored procedures when using Entity Framework - Stack Overflow[^]

CHill60 · Accepted Answer · 2019-01-04T04:01:00

First thing I found on Google:

SQL Server Central[^]:
It turns out that FMTONLY is set to off by default as you had said, but when aplications like SQL Reporting Services and Visual Studio read the SP's for the first time, (to get the column names), they read them with FMTONLY set to on. When FMTONLY is set to on, all column names (metadata) are read while the actual rows returned is zero. This allows the wizard to populate the tableadapter with the avaible column headings when it is first read. Of course when the application actually makes use of the table adapter when called from say a web page, it runs the SP with FMTONLY set to off so it returns actual data. I found this to be a indentical situation with SQL Reporting Services.

Anyhow, when FMTONLY is set to on, it has a wierd behavior of ignoring conditional statements such as and If (condition), It looks through all logic to give all possible result sets that could be returned from the SP. This is why it is able to break through the IF 1=0 logic and turn itself off. That being said, when this is set to off, the creation of the table adapter must run the whole SP before it can get column names, hence why it was timing out (default timeout in Visual Studio set to 30sec and my SP takes about a 1 min to run).

So FMTONLY is pretty much just a setting so an application can 'LOOK' through a section of code and find returnible column names without returning the data. Yet there is a problem with using #TempTables because in order for them to be seen by the scan done by FMTONLY, the code to create them must actually be ran, hence why my front end guy used the statement " IF (1=0) BEGIN SET FMTONLY OFF END ". This would allow the entire SP to run even if the SP was read with FMTONLY initially set to ON.

SET FMTONLY OFF in SQL

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0