XML FeedsTonight I was upgrading some SSIS packages for another blog on automating index statistics. While doing this task, I ran into the error, "Could not find stored procedure 'sp_ExecuteSQL'". After sitting there, in disbelief that sp_executesql wasn’t on the SQL Server 2012 RC0 instance, I realized the problem was me. I actually just gave the answer to what the issue was.
The database that I was testing on was AdventureWorks. AdventureWorks has a collation setting of Latin1_General_100_CS_AS. Latin1_General_100_CS_AS is a case sensitive collation, unlike a more common collation of SQL_Latin1_General_CP1_CI_AS. So sp_ExecuteSQL is not a valid name. sp_executesql is the actual name of the system procedure and the correct case, all lower case.
As a quick test, in your own AdventureWorks database, run the following
- EXEC sp_ExecuteSQL N'SELECT * FROM sys.databases'
resulting in the following error
Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'sp_ExecuteSQL'.
The correct syntax would be
- EXEC sp_executesql N'SELECT * FROM sys.databases'
The fix is simply changing the name to be correct, case wise. The tip that can come from this is, as a DBA, we become used to typing a specific procedure, a specific way on a set of databases configured a specific way. However, we need to take into account that even system procedures will take the collation of the database they are being executed in. Becoming comfortable and letting your guard down while making assumptions of what we are working on can cause problems while writing them, or worse, after they are in a production environment.
Happy DBAing!
Nice catch!
Is that right? I seem to remember even the 2005 versions of AW being a case sensitive collation. I'm downloading them to check... 
I developed a habit of installing servers with Latin1_General_BIN back in the days when you could gain some performance by doing so. Over the years I've found it's actually helped in a couple different and unexpected ways, even after the performance gain went away, so I still do it that way, although I was very glad when sql 2000 allowed for column-level collations. One of the benefits is it forced me to learn the capitalization used for system objects and functions.
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.