Sql-server – Connecting to an SQL server instance without using square brackets to avoid backslash

This is a known issue. Linked servers which are looking for database mirroring logins only work for Windows logins not SQL Logins as talked about on this connect item. Basically you need to either hard code a username and password in the linked server, which isn't recommended or use Windows authentication to access the linked server.

Another option would be to setup alerts and SQL Agent jobs on the two machines so that when the database becomes active on those machines they automatically reconfigure the linked server with the correct servername.

I'm not sure if it answers your question, but I had a similar problem. The resulution was limiting SQL Server to execute the query using one processor only.

If you have the same problem, this should work:

DECLARE @RequestXML xml = '<Foo>
    <Node1>Hello</Node1>
    <Node2>World</Node2>
    <Node3>Today</Node3>
</Foo>'

SELECT
    @RequestXML.query('/Foo/Node1[1]')
    ,@RequestXML.query('/Foo/Node2[1]')
    ,@RequestXML.query('/Foo/Node3[1]') 
OPTION(MAXDOP 1)

The thing is that SQL Server Query Optimizer decides that the query is costly enough to split its execution and merge those results. And sometimes this creates problems, so lowering degree of parallelism (MAXDOP) can solve it.

EDIT

Looking at the execution plan for this query, I find it hard to believe that somewhere in the middle of execution of each branch for @RequestXML.query('...'), estimated number of rows was 180,000. However, when I told it that I promise I will need only the first Foo, he reduced it to 900. So, try this...

DECLARE @RequestXML xml = '<Foo>
    <Node1>Hello</Node1>
    <Node2>World</Node2>
    <Node3>Today</Node3>
    <Node4>Tomorrow</Node4>
</Foo>'

SELECT
    @RequestXML.query('/Foo[1]/Node1[1]')
    ,@RequestXML.query('/Foo[1]/Node2[1]')
    ,@RequestXML.query('/Foo[1]/Node3[1]')
    ,@RequestXML.query('/Foo[1]/Node4[1]')