Skip to main content
K
kt03
Inspiring
December 6, 2012
Question

query from diffrent database server?

  • December 6, 2012
  • 1 reply
  • 772 views

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

This topic has been closed for replies.

1 reply

R
Reed_Powell-ttnmOb
Inspiring
December 6, 2012

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

K
kt03Author
Inspiring
December 6, 2012

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

D
Dan_Bracuk
Inspiring
December 6, 2012

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.

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices