Problem with Count Statement for Multiple Databases

Question

Sorry everyone. It appears that I am a dumbass! The query works fine when I run it against the live database versus my test database...gotta pay more attention to that stuff!

I am having a problem with the statement shown below. It works fine if I run it against one database (sans the DBCATALOG.dbo), so no problem there. When I try running the statement as is, I get an incorrect syntax near ‘<’. I also tried using a tool to create a union for all of our databases (see second statement). This results in an invalid object name error (Invalid object name 'TF7-User-Demo-ScheduledRestore-03.dbo.LinkedDocumentFolderTable', Invalid object name 'TF7-TestDatabase-ScheduledRestore.dbo.LinkedDocumentFolderTable', etc.). It seems to be tied to the .[dbo], but I just don’t know what causes the problem. I can’t find anything in the book stating a count can’t be performed across multiple databases….

SELECT CNT=COUNT(*)
FROM <DBCATALOG>.[dbo].[LinkedDocumentFolderTable]
WHERE IsUnmanagedFolder = 1


SELECT 'TF7-User-Demo-ScheduledRestore-03' AS DBCatalog, * FROM(SELECT COUNT(*)
FROM [TF7-User-Demo-ScheduledRestore-03].[dbo].[LinkedDocumentFolderTable]
WHERE IsUnmanagedFolder = 1)rsOne

UNION ALL

SELECT 'TF7-TestDatabase-ScheduledRestore' AS DBCatalog, * FROM(SELECT COUNT(*)
FROM [TF7-TestDatabase-ScheduledRestore].[dbo].[LinkedDocumentFolderTable]
WHERE IsUnmanagedFolder = 1)rsOne

Answer 1

I'm not totally sure what you're actually trying to do, but those angle (< >) and square ([ ]) brackets aren't valid to have in the queries.

Which database engine are you using?

Answer 2

Your syntax seems to be weird. Try next:

SELECT 'TF7-User-Demo-ScheduledRestore-03' AS DBCatalog, COUNT(*) 
FROM [TF7-User-Demo-ScheduledRestore-03].[dbo].[LinkedDocumentFolderTable] 
WHERE IsUnmanagedFolder = 1

Also do you have all neccessary permissions set? (I'd better not ask, do you have such databases like TF7-User-Demo-ScheduledRestore-03 on same server present at all?)

Answer 3

The quoted identifier for SQL Server is the brackets as you are using elsewhere ("[" and "]"), not the "<" and ">", so my guess is that's why you're getting the first error.

Are the databases actually named TF7-User-Demo-ScheduledRestore-03, etc.?

Also, when selecting from a subquery you need to make sure that all of the columns in the subquery are aliased if they don't already have a column name. For example:

(SELECT COUNT(*) AS my_count)