InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 24 May 2013 15:43:44 GMT20http://www.sqlservercentral.com/Forums/Topic969503-338-1.aspxWow, that's odd.Thank you for the information.Interesting behaviour.Best regards,Thu, 27 Oct 2011 13:47:03 GMTcodebyohttp://www.sqlservercentral.com/Forums/Topic969503-338-1.aspxLOVE IT!!! THANKS, THAT S0LVED MY SSIS ISSUE!!!Thu, 27 Oct 2011 13:07:47 GMTamy.walshhttp://www.sqlservercentral.com/Forums/Topic969503-338-1.aspx[quote][b]mtassin (4/18/2011)[/b][hr]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'[/quote]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!Mon, 18 Apr 2011 11:26:17 GMTloki1049http://www.sqlservercentral.com/Forums/Topic969503-338-1.aspxI 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 typesWhy not add this to your sproc[code]IF 1=0 BEGIN SELECT fieldname1inreturnset = cast(null as fieldtype), ... RETURNEND[/code]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'Mon, 18 Apr 2011 10:58:44 GMTmtassinhttp://www.sqlservercentral.com/Forums/Topic969503-338-1.aspxAwesome info... Thanks a lot SSC Journeyman and Sean Nolan..... Your hours spent to dig this up are really helping me a lot....Thanks again....-BhaktiSun, 17 Apr 2011 06:11:20 GMTbhakti4uhttp://www.sqlservercentral.com/Forums/Topic969503-338-1.aspxThank you Loki and Sean. very useful info.regards,FJThu, 07 Oct 2010 20:04:59 GMTFredy Jameshttp://www.sqlservercentral.com/Forums/Topic969503-338-1.aspxI 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[code]IF 1 = 1BEGIN SET FMTONLY ON SELECT 1 AS [true block] SET FMTONLY OFFENDELSEBEGIN SELECT 1 AS [else block]END[/code]And it doesn't matter when the SET FMTONLY ON occurs, againBoth blocks execute[code]SET FMTONLY ONIF 1 = 1BEGIN SELECT 1 AS [true block] SET FMTONLY OFFENDELSEBEGIN SELECT 1 AS [else block]END[/code]However, if you have a statement following the SET FMTONLY OFF, then things work the way you expectOnly the true block executes[code]SET FMTONLY ONIF 1 = 1BEGIN SELECT 1 AS [true block] SET FMTONLY OFF SET ANSI_NULLS ONENDELSEBEGIN SELECT 1 AS [else block]END[/code]And the statement after the SET FMTONLY OFF can even be a second SET FMTONLY OFF!Only the true block executes[code]SET FMTONLY ONIF 1 = 1BEGIN SELECT 1 AS [true block] SET FMTONLY OFF SET FMTONLY OFFENDELSEBEGIN SELECT 1 AS [else block]END[/code]Or you can code the ELSE block with its own IF conditionOnly the true block executes[code]SET FMTONLY ONIF 1 = 1BEGIN SELECT 1 AS [true block] SET FMTONLY OFFENDELSE IF 1 <> 1BEGIN SELECT 1 AS [else block]END[/code]Tue, 28 Sep 2010 11:16:27 GMTSean Nolan-296153http://www.sqlservercentral.com/Forums/Topic969503-338-1.aspxNow THAT is really interesting -- and strange, indeed. This is actually very useful information. Thanks for posting!Tue, 17 Aug 2010 08:41:20 GMTRob Schripsemahttp://www.sqlservercentral.com/Forums/Topic969503-338-1.aspxI 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:[code="sql"]-- Bit used to store the status of FMTONLYDECLARE @fmtonlyON BITSET @fmtonlyON = 0--This line will be executed if FMTONLY was initially set to ONIF (1=0) BEGIN SET @fmtonlyON = 1 END-- Turning off FMTONLY so the temp tables can be declared and read by the calling applicationSET FMTONLY OFF-- HERE is where you would declare all temp tables to be used throughout the SP/* EXAMPLECREATE #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 statusIF @fmtonlyON = 1 BEGIN SET FMTONLY ON END[/code]Mon, 16 Aug 2010 10:33:04 GMTloki1049http://www.sqlservercentral.com/Forums/Topic969503-338-1.aspxIt 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?Mon, 16 Aug 2010 10:06:01 GMTMike01http://www.sqlservercentral.com/Forums/Topic969503-338-1.aspxSorry 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?Sun, 15 Aug 2010 17:40:00 GMTloki1049