join with table on another database not working

[Santi]

Hi,

I have two databases on the same server: COMU0002 and 2007DU
and two tables: tecnicos on COMU0002 and telf_cli ON 2007DU

Being on COMU0002 this query doesn't work:

SELECT * FROM tecnicos
LEFT JOIN 2007DU.dbo.telf_cli
ON ...

The error says: INCORRECT SYNTAX NEAR '2007'

Even this query being on 2007DU fails on management studio:
SELECT * FROM 2007DU.dbo.telf_cli

but SELECT * FROM telf_cli works ok

Any ideas?
Thank you!

[Erland Sommarskog]

Santi (sa***********@gmail.com) writes:

I have two databases on the same server: COMU0002 and 2007DU
and two tables: tecnicos on COMU0002 and telf_cli ON 2007DU

Being on COMU0002 this query doesn't work:

SELECT * FROM tecnicos
LEFT JOIN 2007DU.dbo.telf_cli
ON ...

The error says: INCORRECT SYNTAX NEAR '2007'

Even this query being on 2007DU fails on management studio:
SELECT * FROM 2007DU.dbo.telf_cli

but SELECT * FROM telf_cli works ok

2007DU does not adhere to the rules for regular identifiers, as they
must start with a letter, $ or a hash mark. An identifier cannot start
with a number.

However, in difference to most other languages, SQL permits you to use
about any string as an identifier, if you quote it. So instead say one of:

SELECT * FROM "2007DU".dbo.telf_cli
SELECT * FROM [2007DU].dbo.telf_cli

The former agrees with the ANSI standards, but the brackets is what
is most commonly used in the SQL Server world.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Santi]

It works! Thank you!