Ah.. so there is a way in MS SQL to link two servers together. Then I would just add one DSN in CF Admin. How do you write your SQL in CFQuery?
SELECT * FROM server1.database1.table1 AS a JOIN server2.database2.table2 AS b on b.index = a.index Thanks, I will go read up on linking two servers. Chad > -----Original Message----- > From: Dawson, Michael [mailto:m...@evansville.edu] > Sent: Friday, January 16, 2009 9:13 AM > To: cf-talk > Subject: RE: join two databases > > You need to link your SQL Server instances. Google "SQL Server Linked". > > You can link the servers both directions, if needed. > > With the server linked in SQL, you can create cross-server queries in > the Query Analyzer/New Query editor. > > mike > > -----Original Message----- > From: Chad Gray [mailto:cg...@careyweb.com] > Sent: Friday, January 16, 2009 8:05 AM > To: cf-talk > Subject: join two databases > > What is the best way to join tables across two databases servers? > > Say I have one MS SQL server that has a database that stores ERP product > data. I have our website MS SQL server that stores extra data about the > product records in the ERP database. > > Is it possible to hook each datasource up in CF Admin and use both DSN's > in the same CFQuery? > > Or should you do something on the MS SQL servers to join them then hook > up one datasource in CF Admin? > > Chad > > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:318067 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4