This extension allows the user to enter the connection details for a SQL Server database and move Grindstone’s data into it, replacing the factory data storage mechanism. It was originally created to demonstrate how to interact with user data lifecycle events, but may actually be used by users that would prefer to keep their Grindstone data in a relational database instead of their Grindstone database file.
This extension requires that the following other extensions are also installed:
I am having trouble getting this extension to load. Grindstone pops up the following window each time it starts. The dump follows:
-- Exception: Failed to intialize logic
CompilationErrorException: (644,54): error CS0121: The call is ambiguous between the following methods or properties: 'Quantum.Extensions.GetDetails(System.Exception)' and 'Quantum.Client.Windows.Extensions.GetDetails(System.Exception)'
at void Microsoft.CodeAnalysis.Scripting.ScriptBuilder.ThrowIfAnyCompilationErrors(DiagnosticBag diagnostics, DiagnosticFormatter formatter) in /_/src/Scripting/Core/ScriptBuilder.cs:line 113
at Func<object[], Task<T>> Microsoft.CodeAnalysis.Scripting.ScriptBuilder.CreateExecutor<T>(ScriptCompiler compiler, Compilation compilation, bool emitDebugInformation, CancellationToken cancellationToken) in /_/src/Scripting/Core/ScriptBuilder.cs:line 87
at Func<object[], Task<T>> Microsoft.CodeAnalysis.Scripting.Script<T>.GetExecutor(CancellationToken cancellationToken) in /_/src/Scripting/Core/Script.cs:line 359
at Task<ScriptState<T>> Microsoft.CodeAnalysis.Scripting.Script<T>.RunAsync(object globals, Func<Exception, bool> catchException, CancellationToken cancellationToken) in /_/src/Scripting/Core/Script.cs:line 459
at async Task<(ValueTuple<string, Guid?, string, Exception> relativePath)> Quantum.Client.Windows.Extension.LoadExtensionsAsync(ProSplashWindow splash) in C:/Source/Quantum/Quantum.Client.Windows/Extension.cs:line 385
I know…trying times. We are all hunkered down in our bunkers.
I apologize you ran into this problem. We’ve published a revision to the extension and the download link in the original post has been updated. Or, you can just click here. 
I am getting the following error when activating Use SQL Server. I think it is trying to insert data out of sequence, inserting into ListValueProperties before the child value has been inserted in ListValues, maybe.
I see that WorkItems is completely populated. I expected to see ListValues and TextProperties to be fully populated. Time slices is not yet populated.
(I get the feeling I am going to be a thorn in your side.)
-- SqlException: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_ListValues_ListValueProperties". The conflict occurred in database "Grindstone4", table "dbo.ListValueProperties", column 'Id'.
The statement has been terminated.
at void System.Data.SqlClient.SqlConnection.OnError(SqlException exception, bool breakConnection, Action<Action> wrapCloseInAction)
at void System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, bool breakConnection, Action<Action> wrapCloseInAction)
at void System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, bool callerHasConnectionLock, bool asyncClose)
at bool System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, out bool dataReady)
at void System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, string resetOptionsString, bool isInternal, bool forDescribeParameterEncryption, bool shouldCacheForAlwaysEncrypted)
at SqlDataReader System.Data.SqlClient.SqlCommand.CompleteAsyncExecuteReader(bool isInternal, bool forDescribeParameterEncryption)
at object System.Data.SqlClient.SqlCommand.InternalEndExecuteNonQuery(IAsyncResult asyncResult, string endMethod, bool isInternal)
at int System.Data.SqlClient.SqlCommand.EndExecuteNonQueryInternal(IAsyncResult asyncResult)
at int System.Data.SqlClient.SqlCommand.EndExecuteNonQueryAsync(IAsyncResult asyncResult)
at void System.Threading.Tasks.TaskFactory<TResult>.FromAsyncCoreLogic(IAsyncResult iar, Func<IAsyncResult, TResult> endFunction, Action<IAsyncResult> endAction, Task<TResult> promise, bool requiresSynchronization)
at async Task Submission#0+SqlServerTransactor.ApplyChangesToSqlAsync(ChangeSet changes)
at async Task Submission#0+SqlServerTransactor.ConnectToSqlServerAsync(string connectionString)
at async Task Submission#0+SqlServerTransactor.ConnectToSqlServerAsync(string connectionString)
at void Submission#0+<<<Initialize>>b__0_1>d.MoveNext()
Heaven forbid a user should come to the developer with problems. You’d think we’re trying to promote customer satisfaction around here. 
Without having the Grindstone database and SQL Server database you’re currently using in front of me, I can’t be sure precisely why this is an issue. One way to breeze past the issue would be to remove the foreign key constraints from PrepareDatabase.sql in the extension. I wouldn’t blame you if you did, after all, SQLite doesn’t do that sort of thing.
But supposing you do what to stick to your guns and see if you can keep the constraints, perhaps a bit more detail would help? The only part of Task ApplyChangesToSqlAsync(ChangeSet) that causes an INSERT on dbo.ListValues is line 217. Here is the entire relevant block for reference:
if (changes.CreateOrUpdateEnumerationValues?.Any() ?? false)
foreach (var kv in changes.CreateOrUpdateEnumerationValues)
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = "IF EXISTS (SELECT Id FROM ListValues WHERE Id = @id) UPDATE ListValues SET ListValuePropertyId = @listValuePropertyId, Name = @name WHERE Id = @id ELSE INSERT ListValues (Id, ListValuePropertyId, Name) VALUES (@id, @listValuePropertyId, @name)";
cmd.Parameters.AddWithValue("@id", kv.Key);
cmd.Parameters.AddWithValue("@listValuePropertyId", kv.Value.AttributeId);
cmd.Parameters.AddWithValue("@name", kv.Value.CorrelatedEntity.Name);
await cmd.ExecuteNonQueryAsync();
}
Does replacing the above with something like this give us any anything enlightening?
if (changes.CreateOrUpdateEnumerationValues?.Any() ?? false)
foreach (var kv in changes.CreateOrUpdateEnumerationValues)
using (var cmd = conn.CreateCommand())
{
try
{
cmd.CommandText = "IF EXISTS (SELECT Id FROM ListValues WHERE Id = @id) UPDATE ListValues SET ListValuePropertyId = @listValuePropertyId, Name = @name WHERE Id = @id ELSE INSERT ListValues (Id, ListValuePropertyId, Name) VALUES (@id, @listValuePropertyId, @name)";
cmd.Parameters.AddWithValue("@id", kv.Key);
cmd.Parameters.AddWithValue("@listValuePropertyId", kv.Value.AttributeId);
cmd.Parameters.AddWithValue("@name", kv.Value.CorrelatedEntity.Name);
await cmd.ExecuteNonQueryAsync();
}
catch (Exception ex)
{
throw new Exception($"I was unable to create or update the \"{kv.Value.CorrelatedEntity.Name}\" list value ({kv.Key}) of {((await SnapshotAsync().ConfigureAwait(false)).Attributes.TryGetValue(Guid.NewGuid(), out var attribute) ? $"a property called \"{attribute.Name}\"" : "an unknown property")} ({kv.Value.AttributeId})", ex);
}
}
Is there an email I can send to you my nearest .gsjdb backup file? That should provide all that you need. Might give you a good forehead slap, “What is this guy doing?!?”
Something consumed the exception. I expected a window to pop-up, or something to get logged, or the event viewer capturing the exception. Nothing. It did load the extension. But it exhibited the same behavior as before, in that the enumeration tables are not completely filled, no time slices, etc.
I will remove the foreign keys and check the table contents.
And this is all happening when you first attempt to set up the SQL Server database? You’re not sneakily deleting a property it’s expecting to exist on the SQL Server side, right?
Starting with a clean database, and working the suggestions one by one, the load fails on missing a lookup value. Dump at bottom.
When I comment out all the foreign keys, the data loads in its entirety and all is grand.
So it leads me back to my original thought: a row is being inserted into the link table ListValueProperties before its associated value has been committed to the lookup table ListValues.
I would guess that this is working from a purely human-transaction basis and not from the perspective of initializing a database. Human behavior, being controlled by you interface, forces the user to add list values and property values before they are assigned to work items. But when initializing the database, the system will need to load things in a given order: list and property values first, then work items, then the link tables, then the time slices.
-- Exception: I was unable to create or update the "Support" list value (7e9446ca-59a8-42bd-8e55-50c5f09921d2) of an unknown property (d740803b-dc65-4d4b-89cd-fa1cc0994428)
at async Task Submission#0+SqlServerTransactor.ApplyChangesToSqlAsync(ChangeSet changes)
at async Task Submission#0+SqlServerTransactor.ConnectToSqlServerAsync(string connectionString)
at async Task Submission#0+SqlServerTransactor.ConnectToSqlServerAsync(string connectionString)
at void Submission#0+<<<Initialize>>b__0_1>d.MoveNext()
SqlException: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_ListValues_ListValueProperties". The conflict occurred in database "Grindstone_02", table "dbo.ListValueProperties", column 'Id'.
The statement has been terminated.
at void System.Data.SqlClient.SqlConnection.OnError(SqlException exception, bool breakConnection, Action<Action> wrapCloseInAction)
at void System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, bool breakConnection, Action<Action> wrapCloseInAction)
at void System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, bool callerHasConnectionLock, bool asyncClose)
at bool System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, out bool dataReady)
at void System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, string resetOptionsString, bool isInternal, bool forDescribeParameterEncryption, bool shouldCacheForAlwaysEncrypted)
at SqlDataReader System.Data.SqlClient.SqlCommand.CompleteAsyncExecuteReader(bool isInternal, bool forDescribeParameterEncryption)
at object System.Data.SqlClient.SqlCommand.InternalEndExecuteNonQuery(IAsyncResult asyncResult, string endMethod, bool isInternal)
at int System.Data.SqlClient.SqlCommand.EndExecuteNonQueryInternal(IAsyncResult asyncResult)
at int System.Data.SqlClient.SqlCommand.EndExecuteNonQueryAsync(IAsyncResult asyncResult)
at void System.Threading.Tasks.TaskFactory<TResult>.FromAsyncCoreLogic(IAsyncResult iar, Func<IAsyncResult, TResult> endFunction, Action<IAsyncResult> endAction, Task<TResult> promise, bool requiresSynchronization)
at async Task Submission#0+SqlServerTransactor.ApplyChangesToSqlAsync(ChangeSet changes)But, but, but @donvnielsen… first the extension creates/updates properties…
if (changes.CreateOrUpdateAttributes?.Any() ?? false)
foreach (var kv in changes.CreateOrUpdateAttributes)
using (var cmd = conn.CreateCommand())
{
var tablePrefix = kv.Value.IsEnumeration ? "ListValue" : "Text";
cmd.CommandText = $"IF EXISTS (SELECT Id FROM {tablePrefix}Properties) UPDATE {tablePrefix}Properties SET Name = @name, Notes = @notes ELSE INSERT {tablePrefix}Properties (Id, Name, Notes) VALUES (@id, @name, @notes)";
cmd.Parameters.AddWithValue("@id", kv.Key);
cmd.Parameters.AddWithValue("@name", kv.Value.Name);
cmd.Parameters.AddWithValue("@notes", (object)kv.Value.Notes ?? DBNull.Value);
await cmd.ExecuteNonQueryAsync();
}
… then list values …
if (changes.CreateOrUpdateEnumerationValues?.Any() ?? false)
foreach (var kv in changes.CreateOrUpdateEnumerationValues)
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = "IF EXISTS (SELECT Id FROM ListValues WHERE Id = @id) UPDATE ListValues SET ListValuePropertyId = @listValuePropertyId, Name = @name WHERE Id = @id ELSE INSERT ListValues (Id, ListValuePropertyId, Name) VALUES (@id, @listValuePropertyId, @name)";
cmd.Parameters.AddWithValue("@id", kv.Key);
cmd.Parameters.AddWithValue("@listValuePropertyId", kv.Value.AttributeId);
cmd.Parameters.AddWithValue("@name", kv.Value.CorrelatedEntity.Name);
await cmd.ExecuteNonQueryAsync();
}
Which is why my position is:
I guess I’ll private message you an email to which you may send this mysterious Grindstone database.
I’m spending way too much time on this; it’s like meth. But there is an issue between Grindstone, SQLServer, and the Use SQL Server extension.
Steps to recreate:
This is where the error occurs.
SqlException: The INSERT statement conflicted with the FOREIGN KEY constraint “FK_ListValues_ListValueProperties”. The conflict occurred in database “Grindstone”, table “dbo.ListValueProperties”, column ‘Id’.
When I comment out the foreign key in PrepareDatabase.sql, then all is fine
/*
ALTER TABLE [ListValues] WITH CHECK ADD CONSTRAINT [FK_ListValues_ListValueProperties] FOREIGN KEY ([ListValuePropertyId]) REFERENCES [ListValueProperties] ([Id])
ALTER TABLE [ListValues] CHECK CONSTRAINT [FK_ListValues_ListValueProperties]
*/
I think there is an issue between SQLServer and the extension when trying to complete properties of changes.CreateOrUpdateAttributeValues?.Any(). Only one value manages to commit into the database. It is not always the same value. I think it is having commitment issues (yes, its a pun.)
I cannot duplicate this issue using Use SQLite. That does not appear to have this issue. I haven’t broke that, yet.
Based on the attached image, it appears that Use SQLServer is overwriting the one and only row when populating it with Property Lists. In the image, the one row contains the GUID for the first property list, Priority, but has the name and notes values for the last Property List, Application.
TextProperties table appeared to behave the same way. It only contained one row, with the GUID from the first text property in the gsjdb file, and the text value from the last text property. Bizarre.
This is running using my local SqlServer, and not a networked SqlServer.
The following update should be applied to the Use SqlServer extension for the database to be populated correctly. The IF EXISTS select and the UPDATE statement require a WHERE clause on the Id to function correctly.
if (changes.CreateOrUpdateAttributes?.Any() ?? false)
foreach (var kv in changes.CreateOrUpdateAttributes)
using (var cmd = conn.CreateCommand())
{
var tablePrefix = kv.Value.IsEnumeration ? "ListValue" : "Text";
cmd.CommandText = $"IF EXISTS (SELECT Id FROM {tablePrefix}Properties where Id = @id) UPDATE {tablePrefix}Properties SET Name = @name, Notes = @notes where Id = @Id ELSE INSERT {tablePrefix}Properties (Id, Name, Notes) VALUES (@id, @name, @notes)";
cmd.Parameters.AddWithValue("@id", kv.Key);
cmd.Parameters.AddWithValue("@name", kv.Value.Name);
cmd.Parameters.AddWithValue("@notes", (object)kv.Value.Notes ?? DBNull.Value);
await cmd.ExecuteNonQueryAsync();
}
Have a good one,
dvn
We made a mistake in writing this extension?
Seriously, thanks for the heads up. But… are you sure you don’t want the street-cred for submitting a PR? As you probably know, all the Epiforge Grindstone 4 extensions are open source and hosted on Github. If you have a Github account it’s simple and easy, and you’ll get proper acknowledgement for finding and fixing this, sir.
Thanks again for your awesome PR, @donvnielsen.
It’s been merged in and now we have a new release on GitHub, and the OP in this thread has been updated with a new download link.
how are you able to see .gsjdb files that nicely in NPP?
There is a NPP plugin called JSTool by Sun Junwen. Excellent little tool!
Have a good one!
There is another NPP plugin named JSON Viewer. I may have used that at the time?
