>>>>>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?
>>>>
>>>>KISS and keep it clear IMHO, yours is not, whether you use the full dbname.schema.tablename or not:
>>>>
>>>>cJoinSqlDatabase = "SQL2Name.dbo."
>>>>

>>>>TEXT TO cSqlSelect TEXTMERGE NOSHOW PRETEXT 15
>>>>	Select t1.fieldName1, ..., t1.fieldNameN, t2.fieldName1, ..., t2.fieldNameN, t3.fieldName1, ..., t3.fieldNameN  
>>>>		FROM myTable t1
>>>>		Join << m.cJoinSqlDatabase >>.MyTable2 t2 on t1.pk_field = t2.pk_field
>>>>              join << m.cJoinSqlDatabase >>.MyTable3 t3 on t2.pk_field = t3.pk_field
>>>>ENDTEXT
>>>>

>>>>PS: Do not forget to rename fields which have the same name (ie: pk_field).
>>>
>>>I just looked into changing my SQL Select to use aliases and realized why I didn't use them before.
>>>My SQL Select has all selected fields with table name aliases. And I would have to change all these aliases with T1 or T2. And I am concerned that I will not change the alias in one place and the code will break.
>>
>>So are you saying you are instead going to take the longer path and alias them all with their databases as well? Like:
>>< m.cJoiningDatabase >..myTable3.myField??? I thought t3.myfield would be easier.
>
>I don't know what is easier. My main goals are 1) not to break anything that works 2) maintain stability.
>And I will think again about this case if I would want to change it as you suggest. I just want to take time to think and consider everything.

One thing keeping me sane is following naming conventions - I reserve "T_" for all cursors, "S_" for SQL alias. I strive to name the SQL alias meaningful, but often fall prey to the drive to save keystrokes like Cetin. It also relieves any worries of keeping record pointers of the tables involved in SQL -I KNOW that SQL tables are opened again, but using their table names is always less assuring than having defined SQL alias. I distrust software at a very cellular level, but that is one of my hang-ups

;-)