Hi Adam,
First, I want to thank you for an excellent product!
After a lot of effort setting things up—integrating it with my company’s PowerShell environment, configuring Windows Authentication, defining roles, etc.—I finally did a demo for the decision-makers, and it was a success!
Now, to move forward, I need to migrate from SQLite to SQL for better persistence of onboarding data and to align with our IT management system guidelines.
We are a fairly large organization and plan to purchase a license for support soon. However, before we can proceed, I need to complete the SQL migration—which is where I’m running into issues:
After updating to version 5.3.3, the psu cli db migrate command initially worked fine. However, the first error I encountered was related to insufficient permissions. It turned out that .NET couldn’t handle the %ProgramData% variable, so I switched to using the full path, which resolved that issue.
Now, I’m facing a new error during the actual migration.
PS C:\Program Files (x86)\Universal> .\psu.exe db migrate -scn "Data Source=C:\ProgramData\UniversalAutomation\database.db" `
>> -sdt SQLite `
>> -tcn "Server=xxxxxx.xxx.xx;Database=PowerU;Integrated Security=True;TrustServerCertificate=True" `
>> -tdt Sql
info: PSUCLI.DataMigrator[0]
[00:00:00] Moving identities.
Unhandled exception: Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while saving the entity changes. See the inner exception for details.
---> Microsoft.Data.SqlClient.SqlException (0x80131904): Cannot insert explicit value for identity column in table 'Identity' when IDENTITY_INSERT is set to OFF.
at Microsoft.Data.SqlClient.SqlCommand.<>c.<ExecuteDbDataReaderAsync>b__195_0(Task`1 result)
at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
--- End of stack trace from previous location ---
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot, Thread threadPoolThread)
--- End of stack trace from previous location ---
at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken)
ClientConnectionId:a7fea4a0-459c-44d2-b4cd-be0d00fbb945
Error Number:544,State:1,Class:16
--- End of inner exception stack trace ---
at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.SqlServer.Update.Internal.SqlServerModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(IEnumerable`1 commandBatches, IRelationalConnection connection, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(IEnumerable`1 commandBatches, IRelationalConnection connection, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(IEnumerable`1 commandBatches, IRelationalConnection connection, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(IList`1 entriesToSave, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(StateManager stateManager, Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Storage.ExecutionStrategy.<>c__DisplayClass30_0`2.<<ExecuteAsync>b__0>d.MoveNext()
--- End of stack trace from previous location ---
at Microsoft.EntityFrameworkCore.Storage.ExecutionStrategy.ExecuteImplementationAsync[TState,TResult](Func`4 operation, Func`4 verifySucceeded, TState state, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Storage.ExecutionStrategy.ExecuteImplementationAsync[TState,TResult](Func`4 operation, Func`4 verifySucceeded, TState state, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Storage.ExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.DbContext.SaveChangesAsync(Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.DbContext.SaveChangesAsync(Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken)
at PSUCLI.DataMigrator.Transfer(PSUDataContext source, PSUDataContext target) in C:\actions-runner\_work\universal\universal\src\Universal.CommandLine\DataMigrator.cs:line 21
at PSUCLI.MigrateDatabaseHandler.HandleAsync(MigrateDatabaseOptions options, CancellationToken cancellationToken) in C:\actions-runner\_work\universal\universal\src\Universal.CommandLine\DatabaseCommand.cs:line 82
at System.CommandLine.Command`2.HandleOptions(TOptions options, IServiceProvider serviceProvider, CancellationToken cancellationToken) in C:\actions-runner\_work\universal\universal\src\Universal.CommandLine\Command.cs:line 29
at System.CommandLine.NamingConventionBinder.CommandHandler.GetExitCodeAsync(Object returnValue, InvocationContext context)
at System.CommandLine.NamingConventionBinder.ModelBindingCommandHandler.InvokeAsync(InvocationContext context)
at System.CommandLine.Invocation.InvocationPipeline.<>c__DisplayClass4_0.<<BuildInvocationChain>b__0>d.MoveNext()
--- End of stack trace from previous location ---
at System.CommandLine.Builder.CommandLineBuilderExtensions.<>c__DisplayClass17_0.<<UseParseErrorReporting>b__0>d.MoveNext()
--- End of stack trace from previous location ---
at PSUCLI.DependencyInjectionMiddleware.<>c__DisplayClass1_0.<<UseDependencyInjection>b__0>d.MoveNext() in C:\actions-runner\_work\universal\universal\src\Universal.CommandLine\DependencyInjectionMiddleware.cs:line 44
--- End of stack trace from previous location ---
at PSUCLI.DependencyInjectionMiddleware.<>c__DisplayClass1_0.<<UseDependencyInjection>b__0>d.MoveNext() in C:\actions-runner\_work\universal\universal\src\Universal.CommandLine\DependencyInjectionMiddleware.cs:line 44
--- End of stack trace from previous location ---
at System.CommandLine.Builder.CommandLineBuilderExtensions.<>c__DisplayClass12_0.<<UseHelp>b__0>d.MoveNext()
--- End of stack trace from previous location ---
at System.CommandLine.Builder.CommandLineBuilderExtensions.<>c__DisplayClass22_0.<<UseVersionOption>b__0>d.MoveNext()
--- End of stack trace from previous location ---
at System.CommandLine.Builder.CommandLineBuilderExtensions.<>c__DisplayClass19_0.<<UseTypoCorrections>b__0>d.MoveNext()
--- End of stack trace from previous location ---
at System.CommandLine.Builder.CommandLineBuilderExtensions.<>c.<<UseSuggestDirective>b__18_0>d.MoveNext()
--- End of stack trace from previous location ---
at System.CommandLine.Builder.CommandLineBuilderExtensions.<>c__DisplayClass16_0.<<UseParseDirective>b__0>d.MoveNext()
--- End of stack trace from previous location ---
at System.CommandLine.Builder.CommandLineBuilderExtensions.<>c.<<RegisterWithDotnetSuggest>b__5_0>d.MoveNext()
--- End of stack trace from previous location ---
at System.CommandLine.Builder.CommandLineBuilderExtensions.<>c__DisplayClass8_0.<<UseExceptionHandler>b__0>d.MoveNext()