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.
| Author |
Topic |
|
chadbryant5
Starting Member
32 Posts |
 Posted - 2007-06-07 : 22:32:16
|
| Hi,I'm a C# developer, not a DBA, but fairly familiar with sql server 2000 and learning 2005. I have a need to update a table on one sql server from another remote sql 2005 server. Both will be SQL 2005, but will not be on the same network, so there will be firewall issues to contend with. Security is not a huge concern as the data is public info and not personal secure information or anything like that. What I'll have is a table of data in one sql database. I will have a copy of that data on another sql server that is used at an ISP and drives a web site. I need the table at the ISP sql server to get updated from the SQL server that is onsite in our office on our private network. I would like to not have to get network admins to open sql server ports if possible, so I guess I'm wondering if there is an easy way with SQL 2005 to update data from one sql server to another in the environment that I have described that maybe could expose the data over a common port (ie port 80). I don't want to have to use integrated security as I'm not sure this would work between remote databases in this scenario. I don't know if the web service end point support in sql 2005 would allow this. The examples I have seen online show a sql endpoint created and then accessed from a .NET application. I can do that if I have to, but I would rather do all of this inside the database in some fashion. I am open to any idea that would work and if I need to, I can ditch the idea of using something over port 80 and can get the necessary ports open between the sql servers. This is a pretty simple scenario that we have with one table of data needing to be updated weekly on a web based sql server from a remote sql server.Any ideas on how you would approach this would be greatly appreciated. Security for the data I'm accessing is not a huge concern as the data is public domain as I've said, but I need to make sure the rest of the server and database on both ends are secured.Thanks for any help! |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-06-08 : 00:54:28
|
| You can try dump table to text file, ftp the file to isp, load it into staging table then update target table with it. |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-06-08 : 03:18:09
|
| The easiest way to achieve this is to make a Linked Server. Once you move away from that you have grief of one sort or another to contend with! So worth checking the network folk if they can get you some "visibility" between the servers.Next up is as rmiao says. Export to one file per table, transfer the file(s), import the data.Alternative is to use some sort of Application to make the transfer. Easiest might be to use XML for the transfer.Kristen |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-06-08 : 12:59:40
|
| Not sure if isp let you connect to their server via linked server. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-06-09 : 04:08:03
|
| Make the Linked Server on the Office PC .. ??If the ISP lets you use SQL Tools .locally, to connect to DB @ the ISP, then Linked Server would be able to reach ISP too, wouldn't it?Kristen |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-06-09 : 20:50:29
|
| But mose ISP don't let you do that as I know. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-06-10 : 02:14:06
|
| Yes, indeed. |
|
|
|
|
|
|
|
|
best way to move data