CODEWITHSUNDEEP
sql-server
Genebert
Genebert

Reputation: 70

Modifying Stored Procedure using Servername and Database Name

I've already searched online if it works but unfortunately, I did not found any answers. I just want to ask if this is possible or not, because when I try the code below it says:

Database 'IPAddress' does not exist. Make sure that the name is entered correctly.

I know the credentials of the said ip address and I can easily log in and run this sp. I already add the server to link server its just a matter of curiosity if this is possible.

USE [IPAddress].[DatabaseName]
GO
/****** Object:  StoredProcedure [dbo].[SP_Name]    Script Date: 1/14/2020 10:36:26 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[SP_Name]

Upvotes: 0

Views: 308

Answers (2)

Venkataraman R
Venkataraman R

Reputation: 12959

You cannot use USE command that way. You can only put database name for USE command. Read more about USE

Instead, you can achieve the same using SQLCMD mode in SSMS. Read more on SQLCMD mode

:CONNECT [ipaddress]
USE [Database]
GO
ALTER PROCEDURE [dbo].[spname]...
GO

Upvotes: 0

Nick.Mc
Nick.Mc

Reputation: 19194

I've never done it before but apparently you can use EXEC AT

https://www.mssqltips.com/sqlservertip/1757/dynamic-sql-execution-on-remote-sql-server-using-exec-at/

Something like this

EXEC ('USE [Database] GO ALTER PROC spName.....') AT [LinkedServer];

Upvotes: 1

Related Questions