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