How to use OPENQUERY to properly execute a Stored Procedure that updates a linked server table?

Question

I'm having a hard time trying to figure this out. I'm using OPENQUERY to execute Stored Procedures on a linked server. I managed to find a way to do it, by using the following:

SELECT *    
FROM   OPENQUERY(
[LINKEDSRV\SQLSRV],  
'SET FMTONLY OFF; SET NOCOUNT ON; EXEC [Database_Name_Example].Data.usp_GetNames 5,''S''')   
GO 

The reason I use SET FMTONLY OFF and SET NOCOUNT ON is because when I tried the code above without them, this message appeared:

Msg 7357, Level 16, State 1, Line 1 Cannot process the object "EXEC [Database_Name_Example].Data.usp_GetNames 5,'S'". The OLE DB provider "SQLNCLI" for linked server "LINKEDSRV\SQLSRV" indicates that either the object has no columns or the current user does not have permissions on that object.

Reading a lot online helped me find the answer. This blog for example: http://www.sommarskog.se/share_data.html#OPENQUERY

So far so good, until I stomped into a Stored Procedure that use SELECT and UPDATE some tables on the linked server. When I execute the OPENQUERY I get the results from the SELECT statement but I noticed that it does not update the table.

The Stored Procedure goes like this:

CREATE PROCEDURE Data.usp_GetNames (@year tinyint=NULL, @online char(1)=NULL)
if @online = 'S'    
  BEGIN
    select nam_codig, nam_desc from Names
    where nam_status = 'P'
    order by nam_date

    UPDATE Names
    SET nam_status = 'S'
    where nam_status = 'P'
END

When I execute it directly in the linked server it updates the table, so the second time I execute it, there will be no more nam_status = 'P'

But if I execute it with OPENQUERY the results are the same, it doesn't UPDATE it just select the data.

What can I do to allow the permissions?

Thank you!!!

Answer 1

Try using four-part naming instead of using OPENQUERY to execute stored procedures via a linked server:

EXECUTE [LINKEDSRV\SQLSRV].[Database_Name_Example].Data.usp_GetNames 5,'S'

Answer 2

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 :)