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.

Rapidly Changing Dimension

Hi All,
I'm trying to figure out how I would best model the following situation
:
I'm trying to model a retailing case which is fairly easy except for
one mind boggling thing (at least for me). I'm having a SKU dimension
of around 150.000 unique products which is already a SCD Type 2 for
some attributes. In addition I'm willing to track changes of the sales
and purchase price. However these prices change almost weekly for quite
a lot of these products leading to a huge dimensional table when using
type 2.
As this is a numerical attribute I'm thinking about putting it into the
fact table; However first of all my fact table will grow with a couple
of gigs (around 1 billion rows) and secondly as not every product is
sold every day I do not have the possibility to view price over a
period on a day to day basis.
A second option would be to have a separate fact table (and olap cube)
and making a linked measure for both prices. However I don't know how
to fetch the correct price in the basic cube when the price-cube does
not have the same granularity of the date dimension but more of a
start-end date structure.
Anyone some brilliant ideas? I ran out of mind juice on this one.Hello DePuurt,
I would put the sales price in the fact table, and use type 1 to track
the current price in the product dimension. This should solve the day
to day price reporting and give you the changes of sales price over
time.
I also put a post together ages ago on different forms of type 2
implementation.
Check out:
http://bi-on-sql-server.blogspot.co...r.blogspot.com/|||A good idea, certainly valid.
However, I still have the issue on reporting the sales price over a
period of time. I want to be able to give a full price history of a
specific product over time; even if the thing didn't sell at all;
Unless I do you a complete full blown type 2 on the product dimension
I'm still not able to do this. An option would be to split the price of
the product dimension and have a subdimension tacking it. This tracking
dimension would have the natural key and the start/end date and both
prices. Creating a join with the original product dimension would yield
the exact information BUT now it's not in the cube if I go for OLAP.
Actually it all comes down to the desired functionality. Having a big
dimension table is the meast desired option in the main cube (sales
analysis), but is actually achievable when I accept the performance
drop. A second option yielding the same functionality would be to
create a second cube on prices. This means building one on the product
dimension with tracking dimension on price and this for the same date
granularity as the main cube. Using the lookup function the user
wouldn't notice it and performance would not be hindered when running
SQL reports. MDX is actually still quite fast, so I can take a small
hit (llokup) there. The last thing would be to go for your option, thus
limiting the possibilities for reporting price over time.
Thanks a lot,
DP.|||Hi DePuurt,
150K rows in a dimension table is nothing to be worried about....on a
recent project we had a 20M row dimension table...LOL!
But you are seeing one of the problems with type 2 dimensions when they
change quickly.....one client of mine had 90M rows in his customer
dimension table linking to 6B rows in a summary fact table...obviously
every question was slowed down...
The answer is to maintain history for type 2 dimensions without
maintaining it in the type 2 dimension table...
We do this all the time for big clients.
Peter
www.peternolan.com