Issue with Bracketed Identifier inside SQL Server OPENQUERY

Question

Not sure, if this should go on Stack Overflow, or the DBA section.

Having an issue with a procedure I'm writing. I have a variable, lets say:

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

This is used inside a cursor, and will basically contain SQL Server names. Several of the server names are followed by instances names. For example

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 doesn't work because of the \ inside the @name

However, if I try this, it works perfectly.

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

The issue I'm having is trying to use the bracketed identifier with the @name inside the OPENQUERY.

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

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

Any ideas on how to use the servername\instance name without having these issues?

Edit, full section of the code:

 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

Hey @Lamak, sorry having issues with m y network and stackoverflow. The entire sql is inside sql, and is being executed by sp_executesql — Arun, Apr 28 '15 at 14:52
and what is the problem you are getting?, is it an error?, if so, please post the error message — Lamak, Apr 28 '15 at 15:07
@Lamak, unfortunately no useful error message. In that scenario, it goes through the sql, and returns a couple hundred server names, does't do anything in the openquery part. It works with when you individually use a servername. If you try to modify the bracketed identifier. You get a generic syntax error starting with `'['` or something similar. — Arun, Apr 28 '15 at 15:16
As an aside, use [QUOTENAME](https://msdn.microsoft.com/en-us/library/ms176114.aspx) instead of adding the rectangular brackets yourself. So instead of `'['+@name+']'`, write `QUOTENAME(@name)` — TT., Apr 29 '15 at 09:03
Other than that I think the answer of Stan Bruce is on point: you need to use the name of a linked server as first parameter in OPENQUERY, not the name of a SQL Server instance. — TT., Apr 29 '15 at 09:08
@TT, thanks for the suggestion, I tried `QUOTENAME(@name)`, but now I get this generic error which isn't true `Msg 137, Level 15, State 2, Line 2 Must declare the scalar variable "@name".` Also the linked servername, includes the instance name unless it's default. — Arun, Apr 29 '15 at 13:25
Make sure the `QUOTENAME(@name)` appears outside of the VARCHAR-expression like `' ... OPENQUERY(' + QUOTENAME(@name) + ', ...'`. — TT., Apr 29 '15 at 13:46
@TT Not sure, what you mean, but I think it does. `* FROM OPENQUERY(QUOTENAME(@name), ''` The `+` was only used for concatenating the `[]` — Arun, Apr 29 '15 at 14:47
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/76550/discussion-between-tt-and-arun). — TT., Apr 29 '15 at 15:13

Stijn · Answer 1 · 2015-04-30T06:38:28.197

0

OPENQUERY can only be used with a valid linked server reference. As per MSDN:

OPENQUERY ( linked_server ,'query' )

linked_server

Is an identifier representing the name of the linked server.

So you can't use servername\instance as the first parameter. You'll have to create a linked server first, and then use the linked server name as the first parameter.

An example using dynamic 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;

edited Apr 30 '15 at 06:38

answered Apr 29 '15 at 08:51

Stijn

1,970
3
27
36

Thanks for the suggestion @Stan Bruce. However all the linked serves do exist, and it is in the format of servername\instancename, unless only default instances exists, then it is just servername. – Arun Apr 29 '15 at 13:32
@Arun Are you sure you have actually created a `Linked Server`? It is not just a regular server, it is a distinct object that needs to be defined in SQL Server. For instance, have you created this linked server as it is done in the following link: [Create Linked Servers (SQL Server)](https://msdn.microsoft.com/en-us/library/ff772782.aspx). – TT. Apr 29 '15 at 13:51
@TT. Yes, I have hundreds of linked servers in this list. If you look at the example you listed they have the instance listed as well. `N'SRVR002\ACCTG` – Arun Apr 29 '15 at 14:45
@Arun ok, just double-checking :). Is it possible you have to omit the instance name? This is just a wild guess though... – TT. Apr 29 '15 at 14:48
@TT, no, I'm sure it's just the [] in the openquery. It works without the variable. For example, if I manually type in `[server\instance]` it works, only errors when the @variable is present. – Arun Apr 29 '15 at 14:50
Edited my answer to include dynamic SQL example. – Stijn Apr 30 '15 at 07:05
@StanBruce Please read my original post. You just posted the same thing. – Arun Apr 30 '15 at 13:57

Issue with Bracketed Identifier inside SQL Server OPENQUERY

1 Answers1