能否创建可连接多服务器的SQL Server存储过程？如何实现？

Great question! This is totally feasible, and leveraging the :CONNECT command (a feature of SQLCMD mode) is a clever way to sidestep the performance overhead and configuration hassle that often comes with linked servers. Let’s break down how this works and the best ways to implement it:

First, a quick clarification: :CONNECT isn’t native T-SQL—it’s a command specific to SQLCMD mode (supported by SSMS and the sqlcmd.exe command-line tool). It lets you switch your active connection to a different SQL Server instance mid-script, so all subsequent queries run directly on that target server (no data shuffling through a middleman like linked servers).

Option 1: Use a SQLCMD Script (Recommended for Simplicity & Performance) #

If you don’t strictly need to wrap logic in a stored procedure, this is the cleanest approach. It’s lightweight, direct, and avoids extra security risks.

1. Enable SQLCMD Mode:

   • In SSMS: Go to the top menu → Query → SQLCMD Mode (you’ll see :SQLCMD in the status bar when it’s active).
   • Via command line: Use sqlcmd.exe instead of running queries directly in SSMS.

2. Write Your Cross-Server Script:
   You can switch between servers as needed, with each block of T-SQL executing on the connected instance:

   -- Connect to first server and run a query
   :CONNECT ServerName1
   SELECT * FROM dbo.CustomerTable;
   GO
   
   -- Switch to second server for another operation
   :CONNECT ServerName2
   INSERT INTO dbo.LogTable (EventDetails) VALUES ('Synced customer data from ServerName1');
   GO
   
   -- Even use SQLCMD variables for flexibility
   :SETVAR TargetServer ServerName3
   :CONNECT $(TargetServer)
   SELECT COUNT(*) FROM dbo.OrderTable;
   GO
   

   This approach runs queries directly on each target server, so you avoid the performance hit of linked server data routing.

Option 2: Wrap Logic in a Stored Procedure (Requires Extra Configuration) #

T-SQL stored procedures don’t natively support SQLCMD commands like :CONNECT, but you can work around this by using xp_cmdshell to call sqlcmd.exe internally. Note: This requires enabling xp_cmdshell (disabled by default for security), so only use this if you absolutely need stored procedure encapsulation.

1. Enable xp_cmdshell (Once):

   sp_configure 'show advanced options', 1;
   RECONFIGURE;
   GO
   sp_configure 'xp_cmdshell', 1;
   RECONFIGURE;
   GO
   

2. Create the Stored Procedure:
   This example builds a dynamic SQLCMD script, saves it to a temp file, executes it via sqlcmd.exe, then cleans up:

   CREATE PROCEDURE dbo.RunCrossServerQueries
       @Server1 NVARCHAR(128),
       @Server2 NVARCHAR(128)
   AS
   BEGIN
       SET NOCOUNT ON;
   
       -- Build the SQLCMD script content
       DECLARE @SqlCmdScript NVARCHAR(MAX) = N'
   :CONNECT ' + @Server1 + '
   SELECT * FROM dbo.CustomerTable;
   GO
   :CONNECT ' + @Server2 + '
   INSERT INTO dbo.LogTable (EventDetails) VALUES (''Ran customer query on ' + @Server1 + ''');
   GO';
   
       -- Create a unique temp file path
       DECLARE @TempFilePath NVARCHAR(256) = N'C:\Temp\CrossServerScript_' + CONVERT(NVARCHAR(36), NEWID()) + '.sql';
       DECLARE @Cmd NVARCHAR(MAX);
   
       -- Write the script to the temp file
       SET @Cmd = N'echo ' + REPLACE(@SqlCmdScript, '''', '''''') + ' > "' + @TempFilePath + '"';
       EXEC xp_cmdshell @Cmd;
   
       -- Execute the script with sqlcmd (use -E for Windows auth; add -U/-P for SQL auth if needed)
       SET @Cmd = N'sqlcmd -S localhost -E -i "' + @TempFilePath + '"';
       EXEC xp_cmdshell @Cmd;
   
       -- Clean up the temp file
       SET @Cmd = N'del "' + @TempFilePath + '"';
       EXEC xp_cmdshell @Cmd;
   END
   GO
   

• SQLCMD Mode Requirement: If you run a script with :CONNECT in regular T-SQL mode (not SQLCMD mode), you’ll get an error—T-SQL doesn’t recognize the command.
• Permissions: The account running the script/stored procedure needs login access to all target servers, plus permissions on the databases/tables you’re interacting with.
• Transactions: Each :CONNECT switches to a new connection, so transactions are isolated per server. If you need distributed transactions, you’ll have to use MSDTC (which adds overhead), so try to keep operations independent when possible.

内容的提问来源于stack exchange，提问作者Anonymous