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
            DECLARE @Status char = 'X'
            SET     [STATUS] = @Status
                  , CLOSE_DATE = @ClosedDate
            WHERE   BASE_ID = @WorkOrderNumber
                    AND LOT_ID = @LotId
                    AND SPLIT_ID = @SplitId


    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;


    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.





  2. Doroteya
    Doroteya avatar
    495 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.

    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