Search code examples
sqlsql-serverexecexecute

How can I use the EXECUTE command for this?

We have a SQL Server 2014, with about 20+ Linked (oracle) Servers. From time-to-time, I need to check the USER ID (on the Oracle side) status for all of these linked servers.

I currently use the following OPENQUERY statement for this:

SELECT * FROM OPENQUERY (MYLINKEDORACLESERVER,'SELECT * FROM SYS.USER_USERS')

Outputs:

enter image description here

However, because of the amount of servers, we're looking for a more automated way of doing this.

SYS.SERVERS outputs a list of all linked oracle servers (I've added the OPENQUERY syntax)

SELECT 'select * from openquery ('+ name +',''SELECT * FROM USER_USERS'')' FROM sys.servers 
WHERE provider='OraOLEDB.Oracle'

Outputs:

enter image description here

How can I leverage the EXECUTE statement so that it automatically EXECUTES each OPENQUERY statement that it outputs?

Desired output:

enter image description here

Solution

  • One method would be to string aggregate them, and then execute them all in a single batch. As you're not in SQL Server 2017+, you'll be to use the old FOR XML PATH method:

    DECLARE @SQL nvarchar(MAX),
        @CRLF nchar(2) = NCHAR(13) + NCHAR(10);

SET @SQL =  STUFF((SELECT @CRLF + N'SELECT * FROM OPENQUERY('+ QUOTENAME(s.[name]) + ',''SELECT * FROM USER_USERS'');'
                   FROM sys.servers 
                   FOR XML PATH(N''),TYPE).value('(./text())[1]','nvarchar(MAX)'),1,2,N'');

PRINT @SQL; --Your best friend
EXEC sys.sp_executesql @SQL;