Name of the remote server in a query

Question

Name of the remote server in a query

Ram

Old Hand

Points: 351
More actions
March 1, 2021 at 10:58 pm

#3851939
Hi,
I want to retrieve the name of the remote server in my SQL server query:
exec ('CREATE OR ALTER PROCEDURE ins_tmp_proc AS
BEGIN
INSERT INTO dbo.tmp (job_id, name, ..., server)
SELECT job_id, name, .., @@SERVERNAME from msdb.dbo.sysjobs
UNION
SELECT job_id, name, .., @@SERVERNAME from <remoteServer>.msdb.dbo.sysjobs
END')
I tried executing just the SELECT statement by itself and I see that @@SERVERNAME part returns the current server name (server name on which the query is run) even for the remoteServer. I tried serverproperty('MachineName') but it still returns the current server name and not the remote server name.
Is there a way to return the name of the remote server in a remote query like above?
Thanks.
- This topic was modified 3 years, 8 months ago by Ram.

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004302 More actions · Answer 1

I guess I'm a little confused... You already know the remote server name or you wouldn't be able to query its' msdb database. Why not just substitute a literal for @@SERVERNAME in the unioned query?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

VoldemarG Hall of Fame Points: 3883 More actions · Answer 2

The way we do this at one of our servers is this:

exec ('CREATE OR ALTER PROCEDURE ins_tmp_proc AS

BEGIN

INSERT INTO dbo.tmp (job_id, name, ..., server)

SELECT job_id, name, .., ' NAME_OF_MY_REMOTE_SERVER' from msdb.dbo.sysjobs

UNION

SELECT job_id, name, .., @@SERVERNAME from <remoteServer>.msdb.dbo.sysjobs

END') AT NAME_OF_MY_REMOTE_SERVER

the above will execute whatever code is between EXEC ('

and ')

AT the remote server whose name is NAME_OF_MY_REMOTE_SERVER, which you obviously KNOW before you attempt to execute any code on it 🙂

Likes to play Chess

VoldemarG Hall of Fame Points: 3883 More actions · Answer 3

'AT' is the actual keyword, part of the syntax. You can run it like the above example from an Agent Job, for example..

and also you need to remember to do

USE MyDatabaseName;

statement before CREATE or ALTER PROC, as in your example. As you are creating that Proc in a particular database. The name of which you very likely know as well.

Likes to play Chess

Jeff Moden SSC Guru Points: 1004302 More actions · Answer 4

I'm thinking that you've changed the purpose of the code. The first query in the code uses the 3 part naming convention and seems to be meant to look at msdb on the current instance and only the second query is meant to look at a different instance.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ram Old Hand Points: 351 More actions · Answer 5

Haa. How did I miss it? Such a simple solution!

Jeff Moden SSC Guru Points: 1004302 More actions · Answer 6

Ram wrote:

Haa. How did I miss it? Such a simple solution!

Curious... which solution?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ScottPletcher SSC Guru Points: 101084 More actions · Answer 7

exec ('CREATE OR ALTER PROCEDURE ins_tmp_proc AS

BEGIN

INSERT INTO dbo.tmp (job_id, name, ..., server)

SELECT job_id, name, ..., @@SERVERNAME from msdb.dbo.sysjobs

UNION ALL

SELECT job_id, name, ..., ''<remoteserver>'' from <remoteServer>.msdb.dbo.sysjobs

END')

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

VoldemarG Hall of Fame Points: 3883 More actions · Answer 8

by the way, since we already have touched the subject,

is it a good idea at all to execute anything like this on a remote server as EXEC (...) AT <remote server>?

Linked servers/ running ad hoc qry on it like that seems like not a best practice, entailing many issues..

Likes to play Chess

ScottPletcher SSC Guru Points: 101084 More actions · Answer 9

VoldemarG wrote:

by the way, since we already have touched the subject,
is it a good idea at all to execute anything like this on a remote server as EXEC (...) AT <remote server>?
Linked servers/ running ad hoc qry on it like that seems like not a best practice, entailing many issues..

Yes, it's often a very good idea, particularly when there's a (very) large table on the remote box that needs to be processed.

As a quick example, say I'm joining a 2-row local table to a 2M row remote table. If I do the request locally, SQL has to bring all 2M rows to the local box. If I run it remotely, SQL has to bring the 2 rows over. Obviously the latter is vastly preferable for performance.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeff Moden SSC Guru Points: 1004302 More actions · Answer 10

VoldemarG wrote:

by the way, since we already have touched the subject,
is it a good idea at all to execute anything like this on a remote server as EXEC (...) AT <remote server>?
Linked servers/ running ad hoc qry on it like that seems like not a best practice, entailing many issues..

I agree with Scott on this but you actually have two different subjects here. The first subject is running the query AT the remote server. Scott covered that as being a good thing to do.

It being an "ad hoc" query might not be so good. It would, IMHO, be better to have a stored procedure on the remote server and execute it using a remote call that uses AT. Of course, "It Depends" a bit on what else is going on and there are other ways to even handle queries to different RDBMS's without having to have a stored procedure on the remote box (OpenQuery across a linked server actually does work a treat in a similar fashion). 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ram Old Hand Points: 351 More actions · Answer 11

My thanks to Jeff, Voldemar and Scott.

@jeff, I was thinking of getting the remote server name through some variable or a function in remote server while I missed the obvious solution of using the servername literal in single quotes, like you all have mentioned. Bit embarassing.

This reply was modified 3 years, 8 months ago by Ram.

Jeff Moden SSC Guru Points: 1004302 More actions · Answer 12

Ram wrote:

My thanks to Jeff, Voldemar and Scott.
@jeff, I was thinking of getting the remote server name through some variable or a function in remote server while I missed the obvious solution of using the servername literal in single quotes, like you all have mentioned. Bit embarassing.

BAWAAA-HAAAA!!! Been there, done that. I remember in my early days with computers (before PC's came out) and I was using some software to draw WBS charts. My boss did a review of the final chart after I printed it out and he said "I need a dotted line for this node to that node". I told him the software wouldn't do that. He smiled, opened his desk drawer, pulled out a ruler and a pen and said, "Do I need to repeat myself"?

Anyway, thanks for the feedback. Really appreciate it. To be honest, I though it might be me that was missing something.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ross McMicken SSCarpal Tunnel Points: 4435 More actions · Answer 13

Jeff Moden wrote:

Ram wrote:
My thanks to Jeff, Voldemar and Scott.
@jeff, I was thinking of getting the remote server name through some variable or a function in remote server while I missed the obvious solution of using the servername literal in single quotes, like you all have mentioned. Bit embarassing.
BAWAAA-HAAAA!!! Been there, done that. I remember in my early days with computers (before PC's came out) and I was using some software to draw WBS charts. My boss did a review of the final chart after I printed it out and he said "I need a dotted line for this node to that node". I told him the software wouldn't do that. He smiled, opened his desk drawer, pulled out a ruler and a pen and said, "Do I need to repeat myself"?
Anyway, thanks for the feedback. Really appreciate it. To be honest, I though it might be me that was missing something.

Ahh, the old "if the software can't do it, maybe you need more hardware" solution.

Jeff Moden SSC Guru Points: 1004302 More actions · Answer 14

Ross McMicken wrote:

Jeff Moden wrote:
Ram wrote:
My thanks to Jeff, Voldemar and Scott.
@jeff, I was thinking of getting the remote server name through some variable or a function in remote server while I missed the obvious solution of using the servername literal in single quotes, like you all have mentioned. Bit embarassing.
BAWAAA-HAAAA!!! Been there, done that. I remember in my early days with computers (before PC's came out) and I was using some software to draw WBS charts. My boss did a review of the final chart after I printed it out and he said "I need a dotted line for this node to that node". I told him the software wouldn't do that. He smiled, opened his desk drawer, pulled out a ruler and a pen and said, "Do I need to repeat myself"?
Anyway, thanks for the feedback. Really appreciate it. To be honest, I though it might be me that was missing something.
Ahh, the old "if the software can't do it, maybe you need more hardware" solution.

Heh... now THAT'S FUNNY!!!! 😀 Thanks for the great laugh... I needed it!

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)