Duplicate Definition Error

A SQL Server Management Studio add-in to source control your database in Subversion or Team Foundation Server.

Moderators: Chris Auckland, David Atkinson, sherr, PhilScrace, andy.campbell.smith

Duplicate Definition Error

Postby conradakunga » Mon Nov 26, 2012 3:19 pm

Version 3.1.0.4829

Greetings,

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

Code: Select all
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

Code: Select all
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?
conradakunga
 
Posts: 1
Joined: Mon Nov 26, 2012 3:11 pm
Location: Kenya

Duplicate Definition Error

Postby jchesney » Tue Nov 27, 2012 10:38 pm

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.
jchesney
 
Posts: 1
Joined: Tue Nov 27, 2012 10:30 pm

Postby adriancearnau » Thu Dec 06, 2012 11:09 am

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.
adriancearnau
 
Posts: 1
Joined: Thu Dec 06, 2012 11:04 am

Postby antiRev » Thu Dec 06, 2012 11:55 am

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?
antiRev
 
Posts: 2
Joined: Thu Dec 06, 2012 11:50 am
Location: United Kingdom

Postby pk_davidson » Tue Jan 01, 2013 1:40 am

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.
pk_davidson
 
Posts: 9
Joined: Tue Nov 06, 2012 5:39 pm

Re:

Postby CraigEddy » Wed Jan 02, 2013 5:38 pm

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...
CraigEddy
 
Posts: 20
Joined: Fri Sep 28, 2012 2:59 pm

Postby pk_davidson » Wed Jan 02, 2013 7:10 pm

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.
Last edited by pk_davidson on Wed Jan 02, 2013 7:37 pm, edited 1 time in total.
pk_davidson
 
Posts: 9
Joined: Tue Nov 06, 2012 5:39 pm

Workaround...Post-update

Postby CraigEddy » Wed Jan 02, 2013 7:20 pm

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
 
Posts: 20
Joined: Fri Sep 28, 2012 2:59 pm

Workaround..Not

Postby CraigEddy » Wed Jan 02, 2013 7:21 pm

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.
CraigEddy
 
Posts: 20
Joined: Fri Sep 28, 2012 2:59 pm

Postby amumaugh » Wed Jan 09, 2013 8:41 pm

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
amumaugh
 
Posts: 13
Joined: Thu Jun 30, 2011 6:40 pm

Postby mmoore » Tue Jan 22, 2013 5:49 pm

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
Mark Moore
Software Engineer
NTS Data Services, LLC
mmoore
 
Posts: 14
Joined: Wed Jul 30, 2008 6:20 pm

Postby pk_davidson » Tue Jan 22, 2013 7:31 pm

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 ?
pk_davidson
 
Posts: 9
Joined: Tue Nov 06, 2012 5:39 pm

Postby Manfred.Castro » Tue Jan 22, 2013 8:30 pm

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.
Manfred Castro
Product Support
Red Gate Software
Manfred.Castro
 
Posts: 209
Joined: Mon Apr 23, 2012 2:49 pm

Still an issue even with version 3.2

Postby CraigEddy » Fri Apr 26, 2013 2:35 pm

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.
CraigEddy
 
Posts: 20
Joined: Fri Sep 28, 2012 2:59 pm

This issue is still occurring in the latest version

Postby nhustak » Fri Jan 10, 2014 9:17 pm

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.
nhustak
 
Posts: 40
Joined: Tue May 09, 2006 8:06 pm

Next

Return to SQL Source Control 3

Who is online

Users browsing this forum: No registered users and 2 guests