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!