Use dynamic query for :Connect in SQL Server?

Question

I have a list of database connections in a table and want to iterate through them, connecting to each one within SQL Server through a script, and then execute a select statement.

Initially, I successfully executed the connection command directly for one database using

:CONNECT your_servername -U your_username -P your_password

However, when attempting to loop through multiple databases and write dynamic SQL, I encountered an error and would appreciate some expert assistance.

Here's the query I'm trying to execute: I have set mode on editor Menu --> Query --> 'SQLCMD MODE'

DECLARE @sql NVARCHAR(MAX);
DECLARE @servername NVARCHAR(100) = 'your_servername  -U';
DECLARE @username NVARCHAR(100) = 'your_username';
DECLARE @password NVARCHAR(100) = '-P  your_password';

SET @sql = ':CONNECT ' + @servername + ' ' + @username + ' ' + @password + '';
print @sql
EXEC sp_executesql @sql;

And here's the error message I'm receiving:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ':'

I've tried troubleshooting it myself but haven't been able to resolve it. Could someone please help me understand what might be causing this error and how to fix it or alternate solutions?

Any guidance or suggestions would be greatly appreciated. Thank you in advance!

Answer 1

As others have mentioned, :CONNECT is an sqlcmd client command and is not a valid SQL keyword.

If you wish to run a query across multiple servers, you could either utilise Linked Servers, or write a batch script that executes multiple sqlcmd commands that connect to each server and execute your desired SQL queries in a loop.

If you just want to execute queries on multiple databases on the same server, you can use the USE keyword to change the database context the following queries will use.

Answer 2

:CONNECT is a client-side SQLCMD command, not an SQL statement, so you can't run it from server-side code.

You should use linked servers. You can get a script to create all your linked servers like this:

SELECT
  'EXEC sp_addlinkedserver @server = ' + QUOTENAME(server_name, '''') + ', @provstr = ' + QUOTENAME(conn_string, '''')
FROM YourServers;

You only need to create them once.

---

Then if you want to automate the running of all those scripts, you can do:

DECLARE @crsr CURSOR;
DECLARE @proc nvarchar(1000), @script nvarchar(max);

SET @crsr = CURSOR FAST_FORWARD FOR
SELECT
  QUOTENAME(server_name) + '.' + QUOTENAME(database_name) + '.sys.sp_executesql',
  script
FROM YourScripts;

OPEN @crsr;
WHILE 1=1
BEGIN
    FETCH @crsr INTO @proc, @script;
    IF @@fetch_status <> 0 BREAK;

    EXEC @proc @script;
END;