Comments
Sort by recent activity
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...
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...
Thanks Jeff. That's a great idea. / comments
Thanks Jeff. That's a great idea.
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...
I spoke to the engineer at my web hosting company (aspwebhosting.com). They made it very clear that giving any user access to the syslogins table is a very bad thing that allows someone to see all the usernames. A hacker then only needs to guess the passwords for each login (he described it as giving someone half the combination to the safe). That really makes a lot of sense to me and I understand why they are not willing to give me that permission.
I really think that there should be an option that eliminates the calls to the syslogins table if I'm not trying to sync the logins. All I want to do is sync the tables and stored procedures.
Thanks,
Ben / comments
I spoke to the engineer at my web hosting company (aspwebhosting.com). They made it very clear that giving any user access to the syslogins table is a very bad thing that allows someone to see all...
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 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...
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...
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...