OPENDATASOURCE doeasn't work in @DynamicSQL

Question

Few days ago I asked on StackOverflow how to query multiple server remotely, a user replied promptly with the right answer and it works perfectly:

DECLARE @ServerName varchar(50), @DynamicSQL NVARCHAR(MAX)
DECLARE @myTableVariable TABLE (id INT, ServerName varchar(50))
insert into @myTableVariable values(1,'[TESLABSQL01T]'),(2,'[TESLABSQL02T]')
select * from @myTableVariable

Declare VarCursor cursor for
Select ServerName from @myTableVariable
Open VarCursor
FETCH NEXT FROM VarCursor INTO @ServerName
WHILE @@FETCH_STATUS = 0 
BEGIN
    SET @DynamicSQL='SELECT @@ServerName AS [ServerName]
                 ,NAME AS DatabaseName 
                 ,COUNT(STATUS) AS [NumberOfConnections]
                 ,GETDATE() AS [TimeStamp]
                 ,hostname
                 ,program_name
                 ,loginame
    FROM '+@ServerName+'.master.sys.databases sd
    LEFT JOIN '+@ServerName+'.master.sys.sysprocesses sp ON sd.database_id = sp.dbid
    WHERE database_id NOT BETWEEN 1 AND 4
    GROUP BY NAME,hostname ,program_name ,loginame'
    

    EXEC (@DynamicSQL)

    FETCH NEXT FROM VarCursor INTO @ServerName
END
CLOSE VarCursor
DEALLOCATE VarCursor

I now would like to use OPENDATASOURCE instead as it doesn't requires linked servers. So I modified the query this way:

DECLARE @ServerName varchar(50), @DynamicSQL NVARCHAR(MAX)
DECLARE @myTableVariable TABLE (id INT, ServerName varchar(50))
insert into @myTableVariable values(1,'[TESLABSQL01T]'),(2,'[TESLABSQL02T]')
select * from @myTableVariable

Declare VarCursor cursor for
Select ServerName from @myTableVariable
Open VarCursor
FETCH NEXT FROM VarCursor INTO @ServerName
WHILE @@FETCH_STATUS = 0 
BEGIN
    SET @DynamicSQL='SELECT @@ServerName AS [ServerName]
                 ,NAME AS DatabaseName 
                 ,COUNT(STATUS) AS [NumberOfConnections]
                 ,GETDATE() AS [TimeStamp]
                 ,hostname
                 ,program_name
                 ,loginame
    FROM OPENDATASOURCE(''SQLNCLI'', ''Server='+@ServerName+';TrustServerCertificate=Yes;Trusted_Connection=Yes;'').master.sys.databases sd
    LEFT JOIN .master.sys.sysprocesses sp ON sd.database_id = sp.dbid
    WHERE database_id NOT BETWEEN 1 AND 4
    GROUP BY NAME,hostname ,program_name ,loginame'
    

    EXEC (@DynamicSQL)

    FETCH NEXT FROM VarCursor INTO @ServerName
END
CLOSE VarCursor
DEALLOCATE VarCursor

and I receive a strange error:

(2 rows affected)

(2 rows affected)
OLE DB provider "MSOLEDBSQL" for linked server "(null)" returned message "Login timeout expired".
OLE DB provider "MSOLEDBSQL" for linked server "(null)" returned message "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.".
Msg 53, Level 16, State 1, Line 0
Named Pipes Provider: Could not open a connection to SQL Server [53].

...so strange because if I run the query in another tab without using the Dynamic SQL everything works fine:

SELECT @@ServerName AS [ServerName]
                 ,NAME AS DatabaseName 
                 ,COUNT(STATUS) AS [NumberOfConnections]
                 ,GETDATE() AS [TimeStamp]
                 ,hostname
                 ,program_name
                 ,loginame
    FROM OPENDATASOURCE('SQLNCLI', 'Server=TESLABSQL01T;TrustServerCertificate=Yes;Trusted_Connection=Yes;').master.sys.databases sd
    LEFT JOIN master.sys.sysprocesses sp ON sd.database_id = sp.dbid
    WHERE database_id NOT BETWEEN 1 AND 4
    GROUP BY NAME,hostname ,program_name ,loginame

Where am I wrong?

The quickest way to debug dynamic SQL is to `PRINT`/`SELECT` the dynamic statement, execute that, debug it, and then propagate the solution. *We* can't run your SQL, so we can't replicate the problem. — Thom A, Jul 02 '21 at 09:26
Note, as well, your SQL is open to injection. Whenever you are injecting dynamic objects into a statement **always** use `QUOTENAME` to mean that the valid is properly delimit identified. — Thom A, Jul 02 '21 at 09:27
Hi @Larnu you can debug on your machine by using `Server=localhost` or a name of a remote server — Francesco Mantovani, Jul 02 '21 at 09:30
@Larnu are you running this query: https://snipboard.io/N85dTL.jpg — Francesco Mantovani, Jul 02 '21 at 09:35
Guessing the problem is you have delimit identified your object names in your data. `[TESLABSQL01T]` is *not* the name of your host. Delimit identify the values in your SQL, not in the data. Again, the **easiest** way to debug a dynamic statement is to `PRINT` it. Your non-dynamic statement is *not* the same as `'Server=TESLABSQL01T` and `'Server=[TESLABSQL01T]` are *not* the same `PRINT` the statement and you'll quickly see the problem. — Thom A, Jul 02 '21 at 09:35
Then why is it `[TESLASQL01T]` in your sample data..? `insert into @myTableVariable values(1,'[TESLABSQL01T]'),(2,'[TESLABSQL02T]')`. I'll repeat myself again, though, `PRINT` your statement, debug that. — Thom A, Jul 02 '21 at 09:38
If I'm honest, this is a typographical error then; in the data you have `INSERT`ed. This is why I mentioned, several times, that `PRINT`ing the statement is so important, and also why using `QUOTENAME` is. — Thom A, Jul 02 '21 at 09:43

OPENDATASOURCE doeasn't work in @DynamicSQL

0 Answers0