Problem with parenthesized identifier inside SQL Server OPENQUERY

Question

Not sure if this should happen in mode or in DBA section.

There is a problem with the procedure I am writing. I have a variable, say:

SET @name sysname --also tried to make as varchar

This is used inside the cursor and will contain the SQL Server names. Multiple server names are followed by instance names. for instance

DECLARE @name = 'SERVER1\INSTANCE1'

Inside the cursor, I have this query.

SELECT @name, * FROM OPENQUERY(@name,
                      'SELECT 
                            i.Name, 
                            i.database_id, 
                            b.mirroring_state 

                            FROM msdb.sys.databases i
                            INNER JOIN msdb.sys.database_mirroring b
                            ON i.database_id = b.database_id
                            WHERE b.mirroring_state IS NOT NULL')

which does not work due to \inside@name

However, if I try this, it works fine.

SELECT 'SERVER1\INSTANCE1', * FROM OPENQUERY([SERVER1\INSTANCE1],

The problem I am facing is trying to use the parenthesized identifier with @nameinside OPENQUERY.

I tried several things, including various combinations OPENQUERY('['+@name+']',

If you just try, FROM OPENQUERY([@name],SQL Server parses it literally like @name.

, servername\instance ?

, :

 DECLARE @name sysname,
        @sql nvarchar(4000)

 DECLARE c1 CURSOR FOR 
                SELECT SUBSTRING (Servername, 2, LEN(Servername)-2) 
                FROM AllServers
OPEN c1
    FETCH NEXT FROM c1
    INTO @name
    WHILE @@FETCH_STATUS = 0
    BEGIN

            set @sql = 'INSERT INTO MirrorResults
                            SELECT ''[' + @name + ']'', * FROM OPENQUERY([' + @name + '], 
                                ''
                                SELECT 
                                    i.Name, 
                                    i.database_id, 
                                    b.mirroring_state 
                                from msdb.sys.databases i
                                INNER JOIN msdb.sys.database_mirroring b
                                ON i.database_id = b.database_id
                                WHERE b.mirroring_state IS NOT NULL
                            '')'


        EXECUTE sys.sp_executesql @sql;
        FETCH NEXT FROM c1
    END
CLOSE c1
DEALLOCATE c1

Answer 1

OPENQUERY . MSDN:

OPENQUERY ( linked_server ,'query' )

linked_server

, .

, servername\instance . , .

SQL:

DECLARE @linked_server VARCHAR(20), @sql NVARCHAR(512);

SET @linked_server = 'SERVER1\INSTANCE1';
SET @sql = 'SELECT * FROM OPENQUERY([' + @linked_server + '], ''SELECT * FROM SomeTable;'');';

EXEC sys.sp_executesql @sql;