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?
Is there any way to measure bandwith usage during merge replication between sql server 2005 and sql server mobile 2005 running on a cradled wm5 mobile device.
Attaching the windows performance monitor to the network connection established over usb would work although I was wondering if there was something specific for this case integrated into Sql server 2005 / sql server mobile 2005 / Sql server management studio / third party tools that i could use ?
I have a problem when i start sincronyzing with the emulator of MSVS2005 to SQL2005 in Windows Vista. I have the same program in the emulator, but sincronyzing with windows XP Pro and no problem...
"Failure to connect to SQLServer with provided connection information. SQL Server does not exist, access is denied because the IIS user is not a valid user on the SQL Server, or the password is incorrect"
I just want to display all the data of a Single table into a Data Grid, I know that we can drag and drop the table on to a form and datagrid is generated, but here I want to retrive those values through my code, how should i do that
I am getting following errors while running the program Error 1) Error No. 28037, MS SQL Server 2005 Evrywhere Edition Error: A request to send data to the computer running IIS has failed. For more information see HRESULT Error 2) Error No. 0, SQL Server 2005 Evrywhere Edition ADO.Net Data Provider Error: The specified table does not exist [ JobLists ].
Can anybody please tell me, where I went wrong ??? In this code anywhere else????
Note: While adding a Data Source of SQL Server 2005 Mobile Edition, I have added that .sdf file into my project, thats why I have written the Data Source as : .DbFile.sdf
@"Data Source = .DbDotNetCF.sdf";
The code is as follows:
using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Text; using System.Windows.Forms; using System.Data.SqlServerCe;
namespace DeviceApplication1 { public partial class Form1 : Form { string filename = @".DbDotNetCF.sdf";
private DataSet dsJobLists;
public Form1() { InitializeComponent(); }
private void DeleteDB() { if (System.IO.File.Exists(filename)) { System.IO.File.Delete(filename); } }
private void Sync() { SqlCeReplication repl = new SqlCeReplication();
if (DbDotNetCFDataSetUtil.DesignerUtil.IsRunTime()) { // TODO: Delete this line of code to remove the default AutoFill for 'dbDotNetCFDataSet.JobLists'. this.jobListsTableAdapter.Fill(this.dbDotNetCFDataSet.JobLists); } } } }
I have created a merge replication correctlly( I suppose, there were no errros) Please help
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.
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?
We are trying to implement merge replication between 2 servers. Database size is around 11GB.
When I tried to create publication, snaphot agent is failing with following errror.
Error: 14151, Severity: 18, State: 1. Replication-Replication Snapshot Subsystem: agent NBTENTSQL1X-PayDirectWeb-PayDirectPub-2 failed. The replication agent had encountered an exception. Source: Replication Exception Type: Microsoft.SqlServer.Replication.ReplicationAgentSqlException Exception Message: String or binary data would be truncated. Cannot find the object 'MSmerge_sel_4D388
The table where the snapshot is breaking contains around 169 columns. Can someone please help me get through this error ?
We have setup a 32-bit IIS server on a 32-bit machine. Installed necessary SQL Server components (32-bit replication components). We have installed SQL Server 2005 64-bit SP1 with latest hotfixes on a 64-bit machine.
On the Mobile device we installed SQL Server 2005 Mobile.
When we start mergereplication the problem we have is error 28009 (An instance of the SQL Server Reconciler cannot be created) on the mobile device. I read allot about it, but still have a questetion.
Should this work?
I found the doc: SQL Server 2005 Upgrade Technical Resource Guide: Note: If your upgrade involves SQL Server CE subscribers, you must perform additional actions on the IIS server. You will need to install SQL Server 2005 client connectivity components along with SQL Server Mobile Edition components on the IIS server. You will also have to replace sqlcesa30.dlll, sqlcerp30.dll, and all the replication components on the IIS server.
From this I conclude it should work or is the doc in error?
If it does not, what can we do?
32-bit SQL Server 2005 on the 64-bit machine should work. (Can a 64-bit SQL Server 2005 run alongside 32-bit SQL Server 2005?).
Hi. I've looked all over MSDN, newsgroups and the web but I can't find the answer to a problem that I am having.
The application that I am working on used both transactional and merge replication. I want to avoid hard coding passwords into an application that kicks off the pull replication on the client machine.
The client machines are all using SQL Server 2005 Express. The other machine is running SQL Server Standard. The passwords and login details are specified in the subscription properties in the Management Studio.
A fragment of the code is posted below. The transactional sychronization works fine without having to specify any passwords - however the merge replication does not work if both of the passwords are not specified.
private void SynchButton_Click(object sender, EventArgs e) { // Set up the subscriber connection details. subscriberConnection = new ServerConnection(subscriberName); try { // Connect to the Subscriber. subscriberConnection.Connect(); // Do the transactional subscription synchronisation independantly of the // merge subscription replication. try { transPullSubscription = new TransPullSubscription(subscriptionDbName, publisherName, publicationDbName, transPublicationName, subscriberConnection); // If the pull subscription and the job exists, start the agent job. if (transPullSubscription.LoadProperties() && transPullSubscription.AgentJobId != null) { TransSynchronizationAgent transSyncAgent = transPullSubscription.SynchronizationAgent; transSyncAgent.Synchronize(); } else { } } catch (Exception ex) { } // Do the merge subscription synchronisation independantly of the // transactional subscription replication. try { // Set up the subscription details for the merge subscription (bi-directional data) mergePullSubscription = new MergePullSubscription(subscriptionDbName, publisherName, publicationDbName, mergePublicationName, subscriberConnection); // If the pull subscription and the job exists, start the agent job. if (mergePullSubscription.LoadProperties() && mergePullSubscription.AgentJobId != null) { MergeSynchronizationAgent mergeSyncAgent = mergePullSubscription.SynchronizationAgent; mergeSyncAgent.DistributorPassword = "<<password>>"; mergeSyncAgent.PublisherPassword = "<<password>>"; mergeSyncAgent.Synchronize(); }etc etc..
Any help or suggestions will be greatly appeciated. Thanks.
Hi All, this system is scheduled to go live in near to a day... but i have hit an issue when switching on SSL. Any help would be greatly appreciated.
Device Env: Windows Mobile 5.0 + Messaging and Security Feature Pack SQL Server Mobile 2005.
Merge replication works fine without SSL. After running the Configure Web Sync wizzard to setup SSL merge replication no longer works. Note i checked using pocket IE by browsing to https://mycompany.com/pub/sqlcesa30.dll?diag and all worked fine. There was no prompt saying the cert was bad etc.
The certificate is a: Equifax secure global ebusiness CA-1 cert. Signature algorithm md5RSA public key RSA(1024 bits) thumbprint algorithm sha1
Stacktrace given is: - ex {"A request to send data to the computer running IIS has failed. For more information, see HRESULT."} System.Data.SqlServerCe.SqlCeException - Errors {System.Data.SqlServerCe.SqlCeErrorCollection} System.Data.SqlServerCe.SqlCeErrorCollection Count 1 Integer Item In order to evaluate an indexed property, the property must be qualified and the arguments must be explicitly supplied by the user. System.Data.SqlServerCe.SqlCeError HResult -2147012858 Integer InnerException Nothing System.Exception Message "A request to send data to the computer running IIS has failed. For more information, see HRESULT." String NativeError 28037 Integer Source "Microsoft SQL Server 2005 Mobile Edition" String StackTrace "at System.Data.SqlServerCe.NativeMethods.CheckHRESULT() at System.Data.SqlServerCe.SqlCeReplication.Synchronize() at MyApp.Helper.Replication.SynchronizeInit() at MyApp.CallSynchronize() at System.Windows.Forms.Control.OnGotFocus() at System.Windows.Forms.Control.WnProc() at System.Windows.Forms.ContainerControl.WnProc() at System.Windows.Forms.Form.WnProc() at System.Windows.Forms.Control._InternalWnProc() at Microsoft.AGL.Forms.EVL.EnterMainLoop() at System.Windows.Forms.Application.Run() at MyApp.Program.Main()
2006-03-27 13:01:49.820 Skipping file 'NotesHistory_21.sch' because it has already been delivered for a previous article or by a previously interrupted snapshot. 2006-03-27 13:01:49.867 OLE DB Subscriber 'LUZERNV4-PDOSQL4': SET ANSI_NULLS ON
2006-03-27 13:01:49.898 OLE DB Distributor 'LARGOS00-SDI': {call sys.sp_MSadd_merge_history90 (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)} 2006-03-27 13:01:49.945 OLE DB Subscriber 'LUZERNV4-PDOSQL4': SET QUOTED_IDENTIFIER ON
2006-03-27 13:01:50.008 [17%] OLE DB Subscriber 'LUZERNV4-PDOSQL4': CREATE TABLE [dbo].[MSmerge_conflict_EFILE_MERGE_NotesHistory]( [NoteID] [int] NOT NULL, [CaseRefNum] [int] NOT NULL, [KeyField] [varchar](500) NOT NULL, [KeyValue] [varchar](500) NOT NULL, [Note] [varchar](800) NULL, [NoteField] [varchar](100) NULL, [ScanDocID] [int] NULL, [PDF_ID] [int] NULL, [RescanCode] [int] NULL, [RescanNote] [varchar](100) NULL, [AuditID] [bigint] NOT NULL, [rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL )
ꜽśã¹? êœ?Å›B 27 13:01î–”Å›è›ªç —êœ?Å›é”?Ä€Percent Complete: 17 2006-03-27 13:01:50.086 OLE DB Subscriber 'LUZERNV4-PDOSQL4': SET ANSI_NULLS ON
2006-03-27 13:01:50.117 Preparing table 'NotesHistory' for merge replication 2006-03-27 13:01:50.164 OLE DB Subscriber 'LUZERNV4-PDOSQL4': SET QUOTED_IDENTIFIER ON
2006-03-27 13:01:50.195 OLE DB Distributor 'LARGOS00-SDI': {call sys.sp_MSadd_merge_history90 (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)} 2006-03-27 13:01:50.258 OLE DB Subscriber 'LUZERNV4-PDOSQL4': if object_id('MSmerge_conflicts_info') is not NULL alter table [MSmerge_conflict_EFILE_MERGE_NotesHistory] add origin_datasource_id uniqueidentifier NULL
2006-03-27 13:01:50.351 OLE DB Subscriber 'LUZERNV4-PDOSQL4': if object_id('MSmerge_conflicts_info') is not NULL create nonclustered index [ncMSmerge_conflict_EFILE_MERGE_NotesHistory] on [MSmerge_conflict_EFILE_MERGE_NotesHistory] ([rowguid], origin_datasource_id)
2006-03-27 13:01:50.383 OLE DB Subscriber 'LUZERNV4-PDOSQL4': exec sp_MSsetconflicttable @article = N'NotesHistory', @conflict_table = N'MSmerge_conflict_EFILE_MERGE_NotesHistory'
2006-03-27 13:01:50.430 OLE DB Subscriber 'LUZERNV4-PDOSQL4': {call sys.sp_MSadd_merge_history90 (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)} 2006-03-27 13:01:50.476 [17%] The merge process was unable to deliver the snapshot to the Subscriber. If using Web synchronization, the merge process may have been unable to create or write to the message file. When troubleshooting, restart the synchronization with verbose history logging and specify an output file to which to write.Percent Complete: 17 2006-03-27 13:01:50.50é”?Ä€ 2006-03-27 13:01:50.539 Applied script 'NotesHistory_21.cft' 2006-03-27 13:01:50.586 OLE DB Subscriber 'LUZERNV4-PDOSQL4': {call sys.sp_MSadd_merge_history90 (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)} 2006-03-27 13:01:50.617 OLE DB Distributor 'LARGOS00-SDI': {call sys.sp_MSadd_merge_history90 (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)} 2006-03-27 13:01:50.711 Percent Complete: 17 2006-03-27 13:01:50.742 {call sp_MSsetconflicttable (N'NotesHistory', N'MSmerge_conflict_EFILE_MERGE_NotesHistory', N'LARGOS00-SDI', N'EFILE', N'EFILE_MERGE')} 2006-03-27 13:01:50.773 OLE DB Distributor 'LARGOS00-SDI': {call sys.sp_MSadd_merge_history90 (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)} 2006-03-27 13:01:50.867 Percent Complete: 0 2006-03-27 13:01:50.898 Category:COMMAND Source: Failed Command Number: 0 Message: {call sp_MSsetconflicttable (N'NotesHistory', N'MSmerge_conflict_EFILE_MERGE_NotesHistory', N'LARGOS00-SDI', N'EFILE', N'EFILE_MERGE')} 2006-03-27 13:01:50.961 Percent Complete: 0 2006-03-27 13:01:50.992 Category:SQLSERVER Source: LUZERNV4-PDOSQL4 Number: 102 Message: Incorrect syntax near 'NoteID'. 2006-03-27 13:01:51.039 Percent Complete: 0 2006-03-27 13:01:51.070 Category:NULL Source: Merge Replication Provider Number: -2147201001 Message: The merge process was unable to deliver the snapshot to the Subscriber. If using Web synchronization, the merge process may have been unable to create or write to the message file. When troubleshooting, restart the synchronization with verbose history logging and specify an output file to which to write. 2006-03-27 13:01:51.133 Disconnecting from OLE DB Subscriber 'LUZERNV4-PDOSQL4' 2006-03-27 13:01:51.180 Disconnecting from OLE DB Subscriber 'LUZERNV4-PDOSQL4' 2006-03-27 13:01:51.226 Disconnecting from OLE DB Subscriber 'LUZERNV4-PDOSQL4' 2006-03-27 13:01:51.258 Disconnecting from OLE DB Subscriber 'LUZERNV4-PDOSQL4' 2006-03-27 13:01:51.305 Disconnecting from OLE DB Publisher 'LARGOS00-SDI' 2006-03-27 13:01:51.336 Disconnecting from OLE DB Publisher 'LARGOS00-SDI' 2006-03-27 13:01:51.383 Disconnecting from OLE DB Publisher 'LARGOS00-SDI' 2006-03-27 13:01:51.414 Disconnecting from OLE DB Publisher 'LARGOS00-SDI' 2006-03-27 13:01:51.445 Disconnecting from OLE DB Distributor 'LARGOS00-SDI' 2006-03-27 13:01:51.492 Disconnecting from OLE DB Distributor 'LARGOS00-SDI' 2006-03-27 13:01:51.523 The merge process will restart after waiting 60 second(s)...
I have setup merge replication which successfully synchronizes with a group of desktop users using SQL Compact Edition.
However now I have setup Article Filters and when I attempt to regenerate the snapshot I get the following error:
Invalid column name 'rowguid'.
Failed to generate merge replication stored procedures for article 'AssignedCriteria'.
When I look at publication properties at the Articles page.. All my tables have the rowguid uniqueidentifier successfully added to tables and selected as a compulsory published column, apart from the table above "AssignedCriteria".. Even when I attempt to select this column in the article properties page and press ok, when I come back it is deselected again. ( The Rowguid column is however physically added to the table)
I have scripted the publication SQL and then totally reinstalled from scratch, including the database but for some reason it doesn't like this table. I remove the article filters, but still this "rowguid" is never "selected" in article properties.
We are using Uniqueidentifiers in other columns as well for historical reasons, but this doesn't appear to be a problem in other tables..
I'm working on developing a software solution using Mobile Client Software Factory. Let me start off by saying that this package is such a dream come true, and I can't thank the guys who put this together enough. The problem is, we're having some trouble getting the components setup for the Framework to work, in particular with SQL Server 2005.
We have SQL Server 2005 running on a machine, and we have our entire database setup what I believe to be correctly. Last night, we tried for hours to get the PPC Emulator running our version of the Framework to connect to the SQL Server (On another machine on the same LAN) to no avail.
We published just one table to test with, we have the snapshot created, the web service is running, and yet it won't let us connect. The most common error we're getting from the webservice log is: Hr=80004005 ERR:OpenDB failed getting pub version 28627.
I was just wondering if someone out there had a guide for correctly setting up SQL Server 2005 for merge replication so that remote clients can access the server through the webservice over the internet. If there is anything specifically special that needs to be done to work with the Mobile Client Software Factory, that would be great to know too.
I'd really appreciate some help, and thanks in advance to anyone who can lend some advice.
I am trying to migrate from my current system, where I do merge replication from Windows Mobile devices running SQL Server CE 2.0 to a central database running SQL Server 2000 sp3a. I want eventually to move to a system running SQL Server 2005 CE replicating to a SQL Server 2005 back-end. But the transition will need to be gradual, and I may have to support both systems for a while until I can convert all clients from the old system to the new. I also need to do thorough testing.
So ... I'm trying to set up a test environment giving me the maximum possible flexibility to do my testing. Ideally, I'd like to set up SQL Server 2000 and SQL Server 2005 on a side-by-side basis, in a manner that would potentially allow mobile devices running both SQL Server CE 2.0 and SQL Server 2005 CE to sync with either back-end server.
Can someone provide me with guidance as what is possible to set up here? I know that SQL Server 2000 and 2005 can be installed side-by-side on the same server. It also appears that you can set up SQL Server 2000 so that EITHER SQL Server CE 2.0 OR SQL Server 2005 CE can sync with SQL Server 2000 (see www.microsoft.com/sql/editions/sqlmobile/connectivity-tools.mspx), but I don't know if it's possible for BOTH SQL Server CE 2.0 AND SQL Server 2005 CE to sync to the same SQL Server 2000. As for SQL Server 2005 ... it appears to be possible to set up SQL Server 2005 so that BOTH SQL Server CE 2.0 devices AND SQL Server 2005 CE devices can sync to the same SQL Server 2005 (see web page cited above). However, I don't know if it's possible to set up a SQL Server 2005 server installation in this manner while at the same time having a side-by-side SQL Server 2000 installation supporting any level of mobile merge replication.
I don't understand why I am having this error. It does create the database on AddSubscription() method but it is failing opening the database on Synchronise(). I have also tried uninstalling and then reinstalling all the SQL Mobile components in the following order.
Been fiddling with SQL 2008 Nov CTP but now looking to deploy a 3.5 .sdf project using Merge replication. The 3.1 install sets up a web endpoint with sqlcesa30.dll and I have been running subscriptions just fine with that. But now I want to be able to create and manage my 3.5 publicatoin/subscriptions under SQL 2005 management console. I don't see how to create 3.5 sql ce databases there or to use 'configure web synchronization' to get a 3.5 web endpoint for syncing.
Am I missing something simple like registering the SQL CE 3.5 dlls to work with SQL management studio? I want to install to this a production server with the smallest possible footprint and I do NOT want to inject SQL 2008 Nov CTP bits into that production server at all! So, how to deploy SQL CE 3.5 to production and set up 3.5 merge replication under SQL 2005?
We are using SQL 2005 (SP1) and mobile agents with SQL Mobile. We are seeing an excessive number of updates after a device's database is reinitialized. My understanding was that if I generate a new snapshot or reinitialize all subscriptions (from the server), the devices would only get what the database looks like right now (inserts only) after syncing and/or reinitializing, but they are actually getting the right number of inserts and a huge number of updates in Replication Monitor. Any ideas?
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?
I am having trouble with getting Merge Replication up with a new SQL 2005 x64 Server. I already have it running smoothly with SQL 2005 Mobile and SQL 2000, but I want to migrate the backend to SQL 2005.
There is a front end server running IIS 6.0 on Windows 2003 x86 Standard (Server A) with SSL required and Basic Authentication set as the only authentication method (default Domain and Realm are also set). The backend server is running SQL 2005 x64 Standard on Windows 2003 x64 Standard (Server B). The mobile devices are running Windows Mobile 5.0 with SQL 2005 Mobile (Client).
From both a standard Windows XP workstation (IE) and the Client (PIE) I can browse to the HTTPS site on port 444 for the sqlcesa30.dll on Server A and get a response in normal and ?diag modes. The ?diag gives success in all but the 8.0 Database Reconciler (I didn't install the SQL 2000 replication components on Server A, my understanding is they should not be needed). Sql Server Module Versions are as follows: sqloledb.dll - 2000.86.1830.0, 9.0 replrec.dll - 2005.90.1399.0, 9.0 replprov.dll - 2005.90.1399.0, 9.0 msgprox.dll - 2005.90.1399.0.
The publication on Server B is enabled for Web Synchronization and the Snapshop Agent has run. There are no reported errors on that side.
Since I ran into issues getting synchronization to run I have opened up permissions fairly wide in this test environment. The PAL and the Database both have the user I am attempting to sync with listed (I am using the Administrator account in the test domain). I have opened full rights on the Snapshot share on Server B to Everyone (both NTFS ACL on the directory and the Share permissions). The virtual directory on Server A containing the sqlcesa30.dll has also been opened very wide, and the Application Pool does have access to it.
The Client is running a custom C#.NET 2005 application. With the exception of changes to set the application to sync with the Server A/B duo instead of the production servers, this application is identical to the code currently being used in production with SQL 2000 Standard.
The persistent error that I am getting from the Client being thrown by SQL 2005 Mobile when I attempt to sync is "An instance of the SQL Server Reconciler error object cannot be created. Try reinstalling the replication components."
SQL 2005 Mobile components on Server A are set to Logging Level 3 but not returning any errors that I can see to troubleshoot with. All replication components appear to be installed and operational, although I have re-installed them.
Besides the obvious question of how to solve the error listed above, is there an issue with am x85 front end IIS 6.0 server talking to an x64 back end SQL 2005 server for merge replication over HTTPS with Windows Mobile 5.0 clients running SQL 2005 Mobile?
Based on everything I have read off the MSDN Forums, MSDN articles, BOL, log files, and web searches it seems like I should have an operational synching topology, however clearly I have missed something because I have yet to get a successful subscription through to the publication.
Any insight/enlightenment would be greatly appreciated, this seems to be a very narrow and specific area of replication and mobility and information is relatively sparse from my experience so far.
I'm currently trying to setup a SQL 2k (SP3, build 922) merge replication publisher and a push subscription to a SQL 2005 (RTM release, no hotfixes) subscriber. The distribution database resides on a separate SQL 2k server (SP3, build 1007). I get the error below leading me to believe merge replication is not compatible between versions.
Replication-agentclassname: agent Agent_Name failed. Procedure or function sp_MSupdatesysmergearticles has too many arguments specified..
I say this b/c I've tried SQL 2005 32-bit and x64 subscribers and both give the same error. Anyone have any ideas if this is by design or just a bug that will be fixed later? Thanks.
Hello, I'm trying to enable extended logging to resolve some conflicts. After doing a google search, I found KB312292 which says to follow what's in books online on modifying the merge agent. I tried to add the following parameters in the agent profile:
-Output x:merge.txt
-OutputVerboseLevel 2
When I save the profile I get an error saying that SQL was expecting an integer for -Output. I've also tried using the -OutputMessageFile parameter but this didn't create the file, nor did it change the logging detail in the msmerge_conflicts_info table.
We have developed a mobile system that uses merge replication for SQL Mobile to SQL 2005. Previously we have developed mutliple mobile systems using merge replication for SQL Ce to SQL 2000.
Based on the knowledge we had gathered over about 4 years, we applied the synchronisation parameters for the SQL 2005 solution as we would for the SQL 2000 solution.
We have found there are some differences. Not too surprising I suppose, only some of these have us a little baffled.
For instance, there was a little flag called keep_partition_changes in SQL 2000 that is supposedly superceded by the use_partition_groups flag. However, if you don't set up your filtering to conform to the standards required by the use_pre_computed_partitions flag if you want it set to true, then the use_partition_groups flag gets set to false - also the @partition_options falg gets set back to 0 (static or non-unique data) when we want it at 3 (Single Parition, One subscriber).
To top it all off, when you get the use_partition_groups flag working, there are restrictions on which columns you can update on the device. WTF? This seems ludicrous, to be unable to update data at the subscriber - particularly information that allows you to effectively "delete" data from your subscription.
Examples of the current behaviour are as follows,
On initialize for a subscriber, the subscriber will receive their own data as inserts, plus exact multiples of that data as updates. Say there are 100 rows in TableA, the subscriber gets 100 inserts, plus 6000 updates. TableB has 20 rows, the subscriber gets 20 inserts, 1200 updates.
Further to this, performance goes out the window when synchronising changes. Typically the data flow will be between 5 and 200 changes in both directions for a synchronisation. We are seeing sync times in the replication monitor of over 20 seconds per user. Surely the calculations do not take that long. The tables in the database are not very large.
This behaviour gets significantly worse as we load the system. The application has an auto sync function which is timed to operate evry 10 minutes. However, now that there is in excess of 50 or so users on the system, those synchronisation times blow out to multiple minutes and the server starts to thrash. We have looked at indexing and maintenance but to no avail.
Everything still points to the merge replication setup.
So, it seems obvious to me that we are mising some key information about how to set up merge replication in SQL 2005. We woudl be very gratefull if someone could point out the errors of our ways.
Sorry for the convoluted post. Hope someone can help us.
We are using SQL Server 2005, on Windows server 2003 R2.
We Have Two Database Servers say DBServer1 and DBServer2, Now I wants to do Replication between these to servers, such that
1. The Changes at DBServer1 should be reflected at DBServer2 2. The Changes at DBServer2 should be reflected at DBServer1 3. Changes includes Data changes and Schema Changes 4. After every Synchronization Both Databases should be Identical
I tried doing so, what i did was I cofigured Distribution at DBServer1, also Publisher and Publication at DBServer1 and Made a Subscription at DBServer2.
What I successfully done is If Publisher means DBServer1 do some changes then it gets updated at DBServer2. But New Rows added at DBServer2 doesn't gets added at DBServer1
Thanks in Advance, Vishalgiri Goswami Kalptaru Infosoft Pvt. Ltd.
I have a merge replication scenario where the technicians are filtered by their individual codes. Each technician has his own mobile device for collecting and synchronising data.
On initialisation of the subscription, the user should only get the data that is relevant to their code.
The client is staging the rollout to all of their technicians and as the system has grown, we have noticed that there are a number (getting larger every day) of updates that are creeping into the initialisation of each subscription.
My understanding is that an initialisation will only ever apply the inserts necessary to create the subscriber database.
Is my understadning incorrect?
Is the some parameter in the creation of the publication that we have set incorrectly?
Is there something wrong with SQLServer 2005 itself?
I am very interested to hear anyones comments or advice around this issue.
Publisher is 2005 x64, subscribers SS2000 (SP3) and SS2005 x64. Pull agents, no filters on subscriptions. We are seeing many seemingly random conflicts on between SS2000 subscriber and publisher. It happens on several different tables.
One table is never editted, only inserts happening everywhere and deletes happening on the SS2000 subscriber. Deletes will sometimes generate conflict. Reason is '"he row was deleted at 'CTS11.CTS' but could not be deleted at 'cts4a.cts'. Unable to synchronize the row because the row was updated by a different process outside of replication." CTS11 is SS2000 subscriber, CTS4A is publisher.
Probably unrelated bug but when looking at conflicts on this same table in SS2005 conflict viewer, get error "ID is neither a DataColumn nor a DataRelation for table summary (System.dATA)" and then "Column ID does not belong to table summary (System.Data)". ID column is rowguid, only unusual thing about table is that it has varchar(8000) field plus some other fields.
Other tables generate conflicts with this reason "The row was updated at 'CTS11.CTS' but could not be updated at 'cts4a.cts'. The merge process was unable to synchronize the row." I enabled verbose logging in the merge agent but the log file didn't contain any further explanation.
This same topology and schema worked fine when all publishers and subscribers were SS2000.
Any insight into how to fix this would be appreciated.
We have a module in our business-application, that automatically installs merge replication of the business-application's database, both at publisher and subscribers. It's intended for Sql-2000. Now we need it to be applicable also for Sql-2005, so the module requires some changes, because, as we noticed, sql-2005's replication technology differs from Sql-2000's one.
A few questions to experts, familiar with Merge Replication in SQL-2005:
1. Is it possible to create hybrid replication, with publisher running at SQL-2000 (MSDE) and subscribers running at SQL-2005 Express? Merge publication is not supported in SQL 2005 Express, but some users may require option to use the application at free-of-charge database platform.
2. How deep are changes in merge replication implementation at system level? Is it just modified a bit since SQL-2000, or changed entirely? This knowledge is needed, because the module uses some low-level features (executing system sp's, querying replication-specified tables, etc.). For example, when we tried to create subscription of existing publication in SQL 2005 using the module as is, we found out that sp_addmergepullsubscription_agent doesn't use @encrypted_password parameter anymore, and subscription creation process failed.
3. If anybody has experience using merge replication creation/deletion/detection scripts, generated by SQL 2000, in SQL 2005! Please, tell - what more problems may happen?
I am in the process of testing a SQL 2005 Std x64 server with merge replication using Windows Mobile 5.0 clients and SQL 2005 Mobile. The test DB is a copy of the currently active DB, but has been expanded to include some new tables to support planned application functionality extensions.
Once the publication exceeds 97 Articles, the error is thrown that "The buffer pool is too small or there are too many open cursors". If I drop one article everything is fine. I ran a test with dummy DB that had 100 blank tables, and this initialized just fine on the client. The additional articles I am publishing (the 98th table) is also empty, but it throws the error anyway.
Is there a limit on the total size/number of changes that can be sent? Since I have run tests sending over 64,000 changes to a client during initialization this does not seem to be the case (I am only attempting a little more than 9,700 changes on this initialization).
Some other ideas that have been tested without success are to stop the user triggers from propagating, and toggling the AWE setting for SQL. The Replication Monitor does say the client completes replication, and it seems to choke at the very end of completing replication when it attempts to write to the tracking tables. The last successful action is sys.sp_MSadd_merge_history90, and it appears to be acting on the last table added to the publication.
There does not appear to be a limit on the number or articles, since I can publish more articles in a dummy DB than I am able here, so it seems to be something to do with size. Any information would be helpful, this is a very frustrating issue. Thanks!
Background - I have a publication that propigates schema changes. I have a view in which I want to remove a column.
Error - Going by what the BOL says, I use Alter View and delete the column from my select statement. I issue the alter view command against the Publication database and it just "churns". I do not get any locking errors or any other type of error, but the statement never completes execution. I watched it run for 10 minutes and cancelled the query. Executing the same statement against a copy of the database that is not being published executes in 1, 2 seconds.
Here is what I am doing:
Old View: Select table1.record_number, table1.record_date, table1.status_code, table2.status_desc,
table2.txt_sort_order
FROM table1 join table2 on table1.status_code = table2.status_code
I have a question for anyone who mas some tips/pointers for optimizing SQL merge replication publications.
The front end web server is running IIS 6.0 on Windows 2003 x86 Server Standard (Server A). The back end database server is running SQL 2000 Standard on Windows 2003 x86 Standard (Server B). The merge replication clients connect via HTTPS over the Internet from a custom C#.NET 2005 application using SQL 2005 Mobile running on Windows Mobile 5.0 (Client).
The publication itself has several filters on it. The entry point uses the user's Windows username to start the filter. Based on the user, it then filters the records in multiple tables. There are 68 articles and 44 filter statements. The filters extend multiple layers deep, in other words they are not all filtering off the HOST_NAME() variable, some tables filter from records in tables that filter from the HOST_NAME() variable. The publication is set to minimize data sent to the clients, and considers a subscription out of date if it has not synced in the last 4 days. All the rowguids are indexed as well.
There are approximately 35 clients actively using the application at any given time. On average, a client will initiate a merge replication 3-4 times per hour from 8am-5pm. Generally, a sync will take between 10 seconds and 2 minutes to complete, with most of them being around 30 seconds on average.
When a client starts a sync, there is a spike to about 50% on the server's CPU graph. If multiple clients attempt to sync at the same time the CPU utilization can be pushed to 100% for extended periods (more than 30 seconds).
I recently completed a project to increase the bandwidth available to the clients, and plan to reduce the number of filters significantly (although this will obviously increase the amount of data going to the clients and the storage needs on the individual devices). I also plan on changing the setting to not minimize the amount of data sent to the clients.
Having said all that, does anyone have any information about how to further optimize merge publications to mobile clients? The next publication will be on SQL 2005 x64 Standard if I can solve the issues in the text environment. I would like to enhance the publication as much as possible to make the end user experience better than it currently is.
I have noticed that four out of the 145 tables being replicated in my topology perform a mysterious update.
If I insert data at the subscriber then synchronise the upload is completed followed by immediate downloading of the same identical set of rows as updates.
Has anyone seen anything like this. I cannot see any difference in the filter joins or article settings compared to any other table in the system but these four consistently do this.
Publisher/Distributor: SQL 2005 SP1 on Win 2003 Server Subscriber: SQL Express 2005 SP1 on Win XP SP2
