Hi!
I'm about to setup a merge replication Sql server 2005 and Sql server CE on pda's. There are 10 service guys in the field which need to sync their data with the server.
I don't want each guy to have all the data from the db. What I found is that I can use SUSER_NAME() to filter data. However I haven't created unique db users on the server. I only have EmployeedId to identify the users. My question is, must I redesign my office application which feeds the sql server to suit SUSER_NAME() or do I have to create a publication for each user in the field??
I have database on SQL Server 2000 set up with a merge publication.This publication is configured with a number of dynamic filters toreduce the amount of data sent to each client. Each client has ananonymous pull subscription. The merge process can be triggered by thewindows sync manager and my application.To improve performance I have created some helper tables to hold themapping between user login and primary keys of selected entities.For the replicated data to be correct the contents of the helper tablesneeds to be up to date.I need to fire off a stored procedure on the publisher beforereplication starts to verify that this data is up to date. I can notsee any documented way of doing this however I have been experimentingwith some unorthodox systems.Firstly has anyone any ideas?I have been considering adding a trigger to some of the tables used bythe Microsoft replication code - yes I know this is very nasty.My problems arise because executing this stored procedure will causesome data to be updated. In updating data we could create a newgeneration in the database. I must therefore run my stored procedurebefore any the Microsoft code makes any generation checks / updates.Anyone done anything similar, Anyone have any better ideas?Any comments would be gratefully received.
I'm using merge replication to maintain a backup copy of my main (publisher)MSDE database. A push subscription periodically (1 per minute) updates the backup DB. It's intended that if the main db goes down then the backup (subscription) db can be configured as a publisher. This must all be performed via scripting. The initial configuration of the main publisher and subscription is controlled via scripting, which works fine. The problems occur when I try to configure the subsciber to become a publisher. A script is executed on the subscriber but fails at the point when it's configuring the publisher detail. The error is something like "unable to configure a publication for a database setup as an anonymous subscription". I'm guessing that there are subscritpion artifacts added to the database which need to be removed before it can be configured as a new publisher.
We just upgraded 2 sql servers from sql server 2000 sp3a to sql server 2005 build 2153, and merge rep exists between these 2 servers.
However, after sql server upgrade, we had to reinitialize merge replication and now the merge agent is reporting 2 errors
1)
Error messages:
The Merge Agent failed to upgrade triggers, metadata and stored procedures on the Subscriber to versions compatible with SQL Server 2005. Restart synchronization, and if this failure continues to occur reinitialize the subscription. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147199403) Get help: http://help/MSSQL_REPL-2147199403
Invalid column name 'metadata_select_proc'. (Source: MSSQLServer, Error number: 207) Get help: http://help/207
Invalid column name 'delete_proc'. (Source: MSSQLServer, Error number: 207) Get help: http://help/207
The merge process could not connect to the Publisher 'Server:database'. Check to ensure that the server is running. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147199368) Get help: http://help/MSSQL_REPL-2147199368
Another merge agent for the subscription(s) is running or the server is working on a previous request by the same agent. (Source: MSSQLServer, Error number: 21036) Get help: http://help/21036
looking inside the sp_MSensure_single_instance stored procedure, it's trying to obtain an exclusive lock on resource 'Merge Agent Name' but fails and returns -1
this is the stored procedure executed by sp_MSensure_single_instance and @retcode = -1 and thus cannot obtain exclusive lock on resource
so i think it's failing because for some reason MSmerge_PAL_role database role does not have enough rights to obtain exclusive lock on necessary resources
i've tried many things to fix this, including scripting out and dropping all merge rep, turning off replication db option and reapplying replication create scripts on the server, and even manually deleting the MSmerge_PAL_role database role but still doesn't work
Transactional replication allows updatable subscriptions where changes at the subscriber are replicated up to the publisher, this can happen via Immediate Updating subscriptions, Queue subscriptions and P2P (new in SQL 2005), all forms of Transactional replication.
Any compared document between merge replication and Transactional with updatable subscribtion ?
Hi.. is it possible to merge 2 columns into 1 to hold data like this... When the user imports the file in particular file they will be ACT_ID1 Tot_ACT1 ACT_ID2 TOT_ACT2 ...... until 15 BB 1245.45 CT some amount ..... The 2 letter character may change prob for each file.. at leat i know of somethem may change So while i transfer the data to the production database can is it possible to do some thing like COL1 BB 1245.45 COL2 CT 12456.12 etc..
I have a question - do you know if there is a sql command to merge two files? Basically, I want to merge the header for the xml file with actual xml data (generated via SQL) in another file - is that possible without running any DOS commands?
I have a problem Setting up pull subscription from a publishser to a subscriber , but I could set up Push subscription with out any problem between these two server .. can any one help me out.
I run SQL Server 2000 Enterprise edition.
I get the following error
"Publication 'ProdDB' does not exist. The step failed."
where ProdDB is the publication database from which my Push subscription is running.
I have a table with a field called SeqId which is not an identity nor a sequence but a kind of autonumber field (max(SeqId) + 1). Now I have to do a MERGE between 2 tables where the one with SeqId is the target.
How can I get the next SeqId for every row added? I tried this:
MERGE dbo.CRM_MNP_ORIGINAL_NRN AS T USING dbo.seriesnacionales AS S ON (T.RANGE1 = S.RANGOINI ) WHEN NOT MATCHED THEN INSERT (SeqId, Range1, Range2, OPERATORCODE, NRN, StartDate, CreateDate) VALUES((SELECT dbo.FN_GetNextSeqId4CRM_MNP_ORIGINAL_NRN()), S.RangoIni, S.RangoFin, S.IdOperador, 'TEST_M', Convert(DATETIME, FECASIGNA , 103), SYSDATETIME()) WHEN MATCHED THEN UPDATE SET T.Range1 = S.RangoIni, T.Range2 = S.RangoFin, T.OPERATORCODE = S.IdOperador OUTPUT $action, Inserted.*, Deleted.*; where the function just return Max(SeqId) + 1
Hi, I have two databases on MS. SQL Server 200 the first one is themain DB and the second one is a replica of it, both of them indifferent territories .I connect them using merge replication justwhen I need to merge the data.The problem is when I make import data or export data from anylocation (MS access ,MS SQL server ) to the second one (subscriber DB)or the main DB(publisher and distributor ) ,the imported data doesnot replicated to the other DB ???Normal transaction replicated smoothly.I hope you can help me.Thanks in advance.Mahmoud Khebaisa.
I need to come up with a solution to my replication issue. The solution that I come up with involves using one publisher to push transactional and merge replication. The database that is published is the same.
Would this cause any issues?? Can this be done?? I would think that if a table is needed for both, I would have to create a separate publication for each type right??
Is there any documentation anywhere to help prove if this is possible??
I have some problem about Merge Replication.i'm trying to merge Database A to Database B in local Server. So Database i want publisher contain this:
Because only Data in Table change therefore so i choosed it 100% Snapshot was generated after that ( Problem not a valid window user i already figured out )After that. I created Local Subscription in same Server ( Pull subscriptions and Subscription type: Client ),now problem is throw out."The schema script 'vwBuyADT_513.sch' could not be propagated to the subscriber."
I tried research so many time in Google but any information i found isn't useful for this problem.This problem still can ignore,Synchronization still running. But after 4-5 Hours running..This messages throw out: "The Publisher failed to allocate a new set of identity ranges for the subscription. This can occur when a Publisher or a republishing Subscriber has run out of identity ranges to allocate to its own Subscribers or when an identity column data type does not support an additional identity range allocation"
My question is: Is there anyway solve 2 probem? : 1. "The schema script 'vwBuyADT_513.sch' could not be propagated to the subscriber." 2. "The Publisher failed to allocate a new set of identity ranges for the subscription. This can occur when a Publisher or a republishing Subscriber has run out of identity ranges to allocate to its own Subscribers or when an identity column data type does not support an additional identity range allocation"
I am using SQL Server 2000 SP4 running on a MS Server 2003 with SP2. I am preforming Merge Replication on a continouas basis and the Merge Agent keeps stopping with the error 203 The process could not enumerate changes at the 'Publisher'. There is no additional information available with this error. Does anyone have any sugestions as to why this is happening. I can manually re-start the agent but it will fail again for particular reason.
I would like to know what alternatives are avaliable to SQL Server Merge Replication. I am also looking for Third Party Tools. Can anyone name a few for me.
Hi, I posted about this before, and set out on my own to get this working, and haven't been able to. I'm trying to get merge replication working with my SQL server 2000, and after 2 weeks I still have nothing. I've gone through multiple 'walkthroughs' which all brought me to the same point. I'm getting down to crunch time, and I'm either going to use this or implement my own merge algorigthm (I'd much rather use this). So here's where I've gotten to:
Hi, I am trying to replicate a production database server,on sql server 2000 at a particular geographic location to a new failover database server on sql server 2000 in a different geographic location via internet. The intention here is to use the failover database during times when the production server is down or busy and the synchronization needs to be sceduled for every 10 min. It will be of huge help if any expert could give the detailed process involved and any precautions that need to be taken. I also need to keep inmind to use the ever/odd sequence number generator during the replication implementation. Also, Whether to use row-level or column-level tracking?
I am having problems with my sql merge replication. Whenever a user syncs up to my main database, most of their records are deleted instead of being merged. Or the records on the main database are inserted and it replaces the whole table with the records on the remote laptops. Is there a way to prevent this from happening? Someone please help me.
I do a merge replication between Sql server 2000 and SQLCE 2.0
On my SQL2000 I have 4 tables i want to merge (specific columns only ) in 1 table for Merge with my SQLCe ( the table will be use for read only)
Question 1: What is the best pratice for keep the information update? Run store procedure before the synch for re-populate the table?:confused: or Make Trigger INSERT, UPDATE, DELETE in the all 4 table?:confused: or a mixte?:confused:
Question 2: Does someone know about some web site talk about this type of trick?
Basically if the type code is 1 one then move the data to column phone1, if the type is 2 then move it to column phone2.
This would be fairly simple if we always have type codes 1 and 2. But sometimes we can have type 1 and not type 2, or we could have type 2 and not type1.
Right now we only have 2 type codes. But, in the future we could be adding a 3rd type. So that would add a 3rd column (phone3).
Below is my code that I have written. I move the data into a temp table then list it. I am thinking of making this a view to my table. It works just fine. My question is, is there a better and more efficient way of doing this?
CREATE TABLE #Contacts ( id INT PRIMARY KEY, phone1 VARCHAR(15), phone2 VARCHAR(15) )
-- Insert the records for type 1
INSERT INTO #Contacts SELECT id, phone_num, NULL FROM test1 WHERE type_code = '1'
-- Insert the records for type 2, if the id does not exist for type 1
INSERT INTO #Contacts SELECT id, NULL, phone_num FROM test1 WHERE NOT EXISTS ( SELECT 1 FROM #Contacts WHERE #Contacts.id = test1.id ) AND test1.type_code = '2'
-- if the id has both type 1 and 2, update the phone2 column with the data from type 2
UPDATE #Contacts SET phone2 = test1.phone_num FROM #contacts JOIN test1 ON test1.id = #Contacts.id WHERE type_code = '2' SELECT id, phone1, phone2 FROM #Contacts DROP TABLE #Contacts
I'm not even sure this is possible but I'm using MERGE in a process that has 3 source tables (the process steps through each source table sequentially) and I need to delete from the Target database occasionally.
My current code is
sqlMerge = "MERGE " + TableName + " AS target USING @CData AS source" + " ON target.TotRsp = source.TotRsp AND target.ClientRef = source.ClientRef AND target.dbPatID = source.dbPatID" + " WHEN MATCHED THEN" + " UPDATE SET dbPatFirstName = source.dbPatFirstName, dbPatLastName = source.dbPatLastName,
[Code] ....
The Target db data is made up from several different clients and when the MERGE runs it uses TotRsp, ClientRef and dbPatID to uniquely match a source row to the target row and if no match it inserts the source row.
My problem is that when this runs with Source A first, it will delete all merged data from Source B & C. Then when Source B runs it will insert all Source B data but delete all from A & C and so on.
Is there way that that I can include additional clauses into NOT MATCHED BY SOURCE THEN so it knows only to delete when data has come from say Source A. Once the data is in the target table there is no reference to which source table it came from tho.
If there isn't a solution I suppose I could always add an extra column to the target db to indicate which source it came from and then have something like
NOT MATCHED BY SOURCE AND t.Source = 'SourceA'.
That's quite a bit of work my end to do that tho so I'd like to be sure it works.
I have installed SQL Server 2005 x64 Enterprise edition with Service Pack 2 on a Windows Server 2003 x64 Standard Edition with Service Pack 2.
Now I have to configure Merge Replication that will work with SQL Server Compact Edition database on Windows Mobile devices.
Distributor and the Publisher are the same server.
IIS 6.0 is installed on the windows server. I have installed the SQL Server Compact Edition Server tools on the server. However the compact edition server tools are only available for 32bit servers and I have also found out from the article http://support.microsoft.com/default.aspx/kb/912430 that you cannot replicate data from SQL Server 2005 to SQL Server Compact Edition by using the 64-bit version of IIS. So if this is true does that mean I can not use merge replication on 64 bit server? Does that mean I have to get another 32 bit server with 32 bit IIS on it to make this work or is there another work around. Am i missing something here?
Does anyone know of any documentation on the performance of partitionmerge/split? Does the merge or split of a partition cause any lockingon the partitioned table? If you were merging or splitting a largevolume of data rebalancing your partitioned table would youpotentially lock users out?
In a nutshell, I want to do a merge replication with a SQL Server and several Access databases. I haven't been able to find anything in the documentation or 3rd party books.
Hi , I would like to get your advice in an issue that I am facing regarding merge replication. The flow is like: 1) User enter his login name and password. 2) Host_name is set as the login name that the user enters. 3) Based on the HOST_NAME property after filtering user gets the records relevent only to him (Differnet subscriber may use same mobile device).
4) He make some changes in the local sdf according to the business logic. 5) The application Syncs with server on the next login session.
My issue is when I am trying to run the application in emulator exactly at the sdf updating part the application terminates. Error mess: Connection to Remote Device has been lost..... If I execute the same sql insert query it works om the local sdf and master database. Emulator used where Pocket PC 2003 SE and Windows Mobile 6 classic emulator. I have installed 1) Visual Studio 2005 SP 1 2) net compact framework SP2.
When I comment all the code related to Sync ,application works witout any issue. And the same application works in my "I mate kjam".
The code that I use is given below: ==========================================================================
public void Sync(string hostname, int newsub) // Parameters for HOSTNAME and flag to denote new user or existing //user.
I am running SQL Server 2000 and SQL Server CE on the handheld device. I have created the publication on the SQL Server.
I can subscribe to the publication successfully on the handheld and it creates the .sdf file just fine.
When I then attempt to synchronize, the process will run for a long time and eventually error out. (The database has a couple large data tables that are used for value lookups)
The error says the connection was broken.
Any suggestions on correcting this?
I am thinking of breaking the replication database into 2. One with the large lookup files and one with the updated tables. I still need to get the large database with the lookup tables onto the handheld though.
Any thoughts, advice or similar experience handling a situation as this would be appreciated.
I want to transfer data between an sql server 2005 and my pocket pc. So i've installed a sql server CE on it.
I know that i can use the merge replication but, i don't have any IIS. So, i've got to transmit data though email that have got a file attached. I Know how to email from sql server 2005. When the pocket receive that email, i don't know how to insert the data in sql ce from outlook compact. Doi have tu use SSIS , in that case how can i run the package?
I have set up merge replication with 1 publisher and 1 subscriber. Distribution is handled by a 3rd server.
I can generate a snapshot at the publisher and apply it to my subscriber. But when I insert some data (approx 30,000 rows) , the Synchronization agent gives the following error when It runs:
The merge process is retrying a failed operation made to article 'xxx' - Reason: 'The Merge Agent was unable to synchronize the row due to one or more unanticipated errors in the batch of changes. When troubleshooting, increase the -OutputVerboseLevel setting, restart the agent, and check for and resolve any errors generated by the database engine. '.
I have increased the OutputVerboseLevel setting and specified a file path in the -OutputMessageFile but the File is not being populated. All the references on books online say to put the file path in the -Output parameter but when I do it says it can only hold an integer value. So I cant see the errors generated by the database engine.
I'm currently investigating the feasibility of having SQL Mobile on a number of devices running Windows CE. These devices will have to synchronize with the server (either using merge replication or RDA) over GPRS. The problem I have is that some of these devices will never be online. Is there are a way of synching these offline devices? The model I had in mind was using a USB key (or some other storage device) to download the latest updates from an online device and transferring this data to the offline device, and vice versa e.g. from offline to online and then merging. Is this feasible?
