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
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?