SQL Server Performance Forum – Threads Archive

Sql server compact edition merge replication error

Hi I am trying to complete sql server compact edition book online walkthrough "Creating a Mobile Application with SQL Server Compact Edition". I am successful to complete it as guided using all default settings (e.g. anonymous access, windows authenticaion etc.) and the sample run on both emulator and my pocket pc svc5000. I have following configuration.. Windows 2000 professional sp4 Sql server 2005 standard edition sp1 VS 2005 profesional edition (sp1 not installed) Sql server compact edition Sql server compact edition server tools But the problem is that in production environment i will not be able to connect to the sql server using named instance (e.g machine_nameinstance_name). i have to use ip address in that situation. so i tried to modify the sample to use ip address in place of named instance and then the problem starts.. I have configued my sql server to allow remote connections using tcp/ip and named pipes using sql surface area config tool. When using named instance i used following code to Synchronize with sqlserver 2005 with and it worked perfectly : Dim filename As New String("Program FilesTestTest.sdf") Dim repl As New SqlCeReplication() repl.InternetUrl =<a target="_blank" href=http://machine_name/Test/sqlcesa30.dll>http://machine_name/Test/sqlcesa30.dll</a> repl.Publisher = "machine_nameinstance_name" repl.PublisherDatabase = "Test" repl.PublisherSecurityMode = SecurityType.NTAuthentication repl.Publication = "Test" repl.Subscriber = "Test" repl.SubscriberConnectionString = "Data Source=’" + filename + "'<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />assword=”;Max Database Size=’128′;Default Lock Escalation =’100′;" Try repl.AddSubscription(AddOption.CreateDatabase) repl.Synchronize() Catch err As SqlCeException MessageBox.Show(err.ToString) End Try When i tried to do replication using ip address it shows following error: System.Data.SqlServerCe.SqlCeException was unhandled HResult=-2147467259 Message="Failure to connect to SQL Server 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." NativeError=29061 Source="Microsoft SQL Server Compact Edition" StackTrace: at System.Data.SqlServerCe.SqlCeReplication.Synchronize() at Test.MainModule.Sync() at Test.MainModule.Main() I am using following code to connect using ip address: Dim repl As New SqlCeReplication() repl.InternetUrl =<a target="_blank" href=http://xxx.xxx.xxx.xxx/csst/sqlcesa30.dll>http://xxx.xxx.xxx.xxx/csst/sqlcesa30.dll</a> repl.PublisherNetwork = NetworkType.TcpIpSockets repl.PublisherAddress = "xxx.xxx.xxx.xxx,xxxx" repl.Publisher = "SQL2005" ‘instance name of sql in sql2005 repl.PublisherSecurityMode = SecurityType.NTAuthentication repl.PublisherDatabase = "Test" repl.Publication = "Test" repl.Subscriber = "Test" repl.SubscriberConnectionString = "Data Source=’" + filename + "'<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />assword=”;Max Database Size=’128′;Default Lock Escalation =’100′;" repl.AddSubscription(AddOption.CreateDatabase) repl.Synchronize() I have give rights to iuser_machine_name in the public access list of publication and on database also. I don’t have any idea on where i am wrong… Please help soon.. Many thanks in advance..

Take help of these 2 KBAs to setup:
http://support.microsoft.com/kb/314783
http://support.microsoft.com/kb/319723 http://msdn2.microsoft.com/en-us/library/ms172357.aspx too. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

Hi Thanks for your valuable suggestions. I finally managed to run to code using ip address on my pc and it worked prefectly. I also found the cause of error. I was using following code. Dim repl As New SqlCeReplication() repl.InternetUrl =<a target="_blank" href=http://xxx.xxx.xxx.xxx/csst/sqlcesa30.dll>http://xxx.xxx.xxx.xxx/csst/sqlcesa30.dll</a> repl.PublisherNetwork = NetworkType.TcpIpSockets repl.PublisherAddress = "xxx.xxx.xxx.xxx,xxxx" repl.Publisher = "SQL2005" ‘instance name of sql in sql2005 repl.PublisherSecurityMode = SecurityType.NTAuthentication repl.PublisherDatabase = "Test" repl.Publication = "Test" repl.Subscriber = "Test" repl.SubscriberConnectionString = "Data Source=’" + filename + "'<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />assword=”;Max Database Size=’128′;Default Lock Escalation =’100′;" repl.AddSubscription(AddOption.CreateDatabase) repl.Synchronize() Every thing was fine except that i have a named instance of sql 2005 on my pc and i was wrong on setting Publisher property of repl object. It should be like that in case of a named instance repl.Publisher = "macnine-nameinstance-name" In case of default instance it should be just machine-name. repl.Publisher = "machine-name" But now when i configured the code in production web server I am again facing problems. i used the same code as given above, except for replacing ip address of web server and Publisher property set to machine-name as my web server has default instance of sql server 2005. When i call Synchronize method it give a long error message. When i check Sql Replication Monitor Histroy i figured out following error msg for my unsuccessful attempt.. Error messages: The merge process could not connect to the Publisher ‘publisher-name:Test’. Check to ensure that the server is running. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147198719) Get help:<a target="_blank" href=http://help/MSSQL_REPL-2147198719>http://help/MSSQL_REPL-2147198719</a> The process could not connect to Publisher ‘publisher-name’. (Source: MSSQL_REPL, Error number: MSSQL_REPL20084) Get help:<a target="_blank" href=http://help/MSSQL_REPL20084>http://help/MSSQL_REPL20084</a> Invalid connection string attribute (Source: MSSQLServer, Error number: 0) Get help:<a target="_blank" href=http://help/0>http://help/0</a> TCP Provider: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond. (Source: MSSQLServer, Error number: 10060) Get help:<a target="_blank" href=http://help/10060>http://help/10060</a> An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (Source: MSSQLServer, Error number: 10060) Get help:<a target="_blank" href=http://help/10060>http://help/10060</a> Login timeout expired (Source: MSSQLServer, Error number: 0) Get help:<a target="_blank" href=http://help/0>http://help/0</a> The merge process failed to execute a query because the query timed out. If this failure continues, increase the query timeout for the process. When troubleshooting, restart the synchronization with verbose history logging and specify an output file to which to write. (Source: MSSQLServer, Error number: 0) Get help:<a target="_blank" href=http://help/0>http://help/0</a> Please help.. Many Thanks in advance..

Hi Satya,
I installed all the required components. However when i try to add a connection, there is no "Microsoft SQL Server 2005 Compact Edition" for my selection. Did I miss anything out? Thanks,
Vince

Did you get successful installation of SQL CE in this case?

]]>