Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

IF (1=0) BEGIN SET FMTONLY OFF END Expand / Collapse
Author
Message
Posted Sunday, August 15, 2010 5:40 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 10, 2012 12:19 PM
Points: 103, Visits: 301
Sorry for the rather undescriptive title, but I am not sure what to call this problem, aside from giving it the name of the section of code causing the problems.

Issue is this, I have a stored procedure (SP) I have created in my database, that happens to use a #TempTable. My frontend developer guy is using Visual Studio Web Developer Express edition (VWD). He says that I have to include this expression at the beginning of my SP so that his tableAdapters for the SP will get the correct names. But this is now causing issues when I try to create a TableAdapter in VWD for a new SP I have created. The tableAdapter wizard times out as if it is actually running the SP start to finish before giving the names of the columns.

I have slowly and painfully ripped apart my views, and the views who's source is a lower level view all the way to what I have found to be the problem. I take a rather larger table (200,000 records) and cross join on a table full of days of the year (366 records) and create dates using a constraint in the WHERE clause. It appears that this is what seems to be causing the time outs, it seems that when the table adapter is being created it tries to run this entire query, and freezes.

Has anyone else ever encountered this sort of behavior with using the "IF (1=0) BEGIN SET FMTONLY OFF END" code at the beginning of a stored procedure?
Post #969503
Posted Monday, August 16, 2010 10:06 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, August 21, 2014 11:29 AM
Points: 1,221, Visits: 1,290
It is running the procedure from start to end. The FMTOnly value is set to OFF by default. So your code will run from start to finish. Based on the logic you are using 1 will never = 0, so it will never fall into the loop. Was the intent to have it fall into this loop?

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #969916
Posted Monday, August 16, 2010 10:33 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 10, 2012 12:19 PM
Points: 103, Visits: 301
I found the answer after hours of looking on the internet, and trying to figure out whats going on with this whole FMTONLY thing. 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.

So my solution was rather crude, but I found it somewhere out on the net, it essentially sets FMTONLY OFF only for the create of the #TempTables and then returns it to its previous state when all of the #TempTables have been declared. Below is my solution for the time being:

-- Bit used to store the status of FMTONLY
DECLARE @fmtonlyON BIT
SET @fmtonlyON = 0

--This line will be executed if FMTONLY was initially set to ON
IF (1=0) BEGIN SET @fmtonlyON = 1 END
-- Turning off FMTONLY so the temp tables can be declared and read by the calling application
SET FMTONLY OFF

-- HERE is where you would declare all temp tables to be used throughout the SP
/* EXAMPLE

CREATE #TempTable1
(
TableID INT IDENTITY(1,1),
SomeINT INT,
SomeChar Char(1),
SomeDate DateTime
)

*/

-- Now the compiler knows these things exist so we can set FMTONLY back to its original status
IF @fmtonlyON = 1 BEGIN SET FMTONLY ON END





Post #969928
Posted Tuesday, August 17, 2010 8:41 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 10:24 AM
Points: 1,973, Visits: 10,962
Now THAT is really interesting -- and strange, indeed. This is actually very useful information. Thanks for posting!

Rob Schripsema
Accelitec, Inc
Post #970468
Posted Tuesday, September 28, 2010 11:16 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, December 23, 2012 10:14 PM
Points: 4, Visits: 117
I have noticed something else interesting about this that you should watch out for.

If you use SET FMTONLY OFF as the last statement in the true statement block of an IF condition then the ELSE statement block of the IF condition will execute regardless of whether the IF condition was true or false.

For example:
Both blocks execute
IF 1 = 1
BEGIN
SET FMTONLY ON
SELECT 1 AS [true block]
SET FMTONLY OFF
END
ELSE
BEGIN
SELECT 1 AS [else block]
END

And it doesn't matter when the SET FMTONLY ON occurs, again
Both blocks execute
SET FMTONLY ON
IF 1 = 1
BEGIN
SELECT 1 AS [true block]
SET FMTONLY OFF
END
ELSE
BEGIN
SELECT 1 AS [else block]
END

However, if you have a statement following the SET FMTONLY OFF, then things work the way you expect
Only the true block executes
SET FMTONLY ON
IF 1 = 1
BEGIN
SELECT 1 AS [true block]
SET FMTONLY OFF
SET ANSI_NULLS ON
END
ELSE
BEGIN
SELECT 1 AS [else block]
END

And the statement after the SET FMTONLY OFF can even be a second SET FMTONLY OFF!
Only the true block executes
SET FMTONLY ON
IF 1 = 1
BEGIN
SELECT 1 AS [true block]
SET FMTONLY OFF
SET FMTONLY OFF
END
ELSE
BEGIN
SELECT 1 AS [else block]
END

Or you can code the ELSE block with its own IF condition
Only the true block executes
SET FMTONLY ON
IF 1 = 1
BEGIN
SELECT 1 AS [true block]
SET FMTONLY OFF
END
ELSE IF 1 <> 1
BEGIN
SELECT 1 AS [else block]
END

Post #994665
Posted Thursday, October 7, 2010 8:04 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 5:15 PM
Points: 9, Visits: 72
Thank you Loki and Sean. very useful info.

regards,
FJ
Post #1000986
Posted Sunday, April 17, 2011 6:11 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 27, 2012 12:50 AM
Points: 1, Visits: 35
Awesome info...
Thanks a lot SSC Journeyman and Sean Nolan.....
Your hours spent to dig this up are really helping me a lot....
Thanks again....

-Bhakti
Post #1094586
Posted Monday, April 18, 2011 10:58 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, August 22, 2014 7:04 AM
Points: 3,673, Visits: 72,433
I didn't know about FMTONLY ON ignoring IF/THEN blocks.

Based on that and the fact that FMTONLY is used to get a list of column names and data types

Why not add this to your sproc

IF 1=0 
BEGIN
SELECT
fieldname1inreturnset = cast(null as fieldtype),
...

RETURN
END

That way the quick FMTONLY on statement will just get a field list back.
My problems with FMTONLY have involved temp tables not getting created in that case... which is why for SSIS I always start my stored proc calls in OLEDB data sources as SET FMTONLY OFF;EXEC sp_executesql N'stored proc name'





--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link
For tips on how to post your problems
Post #1095161
Posted Monday, April 18, 2011 11:26 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 10, 2012 12:19 PM
Points: 103, Visits: 301
mtassin (4/18/2011)


That way the quick FMTONLY on statement will just get a field list back.
My problems with FMTONLY have involved temp tables not getting created in that case... which is why for SSIS I always start my stored proc calls in OLEDB data sources as SET FMTONLY OFF;EXEC sp_executesql N'stored proc name'



Basically, the FMTONLY is used so the calling application can run the code and generate all possible inputs and outputs without running any of the actual queries. In my case, my calling application was a table adapter in a Visual Web Developer dataset. It was attached to a stored procedure based on several in depth views and table joins. The proc takes almost 2 min to finish, so if I set FMTONLY off the table adapter would have to run the entire stored proc just to get the meta data! This caused Visual Web Developer to time out before the table adapter ever got made. So I had to use my work around in that case.

I'm glad my struggles could help someone else!
Post #1095178
Posted Thursday, October 27, 2011 1:07 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, February 7, 2014 11:46 AM
Points: 22, Visits: 81
LOVE IT!!! THANKS, THAT S0LVED MY SSIS ISSUE!!!
Post #1197183
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse