Relationships in Management Studio Express connected to SQL Server Compact Edition

Hello ,
Is possible to specify reationships between tables on SQL Server Compact Edition database in Management Studio Express ?

I can connect to database file , add tables, add data , but I dont't found the right methot to specify reationships as with SQL Server Express.

Any ideas?

Have you found an answer?

Gus

|||

You have to specify the relationships in the query analyzer, like this:

ALTER TABLE MyOrders ADD FK_CustOrder FOREIGN KEY (CustID) REFERENCES MyCustomers(CustID)|||Thanks Erik for the information. I know it can be done in code, I was hoping to find the complete table designer in the ce edition. I understand that it's not there. Apparently 'lightweight' refers not just to the product's functional capabilities, but also its development tools.|||Designing relationships visually will be available in the next version of SQL CE, version 3.5, included in Visual Studio "Orcas".sql

Relationships in Management Studio Express connected to SQL Server Compact Edition

Hello ,
Is possible to specify reationships between tables on SQL Server Compact Edition database in Management Studio Express ?

I can connect to database file , add tables, add data , but I dont't found the right methot to specify reationships as with SQL Server Express.

Any ideas?

Have you found an answer?

Gus

|||

You have to specify the relationships in the query analyzer, like this:

ALTER TABLE MyOrders ADD FK_CustOrder FOREIGN KEY (CustID) REFERENCES MyCustomers(CustID)|||Thanks Erik for the information. I know it can be done in code, I was hoping to find the complete table designer in the ce edition. I understand that it's not there. Apparently 'lightweight' refers not just to the product's functional capabilities, but also its development tools.|||Designing relationships visually will be available in the next version of SQL CE, version 3.5, included in Visual Studio "Orcas".

Relations

Maybe a stupid question but...

is it posible to add relations in sql compact databases (.sdf) ?

I tried in server explorer (in orcas) and there is no option for relations. Also .sdf file cannot be opened in SQL Server Express Management Studio SP2 because is version 3.5.

This SQL code will do the trick:

Code Snippet

ALTERTABLE EQMetaData ADDCONSTRAINT FK_Reference FOREIGNKEY([EQID])REFERENCES Equipments ([ID])

In version 3.5 there will be a GUI for modifying relations, but for now you must use SQL code.

sql

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

Reinitializing SQL Server 2005 CE subscriber results in a hang

I'm having an issue when I'm using merge replication between SQL Server 2000
sp4 and .NET client using SQL Server 2005 Compact Edition (v3.1). The client
is based on the sample code from MSDN library listed on the
SQLCeReplication.BeginReplication page that fires events to show each stage
of replication that happens.
The inital snapshot is pulled down and the database is created successfully.
When I change data in the SQL Server 2000 database, subsequent replications
work ok and the client successfully pulls down the changes.
However, when the subscription is reinitialized (either at the server, or
client), the app hangs. I believe it may be to do with the size of the
database as it works fine with smaller subsets of the data.
In my case, the app successfully completes the inital conversation with SQL
Server 2000 (the OnSynchronization event reports that SyncProgres is 100%)
and the PublisherChanges property says that there are over 2,000,000 changes
to be made. With an empty database, the app goes on to download each
published table in turn. However, when the subscription is marked for
reinitialization, the app just seems to hang - CPU is up to 90-95% and the
local database grows by 50% but then it does not seem to go any further.
Is this a known limitation with SQL Server 2005 CE? Are there any
workarounds, eg so can I identify in advance whether my subscription has been
marked for reinialization, so that I can drop my existing subscription
(deleting the database) and recreate it again using the latest snapshot?
Thanks
Stuart
Hi,
From your description, I understand that after your subscription was
initialized your application hanged. You would like to know if this was a
known limitation with SQL Server 2005 CE or if you can know in advance that
your subscription is in re-initialization.
If I have misunderstood, please let me know.
From my research, there is not a known limitation regarding this issue. We
can query its status via the stored procedure sp_helpmergesubscription,
however it cannot be judged whether or not a subscription is in
reinitializing. You may check whether or not this issue also occurs on
other subscribers with other SQL Server 2005 Editions.
Anyway I will try to consult the product team on this issue to get further
suggestions. Also I appreciate your understanding that this issue seems was
performance related. To track the root cause and workaround this issue,
dump analysis may be required, however this work can only be done by
Microsoft Customer Support Services (CSS). If I could not get an effective
answer from product team, effectively and immediately I recommend that you
contact CSS via telephone so that a dedicated Support Professional can
assist you in a more efficient manner. Please be advised that contacting
phone support will be a charged call.
To obtain the phone numbers for specific technology request please take a
look at the web site listed below.
http://support.microsoft.com/default.aspx?scid=fh;EN-US;PHONENUMBERS
If you are outside the US please see http://support.microsoft.com for
regional support phone numbers.
Best regards,
Charles Wang
Microsoft Online Community Support
================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
|||Hi Stuart,
This is a quick note to let you know that I have not got the response from
the product team. I will try to follow up them now. However since the
process may need a long time, I recommend that you leave me
(changliw_at_microsoft_dot_com) an email response so that I can timely
update you when I got the response. If this issue is urgent to your
business, I recommend that you contact CSS via telephone for the best
support.
Best regards,
Charles Wang
Microsoft Online Community Support
================================================== ===
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====

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.