Joining tables across databases

[John_Leach]

Hi - I am new to R&R, having recently purchased SQL Edition v9.0.____I understand that Auto SQL will not allow table joins across databases and that a User SQL statement is required.____I have established 2 data source connections to SQL Server 2000.____My problem is that when I construct the User SQL, SQL Server 2000 rejects one of the connections.____The User SQL is in the format :-__select__alias1."field", alias2."field"__from__"dbo"."table1" alias1, "dbo"."table2" alias2____The error I receive is in the format ;__DB access: Invalid object name ^dbo.table2^____If table1 and table2 are from a single database, the above User SQL works fine, otherwise not.____Is my connection method incorrect ?__Will Auto SQL handle joins across databases in subsequent releases ?____Any help would be greatly appreciated.__

[Thomas_Ruschak_(Guest)]

>Hi - I am new to R&R, having recently purchased SQL Edition __>v9.0. __>__>I understand that Auto SQL will not allow table joins across __>databases and that a User SQL statement is required. __>__>I have established 2 data source connections to SQL Server __>2000. __>__>My problem is that when I construct the User SQL, SQL Server __>2000 rejects one of the connections. __>__>The User SQL is in the format :- __>select __>alias1."field", alias2."field" __>from __>"dbo"."table1" alias1, "dbo"."table2" alias2 __>__>The error I receive is in the format ; __>DB access: Invalid object name ^dbo.table2^ __>__>If table1 and table2 are from a single database, the above __>User SQL works fine, otherwise not. ____You need to prefix the table names with the name of the database as well as the owner.____Try this:____select alias1."field", alias2."field"__from __"database1"."dbo"."table1" alias1,__"database2"."dbo"."table2" alias2____I^ve done this and it^s worked for me in the same circumstances you^re working with (i.e. sql server 2000, multiple databases)____Sure would be nice if this could be done for you, though -- doesn^t seem like rocket science.____Tom

[John_Leach]

Tom____Many thanks for your posting.____I am still getting the same error but I am sure this is because my select statement syntax for the name and owner of the database is wrong.____Any chance you might post an example of the syntax you are using ?____Thanks once again for your help !!!

[John_Leach]

Tom,____Many thanks for the example.____I neglected to refer to the SQL 2000 server within my select statement.____It now reads :-____select alias1."field", alias2."field"__from __"server"."database1"."dbo"."table1" alias1,__"server"."database2"."dbo"."table2" alias2____Everything now works fine.____Your help was much appreciated.____John