-1

I am using Entity Framework for the first time. It's a simple application that looks up part numbers and displays them in a grid. My application runs fine locally, but when I execute against the production SQL Server database, the pages that have stored procedure calls with Entity Framework fail.

However, it will run an Entity Framework call to a table and return the data, but not a stored procedures. I do this to populate a drop down list and it works fine. When I call the stored procedure to return the search results, it fails.

Could this be a setting on the production SQL Server that my user ID does not have access to execute stored procedures?

Here is the error message from the log:

Date/Time: 12/7/2013 12:23:41 PM

Title: Error
Message: An error occurred while executing the command definition. See the inner exception for details.
StackTrace:
at System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)
at System.Data.Objects.ObjectContext.CreateFunctionObjectResult[TElement](EntityCommand entityCommand, ReadOnlyMetadataCollection1 entitySets, EdmType[] edmTypes, MergeOption mergeOption)
at System.Data.Objects.ObjectContext.ExecuteFunction[TElement](String functionName, MergeOption mergeOption, ObjectParameter[] parameters)
at System.Data.Objects.ObjectContext.ExecuteFunction[TElement](String functionName, ObjectParameter[] parameters)
at FanInfo.FandbEntities1.uspFANSearch(String idParm, Nullable1 diaFromParm, Nullable1 diaToParm, Nullable1 hubFromParm, Nullable`1 hubToParm) in C:\Users\Public\Documents\Projects\Fan Info\Fan Info\Model1.Designer.vb:line 201
at FanInfo.frmMain.LoadSearchResults() in C:\Users\Public\Documents\Projects\Fan Info\Fan Info\Main.vb:line 208

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user2011126
  • 71
  • 8

1 Answers1

1

Since it Works locally but not in Production. The error will be related to the difference between these. Normally this is:

  • Rights
  • Incorrect Connection string
  • Firewall problems

In Your case Access to tables Works. The problem is therefore a rights issue.

You need "Execute" right on the stored procedures.

See: GRANT EXECUTE to all stored procedures

Edit

Based on Your comment there are two possibilities:

  • The Connection to the SQL Server is not using the Security id that you think it is using. Check the Connection string. Also is the code running as a Windows Application or under IIS?
  • The other thing is, is the stored procedures in different schema on the prod database than on the dev database.

Edit 2

Based on Your last comment, there was a difference in the structure of Your stored procedure in Your Development environment and in Production.

Community
  • 1
  • 1
Shiraz Bhaiji
  • 64,065
  • 34
  • 143
  • 252
  • I used the Grant Execute to the stored procedures for the user ID. I also tried restarting the sql server. It still did not resolve the problem. When I go to SQL Server Studio and sign in using the ID, the stored procedure works fine. When I execute via Entity Framework, it fails. I checked the setting on my local SQL Server ID and the login/security/roles match locally and prod. Any other suggestions. Is there another setting in SQL Server that I'm missing. The direct access to a table still works fine. – user2011126 Dec 08 '13 at 02:39
  • Ok. Its an issue with Entity Framework. I create a temp method that calls the stored procedure. It returns everything fine within my app. So I can call the stored procedure without Entity Framework, but when I call a stored procedure within Entity Framework it fails on production. Works fine on my development machine that I used to create the model. – user2011126 Dec 08 '13 at 16:08
  • I rebuilt my app on the client machine and updated My complex function. It worked – user2011126 Dec 10 '13 at 01:28
  • @user2011126 If this answer solves your problem please accept this as answer, it will be help full for future visitors – Fool Apr 03 '14 at 04:21