Problems Updating Linked Server From Stored Procedure

[Wizgod]

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

[Doroteya]

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