Solved

Howto setup SQL Replication with a Compact Framework Application .NET2003 and sqlCE

Posted on 2004-03-24
25
1,192 Views
Last Modified: 2012-06-27
Hello all,

I've been trying for 3 straight days to get replication working, i'm doing something wrong and deciced to ask somebody here at Experts Exchange to provide with some sort of step-by-step tutorial or something like that.

First i would like to say what my goal is for my application.

I have a customer list and i want the customer list in my pocket pc application, there are 200+ customers in my list on my SQL2000 server. it takes 30sec to get the list in my pocket pc application thru a GPRS connection (33k - 56k speed).

I've used RDA pull for this, all works great, but whenever there is a update to the list, i need to drop the local sqlCE table and do a pull again. this again takes 30sec even if there is only 1 record updated. What i want is a system that does a update thus compare tables and only 'pull' the updated record(s).

So after some reading on the internet, i came to the conclusion that I need Replication, and that RDA is not the solution.
perhaps i'm wrong, but i need a way to keep traffic and waiting time for the user at an minimum.

after installing distribution etc.etc. on my SQL2000 server, i can't get it to work. So i decided to take a clean start with help from you.

Any solution is welcome. i'm giving away 500 points for this one, as i need a solid database application. And could really need some good help.

Some specs:
Pocket PC 2003
GPRS T-Mobile NL
.NET 2003
Compact framework 1.1 SP1
Sql CE 2.0
SQL Server 2000 Developer Edition
Windows Advanced Server 2000 On a 1Mbit DSL line

Thanks in advance
Marc
(Netherlands)
0
Comment
Question by:donkie9999
  • 13
  • 12
25 Comments
 
LVL 13

Expert Comment

by:danblake
ID: 10666747
What i want is a system that does a update thus compare tables and only 'pull' the updated record(s).
Use merge replication.

But you will require something like MSDE 2000 on each Pocket PC to make this happen.
I do not believe that MSDE is supported on Pocket PC's yet...

So that could cause a problem.
0
 

Author Comment

by:donkie9999
ID: 10666862
well merge replication is the solution (i think), but i can't get it to work, it's supported on pocket pc i've seen some examples on which it should work but i can't get it to work, perhaps i'm doing something wrong on the server. that's why i've asked for some sort of step-by-step tutorial or something like that.

in books-online help file from SQL CE Server there is:

Dim repl As SqlCeReplication = Nothing
     
      Try
         ' Set the Replication object.
         repl = New SqlCeReplication()
         repl.InternetUrl       = "http://www.northwindtraders.com/sqlce/sscesa20.dll"
         repl.InternetLogin     = "MyLogin"
         repl.InternetPassword  = "<MyPassword>"
         repl.Publisher         = "MyPublisher"
         repl.PublisherDatabase = "MyPublisher Database"
         repl.PublisherLogin    = "MyPublisher Login"
         repl.PublisherPassword = "<MyPublisher Pwd>"
         repl.Publication       = "MyPublication"
         repl.SubscriberConnectionString = "Provider=Microsoft.SQLSERVER.OLEDB.CE.2.0;Data Source=\ssce.sdf"
         repl.Subscriber        = "MySubscriber"
         
         ' Synchronize to the instance of SQL Server 2000 to populate the Subscription.
         repl.Synchronize()
     
      Catch e As SqlCeException
      ' Use your own error handling routine to show error information.
      ' ShowErrors(e)
     
      Finally
         ' Dispose of the Replication object.
         repl.Dispose()
      End Try

this should work, offcourse with different settings, but all i get is synchronize error. thats why i believe that the problem is on the SQL server 2000.
0
 
LVL 13

Accepted Solution

by:
danblake earned 500 total points
ID: 10667061
SQL CE Server -- I forgot about that little edition, you are quite correct.
Have you tried using the inbuilt merge agents to resync ?

I have included information, to get you started:
Setup of merge replication:
http://www.databasejournal.com/features/mssql/article.php/1438231

Validation of Merge Replication:
http://www.mssqlcity.com/Articles/Replic/ValidMR/ValidMR.htm
0
 

Author Comment

by:donkie9999
ID: 10667608
used the step-by-step tutorial on databasejournal.com, when i use the code (on the Pocket PC) mentioned  before I still get the following error.

CEReplTest.exe
SqlCeException

SqlCeReplication::Synchronize+0x7d
Form1::Form1_Load+0x90
Form::Onload+0x15
Form::_SetVisibleNotify+0x1d
Control::set_Visible+0x1f
Application::Run+0x7
Form1::Main+0xd

i've added ( ' repl.AddSubscription(AddOption.CreateDatabase) ') to the code to create the local DB. that part is working the file is there but offcourse it's empty

connection to the sscesa20.dll is working correct as this allready seems to be working oke when i used RDA pull

the settings:
 
         repl = New SqlCeReplication()
         repl.InternetUrl       = "http://www.myserver.nl/sqlce/sscesa20.dll"
         repl.InternetLogin     = "username"
         repl.InternetPassword  = "password"
         repl.Publisher         = "COMPUTERNAME"
         repl.PublisherDatabase = "database_copy"
         repl.PublisherLogin    = "SQLusername"
         repl.PublisherPassword = "SQLpassword"
         repl.Publication       = "database_art"
         repl.SubscriberConnectionString = "Provider=Microsoft.SQLSERVER.OLEDB.CE.2.0;Data Source=\ssce.sdf"
         repl.Subscriber        = "COMPUTERNAME"

the sqlserver agent is providing my with the following error:

(Source: Merge Replication Provider (Agent); Error number: -2147201001)

please advice
0
 
LVL 13

Expert Comment

by:danblake
ID: 10667830
The merge replication error:
http://support.microsoft.com/default.aspx?scid=kb;en-us;318592&Product=sql2k

(Service pack dependancy issues -- can you please check this first.)
0
 

Author Comment

by:donkie9999
ID: 10668060
i'm running SP3 on the SQL Server, and there is no FTP access!!
0
 
LVL 13

Expert Comment

by:danblake
ID: 10668187
What is the full Error message as recorded in the SQL Server error log ?
0
 

Author Comment

by:donkie9999
ID: 10668431
nothing is found in the SQL Server log... strang???

in the merge agents i can see that the PDA has connected, but delivers this message.

The process could not deliver the snapshot to the Subscriber.

The process could not deliver the snapshot to the Subscriber.
(Source: Merge Replication Provider (Agent); Error number: -2147201001)
---------------------------------------------------------------------------------------------------------------

something wrong with the tables? just plain tables no views and or stored procedures
0
 
LVL 13

Expert Comment

by:danblake
ID: 10668538
snapshot to the Subscriber --
what are the security permissions of the connecting agents ?

Does the Central SQL Server (SQL Server Agent / SQL Server service) have permission to update the dependant machines ?
(Domain NT Account, also allowed to login/having admin access at PocketPC databases ?)
From : http://support.microsoft.com/default.aspx?scid=kb;en-us;321822&Product=sql2k#1

Can we increase the replication agent logging (get a more detailed error messages):
http://support.microsoft.com/default.aspx?scid=kb;EN-US;312292

0
 

Author Comment

by:donkie9999
ID: 10668822
ok what i make of this all:

the SQL SERVER can not connect to the PocketPC as the agent is started at the Pocket PC and the SQL SERVER is trying to connect to it.

or

The connection agent (SqlCE) doesn't have the permission to connect.

am i right?

btw. The Subscriber is always an anonymous Subscriber.
i'm a bit confused by it all, will have to do some looking around, i guess. will get back at this very shortly


0
 
LVL 13

Expert Comment

by:danblake
ID: 10668872
I will post some more info later on the security..
Are your pocket pcs configured as a push or pull subscription from the source database publication ?
0
 
LVL 13

Expert Comment

by:danblake
ID: 10668877
From BOL:
When specifying the snapshot location on a network share, it is recommended that you dedicate the share for snapshot storage and files that have the same security standards. Next, give the replication agents Write permission on the share and in the snapshot location and appropriate folders so they can write the snapshot files there.

Subscribers that need to access the snapshot files will need Read permission to the snapshot location and appropriate folders. If the snapshot folder is not shared for the appropriate Subscribers, the replication agents cannot access the folder and replication fails.

0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

Author Comment

by:donkie9999
ID: 10669063
i don't have clue if the pocket pc has a push or a pull subscription? i believe it has something security, but then again, on the pocket pc's part i can't do much. IIS is taking part in communications, perhaps it needs some more configuration on that part..???
0
 
LVL 13

Expert Comment

by:danblake
ID: 10669142
The database you have setup for replication, does it allow push or pull merge subscriptions ?
(Push -- you push the database from the central PC to the subscriber at a reguar time,
Pull -- the pocket PC requests the data from the central PC whenever it wants).

IIS -- should not be a problem here.

What are the Pocket PCs security rights (File-level: NTFS) to the Snapshot directory ?

0
 

Author Comment

by:donkie9999
ID: 10675279
i gave the proper rights to the snapshot folder and the appropriate folders, and the error on the SQL server part is gone, the agent even tells me that there has been 3896 inserts, 0 deletes ..etc.etc.. but it only took 1 sec. and the database on the pocket pc is empty and the application is giving me again the Synchronize error. :-(

any ideas?
0
 
LVL 13

Expert Comment

by:danblake
ID: 10675302
is this the same error as last time ?
0
 

Author Comment

by:donkie9999
ID: 10675345
ok! i've got it partially solved, i've setup replication on the northwind database and no errors at all, even the local DB is created and filled, so thats great, i'm going to try working on the application and do some stuff and i'm going to check the replication functions, will get back with my findings.
0
 
LVL 13

Expert Comment

by:danblake
ID: 10675350
On your main sql-server is a snapshot available, and the snapshot generated correctly ?

(And the merge agents now running correctly logging changes into the system ?)
0
 

Author Comment

by:donkie9999
ID: 10675365
perhaps i need to take in account how the tables are defined?????
0
 

Author Comment

by:donkie9999
ID: 10675542
everything is working correct on the northwind database
are there any restrictions on the table layout etc.etc.
0
 
LVL 13

Expert Comment

by:danblake
ID: 10675567
perhaps i need to take in account how the tables are defined?????
Yes --
PKs need to be present, merge replication will add the necessary row guid fields to ensure synronisation is made between the system.

Without seeing the layout, I can only point to the guidelines for design / replication requirements for each table:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/replsql/replplan_2ipa.asp

Setting up merge replication:
http://www.databasejournal.com/features/mssql/article.php/1438231


One way to test this theory would be to try and take only a few tables from your database to be syncronised and try this.
0
 
LVL 13

Expert Comment

by:danblake
ID: 10675570
One way to test this theory would be to try and take only a few tables from your database to be syncronised and try this.
--> This way if there is a single article causing a problem we can try to eliminate this error.
0
 

Author Comment

by:donkie9999
ID: 10676096
got it....

added a guidRow to the table and it's working on my own tables..

another question:

how to filter the data? i know i can set filters on the publisher, but i want to only sync the data by week number without deleting the records.

any ideas???
0
 
LVL 13

Expert Comment

by:danblake
ID: 10676169
another question:
... ?

how to filter the data? i know i can set filters on the publisher, but i want to only sync the data by week number without deleting the records.

any ideas???

How about putting this in a new question ?
I need some further information on the scenario involved.
0
 

Author Comment

by:donkie9999
ID: 10676191
your right :-)

you have been a good help on this subject and you have did a great job on guiding me thru this problem.

Thx danblake
0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now