locked
EF and stored procedure that does not return any value RRS feed

  • Question

  • I have a stored procedure that does not not return any value. Instead, it accepts a string (text) parameter and updates server data.

    I had problems having EF recognizing this SP. I had to add a select statement into it. Later on I have removed that select.

    Here is a code where I am getting an error trying running my SP:

        

    public virtual int usp_UpdateOrder(string xmlstring)
            {
                var xmlstringParameter = xmlstring != null ?
                    new ObjectParameter("xmlstring", xmlstring) :
                    new ObjectParameter("xmlstring", typeof(string));

                return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction("usp_UpdateOrder", xmlstringParameter);
            }

    Why it still shows a return type and a return statement?

    The error is this:

    System.Data.Entity.Core.EntityCommandExecutionException was unhandled by user code
      HResult=-2146232004
      Message=An error occurred while executing the command definition. See the inner exception for details.
      Source=EntityFramework
      StackTrace:
           at System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)
           at System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition.Execute(EntityCommand entityCommand, CommandBehavior behavior)
           at System.Data.Entity.Core.EntityClient.EntityCommand.ExecuteReader(CommandBehavior behavior)
           at System.Data.Entity.Core.EntityClient.EntityCommand.ExecuteNonQuery()
           at System.Data.Entity.Core.Objects.ObjectContext.ExecuteFunction(String functionName, ObjectParameter[] parameters)
           at Sunriseclub.Models.SunriseclubEntities.usp_UpdateOrder(String xmlstring) in c:\qasource\Sunriseclub\Sunriseclub\Models\SunriseclubModel.Context.cs:line 89
           at Sunriseclub.Models.SunriseclubRepository.placeOrder(String orderpayload) in c:\qasource\Sunriseclub\Sunriseclub\Models\SunriseclubRepository.cs:line 34
           at Sunriseclub.Controllers.SunriseclubController.placeOrder(String orderpayload, String member, String orderdate, String sendcopy) in c:\qasource\Sunriseclub\Sunriseclub\Controllers\SunriseclubController.cs:line 66
           at lambda_method(Closure , ControllerBase , Object[] )
           at System.Web.Mvc.ActionMethodDispatcher.<>c__DisplayClass1.<WrapVoidAction>b__0(ControllerBase controller, Object[] parameters)
           at System.Web.Mvc.ActionMethodDispatcher.Execute(ControllerBase controller, Object[] parameters)
           at System.Web.Mvc.ReflectedActionDescriptor.Execute(ControllerContext controllerContext, IDictionary`2 parameters)
           at System.Web.Mvc.ControllerActionInvoker.InvokeActionMethod(ControllerContext controllerContext, ActionDescriptor actionDescriptor, IDictionary`2 parameters)
           at System.Web.Mvc.Async.AsyncControllerActionInvoker.<>c__DisplayClass42.<BeginInvokeSynchronousActionMethod>b__41()
           at System.Web.Mvc.Async.AsyncResultWrapper.<>c__DisplayClass8`1.<BeginSynchronous>b__7(IAsyncResult _)
           at System.Web.Mvc.Async.AsyncResultWrapper.WrappedAsyncResult`1.End()
           at System.Web.Mvc.Async.AsyncControllerActionInvoker.EndInvokeActionMethod(IAsyncResult asyncResult)
           at System.Web.Mvc.Async.AsyncControllerActionInvoker.<>c__DisplayClass37.<>c__DisplayClass39.<BeginInvokeActionMethodWithFilters>b__33()
           at System.Web.Mvc.Async.AsyncControllerActionInvoker.<>c__DisplayClass4f.<InvokeActionMethodFilterAsynchronously>b__49()
      InnerException: System.Data.SqlClient.SqlException
           HResult=-2146232060
           Message=Invalid object name 'TempData'.
           Source=.Net SqlClient Data Provider
           ErrorCode=-2146232060
           Class=16
           LineNumber=26
           Number=208
           Procedure=usp_UpdateOrder
           Server=localhost
           State=1
           StackTrace:
                at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
                at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
                at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
                at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
                at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
                at System.Data.SqlClient.SqlDataReader.get_MetaData()
                at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
                at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite)
                at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
                at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
                at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
                at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
                at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
                at System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)
           InnerException: 

    something about xmlstringParameter.

    Thanks

    Friday, December 28, 2012 6:53 PM

Answers

  • Something like this:

     --Create an internal representation of the XML document.
     EXEC sp_xml_preparedocument @idoc OUTPUT, @xmlstring;
    
     with TempData (id, member_id, order_id, product_id, amount, cost) 
     AS
     (
        SELECT *
        FROM OpenXML(@idoc, 'root/root', 1)
        WITH (
         id Int,
    member_id Int,
    order_id Int, 
    product_id Int,
    amount numeric (5,2),
    cost money)
     )
     select member_id, GETDATE() from TempData;
    
    
    with TempData2 (id, member_id, order_id, product_id, amount, cost) 
     AS
     (
        SELECT *
        FROM OpenXML(@idoc, 'root/root', 1)
        WITH (
         id Int,
    member_id Int,
    order_id Int, 
    product_id Int,
    amount numeric (5,2),
    cost money)
     )
    select SCOPE_IDENTITY(), product_id, amount, cost
    from TempData2;

    This is not necessarily the best way to go, it is only meant to show you the problem. I'm not familiar enough with CTEs to give guidance on alternatives.

     

    We are seeing a lot of great Entity Framework questions (and answers) from the community on Stack Overflow. As a result, our team is going to spend more time reading and answering questions posted on Stack Overflow. We would encourage you to post questions on Stack Overflow using the entity-framework tag. We will also continue to monitor the Entity Framework forum.

    Wednesday, January 9, 2013 6:58 PM

All replies

  • Call me crazy, but it's like what nobody knows the answer?
    Saturday, December 29, 2012 10:41 PM
  • >>Invalid object name 'TempData'

    There is no such object in your statement. This is a very specific issue.


    Ghost,
    Call me ghost for short, Thanks
    To get the better answer, it should be a better question.

    Monday, December 31, 2012 6:07 AM
  • Hi Markgoldin,

    Please check whether these articles helps or not: http://blog.sqlauthority.com/2012/08/31/sql-server-error-fix-msg-208-invalid-object-name-dbo-backupset-invalid-object-name-dbo-backupfile/

    http://stackoverflow.com/questions/869315/invalid-object-name-error-when-trying-to-execute-stored-procedure

    Good day.


    Alexander Sun [MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, January 2, 2013 4:53 AM
  • I am using with common_table_expression  in my stored procedure. That is what not found.
    Wednesday, January 2, 2013 2:18 PM
  • Hi,

    Could you please provide us with more information about the sp?  Maybe you can share us with some SQL scripts of the SP to repro the issue.  Also, please let us know your EF version.

    Good day!


    Michael Sun [MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, January 3, 2013 9:10 AM
  • USE [Sunriseclub]
    GO
    /****** Object:  StoredProcedure [dbo].[usp_UpdateOrder]    Script Date: 12/28/2012 11:01:02 AM ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER PROCEDURE [dbo].[usp_UpdateOrder](@xmlstring TEXT)

    AS
    DECLARE  @idoc int

    --Create an internal representation of the XML document.
    EXEC sp_xml_preparedocument @idoc OUTPUT, @xmlstring;

    with TempData (id, member_id, order_id, product_id, amount, cost) 
    AS
    (
       SELECT *
       FROM OpenXML(@idoc, 'root/root', 1)
       WITH (
        id Int,
    member_id Int,
    order_id Int, 
    product_id Int,
    amount numeric (5,2),
    cost money)
    )
    -- update order master
    insert orders
    select member_id, GETDATE() from TempData
    -- update order detail
    insert Orderdetail
    select SCOPE_IDENTITY(), product_id, amount, cost
    from TempData
    Friday, January 4, 2013 3:39 PM
  • The exception is coming from Sql Server, not EF. If you execute your stored proc independent of EF, in SQL Management Studio for example, then it will fail.

    As far as I know you cannot use a common table expression in two selects in the same stored proc. So selecting twice from TempData is what's causing the problem. Duplicating the CTE would work, though I don't know if that's the best way.


    We are seeing a lot of great Entity Framework questions (and answers) from the community on Stack Overflow. As a result, our team is going to spend more time reading and answering questions posted on Stack Overflow. We would encourage you to post questions on Stack Overflow using the entity-framework tag. We will also continue to monitor the Entity Framework forum.


    Tuesday, January 8, 2013 11:12 PM
  • < Duplicating the CTE

    How?

    Wednesday, January 9, 2013 5:20 PM
  • Something like this:

     --Create an internal representation of the XML document.
     EXEC sp_xml_preparedocument @idoc OUTPUT, @xmlstring;
    
     with TempData (id, member_id, order_id, product_id, amount, cost) 
     AS
     (
        SELECT *
        FROM OpenXML(@idoc, 'root/root', 1)
        WITH (
         id Int,
    member_id Int,
    order_id Int, 
    product_id Int,
    amount numeric (5,2),
    cost money)
     )
     select member_id, GETDATE() from TempData;
    
    
    with TempData2 (id, member_id, order_id, product_id, amount, cost) 
     AS
     (
        SELECT *
        FROM OpenXML(@idoc, 'root/root', 1)
        WITH (
         id Int,
    member_id Int,
    order_id Int, 
    product_id Int,
    amount numeric (5,2),
    cost money)
     )
    select SCOPE_IDENTITY(), product_id, amount, cost
    from TempData2;

    This is not necessarily the best way to go, it is only meant to show you the problem. I'm not familiar enough with CTEs to give guidance on alternatives.

     

    We are seeing a lot of great Entity Framework questions (and answers) from the community on Stack Overflow. As a result, our team is going to spend more time reading and answering questions posted on Stack Overflow. We would encourage you to post questions on Stack Overflow using the entity-framework tag. We will also continue to monitor the Entity Framework forum.

    Wednesday, January 9, 2013 6:58 PM