“exec sp_executesql” = NOT a Stored Procedure

This has bitten me in the rear twice in the last few days while working on some webmethods. I was erroring out on a sql ExecuteNonQuery with the only message being that there was an error on line 1 of my command. Looking in my SQLProfiler I see my query as

exec sp_executesql N’MySprocName’, N’@myparam char(20)’, @myparam = ‘3/30/2004 1:48:22 PM’

“exec sp_executesql” is used for commands that are text, NOT stored procedures. The problem here is that I forgot to set my sqlcommand type to StoredProcedure and it was being sent to SQL Server as a query string and therefore failing.

This is a good indicator of the problem so that you don’t have to go around and around trying to figure out what is wrong with your stored procedure (as I did – once but not twice!)

  Sign up for my newsletter so you don't miss my conference & Pluralsight course announcements!  

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.