?
Solved

How to Modify Table Schema In Visual Studio IDE for Microsoft Sync Framework Tracked Table

Posted on 2014-12-01
14
Medium Priority
?
174 Views
Last Modified: 2014-12-12
I have a SQL Server CE 3.5 database which I have synced to a SQL Server 2008 Express database. I need to modify one of the tables. Is there any way to do this without scrapping the synchronization structure, modifying the table in both databases and starting over?
0
Comment
Question by:rkulp
[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
  • 7
  • 7
14 Comments
 
LVL 96

Expert Comment

by:Bob Learned
ID: 40476210
If you have SQL Server Management Studio installed, you should be able to connect to the SQL Server CE 3.5 database file.  You might be able to use the Server Explorer in the Visual Studio.
0
 
LVL 1

Author Comment

by:rkulp
ID: 40481592
Hi, Bob. I finally got to try using the management studio. I get an error parsing both
ALTER TABLE Items
SET CHANGE_TRACKING = OFF;

Open in new window

and
ALTER TABLE Items
DISABLE CHANGE_TRACKING;

Open in new window


Inside the IDE, I get an error stating that ALTER TABLE is not supported. I expect I'll have to delete the data sources and rebuild that part of the code. It is a pain but probably quicker than continuing to work with this. I just hoped I could get something that would save time on other projects if the db had to be changed .
Thanks for your help. Any other suggestions would be appreciated.
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 40481644
"I get an error stating that ALTER TABLE is not supported."
Did you mean inside SSMS?

Can you explain how you are using the Sync Framework?
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 1

Author Comment

by:rkulp
ID: 40481887
"Inside the IDE" means inside Visual Studio. Inside SSMS is where I get the errors above. What I am doing is syncing the CE database with a SQL Server Express database on another computer as follows:
    Private Sub btnSynchronize_Click(sender As System.Object, e As System.EventArgs) Handles btnSynchronize.Click
        Try
            Dim HHGCMSSyncAgent As New HHGCMSSyncAgent
            Dim HHGCMSSyncStat As Microsoft.Synchronization.Data.SyncStatistics = HHGCMSSyncAgent.Synchronize()
            MsgBox("Synchronization Successful")
        Catch ex As Exception
            gMsg = "Synchronization Error: " + vbCrLf + ex.Message
            MsgBox(gMsg, MsgBoxStyle.Exclamation + MsgBoxStyle.OkOnly, "Synchronization Error")
        End Try
    End Sub

Open in new window

0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 40482975
What version of Visual Studio are you using (i.e. Visual Studio 2013 Ultimate)?

I work more with Oracle these days, but that looks like the wrong syntax for table change tracking.

Enable and Disable Change Tracking (SQL Server)
http://msdn.microsoft.com/en-us/library/bb964713.aspx

Database:
ALTER DATABASE AdventureWorks2012
SET CHANGE_TRACKING = ON
(CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON)

Open in new window


Table:
ALTER TABLE Person.Contact
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = ON)

Open in new window

0
 
LVL 1

Author Comment

by:rkulp
ID: 40483373
For this project I'm using VS 2010 because VS2013 does not support SQL Server CE.
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 40483449
That is interesting, since I am using SQL Server Compact 4.0 with VIsual Studio 2013 Ultimate.
0
 
LVL 1

Author Comment

by:rkulp
ID: 40483554
That is indeed interesting. When I tried to create the project with VS 2013 I could not do it. I searched online and found comments that is was not supported. I'll try again. For this particular project it really doesn't matter which one is used since it doesn't use any features from VS 2013.
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 40483864
"When I tried to create the project with VS 2013 I could not do it."
What type of project did you try to create?
0
 
LVL 1

Assisted Solution

by:rkulp
rkulp earned 0 total points
ID: 40483994
Windows Forms Application. It would not let me connect to a CE database using the Add DataSource wizard.
I have solved the problem of disabling change tracking based on this post:

http://stackoverflow.com/questions/2834542/help-with-sqlcechangetracking

I added a button that let me disable or enable change tracking based on whether it was enabled. I ran the program in the IDE, exited, changed the columns I needed to change (spelling error that made it not the same as the Express db) and then enabled tracking. These were two columns I had to add to the database to meet a user request.

I still have to rebuild my dataset since there was a change in the database. I don't know how to add columns to an existing dataset and am not sure it can be done. I really don't want to mess around in the designer. Doing that part over seems the easiest way.

Thanks so much for your help.
0
 
LVL 96

Accepted Solution

by:
Bob Learned earned 2000 total points
ID: 40485075
I am using SQL Server Compact Edition 4.0 with Entity Framework 6 and Code First database with migrations.

Code First with SQL CE
http://www.codeproject.com/Articles/680116/Code-First-with-SQL-CE

Entity Framework “Code First + Migration” 101
http://lancelarsen.com/entity-framework-code-first-migration-101/
0
 
LVL 1

Author Comment

by:rkulp
ID: 40485433
Thanks for the links. I'll have to study up on Entity Framework since I've not used it before. This is a good time to start the project over and incorporate things I've not used before. Thanks so much for your help
0
 
LVL 1

Author Closing Comment

by:rkulp
ID: 40495783
Although over the years I've given Bob numerous opportunities to think "What a dummy" he has always been courteous and professional. It is one thing to be smart and knowledgeable and quite another to be very nice. Thanks you, Bob.
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 40496325
Bob doesn't have a long enough memory to remember all the people, but there are some, so don't think he is a saint.  He always tries to treat most people with respect (some don't deserve it).

Thanks,
Bob
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

752 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