Graph Tables not supported? Source Control only shows "error in paring the file"

friedrich_eberharter

It looks like Source Control does not support graph tables. I have a stored procedure that creates a graph table (in SQL Server 2022 with SQL Source Control 7.7.10.18663) and if I want to commit my changes, I get an error:

There was an error parsing the file "C:\Users\XXXX\AppData\Local\Red Gate\SQL Source Control 7\WorkingBases\lamt2ctk.e4q\dbo.YYYY.sql" at line 5, column 7, near the text .

To resolve this issue:

1. Open the affected file.

2. Edit it to ensure the SQL is valid.

3. Commit the changes without using SQL Source Control. For example by using Tortoise SVN, or Microsoft Team Explorer.

Show complete error:

Errors occurred whilst parsing file C:\Users\XXXX\AppData\Local\Red Gate\SQL Source Control 7\WorkingBases\lamt2ctk.e4q\dbo.YYYY.sql

'' at line 5, column 7

It looks like the error is not complete, presumably the text contains NULL.

The SP is valid. I can store it and execute it, that happens multiple times a day since multiple months. Also, I can commit it with other tools and I can compare the database with this SP with SQL Compare and SQL Data Compare. Only SQL Source Control is affected from this problem.

We don't want to change our workflow or user other tools only because we use the feature SQL Graph database. The short term solution is to exclude the stored procedure (not the graph table itself!) but we wish that Source Control could handle this.

So please add support for SQL Graph databases!

DanC

Hi @friedrich_eberharter

Would you be able to provide the syntax for the object you're creating?

Using the example here I'm having no issues: https://learn.microsoft.com/en-us/sql/relational-databases/graphs/sql-graph-sample?view=sql-server-ver16

friedrich_eberharter

Hello Dan,

thanks for the quick answer!

I was probably a bit hasty with my conclusions, there may not be a lack of general support for the graph feature, but unfortunately I can't save the example code via source control either.

Can you include that example code in a SP and then commit it via Source Control?

Here's what I tried:

I swapped all code in our SP with the setup of the example from microsoft.com - see attached file. (I attached a *.txt file - apparently *.sql is not allowed?!?)

I did that both in the database and in the *.sql in my working folder.

Then I linked the database to source control (Link to a working folder > Dedicated database), clicked "Commit" and checked the boxe for the new SP.

While comparing for changes, I didn't saw the same error as described above, but when trying to commit I got an error at step "Sending files to source control server":

Stored Procedures\dbo.InitWayPoint_graph.sql
   at RedGate.Vcs.Common.Generic.GenericClient.CheckForUnacknowledgedConflicts(String workingRootPath, GenericCommitPaths paths)
   at RedGate.Vcs.Common.Generic.GenericClient.<>c__DisplayClass10_0.<CommitAll>b__0()
   at RedGate.Vcs.Common.Generic.GenericClient.WithFilesystemLock(Action action)
   at RedGate.Vcs.Common.Generic.GenericClient.CommitAll(String commitMessage, String workingRootPath, GenericCommitPaths paths, IGenericHooks hooks, IReadOnlyGenericCallbacks callBacks, ICancellableOperationStatus status, Action`1 onCommitted)
   at RedGate.Vcs.Common.Generic.GenericWorkspaceOperations.<>c__DisplayClass23_0.<ReplaceFolder>b__0()
   at RedGate.SQLSourceControl.Engine.Utils.Task.SharedUtilsUtils.DoActionWithCancel(ICancellableOperationStatus status, ICancellable cancellable, Action action)
   at RedGate.SQLSourceControl.Engine.Utils.Task.SharedUtilsUtils.DoActionWithStatus(ICancellableOperationStatus status, ICancellable cancellable, Action action)
   at RedGate.Vcs.Common.Generic.GenericWorkspaceOperations.WithGenericClientAndHooks(ICancellableOperationStatus status, Action action)
   at RedGate.Vcs.Common.Generic.GenericWorkspaceOperations.ReplaceFolder(ICommitLogMessageProvider commitLogMessageProvider, IEnumerable`1 pathsToAdd, IEnumerable`1 pathsToDelete, ConflictAcknowledgements conflictAcknowledgements, Action`1 onCommitted, ICancellableOperationStatus status)
   at RedGate.Vcs.Common.Generic.GenericPreparedCommit.<>c__DisplayClass2_0.<GetOperations>b__2(ICancellableOperationStatus s)
   at RedGate.Vcs.Common.PreparedCommitBase.<>c__DisplayClass15_0.<CreateNamedOperation>b__0(ICancellableOperationStatus s)
   at RedGate.SQLSourceControl.Engine.Utils.Task.NamedOperation.Operation(ICancellableOperationStatus cancellableOperation)
   at RedGate.SQLSourceControl.Engine.Utils.Task.CancellableOperation.<Invoke>b__9_0()
   at RedGate.SQLSourceControl.Engine.Utils.Task.CancellableOperationBase.PerformAction(Action action)
   at RedGate.SQLSourceControl.Engine.Utils.Task.CancellableOperationBase.InvokeWithTracker(Action action)
   at RedGate.SQLSourceControl.Engine.Utils.Task.CancellableOperation.Invoke()
   at RedGate.SQLSourceControl.Engine.Utils.Task.SharedUtilsUtils.DoActionWithCancel(ICancellableOperationStatus status, ICancellable cancellable, Action action)
   at RedGate.SQLSourceControl.Engine.Utils.Task.SharedUtilsUtils.DoActionWithStatus(ICancellableOperationStatus status, ICancellable cancellable, Action action)
   at RedGate.SQLSourceControl.Engine.Utils.Task.MultiStepCancellableOperation`1.ExecuteAsOnOperation(ICancellableOperationStatus status)
   at RedGate.SQLSourceControl.Engine.Utils.Task.MultiStepCancellableOperationWithPostTask`1.ExecuteAsOnOperation(ICancellableOperationStatus status)
   at RedGate.Vcs.Common.PreparedCommitBase.Execute(IReadOnlyFileSet fileSet, ICancellableOperationStatus status)
   at RedGate.Vcs.Common.PreparedCommitWithPreTask.Execute(IReadOnlyFileSet fileSet, ICancellableOperationStatus status)
   at RedGate.SQLSourceControl.Engine.Diff.ChangeSetOperations.CommitAllOperation.Operation(ICancellableOperationStatus cancellableOperationStatus)
   at RedGate.SQLSourceControl.Engine.Utils.Task.CancellableOperation.<Invoke>b__9_0()
   at RedGate.SQLSourceControl.Engine.Utils.Task.CancellableOperationBase.PerformAction(Action action)
   at RedGate.SQLSourceControl.Engine.Utils.Task.CancellableOperationBase.InvokeWithTracker(Action action)
   at RedGate.SQLSourceControl.Engine.Utils.Task.CancellableOperation.Invoke()
   at RedGate.SQLSourceControl.Engine.Utils.Task.MultiStepCancellableOperation`1.InvokeOperations()
   at RedGate.SQLSourceControl.Engine.Utils.Task.MultiStepCancellableOperationWithPostTask`1.InvokeOperations()
   at RedGate.SQLSourceControl.Engine.Utils.Task.MultiStepCancellableOperation`1.<>c__DisplayClass0_0.<.ctor>b__0()
   at System.Threading.Tasks.Task`1.InnerInvoke()
   at System.Threading.Tasks.Task.Execute()

I also sent an error report directly from Source control, you may find more information about my problem in that ticket.

Thanks in advance!

And best regards,

Friedrich

DanC

Hi @friedrich_eberharter

Could you try updating to the latest version to rule that out as a possibility?

https://download.red-gate.com/checkforupdates/SQLSourceControl/SQLSourceControl_7.8.1.19631.exe

I took the file you provided and created that object successfully

friedrich_eberharter

Hi Dan,

sorry for getting to provide this information: I just upgraded to 7.8.1.19631 (and used the version before until yesterday) on SQL Server 2022 (16.0.1000.6).

And I'm also able to create this SP now!

It would be kinda strange if I wasn't, because there is no graph function in the code yet. I don't know exactly what I was thinking earlier, but of course I don't need a *.sql file in the working folder if I want to create a new SP. So I started again from scratch and then it worked to commit above attached SP!

So we are ready for the next step: I added a query with SHORTEST_PATH at the end of the SP. It's example G from the Microsoft documentation for this function and it's the same type of query we use in the production code. You can find the new SP attached.
When I alter the SP in the database, Source Control recognizes the change correctly and I can commit it. 

But at the next compare in the tab "Commit" I get the error originally described:

Can you replicate that?

Best regards,

Friedrich

DanC

Hi @friedrich_eberharter

Glad that initial error has been solved!

I believe this next issue is potentially down to SHORTEST_PATH potentially not being supported in SQL Source Control, so I'll escalate this to the developers and get their thoughts!

If you want to workaround this you can use SQL Compare to commit this to the scripts folder directly

DanC

Hi @friedrich_eberharter

Unfortunately, due to the development team's workload we're unable to do the work to support SHORTEST_PATH

I apologise for this.