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