You can join different tables that are both in the same db in a query (through a union or join). You can't, through a query, join tables from two entirely different databases. That will require some back-end DBA re-architecting. I'm not familiar with ms-sql (I do oracle) but somebody mentioned that "SQL Server Linked" stuff. That's interesting. I'll have to see if that's possible with oracle.
brad -----Original Message----- From: Chad Gray [mailto:cg...@careyweb.com] Sent: Friday, January 16, 2009 9:18 AM To: cf-talk Subject: RE: join two databases I can see this working when you have two databases on the same server. I have two databases on completely different servers. Can you preface the database name with the server name/IP? Server1.database1.dbo.table1? I would think you would need login credentials stored somewhere to do this. Or can you preface the database name with the dns name in CF Admin? Dsn1.database1.dbo.table1? > -----Original Message----- > From: Duane Boudreau [mailto:du...@sandybaynetworks.com] > Sent: Friday, January 16, 2009 9:09 AM > To: cf-talk > Subject: RE: join two databases > > You can reference the database by name in the query > Ex: > > select t1.Field1, t2.Field2, t2.Field3 > from database1.dbo.Table1 t1 > inner join database2.dbo.Table2 t2 on t1.Field = t2.Field > > you might need to put that into a stored procedure or use a dsnless > connection > > > > -----Original Message----- > From: Chad Gray [mailto:cg...@careyweb.com] > Sent: Friday, January 16, 2009 10: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:318069 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
