XML Query treating alias as DB

1

Running this:

INSERT INTO [dbo].[RichTest] ([Name])
SELECT doc.col.value('(.)[1]', 'nvarchar(50)')
FROM @RichTests.nodes('/macs/mac') AS doc(col)
WHERE NOT EXISTS (SELECT 1 FROM [RichTest] WHERE [Name] = doc.col.value('(.)[1]', 'nvarchar(50)')

Getting this:

System.Data.SqlClient.SqlException: Database 'Doc' cannot be opened because it is offline.

Notes:

When this is run in SSMS, it fails with DB 'Doc' error outlined above
Select @@version gives
Microsoft SQL Server 2008 (SP3) - 10.0.5538.0 (X64)

2

Please post the rest of your code:

declaration and setting of @RichTests

Definition of dbo.RichTest table

3

It is a passed in xml variable:

@RichTest xml=null

4

Additional info:

This is failing just on the creation of the Stored Procedure and works in all other implementations for SS (various versions from 2005 through 2014)

5

Can't reproduce the error. I used this:

DECLARE @FOO TABLE (BAR INT)

declare @RichTest xml=null
SELECT doc.col.value('(.)[1]', 'nvarchar(50)') 
FROM @RichTest.nodes('/macs/mac') AS doc(col) 
WHERE NOT EXISTS (SELECT 1 FROM @FOO WHERE BAR = doc.col.value('(.)[1]', 'nvarchar(50)'

which produced no error (also no results, but that's no surprise!)

FWIW

Select @@version

yields

Microsoft SQL Server 2008 (SP4) - 10.0.6241.0 (X64)
Apr 17 2015 10:56:08
Copyright (c) 1988-2008 Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.0 (Build 6002: Service Pack 2)

for me

6

yeah, that's the issue. It's not an actual execution error - the proc actually exists in the target system, however, if you pull it up under right-click - modify and try to run the Alter Proc code in SSMS, this error comes up, even though the code already exists in the DB on the instance. I suspect a Collation issue, but waiting to hear back on the settings at both the DB and instance level.

7

Just did that (wrapped my sample code in a CREATE PROC, then tried to ALTER it)

no problem. There must be a syntax error with your mod.

8

While I see what you are saying, this modification is part of an upgrade that has rolled out to many environments, without issue.

9

Have you verified that on this one system - there isn't a database named 'Doc' or a synonym for a schema named 'doc' that would be redirecting to another database?

10

Checking on that and will let you know when I hear back -thanks.