August 27, 2012 at 8:02 am
Hello all,
Pardon my ignorance ... but i'm trying to add a column in my linked server query which is where the records are coming from. I would need a the target name, in this case it's the [linked server]
This is a sp_who2 executed with a linked server.
SELECT
--@@servername
-- D.text SQLStatement,
--A.Session_ID SPID,
--ISNULL(B.status,A.status) Status,
distinct A.login_name Login, A.host_name 'Originating HostName',
--C.BlkBy,
DB_NAME(B.Database_ID) DBName, B.command,
--ISNULL(B.cpu_time, A.cpu_time) CPUTime,
--ISNULL((B.reads + B.writes),(A.reads + A.writes)) DiskIO,
--A.last_request_start_time LastBatch,
A.program_name
FROM [linked server].master.sys.dm_exec_sessions A
LEFT JOIN [linked server].master.sys.dm_exec_requests B
ON A.session_id = B.session_id
LEFT JOIN (SELECT A.request_session_id SPID, B.blocking_session_id BlkBy
FROM [linked server].master.sys.dm_tran_locks as A
INNER JOIN [linked server].master.sys.dm_os_waiting_tasks
as B ON A.lock_owner_address = B.resource_address) C
ON A.Session_ID = C.SPID
OUTER APPLY master.sys.dm_exec_sql_text(sql_handle) D
August 27, 2012 at 10:29 am
Can't you just hard-code the string in the Select?
select 'Linked Server Name' as LinkedServerName
from [LinkedServer].database.schema.object;
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 27, 2012 at 11:10 am
This will work I guess , although I would like the column at the beginning...
SELECT
distinct A.login_name Login, A.host_name 'Originating HostName',
DB_NAME(B.Database_ID) DBName, B.command,
A.program_name,
data_source as 'Linked Server Name'
FROM [linked].master.sys.dm_exec_sessions A
LEFT JOIN [linked].master.sys.dm_exec_requests B
ON A.session_id = B.session_id
LEFT JOIN (SELECT A.request_session_id SPID, B.blocking_session_id BlkBy
FROM [linked].master.sys.dm_tran_locks as A
INNER JOIN [linked].master.sys.dm_os_waiting_tasks
as B ON A.lock_owner_address = B.resource_address) C
ON A.Session_ID = C.SPID
OUTER APPLY master.sys.dm_exec_sql_text(sql_handle) D,
--Add column from target linked server
[linked].master.sys.servers
August 27, 2012 at 1:19 pm
Actually it doesn't work as it lists back all linked servers on the specific server 🙁
I just need the server where i'm receiving the info from...
thanks
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply