Stack Exchange
Cross server EXEC
I have a several linked servers, with mass of DB's and tons of views and tables.
The database names are like random set of letters and numbers and does not make any sense for me.
Need to find on which server is certain view.
DECLARE @servers AS TABLE (srv_name NVARCHAR(300), srv_providername NVARCHAR(300), srv_product NVARCHAR(300), srv_datasource NVARCHAR(300), srv_pstring NVARCHAR(300), srv_location NVARCHAR(300), srv_cat NVARCHAR(300) );
INSERT INTO @servers EXEC sp_linkedservers

DECLARE @sql nvarchar(MAX) = N''

SELECT @sql += 
N'SELECT 
    ''' + QUOTENAME(srv_name) + ''' AS [Server],
    QUOTENAME(name) AS [DB]
FROM ' + QUOTENAME(srv_name) + '.[master].[sys].[databases]
WHERE HAS_PERMS_BY_NAME(name, ''DATABASE'', ''SELECT'') = 1
UNION ALL
'
FROM @servers

SET @sql = LEFT(@sql, LEN(@sql) - 11);
EXEC @sql

And it fails on EXEC. Says that query is not valid, but if i copy-paste the query an run it - works well.
Need to perform this exec, to generate temp table w/ servers and available databases, to query sys.all_object of each DB on each server
The output on my localhost w/ 1 server
> (1 row(s) affected) Msg 203, Level 16, State 2, Line 17 The name
> 'SELECT 
>     '[localhost]' AS [Server],
>     QUOTENAME(name) AS [DB] FROM [localhost].[master].[sys].[databases] WHERE HAS_PERMS_BY_NAME(name,
> 'DATABASE', 'SELECT') = 1 ' is not a valid identifier.


why FROM @servers is outside string?

cause it's temp table of linked servers which i use to generate dynamic sql

yeap right, stupid question. Sorry

maybe you can put exact error?

have you tried exec sp_executesql @sql ?

Same error I also made.
So just execute like this:
exec sp_executesql @sql
