search

dataplat / dbops

⚙ dbops - Powershell module that provides continuous database deployments on any scale
MIT License
155 stars 39 forks source link

Add failed script name to the deployment result object #134

Closed michaelsync closed 2 years ago

michaelsync commented 2 years ago

I am using this code but when there is a problem in one of the script file, it doesn't show the file name that has error. It shows the error message but we don't know which file has the problem unless we have to remove the -Silent

$DBOpsResult = Install-DBOScript -ScriptPath ./sql-scripts/*.sql -SqlInstance localhost -Database DbUpTest -Credential $sqlcredential -ErrorAction Stop -Silent 
$DBOpsResult.DeploymentLog

image

How can I get this message below instead of the message above?

image

michaelsync-mt commented 2 years ago

(I can't edit my first post so here is the additional information.. Thanks. )

I followed this test https://github.com/dataplat/dbops/blob/61c83830c5dee18650d565640054a55283c98024/tests/oracle/Install-DBOScript.Tests.ps1

I tried this code (with -Silent) but it doesn't work. It doesn't work without the -Silent too. I don't want to see the DbUp C# exception.. All I want to know are the file name that has the issue and the error "SQL error" (not C# exception). It would be great if anyone can help me to point the right direction. Thanks.

$testResults = Install-DBOScript -ScriptPath ./sql-scripts/*.sql -SqlInstance localhost -Database DbUpTest -Credential $sqlcredential -Silent -OutputFile ".\log.txt"
$testResults.Successful 
$testResults.Scripts.Name 
$testResults.SqlInstance 
$testResults.SourcePath 
$testResults.ConnectionType 
$testResults.Configuration.SchemaVersionTable 
$testResults.Error 
$testResults.Duration.TotalMilliseconds 
$testResults.StartTime 
$testResults.EndTime
nvarscar commented 2 years ago

If you run the command with Powershell-native -ErrorAction SilentlyContinue, you should be able to get the result object back even if the command failed. Can you test if it works for you as expected?

michaelsync commented 2 years ago

@nvarscar

I updated the code like that. 

$testResults = Install-DBOScript -ScriptPath ./sql-scripts/*.sql -SqlInstance localhost -Database DbUpTest -Credential $sqlcredential -ErrorAction SilentlyContinue
$testResults.Successful 
$testResults.Scripts.Name 
$testResults.SqlInstance 
$testResults.SourcePath 
$testResults.ConnectionType 
$testResults.Configuration.SchemaVersionTable 
$testResults.Error 
$testResults.Duration.TotalMilliseconds 
$testResults.StartTime 
$testResults.EndTime 
$testResults.EndTime

No. I didn't get what I expected. I don't want to see the C# exception but it's still showing. $testResults.Scripts.Name returns nothing. 

C:\labs\dbup-pwsh> .\run.ps1
[14:56:02][Install-DBOScript] Beginning database upgrade
[14:56:02][Install-DBOScript] Checking whether journal table exists..
[14:56:02][Install-DBOScript] Fetching list of already executed scripts.
[14:56:02][Install-DBOScript] Executing Database Server script '2.error_file.sql'
[14:56:02][Install-DBOScript] Checking whether journal table exists..
[14:56:02][Install-DBOScript] SQL exception has occured in script: '2.error_file.sql'
[14:56:02][Install-DBOScript] Script block number: 0; Block line 1; Procedure ; Number 208; Message: Invalid object name 'Table_2'.
[14:56:02][Install-DBOScript] System.Data.SqlClient.SqlException (0x80131904): Invalid object name 'Table_2'.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
   at DbUp.Support.ScriptExecutor.ExecuteAndLogOutput(IDbCommand command)
   at DbUp.SqlServer.SqlScriptExecutor.ExecuteCommandsWithinExceptionHandler(Int32 index, SqlScript script, Action executeCommand)
ClientConnectionId:050375d6-6e19-482c-b7f3-da3bbb8a2ce2
Error Number:208,State:1,Class:16
[14:56:02][Install-DBOScript] DB exception has occured in script: '2.error_file.sql'
[14:56:02][Install-DBOScript] Script block number: 0; Message: Invalid object name 'Table_2'.
[14:56:02][Install-DBOScript] System.Data.SqlClient.SqlException (0x80131904): Invalid object name 'Table_2'.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
   at DbUp.Support.ScriptExecutor.ExecuteAndLogOutput(IDbCommand command)
   at DbUp.SqlServer.SqlScriptExecutor.ExecuteCommandsWithinExceptionHandler(Int32 index, SqlScript script, Action executeCommand)
   at DBOps.SqlServer.SqlScriptExecutor.ExecuteCommandsWithinExceptionHandler(Int32 index, SqlScript script, Action executeCommand)
   at DbUp.Support.ScriptExecutor.<>c__DisplayClass18_0.<Execute>b__0(Func`1 dbCommandFactory)
   at DbUp.Engine.Transactions.NoTransactionStrategy.Execute(Action`1 action)
   at DbUp.Engine.Transactions.DatabaseConnectionManager.ExecuteCommandsWithManagedConnection(Action`1 action)
   at DbUp.Support.ScriptExecutor.Execute(SqlScript script, IDictionary`2 variables)
ClientConnectionId:050375d6-6e19-482c-b7f3-da3bbb8a2ce2
Error Number:208,State:1,Class:16
[14:56:02][Install-DBOScript] Upgrade failed due to an unexpected exception:
System.Data.SqlClient.SqlException (0x80131904): Invalid object name 'Table_2'.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
   at DbUp.Support.ScriptExecutor.ExecuteAndLogOutput(IDbCommand command)
   at DbUp.SqlServer.SqlScriptExecutor.ExecuteCommandsWithinExceptionHandler(Int32 index, SqlScript script, Action executeCommand)
   at DBOps.SqlServer.SqlScriptExecutor.ExecuteCommandsWithinExceptionHandler(Int32 index, SqlScript script, Action executeCommand)
   at DbUp.Support.ScriptExecutor.<>c__DisplayClass18_0.<Execute>b__0(Func`1 dbCommandFactory)
   at DbUp.Engine.Transactions.NoTransactionStrategy.Execute(Action`1 action)
   at DbUp.Engine.Transactions.DatabaseConnectionManager.ExecuteCommandsWithManagedConnection(Action`1 action)
   at DbUp.Support.ScriptExecutor.Execute(SqlScript script, IDictionary`2 variables)
   at DbUp.Engine.UpgradeEngine.PerformUpgrade()
ClientConnectionId:050375d6-6e19-482c-b7f3-da3bbb8a2ce2
Error Number:208,State:1,Class:16
False
localhost
C:\labs\dbup-pwsh\sql-scripts\1.table_1.sql
C:\labs\dbup-pwsh\sql-scripts\2.error_file.sql
SqlServer
SchemaVersions

Errors             : {System.Data.SqlClient.SqlError: Invalid object name 'Table_2'.}
ClientConnectionId : 050375d6-6e19-482c-b7f3-da3bbb8a2ce2
Class              : 16
LineNumber         : 1
Number             : 208
Procedure          :
Server             : localhost
State              : 1
Source             : Core .Net SqlClient Data Provider
IsTransient        : False
SqlState           :
BatchCommand       :
ErrorCode          : -2146232060
TargetSite         : Void OnError(System.Data.SqlClient.SqlException, Boolean, System.Action`1[System.Action])
Message            : Invalid object name 'Table_2'.
Data               : {HelpLink.ProdName, HelpLink.ProdVer, HelpLink.EvtSrc, HelpLink.EvtID…}
InnerException     :
HelpLink           :
HResult            : -2146232060
StackTrace         :    at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
                        at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock,
                     Boolean asyncClose)
                        at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream,
                     BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
                        at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
                        at System.Data.SqlClient.SqlDataReader.get_MetaData()
                        at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
                        at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean
                     returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)
                        at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
                        at DbUp.Support.ScriptExecutor.ExecuteAndLogOutput(IDbCommand command)
                        at DbUp.SqlServer.SqlScriptExecutor.ExecuteCommandsWithinExceptionHandler(Int32 index, SqlScript script, Action
                     executeCommand)
                        at DBOps.SqlServer.SqlScriptExecutor.ExecuteCommandsWithinExceptionHandler(Int32 index, SqlScript script, Action
                     executeCommand)
                        at DbUp.Support.ScriptExecutor.<>c__DisplayClass18_0.<Execute>b__0(Func`1 dbCommandFactory)
                        at DbUp.Engine.Transactions.NoTransactionStrategy.Execute(Action`1 action)
                        at DbUp.Engine.Transactions.DatabaseConnectionManager.ExecuteCommandsWithManagedConnection(Action`1 action)
                        at DbUp.Support.ScriptExecutor.Execute(SqlScript script, IDictionary`2 variables)
                        at DbUp.Engine.UpgradeEngine.PerformUpgrade()

44.5692
Date        : 22/04/2022 12:00:00 AM
Day         : 22
DayOfWeek   : Friday
DayOfYear   : 112
Hour        : 14
Kind        : Local
Millisecond : 205
Minute      : 56
Month       : 4
Second      : 2
Ticks       : 637862361622055376
TimeOfDay   : 14:56:02.2055376
Year        : 2022
DateTime    : Friday, 22 April 2022 2:56:02 PM

Date        : 22/04/2022 12:00:00 AM
Day         : 22
DayOfWeek   : Friday
DayOfYear   : 112
Hour        : 14
Kind        : Local
Millisecond : 250
Minute      : 56
Month       : 4
Second      : 2
Ticks       : 637862361622501068
TimeOfDay   : 14:56:02.2501068
Year        : 2022
DateTime    : Friday, 22 April 2022 2:56:02 PM

Date        : 22/04/2022 12:00:00 AM
Day         : 22
DayOfWeek   : Friday
DayOfYear   : 112
Hour        : 14
Kind        : Local
Millisecond : 250
Minute      : 56
Month       : 4
Second      : 2
Ticks       : 637862361622501068
TimeOfDay   : 14:56:02.2501068
Year        : 2022
DateTime    : Friday, 22 April 2022 2:56:02 PM
michaelsync commented 2 years ago

Hi @nvarscar Let me know if you need more information on this issue. Thanks.

nvarscar commented 2 years ago

I'm experimenting with what I can do. Seems like returning failed script name should be possible.

nvarscar commented 2 years ago

Version 0.8.0 now properly supports -ErrorAction SilentlyContinue and you would be able to retrieve the result object even when the deployment fails. The object would have a new property ErrorScript, which would contain a name of the failed script.