February 26, 2012 at 4:49 pm
I'm trying to get the following code to work on a linked server:
SELECT * FROM OPENQUERY(AUSYD5320,
'SELECT [SPID] = session_Id
, ecid
,sp.blocked AS [Blocked Process]
, [Database] = DB_NAME(sp.dbid)
, [nt_domain] AS [Domain]
, [User] = nt_username
, [Status] = er.status
, [Wait] = wait_type
, [Individual Query] = SUBSTRING (qt.text,
er.statement_start_offset/2,
(CASE WHEN er.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE er.statement_end_offset END -
er.statement_start_offset)/2)
,[Parent Query] = qt.text
, Program = program_name
, Hostname
, nt_domain
, start_time
, cpu AS [CPU]
, physical_io AS [Disk I/O]
FROM master.sys.dm_exec_requests er
INNER JOIN master.sys.sysprocesses sp ON er.session_id = sp.spid
CROSS APPLY master.sys.dm_exec_sql_text(er.sql_handle)as qt
WHERE session_Id > 50
AND session_Id NOT IN (@@SPID)
ORDER BY 1, 2')
I get the folowing error message when it's run:
OLE DB provider "SQLNCLI10" for linked server "AUSYD5320" returned message "Deferred prepare could not be completed.".
Msg 297, Level 16, State 1, Line 1
The user does not have permission to perform this action.
Running the query locally on the linked server works fine, it's just running it remotely doesn't seem to work.
Any advice would be appreciated, thanks
February 27, 2012 at 3:29 am
This was removed by the editor as SPAM
February 27, 2012 at 3:50 am
Calling user needs to have VIEW SERVER STATE permission to query DMVs on remote server.
February 27, 2012 at 2:18 pm
Thanks guys, VIEW SERVER STATE did the trick. Got thrown by the fact that it threw an error about a deferred prepare.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply