query from diffrent database server?

[kt03]

Hi,

I have couple table with join query and they are from the diffrent database server.  How can I do this? look at the example below.

<cfquery name="getall">

select *

    from tbl1 inner join tbl2

    on tbl1.saleID = tbl2.saleID

</cfquery>

tbl1 is from server1/Database1 and tbl2 is from server2/Database2

Thanks

[Reed_Powell-ttnmOb]

We need to know what kind of db server you're using.  The answer will invariably be that you need, in MS SQL Server terminology, a linked server.  However, my experience has been that this does not always work with CF, even though the query will work in SSMS, because the JDBC driver doesn't understand the 4-part syntax (servname.database.scheme.table.columname) and gives an error like "too many dots." In that case I have not found a workaround that makes it possible to do the JOIN inside of the query.  The solution ends up being that you need to create the linked servers, and then create an SP on one of them (the one that the CF datasource points to) to do the actual query, and then call that SP from your CF code.  That way it's SQL Server, and not JDBC, that handles the 4-part name.

hth,

reed

[kt03]

i am using sql 12 and we do have link server but i never done this before so don't know how to call?

thanks

[Dan_Bracuk]

If both databases are sql server then you can probably use this type of syntax.

from table1 join LINKEDSERVERNAME.OWNER.TABLE on something.

If the linked server is something else then you will be better off with a stored procedure that uses openquery.