tables from 2 different database server

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

ASP.NET

tables from 2 different database server

Author

Topic

franches202
Starting Member

4 Posts

Posted - 2005-11-06 : 18:09:02

quote:
you wanted to join some columns coming from two different tables...right? if that so, use an SQL query. Try using the "inner join" statement.

i wanted to combine the data from two tables. these two tables belong to different servers: table1 is from sql server found in cerebrum station and table2 is from mysql server found in copernicus station. i have this code but the problem is i can only use this code if the two tables belong in the same database server.

quote:

string limitReghrsvalue = "select statuslog.ActId as STATUS_ID,DalsDataNew.ID as MANHOUR_ID,statuslog.ActDate as DATE,statuslog.ProjectCode as PROJECT_CODE,statuslog.MapNumber as MAP_NUMBER,statuslog.ActivityCode as ACTIVITY_CODE,DalsDataNew.ActivityMedium as MEDIUM_CODE,statuslog.RegHrs AS REGHOURS,statuslog.OTHrs AS OTHOURS,statuslog.Status AS STATUS,DalsDataNew.Flag,DalsDataNew.Approvedby from statuslog,DalsDataNew where statuslog.ProjectCode = DalsDataNew.ProjectCode and statuslog.PIN = DalsDataNew.PIN and statuslog.ActDate = DalsDataNew.Date and statuslog.ActivityCode = DalsDataNew.ActivityCode and statuslog.RegHrs = DalsDataNew.RegHours and statuslog.OTHrs = DalsDataNew.OTHours and statuslog.PIN = 'P120' and statuslog.ActDate >= '"+this.firstdate.Text+"' and statuslog.ActDate <= '"+this.lastdate.Text+"'";

Sql Server in Cerebrum: database dals

DalsDataNew

ID | Date | PIN | ProjectCode | ActivityCode | ActivityMedium | RegHrs | OTHrs | Approvedby | Flag

123 |9/17/2005| P120| 1234 | B | W(P) | 5.50 | 0.00 | P083 | 1

124 |9/17/2005| P120| 1234 | I | W(PC) | 1.50 | 2.25 | |

MySqlServer in Copernicus: database stat

Statuslog

ActID | ActDate | PIN | ProjectCode | MapNumber | ActivityCode | RegHrs | OTHrs | Status(%)

1 | 2005-9-17 | P120 | 1234 | map01 | B | 5.50 | 0.00 | 100

2 | 2005-9-17 | P120 | 1234 | map01 | I | 1.50 | 2.25 | 75

the output in datagrid should be:

ID | ActID | Date | ProjectCode | ActivityCode | MediumCode | MapNumber | RegHrs | OTHrs | Status | Approvedby | Flag

123| 1 |9/17/2005| 1234 | B | W(P) | map01 | 5.50 | 0.00 | 100 | P083 | 1

124| 2 |9/17/2005| 1234 | I | W(PC) | map01 | 1.50 | 2.25 | 75 | |

could someone help me how would i do this? : (

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-11-07 : 04:04:01

you need to add Copernicus server as linked server to Cerebrum
use sp_addlinkedserver
then use openquery to get data from it.
look in BOL = books Online = sql server help for correct syntax.

Go with the flow & have fun! Else fight the flow

franches202
Starting Member

4 Posts

Posted - 2005-11-14 : 00:31:35

quote:
Originally posted by spirit1

you need to add Copernicus server as linked server to Cerebrum
use sp_addlinkedserver
then use openquery to get data from it.
look in BOL = books Online = sql server help for correct syntax.

Go with the flow & have fun! Else fight the flow

i was told that linked server is not recommended since i am going to edit or update the data

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-11-14 : 04:51:47

well then how do you excpect to do joins to the 2 tables?

Go with the flow & have fun! Else fight the flow

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-14 : 05:01:39

The resultset obtained by joining tables is not editable