Synchronizing 2 Tables
May 14, 2007
Hi
I am trying to synchronize two tables on different servers, the one with me with the one is australia.
I have a table called MeriesHBMem on both databases and the one with me gets updated everyday, and then i have to use a program called SQL Delta to synchronize all the new rows. How can i go about doing this using SSIS?
Any help would be greatly appreciated! I though about using a trigger for this, but i don't know how to go about connecting the two servers in the trigger's SQL statement, and besides i also think that a trigger will be a huge performance issue.
Data gets inserted into the table, all i want is a SSIS package to execute every 30min or so and take the new data and insert it into the other database's table
Kind Regards
Carel Greaves
View 1 Replies
ADVERTISEMENT
Sep 28, 2007
Hi,I have 2 tables (let's call them tableOld and tableNew) that I'm trying to synchronize. The tables may or may not be on the same server (I'm not sure - let's assume they're on different servers).Given below is the table structures:******************************************************************************* tableOld ------------- tableOld uses a View to access name information.View name = vwUserDetailsColumns in View = Firstname, Lastname, Fullname tableNew--------------- ProjectManagerID (PK, int, not null) <-- autogeneratedFName (varchar(50), null)LName (varchar(50), null)Inactive (int, null) *********************************************************************** Basically, I'm trying to write a script so that when a user runs the script that will do something like this: 1) it will add all the names in tableOld to tableNew (only if they don't already exist in tableNew)2) if there is a name in tableNew that does not exist in tableOld (because the name was deleted from tableOld), tableNew will set Inactive = 1 for that name. Any help is appreciated. Thanks.P.S. Is there a way the script can run automatically any time a user adds or deletes a name from tableOld?
View 10 Replies
View Related
Aug 17, 2006
Scenario regarding SQL 2000.
I have the same table on 2 different servers, Server R and Server L.
This table schema is:
ID int not null
Name varchar(50) not null
Password varchar(50) not null
IsUpdate int not null default (0)
People has an application that enables them to change their passwords in the table on Server L.
There is a for update trigger in the table on server L that sets IsUpdate column to 1 when any password has been modified.
The required scenario:
- Synchronizing passwords in the table on server R with passwords in the table on server L.
- Then setting the IsUpdate column in the table on server L to 0 again.
What is the best technology for that?
Please send me a code proposal.
Many Thanks and Best Regards,
Bishoy
View 4 Replies
View Related
Apr 22, 2008
I have several situations where I have values stored in multiple tables (yes, I know that isn't a good practice but it is my reality) and I need to make sure that they stay consistent.
The problem is, they can be (and frequently are) updated from both locations.
I was going to use an update trigger on each to update the other but I can see that causing potential deadlocks.
Surely I am not the first person with this problem but I haven€™t been able to find anything in the help files or on the forum.
Any ideas?
View 6 Replies
View Related
Sep 7, 2006
Hi, I have developed a software which is to run on multiple PCs on a network sharing the same database on an inhouse server. There is another database which has some similar tables that sites on a server where the website is. I need to have the common tables synchronized. How is this possible and what issues will I have in doing this?
Well there be a problem if the synchronization happens when someone is using our software which is making some changes to that table at the same time? Or if someone on the website is entering some information that updates the database?
I am using Visual Studio 2005 with C#. Access Database.
View 3 Replies
View Related
Mar 16, 2008
Is it possible to use SSIS to synchronize the data between a Foxpro .dbf and a compatible SQL Server table on a near realtime basis?
I have succesfully created an SSIS package that will insert data into the SQL Server Table but this is only useful for migrating data. What I need is a way to insure that the data in the SQL Server table matches that in the .dbf on a near realtime basis.
Or is there a way to link from SQL Server to the .dbf (similar to an Oracle DBLink).
Thanks for any and all assistance.....
Dave
View 9 Replies
View Related
May 18, 2008
what's the best way to synchronize to mssql databases
(one is 2005 express and one 2005)
View 11 Replies
View Related
Nov 22, 2007
what's the best way to synchronize 2 databases(mssql 2005) on 2 different servers.
View 3 Replies
View Related
Feb 25, 2008
Hi
I have two servers, on one i have SQL Server 2000 ( root ) and on the second SQL Server 2005. I need synchronize some data ( not the whole database ) on 2005 but i can't use Analysis Services . What i can do in this situation ?
Dest wishes
View 1 Replies
View Related
May 15, 2007
I have a SQL Server 2005 web database and a local SQL Server 2005 database. I want a tool that can synchronize both the database from the source (web) to the target (local).
View 3 Replies
View Related
Apr 4, 2005
Hi,
I have a problem that I have never considered before. I am developing with C#.Net, Visual Studio.Net ans SQL Server on Windows Server 2003.
Recently we have been upgrading our servers and have installed 2 servers (Live and Dev). The live server is Win Server 2003 Standard while the Dev server is 2003 enterprise.
Both servers are running their own versions of SQL Servers (both 2002). What I need to know, is there a way to copy or 'sync' the dev database to the live without over-writing the live data. I need to be able to copy the dev db structure (the tables anf fields) but not over-write the live data with the dev data (the live data changes rapidly and none can be lost). These 'syncs' would happen 2-3 times a month so the 2 db's, at the time of sync, will look very different so a simple copy of the live data to the dev db is not possible.
Does SQL Server have a wizard or utility? I have not been able to find one.
Thanks,
Justin
View 4 Replies
View Related
Feb 18, 2006
I'm writing a site in ASP.NET 2.0. I have business logic that contains two methods:ModifyUserInDB() // Modifies user data in a SQL 2005 tableandGetUserFromDB() // Gets user data from the SQL 2005 user tableIf the two methods above get executed at the same time by two different users (but on the same record in SQL 2005), how do I prevent the data from being corrupted? Does SQL do this automatically?If I make the methods static, and synchronize them, will it fix the issue? Are static objects the same instance for all users?
View 5 Replies
View Related
Apr 5, 2004
Hi,
Currently developping a c# database based application, which will be used in different establishments worldwide. I'm currently a bit confused by the ms sql features offered. Because the huge data amount which will be fetched during application use from the database, the best option is probably to put a ms sql server in each establishment to realise quick data fething. Problem is that the servers, which are all running on the same database design have to be synchronised real-time. I read about datareplication with the merge option which I thought was a nice solution. The subscribers can only have read-only rights on these subscriber databases, because the merging doesn't work appropriate i heard, so this is no good solution. Then I read about distributing transaction coordination. Seems a good solution as well, but what happens if a server system crashes for one day and then comes back online? It won't be up to date anymore... Right now I think about a combination of these 2 features. DTC in combination with merge replication, but there must be a better solution. Fact is that a lot of users will be editing data worldwide non stop, and everyone has to be up to date. Because of huge data fetching I don't think it's a good solution to let everyone work worldwide with one single database. Is there a possibility to realise this with ms sql server? Because I'm getting more and more confused... Thx in advance.
View 2 Replies
View Related
Sep 15, 2006
Hi,Is it possible to make changes to an Access 2000 database on my desktop, andhave it update those changes to SQL Server 2000 on the web?I have to update stock and prices every night. Right now I am using Access2000, and I just make the changes, and then upload the changed db to theserver, and overwrite the old db.But I am going to be using SQL Server 2000 soon, and I know you don't justoverwrite the db each time.There are about 6 fields that *might* change each night, per record.Is it called replication? How (basically) do I go about it? Are thereprograms out there that will make it easier?Thanks for any advice!J~
View 2 Replies
View Related
Jul 20, 2005
I'm writing a VB6 app which calls several stored procedures on my SQLServer DB.The first stored procedure must complete its inserts before the secondstored procedure can query the modified table for its results. Myproblem is thatthe second stored procedure occasionally returns a different resultset, acting as if the first stored procedure didn't complete (ordidn't run).So how do I ensure (or test/poll?) that the first stored procedureruns to completion before returning control back to my VB app?Thanks in advance,Ralph
View 9 Replies
View Related
Nov 20, 2006
Hello,
I've a DB that is mirrored.
I've actually only one user. I've replicated the user with the sp_help_revlogin Store Procedure to create the logins with the same SID on both SQL servers.
Now the application that is used with this DB will handle per user authentication.
Of course the application will use SQL login for the users connections.
Now, I can easily automate the sp_help_revlogin usage for the creation of new users, but I've another problem. The users can change their passwords through the application.
I'm wondering which process using for this:
Dropping the users on the Mirror server €“ running the sp_help_revlogin on the primary to re-extract the login information and applying the login creation against the mirror server
Checking the modify date of the login in the master DB and dropping this login on the mirror and re-creating it from the primary
Do you have an idea or an advice for this case?
Thank you
Eric
View 1 Replies
View Related
Oct 14, 2005
- Synchronizing Data (100%) (Error)
Messages
Failure to connect to SQL Server with provided connection information. SQL Server does not exist, access is denied because the SQL user is not a valid user on the SQL Server, or the password is incorrect.
View 3 Replies
View Related
Sep 18, 2006
hi..need help here,
I sometime get error when syncronizing data at New Subscription Wizard, and sometime I dont.but right now i cant sync, this is me error messages :
"Synchronizing Data (Error)
Messages
A request to send data to the computer running IIS has failed. For more information, see HRESULT.
HRESULT 0x80070002 (28037)
The operation could not be completed."
I've turn off the firewall, turn off the antivirus, plug the LAN cable but still error (Maybe it has to do with it).A long time a go I've had this error, and then I decided to reinstall SQL Server and try it again and it work, now it back to me again, do I have to reinstalled it again(hopefully it solved my problem)?or any otherway?
And want to ask about SQL Mobile, When I choose the Merge, the uniqueidentifier will be add at my table right?what should I do at inserting query process, I cant just insert it with NULL value right?
After my subscription doing input data, is it my publications will be automatic updated?
I'm little lost here..need advise
View 1 Replies
View Related
Jan 4, 2006
I tried to subscribe to the database that is published on my laptop running on windows XP professional SP2 and using SQL Server 2005. However, in the second step (synchronizing data) the following error message occured:
Synchronizing Data (Error)
Messages
A request to send data to the computer running IIS has failed. For more information, see HRESULT.
HRESULT 0x80072EE2 (28037)
The operation could not be completed.
Anyone can help me on this?
View 4 Replies
View Related
Jun 27, 2007
if a subscriber is offline (for a long time) and has changes, then connects to the server to synchronize but the subscription has expired, what happens to the subscriber's changes? do the changes get lost? do they get merged as normal then an entire new snapshot with the new changes sent back?
thanks,
bryan
View 1 Replies
View Related
Mar 4, 2008
Hi,
I have a potential client who wants to run three web sites at different locations, each storing data to SQL Server (let's say it will be SQL Server 2005, for the sake of argument). Periodically, but at least once a day, they would like all changes to be made in any server to be reflected in the other two.
As a develeoper, my initial approach would be to write web services that can transmit and recieve changes. Triggers on the database tables would copy data into "shadow" tables and then the web services would periodically wake up and send their changes to the other two servers.
However, before I go down this route, I wondered if any of the standard SQL Server tools (like SSIS) would handle this scenario (maybe with an agent FTPing files between the servers)?
Alternatively, does anyone know of any off-the-shelf partner solutions that can acheive the same result?
Many Thanks,
Chris Boothroyd
View 3 Replies
View Related
Apr 19, 2006
Im working on a project. This program will run off of a sql 2005 database. The company that we are selling this too uses sql 2000. They will end up using both. They will enter their data as usual, into the sql 2000, and then I need to get that data from 2000 to the 2005 database, anytime a transaction occurs, I need that transaction to also occur in the 2005 database. The 2000 DB has 4 tables. These 4 tables are split up into different ones in 2005, so I will need to map it just right. (This may sound dumb, but it is what they want so what are ya gonna do?) My question, is there an easy way for sql 2000 to communicate with 2005. I planned on making a trigger in 2000, that triggers a web service, that reads from 2000 and maps it into the right tables in 2005. Is this the best option or are their others Im not thinking of .... thanks.
View 4 Replies
View Related
Mar 25, 2004
Hi All,
I will have to setup two windows 2000 servers at separate locations. Each server will host the MS SQL 2000 database. The data on both database will need to be in sync in real time. Is it possible to do? If yes, any suggestions of how do do it?
Thanks
Khen
View 5 Replies
View Related
Mar 26, 2008
Can someone recommend a utility (preferably open-source) tosynchronize changes across servers? I need to bring only data over insome cases, and only objects in other cases. Any ideas?Thx!
View 5 Replies
View Related
Feb 18, 2007
Hi
We got a server running SQL server 2000 SP4 with a database that€™s being replicated
There are 17 subscribers running MSDE SP4 using merge replication. Replication is started manualy
Initially we tested this with two subscriptions an everything went well, but now, since 3 months, we are facing a weird problem while sync'ing. We have massive data loss on records that where inserted at the subscribers. Records seem to disappear, but only record that have a foreign key constraint. What I mean is that for example a record is inserted at the table that holds our client records with primary key €˜ClientID€™ and then a record is inserted in a table with actions for that client with a foreign key €˜ClientID€™ referring to the client table. After sync€™ing that client record is inserted correctly in database on the publisher but the records in the table with actions are gone.
As far as I know the tables are correctly formed with identity set not for replication and so on.
Shortly, I can€™t find any problem, a specially when it doesn€™t happen always.
If anyones has faced this and got a solution, please let me know.
Thanks.
Raf
View 3 Replies
View Related
Feb 26, 2007
I have several SSIS Packages which are called by few different applications. I need to make packages Mutually Exclusive, so that If one is running; calls to any other Package should be put on wait till the running one has finished its execution.
What are the best ways to do this ?
I would sincerely appreciate inputs in this.
View 6 Replies
View Related
Dec 13, 2006
Since the logins/jobs do not get stay in sync between principal and mirror after a failover (when using sql logins), is there a workaround to schedule a job or something to automate dropping, re-creating and autofixing logins?
I've read somewhere that an alert can be setup to run (after a failover) and a job can be executed, but cant seem to get this to work. Does anyone have any helpful hints on a solution to this
View 4 Replies
View Related
Apr 18, 2007
Hello
We're deploying the db schema changes via an T-SQL Script.
Now I've tried synchronize the changes to the (merge-)subscriber via replication.
I've tried something like that:
create table dbo.c(
c_id uniqueidentifier rowguidcol not null default newid(),
c_desc nvarchar(60) not null,
constraint PK_c primary key (c_id)
)
// table a already existed
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK1_c_a]') AND type = 'F')
ALTER TABLE a DROP CONSTRAINT [FK1_c_a]
go
exec sp_addmergearticle @publication = N'publicationname', @article = N'c', @source_owner = N'dbo', @source_object = N'c', @type = N'table', @description = null, @column_tracking = N'true', @pre_creation_cmd = N'drop', @creation_script = null, @schema_option = 0x000000000000CFF1, @article_resolver = null, @subset_filterclause = null, @vertical_partition = N'false', @destination_owner = N'dbo', @auto_identity_range = N'false', @verify_resolver_signature = 0, @allow_interactive_resolver = N'false', @fast_multicol_updateproc = N'true', @check_permissions = 0, @force_invalidate_snapshot = 1
go
alter table c
add constraint FK_a_c foreign key (a.col2)
references katalog_zivilstand (c)
not for replication
In the end, the update runs well on the publisher (Replication compabilitiy-Level =90, Replicate DDL =1). But on synch to the subscriber, the Foreign Key cannot be propagated "FK.. references invalid table... error number 1767)
I don't see why?
Aren't the statements executed in the same order as on the publisher - in my script?
Thanks much for your advice
Aline
View 3 Replies
View Related
Mar 2, 2015
We have a database in an AlwaysOn Availability Group that has gone into a state of Not Synchronizing / Suspect on the secondary.
The reason why this happened is because the secondary ran out of disk space so the log file wasn't able to be written to. The database was set to synchronous mode.
Is the only way to recover from this to do a re-initialization or is there another way to recover?
View 2 Replies
View Related
Oct 28, 2005
I had a merge subscription expire and now need to synchronize two disparate databases. How do I do so without overwriting the subscriber database with the publication snapshot? Thanks for any help you can give.
View 3 Replies
View Related
Aug 23, 2007
Hello folks,
I am trying to set up replication between a central office and multiple sites. This will be merge repl, since either central or satellites can be modified. However, one issue I ran into is setting up subscribers. I don't always have the option of starting with a publisher's snapshot; some of the subscribers have their own data already, which may or may not overlap with central site, and which needs to be synchronized into the central site somehow.
So far, I haven't seen any solution for this kind of scenario, at least nothing out of the box. Am I doomed to having to roll out custom data transfers (I don't think I can use DTS since all sites are running Express), or is there some standard way of doing this?
Thanks so much!
Sonya
View 6 Replies
View Related
Mar 19, 2007
HI ,
I want to synchronize sql 2005 CE edition with SQL 2000.before this i was using sql ce 2.0 and i manage to synchronize with sql 2000.the codes which i used for synch i am specifying below.
Private Const INTERNETURL As String = "http://ihsan/THPMIS/sscesa20.dll"
Private Const PUBLISHERSERVER As String = "IHSAN"
Private Const PUBLISHERDATABASE As String = "THPMIS"
Private Const PUBLISHERSECURITYMODE As SecurityType = SecurityType.NTAuthentication
Private Const PUBLICATION As String = "THPMIS"
Private SUBSCRIBER As String = Dns.GetHostName()
Private Const LOCALDATABASE As String = "My DocumentsTHPMIS.sdf"
'"Provider=microsoft.sqlserver.oledb.ce.2.0;data source=" &
Private Const CONNECTIONSTRING As String = "Provider=Microsoft.SQLSERVER." & _
"OLEDB.CE.2.0;Data Source=" & LOCALDATABASE
Private Const SQLSTRING As String = "Provider=sqloledb;Data Source=IHSAN;Initial Catalog=THPMIS;User ID=sa;Password=sa"
Private Sub ReplicateData()
Dim replicator As New SqlCeReplication
replicator.InternetUrl = INTERNETURL
replicator.Publisher = PUBLISHERSERVER
replicator.PublisherDatabase = PUBLISHERDATABASE
replicator.PublisherSecurityMode = PUBLISHERSECURITYMODE
replicator.Publication = PUBLICATION
replicator.Subscriber = SUBSCRIBER
replicator.SubscriberConnectionString = CONNECTIONSTRING
replicator.Synchronize()
replicator.Dispose()
End Sub
while i am using sql 2000 with sql CE 2005 it is giving an error unknown connection option in connectionstring:provider. can any one give me some suggession how to solve this problem
Best Regards
Ihsan
View 1 Replies
View Related
Mar 13, 2006
hi,
Problem: Synchronize the data between sqlce in pocket pc emulator and sql server 2000 in desktop computer.
Given: i have configured the IIS and while connecting the pocket pc IE to sql ce server agent..evreythings ok.
called the synchronize method.
ERROR: normally two error occurs:
native error 29045
and native error 29006
connecting with sql server reconciler failed..
any bidy can tell me what is sql server reconciler and why these error occurs...
moreover: if someone can tell me how the sql server 2000 interacts with IIS.
View 1 Replies
View Related
