>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
Christian Isberner
Software Consultant