Database Change Management
Database Change Management
Home | Profile | Register | Active Topics | Active Polls | Members | Private Messages | Search | FAQ
Username:
Password:
Save Password
Forgot your Password?




 All Forums
 Support
 DB Ghost Support Forum
 Bug when "Checking for differences with triggers"
 New Topic  New Poll New Poll
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Dave


7 Posts

Posted - 02/15/2008 :  06:10:15  Show Profile Send Dave a Private Message  Reply with Quote
This bug may not come up for that many people as most people aren't crazy enough to use big triggers, but...

If the "target" db has triggers that work out to more than 1 syscomments row you get a "Invalid procedure call or argument" error from dbghost when comparing 2 dbs (or build and compare)

This does not happen if the Source db triggers are large, in that circumstances it works perfectly, giving correct compare functionality and everything, the problem is only big triggers in the target.

To replicate
1)a trigger with the same name must exist in source and target.
2)The target trigger can be anything even just huge comments, check in syscomments for the objectid and make sure it's at least 2 rows.
3)Run a compare, you can even have this compare just do a check for differences on triggers only
4)no fourth thing... it will now error

Dave

Edited by - Dave on 02/15/2008 06:13:07

Google AdSense

USA
Mountain View


leachm



125 Posts

Posted - 02/16/2008 :  04:09:20  Show Profile  Click to see leachm's MSN Messenger address Send leachm a Private Message  Reply with Quote
Hi Dave,

We're looking into this.

regards,

Malcolm Leach
You must be logged in to see this link.
DB Ghost Build, Compare and Synchronize = Change Management for SQL Server
Go to Top of Page

leachm



125 Posts

Posted - 02/18/2008 :  02:22:45  Show Profile  Click to see leachm's MSN Messenger address Send leachm a Private Message  Reply with Quote
Could you tell us what version of DB Ghost you are using please? We can't replicate the problem here.

regards,

Malcolm Leach
You must be logged in to see this link.
DB Ghost Build, Compare and Synchronize = Change Management for SQL Server
Go to Top of Page

Dave



7 Posts

Posted - 02/18/2008 :  05:51:10  Show Profile Send Dave a Private Message  Reply with Quote
I've got 4.5.2043 I'm pretty sure I downloaded it fresh a few days ago.

Sql wise, I'm running against SQL2000 SP4 on my local PC
Go to Top of Page

leachm



125 Posts

Posted - 02/18/2008 :  07:50:12  Show Profile  Click to see leachm's MSN Messenger address Send leachm a Private Message  Reply with Quote
This is the test script I am using - it creates two rows in syscomments for the trigger and DB Ghost reports the difference and synchronises it perfectly

- what is different about your setup?
- can you run this script and see if it works/fails for you?

create database BigTriggerSource
go
use BigTriggerSource
go
create table table1 (id int primary key)
go
create trigger trigger1 on table1 after insert as
BEGIN
/*

Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments
Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments
Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments
Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments
Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments
Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments
Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments
Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments
Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments
Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments
Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments
Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments
Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments
Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments
Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments
Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments
Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments
Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments
Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments
Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments
Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments
Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments
Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments
Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments
Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments
Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments
Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments
Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments
Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments
Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments
Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments
Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments
Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments
Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments
Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments
Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments

*/

select top 1 1 from syscomments

END

go

select * from sysobjects so
inner join syscomments sc on sc.id = so.id
where so.xtype = 'TR'
go

create database BigTriggerTarget
go
use BigTriggerTarget
go
create table table1 (id int primary key)
go
create trigger trigger1 on table1 after insert as
BEGIN
/*

--- this line is different

Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments
Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments
Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments
Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments
Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments
Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments
Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments
Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments
Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments
Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments
Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments
Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments
Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments
Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments
Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments
Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments
Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments
Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments
Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments
Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments
Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments
Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments
Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments
Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments
Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments
Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments
Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments
Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments
Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments
Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments
Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments
Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments
Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments
Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments
Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments
Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments Huge comments

--- this line is different too

*/

select top 1 1 from syscomments

END

go

select * from sysobjects so
inner join syscomments sc on sc.id = so.id
where so.xtype = 'TR'

Go to Top of Page

Dave



7 Posts

Posted - 02/18/2008 :  09:28:23  Show Profile Send Dave a Private Message  Reply with Quote
I run your script on my environment and when I try the compare it fails in exactly the same way.

SQL profiler shows me that this is what it's running:
use [BigTriggerSource]
go
set nocount on select su.name,so.name,sc.number,sc.text,object_name(so.parent_obj),objectproperty(so.id,'IsExtendedProc'),objectproperty(so.id,'ExecIsTriggerDisabled'),objectproperty(so.id,'IsExecuted'),objectproperty(so.id,'ExecIsFirstDeleteTrigger'),objectproperty(so.id,'ExecIsFirstInsertTrigger'),objectproperty(so.id,'ExecIsFirstUpdateTrigger'),objectproperty(so.id,'ExecIsLastDeleteTrigger'),objectproperty(so.id,'ExecIsLastInsertTrigger'),objectproperty(so.id,'ExecIsLastUpdateTrigger') from syscomments sc join sysobjects so on so.id=sc.id join sysusers su on su.uid=so.uid where so.xtype='TR' and objectproperty(so.id,'IsReplProc')=0 and objectproperty(so.id,'IsMSShipped')=0
go
set noexec off set parseonly off 
go
use [BigTriggerTarget]
go
set nocount on select su.name,so.name,sc.number,sc.text,object_name(so.parent_obj),objectproperty(so.id,'IsExtendedProc'),objectproperty(so.id,'ExecIsTriggerDisabled'),objectproperty(so.id,'IsExecuted'),objectproperty(so.id,'ExecIsFirstDeleteTrigger'),objectproperty(so.id,'ExecIsFirstInsertTrigger'),objectproperty(so.id,'ExecIsFirstUpdateTrigger'),objectproperty(so.id,'ExecIsLastDeleteTrigger'),objectproperty(so.id,'ExecIsLastInsertTrigger'),objectproperty(so.id,'ExecIsLastUpdateTrigger') from syscomments sc join sysobjects so on so.id=sc.id join sysusers su on su.uid=so.uid where so.xtype='TR' and objectproperty(so.id,'IsReplProc')=0 and objectproperty(so.id,'IsMSShipped')=0
go
set noexec off set parseonly off 
go


And when I run that myself in query analyser it all looks fine, but the db ghost compare tool still fails.

My SQL environment is SQL Server Developer Edition (8.00.2039(SP4)) with collation "SQL_Latin1_General_CP1_CI_AS"

Just checked windows update and everything is up to date so nothing exciting there either. I'll try putting those DBs on a real server and see if that makes a difference.
Go to Top of Page

leachm



125 Posts

Posted - 02/18/2008 :  09:33:54  Show Profile  Click to see leachm's MSN Messenger address Send leachm a Private Message  Reply with Quote
Actually I have reproduced this now by using an MSDE SP4 instance - I was using a SQL 2005 developer edition instance before (I should have read your previous post more clearly, sorry)

I'll get back to you when we have a fix for this.
Go to Top of Page

Dave



7 Posts

Posted - 02/18/2008 :  10:02:51  Show Profile Send Dave a Private Message  Reply with Quote
Cool, that's where I'd got up to... works against my 2005 instance, but not on the 2000 dev edition.
Go to Top of Page

Dave



7 Posts

Posted - 02/19/2008 :  03:35:42  Show Profile Send Dave a Private Message  Reply with Quote
Just took the testing 1 step further, doesn't work on SQL 2000 server version as well as developer edition.

Works fine in 2005 but we're not there yet.
Go to Top of Page

leachm



125 Posts

Posted - 02/25/2008 :  02:39:20  Show Profile  Click to see leachm's MSN Messenger address Send leachm a Private Message  Reply with Quote
Hi Dave,

This issue has been fixed in the latest live release (4.5.2045)

It can be downloaded from the member's area:

You must be logged in to see this link.

regards,

Malcolm Leach
You must be logged in to see this link.
DB Ghost Build, Compare and Synchronize = Change Management for SQL Server
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  New Poll New Poll
 Reply to Topic
 Printer Friendly
Jump To:
Database Change Management © Copyright 2005 Innovartis Ltd. Go To Top Of Page
Snitz Forums 2000
RSS Feed 1 RSS Feed 2
Powered by ForumCo 2000-2008
TOS - AUP - URA - Privacy Policy
ForumCo Free Blogs and Galleries
Signup for a free forum or Go Banner Free