Problems Updating Linked Server From Stored Procedure

2 posts, 0 answers
  1. Wizgod
    Wizgod avatar
    3 posts
    Member since:
    Nov 2007

    Posted 16 Jun Link to this post

    Greetings Programs!

    I have the following stored procedure updating a table in a linked server:

    CREATE PROCEDURE [dbo].[UspUpdateVisual]
        @WorkOrderNumber varchar(30)
      , @LotId varchar(3)
      , @SplitId varchar(3)
      , @ClosedDate datetime
    AS
        BEGIN
            DECLARE @Status char = 'X'
     
            UPDATE  SANDBOX.PSL.dbo.WORK_ORDER
            SET     [STATUS] = @Status
                  , CLOSE_DATE = @ClosedDate
            WHERE   BASE_ID = @WorkOrderNumber
                    AND LOT_ID = @LotId
                    AND SPLIT_ID = @SplitId
        END

     

    Now the stored procedure does get called with the following code (I have tested it by updating a local table) but it is not updating the linked server. When stepping through the code, it calls the context.ExecuteNonQuery but does not return from it or throw an error.

    DbParameter[] parameters =
    {
        new OAParameter {ParameterName = "WorkOrderNumber", Value "1"}
        new OAParameter {ParameterName = "LotId", Value = "1"},
        new OAParameter {ParameterName = "SplitId", Value = "0"},
        new OAParameter {ParameterName = "ClosedDate", Value = DateTime.Now}
    };
     
    using (var context = new EntitiesModel())
    {
        context.ExecuteNonQuery("[dbo].[UspUpdateVisual]", CommandType.StoredProcedure, parameters);
    }

    However, when I use SqlCommand, the linked server updates with no issues.

        using (var conn = new SqlConnection(Database.ConnectionString))
        {
            using (var command = new SqlCommand("[dbo].[UspUpdateVisual]", conn)
            {
                CommandType = CommandType.StoredProcedure
            })
            {
                command.Parameters.Add("@WorkOrderNumber", SqlDbType.VarChar).Value = "1";
                command.Parameters.Add("@LotId", SqlDbType.VarChar).Value = "1";
                command.Parameters.Add("@SplitId", SqlDbType.VarChar).Value = "0";
                command.Parameters.Add("@ClosedDate", SqlDbType.DateTime).Value = DateTime.Now;
                conn.Open();
                command.ExecuteNonQuery();
            }
        }
    }

     

    Would anyone have any idea why the context.ExecuteNonQuery doesn't work and the command.ExecuteNonQuery does? I would prefer to use the context call.

     

    Thanks!

     

    Wg

  2. Doroteya
    Admin
    Doroteya avatar
    502 posts

    Posted 22 Jun Link to this post

    Hi Wizgod,

    By design, Telerik Data Access does not commit in the database the changes that are made by stored procedures and functions. In order to do so, you need to explicitly call the SaveChanges() method of the context after the call to the ExecuteNonQuery() method. More details on the topic are available in this KB article.

    I hope this helps.

    Regards,
    Doroteya
    Telerik
     
    Check out the latest announcement about Telerik Data Access vNext as a powerful framework able to solve core development problems.
  3. DevCraft banner
Back to Top