Error: A duplicate definition was found for the stored proc

gvar369

Hi,

SQL Source control gave me this error, after i commited couple of new SP's and Tables to the source control.
My environment:
SQL Server 2008, Sourcegear Vault Professional, SQL Source Control 2.1.0.31

I've already reported about this error through the SQL Source Control error reporting tool, but haven't received any response to it.

Following is the complete error details,

RedGate.SQLSourceControl.Engine.SqlCompareException: A duplicate definition was found for the stored procedure <stored_procedure>. 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 stored procedure <stored_procedure>. . 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 stored procedure name (<stored_procedure>) 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.
   at System.ThrowHelper.ThrowArgumentException(ExceptionResource resource)
   at System.Collections.Generic.Dictionary`2.Insert(TKey key, TValue value, Boolean add)
   at System.Collections.Generic.Dictionary`2.Add(TKey key, TValue value)
   at RedGate.SQLCompare.Engine.KeyedCollection`1.Add(T value)
   at RedGate.SQLCompare.Engine.SerializableDatabaseObjectCollection`1.Add(T value)
   --- End of inner exception stack trace ---
   at RedGate.SQLCompare.Rewriter.Analysis.Analyser`1.#KPz(Exception exception)
   at RedGate.SQLCompare.Rewriter.Analysis.Analyser`1.WaitForBackgroundThread()
   at #Eyg.#Gyg.#CGh(FileInfo file)
   at #Eyg.#Gyg.#vl()
   --- End of inner exception stack trace ---
   at #Eyg.#Gyg.#vl()
   at RedGate.SQLCompare.Engine.Database.Register(String path, ScriptDatabaseInformation dbinfo, Options options)
   at #G3c.#L3c.#t.#A2.#m36()
   at #oEc.#7Jf.#gKf(Action )
   --- End of inner exception stack trace ---

Server stack trace: 
   at #oEc.#7Jf.#gKf(Action )
   at #oEc.#7Jf.#t.#z2.#rkW()
   at #oEc.#2aV.#hKf(ICancellableOperationStatus , ICancellable , Action )
   at #oEc.#7Jf.#jKf(ICancellableOperationStatus , ICancellable , Action )
   at #G3c.#L3c.#l36(String , #x36 )
   at #G3c.#K3c.#83c(String , #O9U )
   at #G3c.#L3c.#f6c(String , ScriptDatabaseInformation , Options , ICancellableOperationStatus )
   at #oEc.#VXM.#t.#WXM.#f6c(String )
   at #oEc.#VXM.#t.#WXM.#UtV()
   at RedGate.SQLSourceControl.Engine.Cancellables.CancellableOperation.#t.#izb.#j5f()
   at System.Runtime.Remoting.Messaging.StackBuilderSink._PrivateProcessMessage(IntPtr md, Object[] args, Object server, Int32 methodPtr, Boolean fExecuteInContext, Object[]& outArgs)
   at System.Runtime.Remoting.Messaging.StackBuilderSink.PrivateProcessMessage(RuntimeMethodHandle md, Object[] args, Object server, Int32 methodPtr, Boolean fExecuteInContext, Object[]& outArgs)
   at System.Runtime.Remoting.Messaging.StackBuilderSink.AsyncProcessMessage(IMessage msg, IMessageSink replySink)

Exception rethrown at [0]: 
   at System.Runtime.Remoting.Proxies.RealProxy.EndInvokeHelper(Message reqMsg, Boolean bProxyCase)
   at System.Runtime.Remoting.Proxies.RemotingProxy.Invoke(Object NotUsed, MessageData& msgData)
   at RedGate.SQLSourceControl.Engine.Action.EndInvoke(IAsyncResult result)
   at RedGate.SQLSourceControl.Engine.Cancellables.CancellableOperation.#zIc(ICancellableOperationStatus , Pair`2[] )
   at #oEc.#VXM.#t.#WXM.#rYM()
   at #oEc.#VXM.#t.#WXM.#qYM()
   at #oEc.#VXM.#t.#XXM.#zYM(ToCommitChangeSet& toCommitChangeSet)
   at #oEc.#VXM.#mYM[#eTb](Func`2 , ICancellableOperationStatus , IReadOnlySourceControlServerCallBacks )
   at #oEc.#VXM.#jYM(ICancellableOperationStatus , IReadOnlySourceControlServerCallBacks )
   at #oEc.#4Ec.#7Jc(ICancellableOperationStatus , IReadOnlySourceControlServerCallBacks )
   at #oEc.#4Ec.#t.#u3.#ofA(ICancellableOperationStatus )
   at RedGate.SQLSourceControl.Engine.Cancellables.MutexedCancellableOperation`1.#t.#u3.#k5f()
   at RedGate.SQLSourceControl.Engine.Cancellables.CancellableOperationBase.InvokeWithTracker(Action action)
   at RedGate.SQLSourceControl.Engine.Cancellables.MutexedCancellableOperation`1.Invoke()
   at #eEc.#Qlg.Invoke()
   at #JLc.#PLc.#t.#Jfb.#EJf()
   at RedGate.SQLSourceControl.Engine.SmartAssembly.ExceptionReporting.ErrorReporterBase.Do(Action , Predicate`1 , Boolean )
   at RedGate.SQLSourceControl.Engine.SmartAssembly.ExceptionReporting.ErrorReporterBase.DoWithObviousExceptionsRethrowAll(Action action)
   at RedGate.SQLSourceControl.CommonUI.Forms.ErrorDialog.DoWithObviousExceptionsRethrowAll(Action action)
   at #JLc.#PLc.#CTc(ICancellableOperation`1 , Object )

Let me know if you need any further information, as i need this thing to be resolved as quickly as possible.

Thanks,
Vijay

AndrewBA

I have also received this error this morning - strange because everything seemed to be working OK on Friday afternoon.

I am fully up-to-date with Windows updates, and have tried rolling back to v1.1. Other developers attaching to the same SVN repository are having the same issue.

I created a new SVN folder and linked my database to it. This worked for a while and then came back with the same error.

My instance default collation is Latin1_General_CI_AS, my db collation is SQL_Latin1_General_CP1_CI_AS.

The error only happens on 1 of my SourceControl db's.

What exactly does 'Ensure that case sensitivity options are set correctly and all object creation scripts are valid' mean and how can I proceed?

Windows 7 Pro SP1 x64, SQL Server 2008, SVN, SQL Source Control 2.1.0.31 (I recently updated to the 14 day trial of this)

James B

This error will generally occur if there are multiple files that contain the create SQL for the object in question within your SVN repo.

This can occur if a developer makes a backup of a script in SVN for instance - as SQL Source Control parses the whole folder structure, if there is more than one file that "creates" an object, then it doesn't know which to use and you get the error. It can also occur if a file has multiple object creation statements.

So, you need to check through your repo for any files that may reference the object the error refers to and remove the invalid ones.

You'll also then need to check your workingbases and transients for the same thing (or just unlink + relink which should give you new sets of those)

AndrewBA

Thanks for the response, this was indeed the problem.

gvar369

Thanks james, I tried to unlink and relink the database, it solved my problem. But i guess, the problem started when i changed the schema of a SP.
Will try to do the same things i did before the source control crashed and will let you know if thats causing the problem.

Vijay

AndrewBA

As a follow up to this; I automatically create a "CreateDatabaseObjects.sql" script from the SourceControl db object scripts in SVN. The create objects script was in the database directory that SourceContol is linked to and I think that SourceContol found it and threw the duplicate objects error.

James B

Yes, that would almost certainly be it. If you are going to have extra "maintenance" scripts they need to be stored above the folder you're linking to, otherwise SQL Source Control will try to parse it and you'll encounter this kind of problem.