Getting server name who is executing query in a linked Server

Question

In SQL Server, I have a server A and I linked a server B to A, so from server A I can execute query like :

exec B.DataBase1.dbo.simplePs.

My question is how can I get the server who is executing the PS? I tried @@ServerName like this :

Create procedure simplePs
as
    insert into TRACE(EventInfo) values (@@SERVERNAME)

but I get server B not server A.

Please help.

Answer 1

You could try querying the sys.sysprocesses table - there's lots of useful connection information there e.g.

insert into TRACE(EventInfo) 
values (select distinct hostname from sys.sysprocesses where spid = @@SPID)

will insert the source hostname of the connection.