CODEWITHSUNDEEP
sqldatabasecross-database
Chris Harrington
Chris Harrington

Reputation: 1652

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

Upvotes: 1

Views: 314

Answers (3)

cjk
cjk

Reputation: 46425

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

Upvotes: 1

Eric Petroelje
Eric Petroelje

Reputation: 60498

Try enclosing the database name with brackets:

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

Upvotes: 1

MartW
MartW

Reputation: 12538

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

Upvotes: 2

Related Questions