Commit Changes fails on partition function every time

segorov

Hi all,

My SQL Source Control commit tab has been failing on one database for a while now (working for the rest) and I'm running out of things to try. We are running a Mercurial repo. I don't really need to commit partition functions through source control so I would be fine with excluding them entirely, but a solution where the compare succeeds with them would be preferred.

Hoping that someone can give me some insight. The short error message is:

Failed to locate the partition function [PF_Info5daysHist] for the [PS_Info5daysHist] partition scheme.

Here is what I've tried:
- Verified both files exist in the repo (with .sql extensions of the names above)
- Re-linked the database to source control
- Tried reinstalling older versions of SQL Source Control (back to 2.2)
- Deleted the SQL Source Control 3 folder in C:\Users\<myusername>\AppData\Local\Red Gate\SQL Source Control 3
- Attempted to filter out partition functions from being compared using the method described here: http://www.red-gate.com/SupportCenter/G ... 1202000521 (I couldn't find a parameter in there for Partition Functions/Schemes. Can anyone point me to the right parameter name? It seems like it doesnt exist in the list that comes up by default or is named something else)
- Attempted to filter out partition functions using the .scp/.scpf files in my repository

I've tried the above steps in various combinations, always relinking the db after reinstalling or changing filter files. Other people in my organization are using the exact same setup for this database and can complete the compare and commit changes successfully. Any insight on the issue would be much appreciated. Here is the full error message thrown by SQL Source Control 3:

RedGate.SQLSourceControl.Engine.SqlCompareException: Failed to locate the partition function [PF_Info5daysHist] for the [PS_Info5daysHist] partition scheme. ---> RedGate.SQLCompare.Engine.SqlCompareException: Failed to locate the partition function [PF_Info5daysHist] for the [PS_Info5daysHist] partition scheme.
at RedGate.SQLCompare.Engine.PartitionScheme.PatchUpAfterDeserialization(Database #RHc)
at #Eyg.#Gyg.#vl(IEnumerable`1 #rXrc)
at RedGate.SQLCompare.Engine.Database.Register(String path, ScriptDatabaseInformation dbinfo, Options options, IEnumerable`1 filesToUse, IReadFromFolderCache cache)
at #NsZc.#y0Lc.#PsZc(String , ICompareScriptDatabaseInformation , ICompareOptions , Boolean )
at RedGate.SQLSourceControl.Engine.Diff.CompareEngineUtils.WrapRegisterWithCache(ICompareDatabase db, String path, ICompareScriptDatabaseInformation scriptFolderOptions, ICompareOptions options, Boolean isDatabaseCaseSensitive)
at #qlhb.#L3c.#A2.#m36()
at #NsZc.#MsZc.#gKf(Action )
--- End of inner exception stack trace ---

Server stack trace:
at #NsZc.#MsZc.#gKf(Action )
at RedGate.SQLSourceControl.Engine.Diff.CompareEngineUtils.DoWrapped(Action #sxPb, ICompareActionExecutor #yu1c)
at RedGate.SQLSourceControl.Engine.Diff.CompareEngineUtils.#z2.#rkW()
at RedGate.SQLSourceControl.Engine.SharedUtilsUtils.DoActionWithCancel(ICancellableOperationStatus status, ICancellable cancellable, Action action)
at RedGate.SQLSourceControl.Engine.Diff.CompareEngineUtils.DoWrappedActionWithCancel(ICancellableOperationStatus status, ICancellable database, Action action)
at #qlhb.#L3c.#l36(String , #x36 , ICancellableOperationStatus )
at #qlhb.#K3c.#83c(String , #O9U , ICancellableOperationStatus , IReadOnlySourceControlServerCallBacks )
at #qlhb.#L3c.#f6c(String , ICompareScriptDatabaseInformation , ICompareOptions , Boolean , ICancellableOperationStatus , IReadOnlySourceControlServerCallBacks )
at #GWeb.#WXM.#f6c(String )
at #GWeb.#WXM.#YtV()
at #GWeb.#Wheb.#tieb(Func`1 )
at #GWeb.#WXM.#XtV()
at RedGate.SQLSourceControl.Engine.Cancellables.CancellableUtils.#y2.#RKc()
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 RedGate.SQLSourceControl.Engine.Diff.Memoization.ReattachableMemoizer.#YZBc.#VAb.#xSXc()
at RedGate.SQLSourceControl.Engine.Diff.Memoization.ReattachableMemoizer.#uz2b(String , IDatabaseConnection , #1heb , ICancellableOperationStatus , IReadOnlySourceControlServerCallBacks , Action )
at #qlhb.#0heb.#wieb(IBoundDatabase , ICompareOptions , Boolean , ICancellableOperationStatus , IReadOnlySourceControlServerCallBacks , Action )
at #GWeb.#VXM.#mYM[#NrPb](IBoundDatabase , Func`2 , ICancellableOperationStatus , IReadOnlySourceControlServerCallBacks , IDifferenceSelector , Action )
at #GWeb.#VXM.#jYM(IBoundDatabase , ICancellableOperationStatus , IReadOnlySourceControlServerCallBacks , IDifferenceSelector )
at #GWeb.#4Ec.#7Jc(ICancellableOperationStatus , IReadOnlySourceControlServerCallBacks , IDifferenceSelector )
at #GWeb.#4Ec.#y2.#QB7b(ICancellableOperationStatus )
at RedGate.SQLSourceControl.Engine.Cancellables.MutexedCancellableOperation`1.#u3.#k5f()
at RedGate.SQLSourceControl.Engine.Cancellables.CancellableOperationBase.InvokeWithTracker(String featureUsageKey, Action action)
at RedGate.SQLSourceControl.Engine.Cancellables.MutexedCancellableOperation`1.Invoke()
at #eEc.#Qlg.Invoke()
at #JLc.#PLc.#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 )

Thanks

David Atkinson

Try the comparison option:

<IgnoreFileGroups>False</IgnoreFileGroups>

I think that this ignores filegroups, partition schemes and functions.

David Atkinson
Red Gate

segorov

Hi David,

I tried setting that option to False and True and it still fails on the partition function both ways. Wouldn't it need to be set to True to ignore the objects you mentioned or is the name counter-intuitive?

Thanks

David Atkinson

I would expect that True would ignore the filegroups. If this isn't the case could you email support@red-gate.com for troubleshooting?

It might be helpful if you tried the equivalent option in SQL Compare to see if this behaves as you would expect.

segorov

I forgot to mention in my original post that the compare does indeed work with regular SQL Compare (but throws warnings regarding partition functions). I will attach the details to my email. Thank you.

bensala

Did you ever get a solution to this? I have the same issue with any partition function that uses a nonstandard DATETIME2 type, in my case, I use DATETIME2(2) all over the place.

To get around this, I have to drop the offending functions/schemas and all tables that use it. Link the database and get latest, re-add all the tables schemas and commit everything but the PF and PS. Manually commit PF and PS into TFS.

Every time I go to commit after this, it works, but it shows the PF and PS as needing to be commited. It never actually commits it even if you select it or not. This has been a bug for a long time and i've submitted several error reports.

I keep hoping they fix it when i update SQL source control, but it has yet to happen....