>>>>Hi,
>>>>
>>>>I have a situation where in the SQL Select, the JOIN should have a table (MyTable2) from another SQL Server database. Here is an example:
>>>>

>>>>local cJoinSqlServer
>>>>cJoinSqlServer = "SQL2Name.dbo."
>>>>cSqlSelect = "select * from mytable join " + cJoinSqlServer + "MyTable2 on MyTable.pk_field = MyTable2.pk_field"
>>>>

>>>>The above syntax works.
>>>>And then I tried to add the cJoinSqlServer to the ON part as follows:
>>>>

>>>>local cJoinSqlServer
>>>>cJoinSqlServer = "SQL2Name.dbo."
>>>>cSqlSelect = "select * from mytable join " + cJoinSqlServer + "MyTable2 on MyTable.pk_field = " + cJoinSqlServer + "MyTable2.pk_field"
>>>>

>>>>
>>>>The above works too; no error. But do I really need to add this prefix (of the second SQL Server DB) to the ON part?
>>>
>>>To be clear: "Server" is the incorrect description here. What you are joining are two tables of two databases on the same SQL Server instance. So better you would call it cJoinSqlDatabase.
>>>
>>>In your case I would write it like the following:
>>>
>>>

>>>cJoinSqlDatabase = "SQL2Name.dbo."
>>>
>>>TEXT TO cSqlSelect TEXTMERGE NOSHOW PRETEXT 15
>>>	Select * 
>>>		FROM myTable
>>>		Join <<cJoinSqlDatabase>>.MyTable2
>>>		on MyTable.pk_field = MyTable2.pk_field
>>>ENDTEXT
>>>

>>
>>You are correct that I used the wrong term. This case is of two databases on the same SQL Server.
>>
>>However, my actual case is a bit more complicated. There are two joins. Let me show you:
>>

>>cJoinSqlDatabase = "SQL2Name.dbo."
>>
>>TEXT TO cSqlSelect TEXTMERGE NOSHOW PRETEXT 15
>>	Select * 
>>		FROM myTable
>>		Join <<cJoinSqlDatabase>>.MyTable2
>>		on MyTable.pk_field = MyTable2.pk_field
>>              join <<cJoinSqlDatabase>>.MyTable3
>>              on MyTable2.pk_field = MyTable3.pk_field
>>ENDTEXT
>>

>>
>>The above worked but in once case, yesterday, wrong query was selected. What Naoto said about ambiguity made sense and I added the cJoinSqlDatabase to the second JOIN ON clause. So the final code became:
>>
>>

>>cJoinSqlDatabase = "SQL2Name.dbo."
>>
>>TEXT TO cSqlSelect TEXTMERGE NOSHOW PRETEXT 15
>>	Select * 
>>		FROM myTable
>>		Join <<cJoinSqlDatabase>>.MyTable2
>>		on MyTable.pk_field = MyTable2.pk_field
>>              join <<cJoinSqlDatabase>>.MyTable3
>>              on <<cJoinSqlDatabase>>MyTable2.pk_field = <<cJoinSqlDatabase>>MyTable3.pk_field
>>ENDTEXT
>>

>>
>>I think if I didn't add the cJoinSqlDatabase to the second JOIN, SQL Server could have using the MyTable2 from the first (default) SQL Database instead of the second one. By explicitly adding the cJoinSqlDatabase I am making sure that the correct database is use, in each case.
>>
>>Does it make sense?
>
>If you have ambiguity In this case I agree with Naoto to create alias names for the tables. However in your example you don't have ambiguity because all table names are different. The case of ambiguity would exist if you had "MyTable" in both database. In that case you need to use the example of Naoto to create alias names for the tables. Those aliasnames you would also need to use in the field list. It is best practice in queries to only select those fields that you actually need in the context where the result is being used, to avoid unnecessary traffic.
>
>

>cJoinSqlDatabase = "SQL2Name.dbo."
>
>TEXT TO cSqlSelect TEXTMERGE NOSHOW PRETEXT 15
>	Select myTable1.Field1,
>                  MyTable1.pk_field AS pkField1,
>                  MyTable2.Field2,
>                  MyTable2.pk_field AS pkField2
>		FROM MyTable MyTable1
>		Join <<cJoinSqlDatabase>>.MyTable MyTable2
>		on MyTable1.pk_field = MyTable2.pk_field
>ENDTEXT
>

In my case all tables, MyTable, MyTable1, MyTable2, and MyTable3 exist in both databases. It is just that some customers, instead of using the table in their "own" database, use it in another database. This is why I want to explicitly have the db name prefix for all tables that are not in the default database.