Comments
Sort by recent activity
I'm not sure I fully understand the explanation of why this won't work with SQL Server 2005. Did you try the same test with a SQL Server 2005 database? I would be very interested to see the results as I have the option to switch to SQL Server 2005 at my web hosting company.
Thanks,
Ben / comments
I'm not sure I fully understand the explanation of why this won't work with SQL Server 2005. Did you try the same test with a SQL Server 2005 database? I would be very interested to see the resul...
Thanks Jeff, that could work for me. Like you say, it's not ideal, but at least I could generate a SQL script to make the schema changes.
Unfortunately, it doesn't work for SQL Data Compare, which I like to use to get a local copy of the data to work with. SQL Data Compare also seems to need access to the master database.
I'm really lost without these tools. I've got so used to these tools being part of my development process that it's REALLY hard to lose them.
Ben / comments
Thanks Jeff, that could work for me. Like you say, it's not ideal, but at least I could generate a SQL script to make the schema changes.
Unfortunately, it doesn't work for SQL Data Compare, which...
Thanks for the help. I guess I'll move to a different hosting company that gives me SQL Server 2005. / comments
Thanks for the help. I guess I'll move to a different hosting company that gives me SQL Server 2005.
So if I move to hosting with a SQL Server 2005, then I'll only need permissions on my own database?
I'm sure there are technical reasons that you need to access the master database, but I can see why some web hosting companies would not want to give me any permissions to the master database. Hopefully moving to 2005 will help.
Thanks,
Ben / comments
So if I move to hosting with a SQL Server 2005, then I'll only need permissions on my own database?
I'm sure there are technical reasons that you need to access the master database, but I can see w...
Thanks for the update. The explanation makes sense, though I wonder why people would want to put their data in an inconsistent state. I'm synchronizing all the tables with this batch file:
SQLDataCompare.exe /server1:Production /database1:mydatabase1 /username1:myusername1 /password1:mypassword1 /server2:localhost /database2:mydatabase2 /out:DataCompareResults.txt /scriptfile:DataCompareSynchronize.sql /force /options:Default /synchronize > DataCompareOutput.txt
Some of the tables are lookup tables, where none of the data is changing day to day. I wonder if these qualify as "not being migrated".
Anyway, thanks for all the help. Despite my moaning about this issue, SQL Compare and SQL Data Compare are great products that have become an indispensible part of my development process.
Ben Mills / comments
Thanks for the update. The explanation makes sense, though I wonder why people would want to put their data in an inconsistent state. I'm synchronizing all the tables with this batch file:
SQLDat...
Hi Brian,
I don't see how this is by design. Surely a tool to sync data shouldn't be changing the schema in any way.
The problem for me is that I use a modelling tool (Dezign for Databases) to generate new versions of the schema into an empty database. I then sync that database with my development copy to get the schema changes (whilst leaving the data in place in the development copy). The tool creates the foreign keys without the WITH NOCHECK option and then SQL Data Compare is trying to set the WITH NOCHECK option. The result is a battle between one tool trying to remove the option and the other trying to add it.
I can ignore these differences, but I think that this is a bug that should be fixed in the next version.
Thanks for the help,
Ben / comments
Hi Brian,
I don't see how this is by design. Surely a tool to sync data shouldn't be changing the schema in any way.
The problem for me is that I use a modelling tool (Dezign for Databases) to gen...
Hi Brian,
I realize this is a strange one and seems to be hard to reproduce, but this has only started since I switched to SQL Compare 5. Here's what I'm going to do now:
1. I ran a compare using the UI and saw the differences.
2. I ran the sync.
3. I closed the UI and reopened.
4. I ran another compare and everything matches.
5. I will not touch the schema of either database.
6. Let's see what happens tonight when I run the compare and sync script.
Thanks for taking the time to look into this.
As I said, I realize this is a hrad one for you to reproduce.
Ben / comments
Hi Brian,
I realize this is a strange one and seems to be hard to reproduce, but this has only started since I switched to SQL Compare 5. Here's what I'm going to do now:
1. I ran a compare using...
I think I may have figured out what the problem is. Here's my sequence of events:
1. Compare and sync A with B using SQL Compare.
2. Compare schemas again and everything matches.
3. Compare and sync DATA in A with B using SQL Data Compare.
4. Compare schemas again and there are WITH NOCHECK discrepancies.
It seems that SQL Data Compare is causing schema changes. I seem to remember reading that SQL Data Compare has a new algorithm that disables the foreign keys before copying data. It seems that it is not reenabling them to their original state.
Ben / comments
I think I may have figured out what the problem is. Here's my sequence of events:
1. Compare and sync A with B using SQL Compare.
2. Compare schemas again and everything matches.
3. Compare and...
Thanks for offering to take a look at this, but I'm able to send our database snapshot to you (company policy). As I said, I'm dealing with this issue by using the IgnoreWithNocheck option. I just wanted to make you aware of it.
Thanks,
Ben / comments
Thanks for offering to take a look at this, but I'm able to send our database snapshot to you (company policy). As I said, I'm dealing with this issue by using the IgnoreWithNocheck option. I jus...
I'm using SQL Compare 5.1.0.8. The databases are SQL Server 2000.
Here is a snippet of the HTML report generated by the command line. The left side (database 1) is the remote SQL Server at my hosts. The right hand side (database 2) is the local SQL Server on our network.
-- Foreign Keys <> -- Foreign Keys
ALTER TABLE [dbo].[AdditionalHost] ADD CONSTRAINT [FK_AdditionalHost_Site] FOREIGN KEY ([SiteID]) REFERENCES [dbo].[Site] ([SiteID]) <> ALTER TABLE [dbo].[AdditionalHost] WITH NOCHECK ADD CONSTRAINT [FK_AdditionalHost_Site] FOREIGN KEY ([SiteID]) REFERENCES [dbo].[Site] ([SiteID])
When I look at the foreign keys in Enterprise Manager, the "Check existing data on creation" is NOT checked for either database. I'm not sure if this is the checkbox that corresponds to the WITH NOCHECK option. / comments
I'm using SQL Compare 5.1.0.8. The databases are SQL Server 2000.
Here is a snippet of the HTML report generated by the command line. The left side (database 1) is the remote SQL Server at my hos...