August 15, 2010 at 5:40 pm
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?
August 16, 2010 at 10:06 am
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/
August 16, 2010 at 10:33 am
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
August 17, 2010 at 8:41 am
Now THAT is really interesting -- and strange, indeed. This is actually very useful information. Thanks for posting!
Rob Schripsema
Propack, Inc.
September 28, 2010 at 11:16 am
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
October 7, 2010 at 8:05 pm
Thank you Loki and Sean. very useful info.
regards,
FJ
April 17, 2011 at 6:11 am
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
April 18, 2011 at 10:58 am
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'
April 18, 2011 at 11:26 am
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!
October 27, 2011 at 1:07 pm
LOVE IT!!! THANKS, THAT S0LVED MY SSIS ISSUE!!!
October 27, 2011 at 1:47 pm
Wow, that's odd.
Thank you for the information.
Interesting behaviour.
Best regards,
Best regards,
Andre Guerreiro Neto
Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply