Solved

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

Posted on 2014-12-01
14
155 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
  • 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
 
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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 500 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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Suggested Solutions

Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

706 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now