Comments
Sort by recent activity
Thank you very much Chris and Michelle! The workaround you gave works just fine and has helped me avert a crisis. It's now extra steps in my version upgrade process, but I can live with it for now.
Hopefully the developers will approve the feature request to force scripts to a specific SQL Server version. I understand the compatibility problems involved, but those of us who needs this feature wouldn't attempt to sync an older database to a newer database which contains object types that are not supported in the target database. We understand these limitations and would live with the warning and error messages produced in the script generation. Please do consider adding this feature. Thanks again for your help. / comments
Thank you very much Chris and Michelle! The workaround you gave works just fine and has helped me avert a crisis. It's now extra steps in my version upgrade process, but I can live with it for no...
Jonathan Watts wrote:
We will consider a force SQL 2000 or SQL 2005 syntax option for a future version of SQL Compare, but as SQL Compare 6 has almost reached code lock off, this feature will not make the initial release.
I also just got bit by this problem too. Our database is set to compatibility mode 8.0 (2000) because many of our clients are still running SQL 2000. I just upgraded my PC to Win7, so I could no longer run SQL 2000. I therefore moved my staging and live databases to SQL 2005, but kept the the compatibility mode of both databases set to 8.0. On the last version update script I made (with SQL Compare 8 ) and deployed to our clients, all of them still on SQL 2000 get this DROP INDEX syntax error.
Please don't say I have to install and run SQL 2000 inside Windows Virtual PC just to create these SQL Compare scripts that will work for our SQL 2000 clients. You guys REALLY NEED to change SQL Compare to respect the compatibility mode settings of the databases, in spite of the minor concerns you mentioned.
In the meantime, is there anything I can do? Right now, I and my SQL 2000 clients are screwed. Thanks. / comments
Jonathan Watts wrote:
We will consider a force SQL 2000 or SQL 2005 syntax option for a future version of SQL Compare, but as SQL Compare 6 has almost reached code lock off, this feature will no...
Brian Donahue wrote:
I have logged a bug SC-6379.
Thanks Brian. Just to clarify, it's also adding an extra "dbo" as well the extra brackets. / comments
Brian Donahue wrote:
I have logged a bug SC-6379.
Thanks Brian. Just to clarify, it's also adding an extra "dbo" as well the extra brackets.
Hi Brian,
Any update on when the fix for this bug will be released?
Thanks,
John / comments
Hi Brian,
Any update on when the fix for this bug will be released?
Thanks,
John
Brian Donahue wrote:
Hello,
Thanks for supplying all of the output from SQL Compare... would it be possible to script a scenario that caused this problem to happen? I can see where the problem occurs in your deployment but I can't figure out how to reproduce it so I can submit a proper bug report.
I'll see what I can do. It may take some time for me to set this up. / comments
Brian Donahue wrote:
Hello,
Thanks for supplying all of the output from SQL Compare... would it be possible to script a scenario that caused this problem to happen? I can see where the problem o...
Here is a simple scenario for you to reproduce this error. Hope this helps. -- Create identical TestA and TestB databases
create database TestA ;
go
use TestA ;
go
create default [dbo].[empty_number] AS 0 ;
go
create table dbo.Table1
(
column1 char(10) not null,
column2 int not null
) ;
execute sp_bindefault N'dbo.empty_number', N'dbo.Table1.column2' ;
go
create database TestB ;
go
use TestB ;
go
create default [dbo].[empty_number] AS 0 ;
go
create table dbo.Table1
(
column1 char(10) not null,
column2 int not null
) ;
execute sp_bindefault N'dbo.empty_number', N'dbo.Table1.column2' ;
go
-- Now drop the "column2" in Table1 in the TestA database.
use TestA ;
go
execute sp_unbindefault N'dbo.Table1.column2' ;
go
alter table dbo.Table1 drop column column2 ;
go
-- Now use SQL Compare 10 to generate a script to make TestB match TestA. Be sure to
-- check the "Add object existence checks" option before generating the script.
-- Run the generated script on the TestB database and see the error.
/ comments
Here is a simple scenario for you to reproduce this error. Hope this helps.-- Create identical TestA and TestB databases
create database TestA ;
go
use TestA ;
go
create default [dbo].[...
Thank you Chris and Brian. I ran a couple tests and it works perfectly. I appreciate your quick turnaround on providing a patch. / comments
Thank you Chris and Brian. I ran a couple tests and it works perfectly. I appreciate your quick turnaround on providing a patch.
Thanks Brian. The thing is, the relation does exist because it was re-created further up the script. The NOCHECK option on the ADD CONSTRAINT applies only to checking for conforming data. The constraint still remains enforced. It needs to be marked as unenfoced, but the IF NOT EXISTS is preventing it. So the "ALTER TABLE [dbo].[cartrule] NOCHECK CONSTRAINT [FK_cartrule_items] " line to disable enforcement does not get processed. When I manually remove the IF NOT EXISTS lines and let the ALTER TABLE NOCHECK CONSTRAINT lines get processed, they databases now match. When I generate a deployment script without these two new options enabled, there are no IF NOT EXISTS lines at all and the ALTER TABLE NOCHECK CONTSTRAINT lines are always processed. / comments
Thanks Brian. The thing is, the relation does exist because it was re-created further up the script. The NOCHECK option on the ADD CONSTRAINT applies only to checking for conforming data. The co...
Thanks for the response, Brian. Regardless of the reason to drop and re-add a foreign key, it is a reproducible bug that occurs when "Add object existence checks" and "Use DROP and CREATE instead of ALTER" options are both enabled. Non-enforced foreign key relations are erroneously made enforced after running the generated deployment script. As I pointed out above, the cause is the incorrect IF NOT EXISTS wrapper that is added to script, preventing it from specifying the NOCHECK option on the relations. When these two options are not enabled, the NOCHECK is properly specified, so the problem is definitely in these new options.
Hopefully this can be fixed with a patch release soon, as using these two new options can really screw up deployments. I'm just glad I caught this before I deployed it to my 200+ clients.
Thanks,
John / comments
Thanks for the response, Brian. Regardless of the reason to drop and re-add a foreign key, it is a reproducible bug that occurs when "Add object existence checks" and "Use DROP and CREATE instead ...