[SOLVED] Cross-database queries with numbered database name

Cross-database queries with numbered database name

I'm a bit of a novice when it comes to SQL Server 2005. I have a database containing most of the stored procedures and tables (we'll call it 'GrandDatabase'). Each user has its own separate database named after the user's numbered ID. So I have a database list as follows, for example:

GrandDatabase
100
101
102
...

I need to join tables across the GrandDatabase and a user's database. I've read elsewhere that the following should work, when executed from GrandDatabase:

SELECT
    *
FROM
    GrandDatabase.User INNER JOIN
    100.dbo.UserInfo ON GrandDatabase.User.UserID = 100.dbo.UserInfo.UserID

This gives me a syntax error, complaining about the '.' right after the first reference to the 100 database. I did a little tweaking and discovered that this code works fine when I use non-numbered databases (for instance, replacing the '100' above with 'User100'). Does anybody know how to make this work with numbered database names?

Thanks!
Chris

Solution

Try putting the numbers into square brackets and using aliases, e.g.:

SELECT    *
FROM    GrandDatabase.User 
INNER JOIN    [100].dbo.UserInfo u
   ON GrandDatabase.User.UserID = u.UserID