<p>I'm executing stored procedures using SET FMTONLY ON, in order to emulate what our code generator does.  However, it seems that the results are cached when executed like this, as I'm still getting a <em>Conversion failed</em> error from a proc that I have just dropped! This happens even when I execute the proc without SET FMTONLY ON.</p>

<p>Can anyone please tell me what's going on here?</p>

[{'answer_id': 72094, 'author': 'Curt Hagenlocher', 'author_id': 533, 'author_profile': 'https://Stackoverflow.com/users/533', 'pm_score': 0, 'selected': False, 'text': "<ol>\n<li><p>This sounds like a client-side error.  Do you get the same message when running through SQL Management Studio?</p></li>\n<li><p>Have you confirmed that there isn't another procedure with the same name that's owned by a different schema/user?</p></li>\n</ol>\n"}, {'answer_id': 232380, 'author': 'Rick', 'author_id': 14138, 'author_profile': 'https://Stackoverflow.com/users/14138', 'pm_score': 2, 'selected': True, 'text': '<p>Some statements will still be executed, even with <strong><code>SET FMTONLY ON</code></strong>.  You "Conversion failed" error could be from something as simple as a <code>set variable</code> statement in the stored proc.  For example, this returns the metadata for the first query, but throws an exception when it runs the last statement:</p>\n\n<pre><code>SET FMTONLY on\n\nselect 1 as a\n\ndeclare @a int\nset @a = \'a\'\n</code></pre>\n\n<p>As for running a dropped procedure, that\'s a new one to me.  SQL Server uses the system tables to determine the object to execute, so it doesn\'t matter if the execution plan is cached for that object.  If you drop it, it is deleted from the system tables, and should never be executable.  Could you please query sysobjects (or sys.objects) just before you execute the procedure?  I expect you\'ll find that you haven\'t dropped it.</p>\n'}, {'answer_id': 1145031, 'author': 'Community', 'author_id': -1, 'author_profile': 'https://Stackoverflow.com/users/-1', 'pm_score': 0, 'selected': False, 'text': '<p>DDL statements are parsed, but ignored when run if SET FMTONLY ON has been executed on the connection.  So if you drop a proc, table, etc when FMTONLY is ON, the statement is parsed, but the action is not executed.</p>\n\n<p>Try this to verify</p>\n\n<pre><code>SET FMTONLY OFF\n</code></pre>\n\n<p>--Create table to test on</p>\n\n<pre><code>CREATE TABLE TestTable (Column1 INT, Column2 INT)\n</code></pre>\n\n<p>--insert 1 record</p>\n\n<pre><code>INSERT INTO TestTable (Column1, Column2)\nVALUES (1,2)\n</code></pre>\n\n<p>--validate the record was inserted</p>\n\n<pre><code>SELECT * FROM TestTable\n</code></pre>\n\n<p>--now set format only to ON</p>\n\n<pre><code>SET FMTONLY ON\n</code></pre>\n\n<p>--columns are returned, but no data</p>\n\n<pre><code>SELECT * FROM TestTable\n</code></pre>\n\n<p>--perform DDL statement with FMTONLY ON</p>\n\n<pre><code>DROP TABLE TestTable\n</code></pre>\n\n<p>--Turn FMTONLY OFF again</p>\n\n<pre><code>SET FMTONLY OFF\n</code></pre>\n\n<p>--The table was dropped above, so this should not work</p>\n\n<pre><code>SELECT * FROM TestTable\n\nDROP TABLE TestTable\n\nSELECT * FROM TestTable\n</code></pre>\n'}]
['https://Stackoverflow.com/questions/72070', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/8741/']