Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL CE Replication

Posted on 2003-11-11
29
Medium Priority
?
1,955 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 17
  • 11
29 Comments
 
LVL 22

Expert Comment

by:_TAD_
ID: 9725984


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
ID: 9726056
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_
ID: 9726231


 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 3

Author Comment

by:Shay050799
ID: 9726262
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_
ID: 9726341


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
ID: 9726390
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_
ID: 9726588


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_
ID: 9726593


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
ID: 9726692
same error :-(
0
 
LVL 22

Expert Comment

by:_TAD_
ID: 9726848


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
ID: 9726893
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_
ID: 9726916


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
ID: 9726922
i tried that also i got the same error :-(
0
 
LVL 22

Expert Comment

by:_TAD_
ID: 9726924


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
 
LVL 3

Author Comment

by:Shay050799
ID: 9726941
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_
ID: 9727014

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_
ID: 9727045


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_
ID: 9727050


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

Author Comment

by:Shay050799
ID: 9727183
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_
ID: 9727257


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
ID: 9735245
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_
ID: 9745585


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
ID: 9748736
twice :-)
0
 
LVL 22

Expert Comment

by:_TAD_
ID: 9749993


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 2000 total points
ID: 9750023


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
ID: 9750030
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_
ID: 9750037


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_
ID: 9750046


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

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

Expert Comment

by:bobraider
ID: 10434731
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction Although it is an old technology, serial ports are still being used by many hardware manufacturers. If you develop applications in C#, Microsoft .NET framework has SerialPort class to communicate with the serial ports.  I needed to…
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

618 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