Cross-Server Joins
I have heard tell that it is possible to perform cross-database and even cross-server joins in MSSQL but I can't find any good info on how it is done. Can someone point me in the right direction, specifically for the cross server stuff? -- Jay ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:252179 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Cross-Server Joins
I have heard tell that it is possible to perform cross-database and even cross-server joins in MSSQL but I can't find any good info on how it is done. Can someone point me in the right direction, specifically for the cross server stuff? Before you reply, my mistake. By cross-server I should have said cross-platform. I am trying to link a MySQL database into MSSQL. I have found several references online but none of them work so I was wondering if any of you had experience. -- Jay ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:252182 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Cross-Server Joins
Hi Jay You need to have a look at linked servers, this article has a heap of stuff in it. http://www.databasejournal.com/features/mssql/article.php/3085211 Im sure there is a simpler route by prefixing the table with the db.owner.table or something like that, Neil Ravo Robertson was the chap I saw doing it a while back I will give him a shout and ask what he was doing. HTH Jose Diaz On 9/6/06, James Smith [EMAIL PROTECTED] wrote: I have heard tell that it is possible to perform cross-database and even cross-server joins in MSSQL but I can't find any good info on how it is done. Can someone point me in the right direction, specifically for the cross server stuff? -- Jay ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:252183 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Cross-Server Joins
Ah okay, Linked servers I would say would be the way. Jose On 9/6/06, James Smith [EMAIL PROTECTED] wrote: I have heard tell that it is possible to perform cross-database and even cross-server joins in MSSQL but I can't find any good info on how it is done. Can someone point me in the right direction, specifically for the cross server stuff? Before you reply, my mistake. By cross-server I should have said cross-platform. I am trying to link a MySQL database into MSSQL. I have found several references online but none of them work so I was wondering if any of you had experience. -- Jay ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:252184 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Cross-Server Joins
However James Ive never actually done what your requesting myself, if it works I'd love to know heh ;) On 9/6/06, Jose Diaz [EMAIL PROTECTED] wrote: Ah okay, Linked servers I would say would be the way. Jose On 9/6/06, James Smith [EMAIL PROTECTED] wrote: I have heard tell that it is possible to perform cross-database and even cross-server joins in MSSQL but I can't find any good info on how it is done. Can someone point me in the right direction, specifically for the cross server stuff? Before you reply, my mistake. By cross-server I should have said cross-platform. I am trying to link a MySQL database into MSSQL. I have found several references online but none of them work so I was wondering if any of you had experience. -- Jay ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:252185 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Cross-Server Joins
Indeed, Linked Servers is what you want to look for - not sure about the performance though ;-) -Original Message- From: Jose Diaz [mailto:[EMAIL PROTECTED] Sent: 06 September 2006 11:22 To: CF-Talk Subject: Re: Cross-Server Joins Ah okay, Linked servers I would say would be the way. Jose On 9/6/06, James Smith [EMAIL PROTECTED] wrote: I have heard tell that it is possible to perform cross-database and even cross-server joins in MSSQL but I can't find any good info on how it is done. Can someone point me in the right direction, specifically for the cross server stuff? Before you reply, my mistake. By cross-server I should have said cross-platform. I am trying to link a MySQL database into MSSQL. I have found several references online but none of them work so I was wondering if any of you had experience. -- Jay ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:252186 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Cross-Server Joins
There is a good walkthrough at http://developer.infi.nl/index.php?ID=6article=6 but after creating the linked server it fails when I try to access it. Neither server craches the enterprise manager just sits there doing nothing, no processors get 100% used, no memory gets hogged, it just sits there with an hourglass for as long as you are prepared to wait. Ah okay, Linked servers I would say would be the way. I have heard tell that it is possible to perform cross-database and even cross-server joins in MSSQL but I can't find any good info on how it is done. Can someone point me in the right direction, specifically for the cross server stuff? Before you reply, my mistake. By cross-server I should have said cross-platform. I am trying to link a MySQL database into MSSQL. I have found several references online but none of them work so I was wondering if any of you had experience. ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:252187 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Cross-Server Joins
Check out the OPENROWSET function in T-SQL:
OPENROWSET
Includes all connection information necessary to access remote data from
an OLE DB data source. This method is an alternative to accessing tables
in a linked server and is a one-time, ad hoc method of connecting and
accessing remote data using OLE DB. The OPENROWSET function can be
referenced in the FROM clause of a query as though it is a table name.
The OPENROWSET function can also be referenced as the target table of an
INSERT, UPDATE, or DELETE statement, subject to the capabilities of the
OLE DB provider. Although the query may return multiple result sets,
OPENROWSET returns only the first one.
OPENROWSET ( 'provider_name'
, { 'datasource' ; 'user_id' ; 'password'
| 'provider_string' }
, { [ catalog. ] [ schema. ] object
| 'query' }
)
Mark
-Original Message-
From: James Smith [mailto:[EMAIL PROTECTED]
Sent: Wednesday, September 06, 2006 6:17 AM
To: CF-Talk
Subject: RE: Cross-Server Joins
I have heard tell that it is possible to perform cross-database and
even cross-server joins in MSSQL but I can't find any good info on how
it is done. Can someone point me in the right direction, specifically
for the cross server stuff?
Before you reply, my mistake. By cross-server I should have said
cross-platform. I am trying to link a MySQL database into MSSQL.
I have found several references online but none of them work so I was
wondering if any of you had experience.
--
Jay
~|
Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting,
up-to-date ColdFusion information by your peers, delivered to your door four
times a year.
http://www.fusionauthority.com/quarterly
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:252202
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Cross-Server Joins
Using mySQL it's possible (as long as the user you're logged in as has permissions on both tables. Simply prepend the tablename with the database name like so: SELECT d1t1.id, d2t2.id FROM database1.table1 AS d1t1 INNER JOIN database2.table2 AS d2t2 ON d1t1.id = d2.t2.id !//-- andy matthews web developer certified advanced coldfusion programmer ICGLink, Inc. [EMAIL PROTECTED] 615.370.1530 x737 --//- -Original Message- From: James Smith [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 06, 2006 3:59 AM To: CF-Talk Subject: Cross-Server Joins I have heard tell that it is possible to perform cross-database and even cross-server joins in MSSQL but I can't find any good info on how it is done. Can someone point me in the right direction, specifically for the cross server stuff? -- Jay ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:252206 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Cross-Server Joins
I think he sorted it, that will work for the same DB instance/type but for X-Server joins you will need to use Linked Servers. -Original Message- From: Andy Matthews [mailto:[EMAIL PROTECTED] Sent: 06 September 2006 14:19 To: CF-Talk Subject: RE: Cross-Server Joins Using mySQL it's possible (as long as the user you're logged in as has permissions on both tables. Simply prepend the tablename with the database name like so: SELECT d1t1.id, d2t2.id FROM database1.table1 AS d1t1 INNER JOIN database2.table2 AS d2t2 ON d1t1.id = d2.t2.id !//-- andy matthews web developer certified advanced coldfusion programmer ICGLink, Inc. [EMAIL PROTECTED] 615.370.1530 x737 --//- -Original Message- From: James Smith [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 06, 2006 3:59 AM To: CF-Talk Subject: Cross-Server Joins I have heard tell that it is possible to perform cross-database and even cross-server joins in MSSQL but I can't find any good info on how it is done. Can someone point me in the right direction, specifically for the cross server stuff? -- Jay ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:252209 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Cross-Server Joins
Yah... Saw that. Never would have thought you could do a join between two diff databases. I guess you'd have to use ANSI standard functions eh? !//-- andy matthews web developer certified advanced coldfusion programmer ICGLink, Inc. [EMAIL PROTECTED] 615.370.1530 x737 --//- -Original Message- From: Robertson-Ravo, Neil (RX) [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 06, 2006 8:57 AM To: CF-Talk Subject: RE: Cross-Server Joins I think he sorted it, that will work for the same DB instance/type but for X-Server joins you will need to use Linked Servers. ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:252210 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Cross-Server Joins
However James Ive never actually done what your requesting myself, if it works I'd love to know heh ;) I have used cross server joins before on MSSQL 2000 servers and the performance was so bad I had to take it out. I eventually had to call a proc on the second server to gather data, and then pass the result set back to first server which I dumped into a temp table and THEN joined my stuff to it. It was a pain, but it performed a few thousand times better. I really think we had some configuration awry somewhere though... ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:252211 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Cross-Server Joins
OK, sorted. I used the previously mentioned tequnique but changed the
connection string to...
DRIVER={MySQL ODBC 3.51
Driver};SERVER=localhost;DATABASE=intranet;UID=;PWD=;OPTION=3
And used the MyODBC 3.51.12 driver, 3.51.11 or earlier didn't work (I was on
11).
I now have CF joining tables from a MSSQL database with those from a MySQL
database just fine, the performance is sucky but for this it doesn't matter
as it is being upgraded to MSSQL shortly anyway, just not shortly enough for
this project ;-)
--
Jay
~|
Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting,
up-to-date ColdFusion information by your peers, delivered to your door four
times a year.
http://www.fusionauthority.com/quarterly
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:252216
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
