Last Friday I had a battle and won. I was working in VS 2010 on an internal project that was being targeted for .NET 3.5. Everything was going good until I decided to be fancy. The application pulled down several tables that contain mostly static stuff such as states, counties, municipalities, old orders, products, etc. In total, it’s around 1GB of data. I decided to modify the datasets to support offline access. I thought that would improve performance as the data would be available locally and not on the SQL server. Little did I realize, the application that accessed the data was setup to read the column definitions from the database to display in the application and omitted columns on the client side.
So, after ADO.NET Sync services was enabled on those 6 tables, the application displayed the LastEditDate and EditDate. This was obviously causing confusion and a few errors within the application. I determined that it had to be removed. However, since Visual Studio 2010 connected to it remotely to make the changes, there was nothing installed on the database server. Besides adding the two columns above to the database, each table had a phantom table added that looked like tablename_Tombstone. Having never setup the Sync services before, I started by removing the 2 columns from each of the 6 tables and the Tombstone tables from the database. However, errors were occurring even more often now. Totally confused, I installed Red Gate’s SQL Compare (Great product!!!). I ran a comparison between the database and one of the database backups. Bing!
The issue was that Sync Services creates triggers on each table to look at the inserts, updates, and deletes for each table and modifies the Tombstone table as needed. I then had to open up SQL Server 2000 Enterprise Manager (can’t believe that console is still legal) to find the triggers. Of course I had to Google, err I mean Bing it to find out how to manage triggers in SQL Server Enterprise Manager 2000. Everything is finally back to order again. Phew!