Cross-database queries with numbered database name

Question

Hi there,

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

Answer 1

Try using [100].dbo.UserInfo instead of just the 100.

Answer 2

Try enclosing the database name with brackets:

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

Answer 3

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