CODEWITHSUNDEEP
sqlsql-servert-sql
PeterK
PeterK

Reputation: 111

SQL could not find server - though the query is searching the DBname as the server

I have this SQL query in SSMS where I am trying to look for the phrase ATLAS within the SUNAME of selected databases.

When I run this, I get a Could not find server 'DB1' in sys.servers. where DB1 is quoting the database name...so in affect it thinks the database name is the server I presume?

DECLARE @Sql NVARCHAR(MAX);
SET @Sql =
STUFF(
(SELECT
    NCHAR(10) + N'UNION ALL' + NCHAR(10) +
N'SELECT
    '+ QUOTENAME(d.name, '''') + N' AS dbName,
    SUNAME COLLATE Latin1_General_CI_AS
        FROM ' + QUOTENAME(d.name) + '.dbo.PL.ACCOUNTS
            WHERE SUNAME = ''ATLAS'''
FROM sys.databases d
where name like '%AccountsLive'
FOR XML PATH(''), TYPE)
.value('text()[1]','nvarchar(max)'), 1, 11, '');
exec( @Sql );

Running a print( @Sql) of this comes up with the below, which looks fine to me?

SELECT
    'DB1' AS dbName,
    SUNAME COLLATE Latin1_General_CI_AS
        FROM [DB1].dbo.PL.ACCOUNTS
            WHERE SUNAME = 'ATLAS'

Any help appreciated!

Upvotes: 1

Views: 61

Answers (1)

Martin Cairney
Martin Cairney

Reputation: 1767

You have too many periods in the name.

The structure is [server name].[database name].[schema name].[object name]

In this structure you have :

Object Name = 'ACCOUNTS'
Schema Name = 'PL'
Database Name = 'dbo'
Server Name = 'DB1'

I thik you need to also add [] around PL.ACCOUNTS

Upvotes: 1

Related Questions