Comments
Sort by recent activity
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 ...
Hi David,
Just curious if this has been scheduled for 9.5? We really need this feature. If it's just for server side code (stored procedures, functions), that's totally fine. Thanks! / comments
Hi David,
Just curious if this has been scheduled for 9.5? We really need this feature. If it's just for server side code (stored procedures, functions), that's totally fine. Thanks!
Thanks very much for the update, I appreciate it. Basically, I'd love a configuration setting which would change the header for new or altered stored procedures (and functions) from something like... CREATE PROCEDURE MyStoredProc
or
ALTER PROCEDURE MyStoredProc
to this... IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME = 'MyStoredProc' AND ROUTINE_TYPE = 'PROCEDURE') ;
DROP PROCEDURE MyStoredProc ;
GO
CREATE PROCEDURE MyStoredProc
Of course there may be additional permissions implications concerning this, but for me personally, I don't care. All permissions get rebuilt later on in a post-update section we append to the generated sql compare scripts. Thanks. / comments
Thanks very much for the update, I appreciate it. Basically, I'd love a configuration setting which would change the header for new or altered stored procedures (and functions) from something like...
David Atkinson wrote:
Is there any reason why you can 'recompare' with SQL Compare and generate a new script, rather than relying on the previously generated one?
We can, but it becomes quite tedious when you have over 200 clients and maybe only a dozen or so may have needed and applied service packs for updated sprocs. We post our SQL Compare version update scripts on our web site for clients to download and apply. These clients would now have deviated from the regular update path and managing them separately with special "recompares" just because they have some different code becomes a pain for us and them to manage. / comments
David Atkinson wrote:
Is there any reason why you can 'recompare' with SQL Compare and generate a new script, rather than relying on the previously generated one?
We can, but it becomes quite t...
I'd just like to add my enthusiastic +1 for this enhancement as well. Of course, we would need/use it only for textual objects (functions, sprocs, and sometimes descriptions). For our case, we sometimes need to provide new sprocs or functions to clients on older versions as a service pack fix. But when they later upgrade to the latest version of our database, we don't want the update scripts to fail because the objects already exist.
As far as permissions on dropped objects go, that is not an issue for us since our clients run a "post update" which rebuilds permissions on all objects anyway.
I really hope this enhancement can make it into 9.5 as you mentioned. Thanks! / comments
I'd just like to add my enthusiastic +1 for this enhancement as well. Of course, we would need/use it only for textual objects (functions, sprocs, and sometimes descriptions). For our case, we so...