Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Linked server query column needed Expand / Collapse
Author
Message
Posted Monday, August 27, 2012 8:02 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 12:31 PM
Points: 75, Visits: 345
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


Post #1350399
Posted Monday, August 27, 2012 10:29 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Post #1350478
Posted Monday, August 27, 2012 11:10 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 12:31 PM
Points: 75, Visits: 345
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
Post #1350494
Posted Monday, August 27, 2012 1:19 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 12:31 PM
Points: 75, Visits: 345
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
Post #1350544
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse