I am finally back to working with MVC (now version 3.0 with Razor) and other technologies like dependency injection (Unity this time) and ORM (Entity Framework). So far so good, but expect more updates.
I was trying to compile my VS2010 MVC project, which includes a Database Project (BTW, awesome upgrade on the way Database projects work now in conjunction with Visual Studio) right after adding a new Store Procedure when I got this error message during the build process:
“Error SQL03070: This statement is not recognized in this context.”
The stored procedure causing the error is also displayed to read something like this:
“{my path}\MyProject.DB\SCHEMA OBJECTS\SCHEMAS\DBO\PROGRAMMABILITY\STORED PROCEDURES\{name of my new stored procedure}.PROC.SQL(X,Y):”
The problem has to do with the way I created the stored procedure. I was making a change to an already existing stored procedure from a QUERY window in SQL Server 2005 management studio, and somehow I copy and paste with a “ALTER PROCEDURE” command instead of the requireed “CREATE PROCEDURE” command.
Mistake number two was to keep the USE statement as in “USE [name_of_my_database]” when is not allowed inside the database project.
Mistake 3 and 4 had to do with keeping “SET ANSI_NULLS ON’ and “SET QUOTED_IDENTIFIER”.
Once I removed those 4 statements from the stored procedure, I was able to compile and build without any errors. Lesson learned.
