Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server DTS
|
SQL Server DTS Discussion specific to Data Transformation Service with SQL Server. General SQL Server discussions should use the general SQL Server forum. Readers of the book Professional SQL Server 2000 DTS with questions specific to that book should post in that book forum.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server DTS section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old September 16th, 2004, 07:30 AM
Authorized User
 
Join Date: Sep 2004
Posts: 67
Thanks: 1
Thanked 0 Times in 0 Posts
Default Re: Remote and Local table Synchronization



Hello!
I am hoping that someone may be able to assist me with information on how to synchronize a table on my local server with a table on a remote SQL server.

Background Information:
I have a SQL server database hosted by a website hosting company which is my remote server. This database records information from my website and I need to get this information from the remote database table to a database table running on my local SQL server.

I have set up access to the remote server through the 'Client Network Utility' on my local server, and I have added this server as a 'linked server' through Enterprise Manager.

Currently, I am using a DTS package that executes every few minutes and drops the local table, re-creates it and populates it with a SELECT * FROM the remote server statement.

I don't like having to drop the local table and re-creating it every time as I really feel that this isn't the best way to be going about it. Plus I then have to copy ALL of the data across from the remote table to the local table each time.

I was wondering if there is some way to simply compare the remote and local tables and then update the local table with only the differences in the remote table.

Any help would be much appreciated.

Thanks :)
 
Old September 16th, 2004, 11:48 AM
Friend of Wrox
 
Join Date: Nov 2003
Posts: 625
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to jemacc
Default

You may want to lookup information on replication on Books Online
Replication Options will give you a good reference on which type of replication will best suit your needs.
 
Old September 16th, 2004, 08:55 PM
Authorized User
 
Join Date: Sep 2004
Posts: 67
Thanks: 1
Thanked 0 Times in 0 Posts
Default

Quote:
quote:Originally posted by jemacc
 You may want to lookup information on replication on Books Online
Replication Options will give you a good reference on which type of replication will best suit your needs.

Hello,
Unfortunately I am unable to use Replication because the web hosting provider that runs the Remote server doesn't allow it. They informed me that I have to use DTS.
A real bummer because Replication is how I had originally planned to do it hence why I am now in a frenzy trying to figure out a similar solution using DTS.
 
Old September 16th, 2004, 09:17 PM
Friend of Wrox
 
Join Date: Nov 2003
Posts: 625
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to jemacc
Default

this is from a another forum

Based on the discussion at:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=23038

and BSmith's idea (no relation), I came up with this silly stored proc to compare any two tables or views. Supply the table names and the columns you wish to compare and have fun!

The key to the GROUP BY approach is that it handles NULLS quite well which JOINS have trouble with.


CREATE PROCEDURE CompareTables(@table1 varchar(100), @table2 Varchar(100), @T1ColumnList varchar(1000), @T2ColumnList varchar(1000) = '')AS-- Table1, Table2 are the tables or views to compare.-- T1ColumnList is the list of columns to compare, from table1.-- Just list them comma-separated, like in a GROUP BY clause.-- If T2ColumnList is not specified, it is assumed to be the same-- as T1ColumnList. Otherwise, list the columns of Table2 in-- the same order as the columns in table1 that you wish to compare.---- The result is all records from either table that do NOT match-- the other table, along with which table the record is from.declare @SQL varchar(8000);IF @t2ColumnList = '' SET @T2ColumnList = @T1ColumnListset @SQL = 'SELECT ''' + @table1 + ''' AS TableName, ' + @t1ColumnList + ' FROM ' + @Table1 + ' UNION ALL SELECT ''' + @table2 + ''' As TableName, ' + @t2ColumnList + ' FROM ' + @Table2set @SQL = 'SELECT Max(TableName) as TableName, ' + @t1ColumnList + ' FROM (' + @SQL + ') A GROUP BY ' + @t1ColumnList + ' HAVING COUNT(*) = 1'exec ( @SQL)



If this does not work for you
Use exists or not exist to compare which rows are in one table
I would create a view to get the results that are or are not the the table you want to update

create view compare
as
SELECT DISTINCT column
FROM table1
WHERE NOT EXISTS
(SELECT *
FROM table2
WHERE table1.column = table2.column)

then insert the results to the table you wnat to update





Similar Threads
Thread Thread Starter Forum Replies Last Post
Sending E-Mail from Local to Remote Server tact_259 Classic ASP Basics 6 April 13th, 2008 01:46 AM
Browser transform using local XSL and remote XML sosarder XSLT 1 March 13th, 2007 02:58 PM
A Better way to sync a remote and local database? kevorkian SQL Server 2000 1 April 5th, 2005 08:09 AM
Synchronize a remote and local database - MAYBE??? kevorkian SQL Server 2000 3 March 8th, 2005 02:00 PM
stored proc working local and not remote jazzlover2002 SQL Server 2000 3 October 25th, 2003 02:02 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.