Fully Qualifying Server

Question

Fully Qualifying Server

cbrammer1219

SSCarpal Tunnel

Points: 4476
More actions
June 2, 2022 at 9:28 pm

#4043445

So I am trying to execute a stored procedure on another server from another server but can not get the format correctly, I am putting it into a variable @server and tryin g to format it like this, but i keep getting error Database 'DataServer70801' does not exist. Make sure that the name is entered correctly.
USE DataServer70801\workflow,1113
EXEC msdb.dbo.sp_update_job @job_name = @job_name, @enabled = 1

Viewing 6 posts - 1 through 5 (of 5 total)

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

ratbak Ten Centuries Points: 1358 More actions · Answer 1

The USE statement is for specifying database to use on the connected server, not for specifying a server.

You need to define & use a linked server (the definition would include the instance name & port if non-default). You can execute stored procedure on linked server using four-part naming -- e.g.,

EXEC DataServer70801.msdb.dbo.sp_update_job @job_name = @job_name, @enabled = 1

cbrammer1219 SSCarpal Tunnel Points: 4476 More actions · Answer 2

So is this possible

set @execSrvQuery = (

select top(1) Replace(d.SQLServerName+'\'+ s.SQLInstanceName+','+Convert(varchar(10),TCPPortNum),',0',',1113') as SQLInstanceName

FROM Catalog.eAudit.DatabaseServerSQLInstance s

inner join [Catalog].[eAudit].[DatabaseServer] d on d.DatabaseServerID=s.DatabaseServerID

where s.SQLInstanceName like '%KCW%' )--and s.SQLInstanceName not like '%SSAS%'

union

Select @@ServerName)

EXEC [@execSrvQuery].[msdb].dbo.sp_update_job @job_name = @job_name, @enabled = 0

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 3

Are you looking at running this from an agent job or within a stored procedure? If so - then you need a linked server and you can either use 4-part naming or EXECUTE AT statement. There are several examples of how to do that at: https://docs.microsoft.com/en-us/sql/t-sql/language-elements/execute-transact-sql?view=sql-server-ver16

And no - you can't construct a query using a 'variable' as part of the object name.

From the above reference:

-- Setup the linked server.  
EXEC sp_addlinkedserver 'SeattleSales', 'SQL Server'  
GO  
-- Execute the SELECT statement.  
EXECUTE ('SELECT ProductID, Name   
    FROM AdventureWorks2012.Production.Product  
    WHERE ProductID = ? ', 952) AT SeattleSales;  
GO

In the above - you can define the 'query' and parameters using variables, but you cannot specify the linked server using a variable. So - you will need dynamic SQL to construct the statement to be executed.

With that said - if you are looping through a list of instances where you are going to run some defined code against each instance, then I would recommend creating a temporary linked server. Something like this:

--==== Create cursor to loop over list of instances
Declare instanceList Cursor Local Fast_Forward
    For
 Select ...
   From your_instance_table;

 --==== Open/Fetch
   Open instanceList
  Fetch Next From instanceList Into ...;

  While @@fetch_status = 0
  Begin

Execute sp_addlinkedserver 'Temp', ... --add all parameters needed for the linked server for this instance
Execute ('your query', parm1, parm2, ...) At temp;
Execute sp_droplinkedserver 'Temp';

 --==== Fetch Next
  Fetch Next From instanceList Into ...;
    End

 --==== Close/Deallocate
        Close instanceList;
        Deallocate instanceList;

You may also need to add a BEGIN TRY/CATCH to catch instances where you can't create the linked server (or other errors) so you can ignore those or put in some retry logic or additional checking/notifications. Not sure if it will be captured in a try/catch - might need additional error checking...

This reply was modified 2 years, 5 months ago by Jeffrey Williams.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 4

One other note - if this was something I was developing I would not build this in T-SQL. I would use Powershell to get the list of instances and use Invoke-SqlCmd to connect to each instance and run the query.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 5

I'd also suggest using OPENQUERY instead of just EXECUTE. Or, like you said, EXECUTE AT.

--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)