In EF Code First and SQL 2005, what causes "Incorrect syntax at stored procedure name>"?

code-first ef-code-first entity-framework sql-server-2005 stored-procedures

Question

The examples for System.Data.Entity.Database.SqlQuery method I've found appear to work well with SQL 2008 R2 but do not appear to work with SQL 2005.

This call will work with SQL 2008 R2:

var myEntities = dbContext.Database.SqlQuery<MyEntity>("GetDataFromMySp @EntityId = {0}", entityId);

However, in SQL 2005 this statement will throw a SqlException with an error message of "Incorrect syntax near 'GetDataFromMySp'".

1
8
6/23/2011 9:18:25 PM

Accepted Answer

Solution found by @Dan himself (couldn't post due to rep)

The solution I found to this issue was simply to add the keyword "EXEC" to the query:

var myEntities = dbContext.Database.SqlQuery<MyEntity>("EXEC GetDataFromMySp @EntityId = {0}", entityId);

This solution fixed the issue with SQL Server 2005 and still worked with SQL Server 2008 R2.

18
6/19/2011 5:30:14 PM

Popular Answer

You only need EXEC if there are any statements before "GetDataFromMySp" in the batch. A stored procedure name by itself should be OK.

I'd run SQL Profiler to see what is being actually being sent by EF.

Things like "prepared statements" issue extra commands as part of the batch in addition to your command text.

See this: http://social.msdn.microsoft.com/Forums/eu/adonetefx/thread/bae30a3d-7a66-4aff-af99-6426cf0483b5



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow