Duplicate Definition Error

conradakunga

Version 3.1.0.4829

Greetings,

I am suddenly getting the following exception when trying to check in.

RedGate.SQLSourceControl.Engine.SqlCompareException: A duplicate definition was found for the trigger [dbo].[PrivateEquityClients.DeleteTrigger]. Ensure that case sensitivity options are set correctly and all object creation scripts are valid. If the problem persists, contact our support. ---> #8rg.#7rg: A duplicate definition was found for the trigger [dbo].[PrivateEquityClients.DeleteTrigger]. Ensure that case sensitivity options are set correctly and all object creation scripts are valid. If the problem persists, contact our support. ---> #8rg.#7rg: A duplicate trigger name ([dbo].[PrivateEquityClients.DeleteTrigger]) has been found. This may occur if the SQL Server that you are registering is case sensitive but the case sensitive option is not set. ---> System.ArgumentException: An item with the same key has already been added.

I know for a fact that there is a single version of that trigger.

I have even ran this script to check for whitespace issues

SELECT 
* 
FROM 
sys.sysobjects a 
INNER JOIN sys.sysobjects b ON REPLACE(RTRIM(LTRIM(a.name)),' ','') = REPLACE(RTRIM(LTRIM(b.name)), ' ','') AND a.id <> b.id

What could be the problem?

jchesney

Hi!

I encountered the same error today when I modified a Trigger definition to include the Schema declaration on the trigger, and add the [] to name of the objects.

I fixed the issue in the Table definition in TFS (in your case I think it would be in the table definition for PrivateEquityClients). However, this does not fix the key problem.

To fix it, I did the following:
1. I openned the Setup tab for SQL Source Control
2. Holding down the Ctrl and Shift buttons, I right clicked on the Schema directory, and selected Open Working Base Location
3. In the Tables directory, I found the table script that contains the trigger definition, and hand editted the script. Note that you will need to clear the Read-Only attribute on the file.

Once I had done this, the comparison worked. What doesn't make sense to me is why SSC uses its own AppData folder for the schema even though during the setup you need to fill in the directory where the TFS workspace puts the Schema.

I hope this helps you!

j.

adriancearnau

I think I can replicate this problem easily.

Every time I reorder the columns in a table with triggers and then commit a migration script with the modifications, the table definition SQL file gets modified and includes the same triggers twice.

If there are also trigger modifications caught in the migration script, the table definition file ends up containing both the previous and the current trigger definitions.

This is quite a nasty regression, I haven't had this problem in previous versions of SQL Source Control. Any idea where I could report this bug properly?

By the way, thanks a lot for the Ctrl+Shift tip, didn't know I can access the working base folder like that, always had to rummage through AppData before.

antiRev

I've had the same problem too, and I've been trying the SeparateTrigger option to see if that would help "SQL Source Control" realise its the same trigger, but setting the option to true doesn't actually seem to have any affect at all. BUT to be honest I'm not sure what the option is supposed to do?
Has anyone used this option?

pk_davidson

I am just now having this same problem pop up.
I'm trying the suggestions here but have no idea what I did that caused this to start happening.
I had modified the schema to add (5) new vchar fields and then committed the changes.

I had recently added SQL 2008 R2 to my dev machine and this was the first time I worked in SSMS since then. I was working in SSMS 2005 not the latest installed 2008. I have the 2008 engines all set to start manually only so they won't take up memory. we are still using 2005 but I needed 2008 R2 to look at some other servers in the organization.

Scary that it looks like there have been no RedGate responses to this yet and it's been posted for almost a month now.

EDIT- so I followed J.'s suggestions and was able to eventually clean up my source control version. It was a cascading problem. I would clean up one table definition then committ the changes from the working base (thank you THANK YOU for the right click trick, I would have been clueless without it). Then another audit trigger would should up with a duplicate definition in it in another table !

What was also very strange and very concerning was that for my table with the original problem, the apparent committ that hosed everything also dropped all of my Foreign Key relationships that pointed to the primary key in that table ! Fortunately, my local copy still had these so I just had to continually committ those changes back to the source control until it "stuck" and no more duplicate triggers showed up.

I too would sure like to know how to report this as a bug to redgate because it seems to be something fairly new and has happened to a handful of people now.

What say you redgate sysops ?
Any ideas as to what's causing this ?
mucking around in the working base leaves me feeling a bit uncomfortable.

CraigEddy

adriancearnau wrote:

I think I can replicate this problem easily.

Every time I reorder the columns in a table with triggers and then commit a migration script with the modifications, the table definition SQL file gets modified and includes the same triggers twice.

If there are also trigger modifications caught in the migration script, the table definition file ends up containing both the previous and the current trigger definitions.

This is quite a nasty regression, I haven't had this problem in previous versions of SQL Source Control. Any idea where I could report this bug properly?

By the way, thanks a lot for the Ctrl+Shift tip, didn't know I can access the working base folder like that, always had to rummage through AppData before.

Exact same problem here. Table with a trigger, added two fields, modified the trigger, and used a migration script to encapsulate the changes.

The issue isn't a SQL script error, it's an error in the processor that's creating the script. I suspect it's actually a SQL Compare issue...

pk_davidson

Just for clarity sakes:
I modified only my schema, only in one table. I did not touch the trigger files but the table does have two trigger files associated with it.
After cleaning up the first problem with the modified table (the code in the base had put in the two trigger creations twice), I then had to clean up another table which also had one trigger file that was in the base code twice.

Also, I didn't use a migration script, I was just committing changes directly from SSMS via SQL Source Control: Commit Changes tab.

CraigEddy

Here is what I did to get my environment back to a usable state:

1) After committing the change (mine was via a migration script), I went into the WorkingBases folder, found the modified table file (sort by date is easiest), made it not Read-only, edited the file to remove the extra trigger definition. Save the file.

2) Manually check out and perform the same edit on the file in actual TFS Tables folder. Check in this change.

3) In SQL Source Control, un-link my database. Ignore any errors that occur ("folder is not empty"). Close SSMS.

4) Re-open SSMS and link the database again.

CraigEddy

Didn't exactly work for me because the migration script seemed to have gotten "disconnected" from the object: when another user did a get-latest on the table it didn't pull the migration script, and the table's schema change failed. The migration script included SQL INSERT statements to properly populate the temporary table created with the new NOT NULL columns I added.

I guess because of the manual edit I performed on the .SQL file for the table (in TFS).

Bummer.

amumaugh

I updated the working base file, updated the SVN file, unlinked, relinked, resolved conflicts. This process added a couple of revisions but got rid of the error on the commit tab. I went to deploy using SQL Compare and got the error again on the checking dependencies step.

I think what's happening in my case is that the the table creation script has the faulty code where the trigger is defined twice and the deployment process is checking that file even though it isn't used in the deployment because it's covered by a migration script.

I don't know of any way to go back and modify files within a previous revision so I told the deployment to skip the migration script, copied the deployment script to SSMS and modified it manually with the neccessary change from the migration script.

NOTE: The migration script was fine but I had to tell SQL Compare to skip it anyway because the underlying object script (unused in deployment) was faulty for that revision

mmoore

Ok, well, I have tried most of what you guys have said, and I had to make a change to my trigger, guess what, my error is back. It put Duplicate names back in the SQL file it generates, causing the issue again.

Is there a way to roll back to the previous version, where this DID NOT happen to me!

Thanks

pk_davidson

Good question...
I assume you mean roll back your redgate install ?
You can get old versions at:
http://www.red-gate.com/supportcenter/G ... d_versions

But I'd suggest you contact support.
They may not be aware of this problem, since no one has chimed in on this thread...
And your products are tied together and then there's the question of what about having committed with the newer version ? Can you simply go back ?

Manfred.Castro

I think we've identified the issue at the root of these duplicate trigger issues - it's a bug we've logged in our internal bugtracking system under the reference SOC-4512. Here's how you can fix it - it's a bit of a chore, but it should clear up the issue.

1.) Make a note of the trigger throwing the error.

2.) Browse to the creation script for the relevant object directly in your source control repository - the folder structure that SQL Source Control creates is pretty self-explanatory

3.) Open the creation script and remove any and all duplicated CREATE statements. There should only be one CREATE statement for each trigger. Commit the changes to source control.

4.) Unlink and relink your database in SQL Source Control to rebuild the local Working Base. You should now be able to commit/get latest, unless ...

5.) .... it throws the same error for a different trigger, in which case repeat from step 1.

It seems that it's table rebuilds, like re-ordering columns that cause SQL Source Control to write these duplicate definitions. I'm pushing hard to get this bug fixed as soon as possible, because hand-editing the SQL statements in source control is a pain, and this is a showstopper bug, but this should at least get you up and running for the time being. I'll let you know as soon as there's an update on that bug.

CraigEddy

So I have encountered this issue again. I am using "Version 3.2.0.27 - March 13th, 2013" whose release notes specifically mention that this issue was fixed.

However, I encountered it again when I edited a table that has a trigger: inserting a column into the middle of the column list.

It seems to have to do with the affected tables also being in a migration script. I ended up having to delete the triggers AND the migration script before I could successfully sync my database to source control.

nhustak

It's adding two copies of the same trigger into the create code. One with the source table wrapped in [], one without. I am basically stuck and nothing I have read here lets me get by it.

BTW, I am using no migration scripts.

rick@syncbak

I'm experiencing the same problem today. I tried to link and synch source control & got the infamous "A duplicate definition was found for trigger..." error. I went into the existing source control file, found the duplicate, removed it, & checked in. I then committed outstanding changes, & the duplicate reappeared, obviously fabricated by Red Gate Source Control. So I manually removed the dupe again.

Please fix this Red Gate!


SQL Source Control 3.5.4.332
SQL Server 2012
SSMS 11.0.3128.0
MDAC 6.3.9600.16384
Microsoft MSXML 3.0 6.0
.NET Framework 4.0.30319.34003
Windows 8.1 Pro (6.3.9600)

jsreynolds1

I just started getting these same errors as well based on constraints.

Andy C S

Can you drop us an email at support@red-gate.com and quote the ticket number #14507? We'd be interested into looking into this problem in more detail to see if we can sort it out - thanks!

nhustak

Filed a support request - this problem really needs to get fixed. Been going on way too long.

A duplicate definition was found for the trigger [dbo].[trg_users_IDU]. Ensure that case sensitivity options are set correctly and all object creation scripts are valid. If the problem persists, contact our support.

pk_davidson

Well, here I am on version 3.7.0.2 and this is still happening to me.

It is now only occurring on my test server and fortunately not on production but still it's so very annoying.

What's even more annoying, I deleted the triggers from the database completely. I suspect (HOPE) this is an error from an old issue that was never cleaned up?

I'm hoping one more dive into workingbases and I never see this again?

freecell1

I just had this issue using SSC 3.7. Someone reordered the columns in a table and also edited the trigger in the same commit. No migration script. After that, no one else could get latest.

As a workaround, just having the other developers unlink and relink the database solved the problem.

Rob C

freecell1 wrote:

I just had this issue using SSC 3.7. Someone reordered the columns in a table and also edited the trigger in the same commit. No migration script. After that, no one else could get latest.

As a workaround, just having the other developers unlink and relink the database solved the problem.

Unlinking and relinking is usually a good place to start. If that doesn't work for some reason, you can always create a new database locally and do a "get latest" to get back to where you were.

grh395

Ha Ha - yes, thanks! I will remember the Ctrl+Shift tip now. So neat & simple. :idea: I never realised I could access the working base folder like that.

AlexMBanks

I'm still having this problem in 2018, on version 6.0.2.6908. No triggers involved, but getting the same duplicate error for an index instead.
There is no duplicate definition in the source code and we can commit and update an empty database with no issues. So when we rebuild the database from scratch (i.e. no data), it works. If there's data in the table then it fails.

For whatever reason, the CREATE INDEX code appears in one of the automatically generated AutomaticSchemaChange.patch files. I don't know if this is what is being picked up by SQL Compare.

RichardL

Hi @AlexMBanks

Thanks for your post. 

Just checking in to see if you're still experiencing this issue on Source Control? If you are you can always send us a ticket if you have a support contract so that a product specialist can help you with it. 

Kind regards

Richard Lynch. 

AlexMBanks

Hi @RichardL

Yes, I'm afraid we're still having this issue. We've worked around it a couple of times but I can't recall exactly what we did - I think we had to update to a particular revision, then update from there to HEAD. I'll try to duplicate the workaround and get in touch with the support team. We're looking to move to ReadyRoll soon anyway, but obviously we need to get our build stable first.

Regards,
Alex 

conradakunga

I finally isolated the problem. Or I think I did. 

I always got this problem when I pushed changes to a remote repository from within SSMS. 

I push from outside using command line or tortoisegit - and have yet to run into the problem 

Nique

I had an issue to this which Ive been pulling my hair out because of for the last 5 days.
I did contct support but they werent able to isolate the problem

The attached is a version of my solution which  may help others. It looks ugly as I've rendered this out of our documentation system

We use BitBucket and Sourcetree and, although your tools maybe sifferent, Im sure the process will be similar

Good Luck folks