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

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?
LVL 1
rkulpAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bob LearnedCommented:
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
rkulpAuthor Commented:
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
Bob LearnedCommented:
"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
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

rkulpAuthor Commented:
"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
Bob LearnedCommented:
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
rkulpAuthor Commented:
For this project I'm using VS 2010 because VS2013 does not support SQL Server CE.
0
Bob LearnedCommented:
That is interesting, since I am using SQL Server Compact 4.0 with VIsual Studio 2013 Ultimate.
0
rkulpAuthor Commented:
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
Bob LearnedCommented:
"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
rkulpAuthor Commented:
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
Bob LearnedCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rkulpAuthor Commented:
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
rkulpAuthor Commented:
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
Bob LearnedCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.