Relationship diagrams in SQL mobile 2k5

I looked in the manual as to how I should go about putting a realtionship diagram for my database. Unfortunatly i do not see the Diagram node in the object browser they are reffering to. Does anyone know how I can create a relationship diagram for my mobile database?

You can choose Add Data Connection in Visual Studio 2005, connect to your SQL Mobile database, and then use the Query Designer, choosing all of the tables you wish to see in your diagram. The diagram is presented right there in Visual Studio 2005.

Darren

Reinitializing subscriptions not working

Hi,

I'm doing merge sync between SQL Compact on mobile devices and SQL Server 2005. I recently made a schema change on the server and noticed that it caused an error on the subscribers when syncing. I fixed the schema problem, and did a reinitialize all subscriptions with a new snapshot. On the mobile devices I forced a reinitialisation (upload changes first). I'm still getting the same error message, which relates to a schema change which is no longer relevant to the server db and snapshot. For some reason the subscriber dbs are trying to reapply the original problem schema change even though they're supposed to be reinitializing to a new snapshot. I've even tried dropping the affected tables from the publication, and reinitializing all subscriptions with a new snapshot, but still the same error on the subscribers. Does anyone know why the subscriber dbs are still trying to apply a now defunct schema change (on tables which no longer exist in the publication)? Is there any way to flush this from the subscribers so they correctly pick up the new snapshot?

Regards,

Greg McNamara

Some additional information on the above:

The "rogue" schema change appears to be coming from the publisher, and not cached on the subscriber. The basic problem is that I'm trying to reinitialize subscribers, but it's trying to make a schema change on the subscribers which was made before the current snapshot was created. My understanding of reinitialization was that it would upload subscriber changes and then basically rebuild the subscriber db from the snapshot. Instead it seems to be applying incremental, historical schema changes on the subscriber.

Hope someone can help me with this.

Regards,

Greg McNamara

|||

And more info:

The schema change causing the original problem was the addition of a foreign key constraint. Records in a table were deleted on the server db before adding the constraint but the subscriber dbs still contain records. The sync is failing because it's trying to apply a constraint against existing records and failing. Reinitializing the subscribers is not fixing the problem (as detailed above). I decided to try deleting the records on the subscriber db before re-syncing. I now get a different error message on sync:

"Either the cursor is not on a row or there are no rows left"

Apparently this is a SQL Compact engine internal error. The malfunctioning reinitialization function seems to have caused this. I tried a repair/compact on the db but still get the same error message on syncing.

Back to the reinitialize not using the current snapshot, is anyone aware of places I should look to see if an old snapshot is being cached and somehow used by the merge sync agent?

Regards,

Greg McNamara

Registration of mobile data provider for SQLCE

Hi All,
I have just installed SQL Server Compact Edition (including the SDK) for use with Borland Developer Studio 2006. I have an ADOConnection component on my form, but the Provider I want (Microsoft.SQLSERVER.MOBILE.OLEDB.3.0) is not available. Typing in the connection string: "Provider=Microsoft.SQLSERVER.MOBILE.OLEDB.3.0;Data Source=C:\Program Files\Microsoft SQL Server Compact Edition\v3.1\SDK\Samples\Northwind.sdf..." results in an "Unspecified Error". I have checked the HKEY_CLASSES_ROOT\CLSID and the provider associated with SQLCE is not there.
I've read a couple of posts indicating that it should have been registered during install. Is this the case or do I have to do something more to register this provider?
I've checked the assembly (system.data.sqlserverCe - it seems OK) and re-registered sqlceolebd30.dll. I'm not sure what else needs to be done.
TIA,
Luke
Have you downloaded and installed this? http://www.microsoft.com/downloads/details.aspx?FamilyId=%2085E0C3CE-3FA1-453A-8CE9-AF6CA20946C3&displaylang=en|||Thanks for you reply Erik.
I have downloaded that msi, along with the SSCE31SDK-ENU.msi.
On the link that you specified the Additional Information states in part that " Installing SQL Server 2005 Compact Edition by running SQLServerCE31-EN.msi installs the provider (System.Data.SqlServerCe.dll) in the GAC (global assembly cache) and registers the OLEDB provider (sqlceoledb30.dll)."
For some reason that hasn't happened. I'll look into some other way of registering the provider.
Thanks again.
|||

You can test if it is an OLEDB provider problem by running this VBA code in Excel/word or similar:

Code Snippet

Sub test()

Dim conn As New ADODB.Connection
conn.ConnectionString = "Provider=Microsoft.SQLSERVER.MOBILE.OLEDB.3.0;Data Source=C:\Program Files\Microsoft SQL Server Compact Edition\v3.1\SDK\Samples\Northwind.sdf"
conn.Open

End Sub

(remember to add a reference to "Microsoft ActiveX Data Objects 2.8 Library")

If this works, it is a 3rd party problem...

|||Thanks Erik. I tried that in Excel and it worked so I figured it must be Delphi related, and it was.
For some reason I am not able to hook up the ADO components at design time - it raises an error. If I set the connection string at run time it works. I tried this yesterday with no luck, but this morning I uninstalled/reinstalled SQL Server Compact (with SDK and server tools) and the run time test worked, so I may well have done something with my original install that was causing me problems.