>>>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