Solved

SQL CE Replication

Posted on 2003-11-11
29
1,942 Views
Last Modified: 2012-06-21
Hi All,
i need to create replication ro use with SQL 2000 and pockte PC running Sql CE 2.0.
i spent 2 days trying almost every spolution online none of them worked (so please do not send me to another website).

 have my SQL server setup with IIS and virtual dirctory where i registered sscesa20.dll, i can access it using regular browser.

on the device i am using C#:
with SqlCeReplication object.
i setup the parameters but unable to connect error:"A request to send data to a computer running IIS has failed" !

any ideas ?
Shay
0
Comment
Question by:Shay050799
  • 17
  • 11
29 Comments
 
LVL 22

Expert Comment

by:_TAD_
Comment Utility


Could you please post your connection code?

The one time I've had to do this I used the example of the MSDN and miraculously it worked.

MSDN SqlCeReplication object (I know... you've been here):
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatasqlservercesqlcereplicationclasstopic.asp


Your code should look just like this

<C#>

        SqlCeReplication repl = null;

        try {
            // Set the Replication object
            repl = new SqlCeReplication();
            repl.InternetUrl       = "<http://www.northwindtraders.com/sqlce/sscesa20.dll>";
            repl.InternetLogin     = "MyLogin";
            repl.InternetPassword  = "<password>";
            repl.Publisher         = "MyPublisher";
            repl.PublisherDatabase = "MyPublisher Database";
            repl.PublisherLogin    = "MyPublisher Login";
            repl.PublisherPassword = "<password>";
            repl.Publication       = "MyPublication";
            repl.Subscriber        = "MySub";
            repl.SubscriberConnectionString = "Provider=Microsoft.SQLSERVER.OLEDB.CE.2.0;Data Source=\\ssce.sdf";

            // Create the Local SSCE Database subscription
            repl.AddSubscription(AddOption.CreateDatabase);

            // Synchronize to the SQL Server 2000 to populate the Subscription
            repl.Synchronize();
        }
   
        catch(SqlCeException) {
            // Use your own error handling routine to show error information.
        }

        finally {
            // Dispose of the Replication Object
            repl.Dispose();
        }




<---->


Okay... a few other things to double check:

1)  Can you get there from another browser that is NOT on your development machine?  A machine completely untouched by your processes?

2) you said you registered the dll.  Does that mean you ran the regsvr32.exe program?  While I think it is a good practice to do so, I do not believe it is mandatory.

3) Your error sounds vaguely like a security permissions error.  I can't say for certain, but I would double check to make sure that your .net user has been granted rights and privlidges to the database.
0
 
LVL 3

Author Comment

by:Shay050799
Comment Utility
i think the problem is with my parameters.
do i need to create publication on the sql server on my desktop ?

what the parameters i need to put in to
repl.InternetUrl       = "<http://www.northwindtraders.com/sqlce/sscesa20.dll>";
            repl.InternetLogin     = "MyLogin";
            repl.InternetPassword  = "<password>";
            repl.Publisher         = "MyPublisher";
            repl.PublisherDatabase = "MyPublisher Database";
            repl.PublisherLogin    = "MyPublisher Login";
            repl.PublisherPassword = "<password>";
            repl.Publication       = "MyPublication";
            repl.Subscriber        = "MySub";
??
0
 
LVL 22

Expert Comment

by:_TAD_
Comment Utility


 repl.Publication is required it specifies the publication name that has been enabled for anonymous merge subscriptions.  Which, in a nutshell, tells you what public synonymn is being used for users who are *not* the adminstrator.


I am not real savy on where this needs to be... I *think* it needs to be on the computer hosting the IIS server.  However, it may also be needed on the database server.

In your testing enviroment this may be the same server and you won't have a problem, but in production it may be two different servers and then you have to have an explicit trust between the two servers in order for it to work properly.



0
 
LVL 3

Author Comment

by:Shay050799
Comment Utility
do i need to create publication for the DB in the SQL server ???

i created one, and using the Anonymous login, i get an exception but the error message in it is blank !!!
any ideas ?
0
 
LVL 22

Expert Comment

by:_TAD_
Comment Utility


Blank?  

dang... sucks to be you.  :-P

Seriously though...

Not even an error code, it just blew up on you?


At least the error is different... that means we are either going forward or making it worse...

Can you post your properties and their values?

I'd like to see what you've got so far.


This page has a list of all the members of the class you are using and a brief description of each:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatasqlservercesqlcereplicationmemberstopic.asp


I suppose it may be possible that you are inadvertantly pointing a property to the wrong place and that is why you are getting an error.
0
 
LVL 3

Author Comment

by:Shay050799
Comment Utility
repl.Publisher = "SHAY"; // my computer name
repl.PublisherLogin = "IUSR_SHAY";  // Anonymous login (is this correct ?)
repl.PublisherPassword = "";
repl.Publication = "TESTDB"; // the name of the publication i created
repl.PublisherDatabase= "TESTDB"; // the name of the DB i want to sync
repl.InternetUrl= "http://10.10.10.11/IISTest_vd/sscesa20.dll"; // the HTTP connection
repl.InternetLogin = "ShayTal"; // my windows login
repl.InternetPassword = "Shay"; // my windows password
repl.Subscriber = "POCKETPC"; // not sure about this one, where do i specify it ?
strDataSource = "ShayDB.sdf"; // a CE local filename
ssceConn.ConnectionString = "Data Source=" + strDataSource;
repl.SubscriberConnectionString = "Provider=Microsoft.SQLSERVER.OLEDB.CE.2.0;Data Source="      + strDataSource;
0
 
LVL 22

Expert Comment

by:_TAD_
Comment Utility


this looks correct so far.

Now we are going to make a change to your machine.config file.

This is only a TEMPERARY change.  If you leave your config file like this, you open up a huge security hole that will let anyone into your system with or without proper credentials.

Search your computer and Open the machine.config file and change the processModel userName from "machine" to "SYSTEM" and try your code again.


As mentioned previously, this will open a security hole, but at least you will know if it is your credentials that are failing, or if it is something else.
0
 
LVL 22

Expert Comment

by:_TAD_
Comment Utility


Oh... the machine.config file is a .NET framework file that should be located on the computer with the IIS Server running on it.
0
 
LVL 3

Author Comment

by:Shay050799
Comment Utility
same error :-(
0
 
LVL 22

Expert Comment

by:_TAD_
Comment Utility


Okay... I've been going on the premise that everything has been set up correctly and that it was only your properties that may be in question.


Let's step back and make sure you IIS and SQL Server are configured properly.

This web page has a pretty decent walk through on how to set up IIS and SQL server so they talk to each other (and they will have to)

http://www.devbuzz.com/content/zinc_ss_ce_configuration_pg1.asp


If you've done this properly, then the security and permissions should be ok.


Since you mentioned that you've been fighting this for two days, I'm going to assume you've stumbled across a couple of these set-up pages...


So.... can your pocket PC access anything on that web server?  Does IIS serve up a simple web page to the Pocket PC?
(assuming yes)




Have you seen this page yet?
http://samples.gotdotnet.com/quickstart/CompactFramework/doc/sqlcemergereplication.aspx

This is more or less a wlk-through of exactly what you are trying to do.  Of course, it is not very helpfull when it comes to the values of the properties since they are all user entered.  Not to mention it doesn't even discuss making sure the computers are configured properly.

0
 
LVL 3

Author Comment

by:Shay050799
Comment Utility
i put index.html inside my new virtual directory where the SScesa20.dll resides, and i can see the page on my pocket pc..so i believe the IIS setup properly.

now about the SQL server i am not sure...
i created a new app using SqlCeRemoteDataAccess, i am getting an error "The OLE DB provider for SQL Server CE can not be initialized"
i suspect that they both the same problem

this is my code:
string rdaOleDbConnectString = "Provider=sqloledb; Data Source=SHAY_SQL;Initial Catalog=TESTDB;User Id=ShayTal;Password=Shay";  // this is windows log in

System.Data.SqlServerCe.SqlCeRemoteDataAccess rda =  null;
rda = new SqlCeRemoteDataAccess();
rda.InternetUrl = "http://10.10.10.11/IISTest_vd/sscesa20.dll";
rda.LocalConnectionString= "Provider=Microsoft.SQLSERVER.OLEDB.CE.2.0;Data Source=ssce.sdf";
rda.InternetLogin = "ShayTal";
rda.InternetPassword = "ShayTal";
try
{
 rda.Pull("Table_1", "Select * from   Table_1",rdaOleDbConnectString,RdaTrackOption.TrackingOnWithIndexes, "ErrorTable");
}

what do you think ?
0
 
LVL 22

Expert Comment

by:_TAD_
Comment Utility


string rdaOleDbConnectString = "Provider=sqloledb; Data Source=SHAY_SQL;Initial Catalog=TESTDB;User Id=ShayTal;Password=Shay";  // this is windows log in


This should not be a windows log-in.  This should be a database account.


by the time you execute this connection string you should already be athenticated as a valid user on the database server

try code more like this:

string rdaOleDbConnectString = "Provider=sqloledb; Data Source=SHAY_SQL;Initial Catalog=TESTDB;User Id=sa;Password=";  //sa account and password
0
 
LVL 3

Author Comment

by:Shay050799
Comment Utility
i tried that also i got the same error :-(
0
 
LVL 22

Expert Comment

by:_TAD_
Comment Utility


InternetLogIn gives you access to the computer the database is on.

UserId and password in the connection string gives you access to the database.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 3

Author Comment

by:Shay050799
Comment Utility
i go to my sql server manager, the if i right click on it, and choose general tab, the connection one uses Windiws Authentication.

i tried to change it to SQL server authentication, i can not find any matching username and password
0
 
LVL 22

Expert Comment

by:_TAD_
Comment Utility

okay... this connection string is what worked for me

PC-02-49 is the name of my computer
sa is the system administrator account for sql server on my computer
Northwind is obviously the name of the database
and my provider has to be SQLOLEDB.1 (all upper case)

                  OleDbConnection oconn = new OleDbConnection("Provider=SQLOLEDB.1;Initial Catalog=Northwind;Data Source=PC-02-49;User Id=sa;password=");
                  oconn.Open();
                  Console.WriteLine("DB OPENED");
0
 
LVL 22

Expert Comment

by:_TAD_
Comment Utility


You may have to create a new user.

Using SQL Server Enterprise Manager drill down to the Master Database and select "users"  create a new user that does not use windows authentication, but instead has its own sqlserver password.  

creating "sa" user with a blank password is usually a pretty good start.

You then want to give your user system administrator access and grant access to your databases
0
 
LVL 22

Expert Comment

by:_TAD_
Comment Utility


If possible, make your new user the dbo (data base owner)
0
 
LVL 3

Author Comment

by:Shay050799
Comment Utility
i do not have the reference OleDbConnection
it suppose to be in System.Data.OleDb, i can not find it on my system
0
 
LVL 22

Expert Comment

by:_TAD_
Comment Utility


Almost forgot.. you are trying to make this connection using the compact framework.


The oleConnection object is something like OleRemoteConnect...??? or something like that.  It's been long enough since I've done it that I'll need to look it up
0
 
LVL 3

Author Comment

by:Shay050799
Comment Utility
hey man,
ok i got this far..i think i am connecting, i am getting a different error message now..
ever seen any of those ?

"initializing sql server reconciler has failed !!!"

Shay
0
 
LVL 22

Expert Comment

by:_TAD_
Comment Utility


well...  At least you are getting an error message now.

Have you registered your dll yet?


Start> Run> regsvr32 "<fullPathOfDll>"

0
 
LVL 3

Author Comment

by:Shay050799
Comment Utility
twice :-)
0
 
LVL 22

Expert Comment

by:_TAD_
Comment Utility


Have you seen this site?
http://www.dbforums.com/t926219.html

I'm not sure how pertinent it is to your exact problem, but the question is:

<---------->
I can´t merge Replication on my PocketPC. (MS SQL Server
2000 - MS SQLCE 2.0 Server)

When I try to synchronize(), I obtain the next error:
Initializing SQL Server Reconciler has failed.[,,,,]
HRESULT:-2147198719

I have registered sscesa20.dll in my shared virtual folder
and restarted IIS, but no success.
Can anyone help me, please?
<---------->

And it has a resolution!

maybe it can help....
0
 
LVL 22

Accepted Solution

by:
_TAD_ earned 500 total points
Comment Utility


I read through the thread and it appears as if the issue is an IIS/ SQL Server permissions thing.  Take a look and make sure that all of your set-ups match what they are describing.

We are definately making progress... at least the error messages are changing.

:-)
0
 
LVL 3

Author Comment

by:Shay050799
Comment Utility
been there done that :-)
i think its a known problem, i think a quick call to microsoft will solve our problem...
hey appreciate your time and help :-)
0
 
LVL 22

Expert Comment

by:_TAD_
Comment Utility


Here are the properties that they are using to log in:


repl.Publisher = "MHPA22AC"; // <SQL Server Name>
repl.PublisherLogin ="sqlaccount";
repl.PublisherPassword = // sqlaccountpass

repl.InternetLogin = "cristina";
repl.InternetPassword = //my pass;

repl.Subscriber ="CEDevSuscription";

repl.Distributor= "MHPA22AC"; // <SQL Server Name>
repl.DistributorLogin="sqlaccount";
repl.DistributorPassword= // sqlaccountpass
0
 
LVL 22

Expert Comment

by:_TAD_
Comment Utility


I'm glad I could be of help... (sorta)

if nothing else, at least I learned something as well.
0
 

Expert Comment

by:bobraider
Comment Utility
Shay, I'm having the same problem you were.  I've done everything I know to get past it and I'm still coming up with the blank exception.  How did you fix it?  Any help would really be appreciated.

-Bob

bobraider@hotmail.com
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduction This article series is supposed to shed some light on the use of IDisposable and objects that inherit from it. In essence, a more apt title for this article would be: using (IDisposable) {}. I’m just not sure how many people would ge…
This article describes a simple method to resize a control at runtime.  It includes ready-to-use source code and a complete sample demonstration application.  We'll also talk about C# Extension Methods. Introduction In one of my applications…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

762 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

9 Experts available now in Live!

Get 1:1 Help Now