How to create stored procedure in remote server using linked server

Question

I tried to create an stored procedure in a remote server using this way

EXECUTE (N'USE [master]; EXEC sp_executesql N'+ @sqlCommand) AT [LinkedServer]

However I received this error

Msg 111, Level 15, State 1, Procedure StoredProcedure, Line 5 [Batch Start Line 0] 'CREATE/ALTER PROCEDURE' must be the first statement in a query batch.

in the sqlCommand has the query similar this

SET @sqlCommand = '
CREATE OR ALTER PROCEDURE [dbo].[storedName]
 @nameDB varchar(50)
AS
BEGIN
 SET NOCOUNT ON;
 DECLARE @SQLProcessesRep varchar(max);
 --Actions in Stored
END'

Answer 1

You can call sp_executesql directly in the context of the linked server's master database, using four-part naming.

EXEC LinkedServer.master.sys.sp_executesql @sqlCommand;

If the linked server name is in a variable you can even do this dynamically

DECLARE @proc nvarchar(1000) = @dbname + N'.master.sys.sp_executesql';
EXEC @proc @sqlCommand;

• lego con tsijiari: Thanks a lot this option EXEC LinkedServer.master.sys.sp_executesql @sqlCommand; works.

Answer 2

well yeah

You have two options:

1. Remove the USE [master]; before the EXEC, and rely on the user's default database being master
2. Execute sys.sp_executesql in the context of the master database

Your second option would look like this:

EXECUTE (N'EXEC master.sys.sp_executesql N'+ @sqlCommand) AT [LinkedServer];

If you hit any additional issues, please ask a new question.

• lego con tsijiari: Thanks a lot, is a great solution, regards.