RDA Pull method not creating tables/inserting data

I can't see what is going on, this is the situation:

I call the Pull method, specify the table to be affected, the query to be used, the connection string to the remote SQL server, the tracking options (On) and the Error table. The pull method executes with no errors however, no table is ever created. I don't know why, here's what I have done so far:

I read the SQL BOOKS ONLINE help on preparing RDA, I set up the IIS virtual directory for anonymous access and on the connection string I send in the user name and password for the SQL server, I went into the SQL Server and grated access to the user name to the database that I am going to access and I made the user a db_owner.

So, according to SQL BOOKS ONLINE I have everything right however, it won't populate, so right now I am open to suggestions on how to get this to work, heres the code:
---------
string rdaOleDbConnectString = "Provider=SQLOLEDB;Data Source=<Server>;Initial Catalog=<DB>; User Id=<User>;Password=<Password>"; (it's not exactly like this, but in it has the proper values)
string connectionString = "Data Source=\"\\Program Files\\client\\db\\MobileDB.sdf\"";

SqlCeRemoteDataAccess rda = new SqlCeRemoteDataAccess("http://10.1.1.206/mobile/sqlcesa30.dll",
connectionString);

IList _tableNames = new ArrayList();
IList _queries = new ArrayList();

############
Code that prepares tables and queries
############

for (int counter = 0; counter < _tableNames.Count; counter++)
{
rda.Pull(_tableNames[counter].ToString(), _queries[counter].ToString(), rdaOleDbConnectString, RdaTrackOption.TrackingOn, "MobileError");
}

the For loop runs with no problems but no data is ever put (or tables created) into the Mobile DB.

A few things here -

1. from pocket internet explorer on your device or emulator, do you get a correct diagnostic message when you use the url http://10.1.1.206/mobile/sqlcesa30.dll ? if not, then your first issue is you aren't successfully getting from device to the server.

2. your connection string is screwed up. try this instead: connectionString = @."Data Source = \Program Files\client\db\MobileDB.sdf"

3. you do realize that the database must exist already before the pull? and that none of those tables can exist when you call pull? (you need to drop all the tables you want to pull from the server before you call rda.Pull()

4. the values you are using for <user> and <password> must represent a valid SQL Server login as well as have permissions on the specific database you are pulling from <DB>

5. what are you using for primary keys on the tables you are pulling? IDENTITY columns are going to get you into trouble with multiple users pulling with tracking turned on. better to switch to uniqueidentifiers (GUIDs).

Try that much and let me know.

Darren

|||

To answer your questions:

1. I get "SQL Server Mobile Server Agent 3.0" when I try to browse to the URL

2. I will try it without the quotes.

3. Yes, I know the tables must not exists prior to PULL

4. I had made the SQL user a db_owner

5. GUIDS

|||

It did not work, I changed the connection string from:

"Data Source=\"\\Program Files\\client\\db\\MobileDB.sdf\"";

to

"Data Source=\\Program Files\\client\\db\\MobileDB.sdf";

and it still didn't work

|||

if I understand correctly, you are not getting an exception, you're just not getting the tables created in the SQL mobile database after the pull? Are you just using a SELECT * FROM statement to pull each table? Maybe try reducing this to pulling a single table in case there is some issue with your logic to iterate through a collection of tables pulling each one. Also - not knowing the schema of the server side db, are there any constraints on those tables?

-Darren

|||

I just remembered something that I bet is your issue - when you install SQL Server, you have to specify an authentication mode for the server. By default, it is Windows Authentication. In your RDA connection string, you are using SQL Server authentication. As a result, you need to make sure your instance of SQL Server is set to "Mixed Mode (Windows Authentication and SQL Server Authentication).

Hope that helps.

Darren

|||Thanx, I'll try that|||we decided to give merge replication a shot, we may comeback to trying RDA later.