How to use OPENQUERY to properly execute a Stored Procedure that updates a linked server table?
OpenRowset is primarily designed for retrieving data, though you can use it to insert and execute stored procedures. I would recommend that you switch to CLR function for executing the remote stored procedure. Heres a simple code you can compile to a .net assembly.



using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SqlServer.Server;
using System.Data.SqlClient;
using System.Data;
using System.IO;
using System.Collections;
using System.Data.SqlTypes;
using System.ComponentModel;

namespace ETL
{
    public class SQLServer
    {

        [SqlFunction(Name = "ExecSQL", SystemDataAccess = SystemDataAccessKind.Read, DataAccess = DataAccessKind.Read)]
        public static SqlString ExecSQL(string server, string Database, string cmdSQL)
        {
            string rValue = "0";
            using (var conn = new SqlConnection(string.Format(@"Server={0};DataBase={1};Integrated Security=SSPI", server.ToString(), Database.ToString())))
            using (var command = new SqlCommand(cmdSQL.ToString(), conn)
            {
                CommandType = CommandType.StoredProcedure
            })
            {
                command.CommandTimeout = 800;
                conn.Open();
                command.ExecuteNonQuery();
                rValue = "1";  
            }
            return rValue;
        }
    }
}



Good Luck :)