.Net sqlClient Data Provider error message in not showed

[Oleksii_Bereza]

Hi,

When I try to deploy a NuGet package with Deploy.ps1 file which calls sqlpackage.exe to deploy a dacpac file I can not see sql related error messages which I see when I call Deploy.ps1 script manually.
Therefore it is hard to understand the root cause of the issue.

Octopus log doesn’t show the error like below

Error SQL72014: .Net SqlClient Data Provider: Msg 8134, Level 16, State 1, Line 13 Divide by zero error encountered.
Error SQL72045: Script execution error. The executed script:
SELECT 1 / 0;

Final part of the Deploy.ps1:

& “$sqlPackageFileName” /Action:Publish
/SourceFile:"""$sourceFile""" `
/Profile:"""$profileDirectory$profile""" 2>&1 | tee -filePath $logFilesDirectory\Output_$timestamp.txt

Output in console (the same message is in Output.txt):

Initializing deployment (Start)
Initializing deployment (Complete)
Analyzing deployment plan (Start)
Analyzing deployment plan (Complete)
Updating database (Start)
An error occurred while the batch was being executed.
Updating database (Failed)
sqlpackage.exe : *** Could not deploy package.
At C:\Projects\MyProject\Project\bin\Debug\MyProject.Deploy.ps1:43 char:1
+ & “$sqlPackageFileName” `
+ ~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (*** Could not deploy package.:String) [], RemoteException
+ FullyQualifiedErrorId : NativeCommandError

Error SQL72014: .Net SqlClient Data Provider: Msg 8134, Level 16, State 1, Line 13 Divide by zero error encountered.
Error SQL72045: Script execution error. The executed script:
SELECT 1 / 0;

Log in octopus (the same message is in Output.txt):

Initializing deployment (Start)
Initializing deployment (Complete)
Analyzing deployment plan (Start)
Analyzing deployment plan (Complete)
Updating database (Start)
An error occurred while the batch was being executed.
Updating database (Failed)
sqlpackage.exe : *** Could not deploy package.
At C:\Projects\MyProject\Project\bin\Debug\MyProject.Deploy.ps1:43 char:1
+ & “$sqlPackageFileName” `
+ ~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (*** Could not deploy package.:String) [], RemoteException
+ FullyQualifiedErrorId : NativeCommandError

The remote script failed with exit code 1

Thanks,
Oleksii

[Robert_Wagner]

Hi,

Thank you for getting in touch. Does the output appear if you remove the 2>&1? What about when you remove the tee statement?

Also at what logging level is the *** Could not deploy package. and subsequent messages logged at? Have you turned the log view up to Verbose? Are you able to attach that section from the raw log

Regards

Robert W

[Oleksii_Bereza]

Hi Robert,

If I remove 2>&1 I can see the expected error message in Octopus Log, but still don’t see it in Output.txt.
So, power shell statement “2>&1 | tee” doesn’t work properly.

The following approach also doesn’t work (but it work in console)
2> Errors.txt
Get-Content Errors.txt

It looks like there is some problem that Octopus can not read standard errors stream (2>) if it defined.

Also I tried to I to invoke it using cmd.exe /c. It did not help as well.
https://help.octopusdeploy.com/discussions/questions/7492-suppressing-errors-in-custom-powershell-scripts

Thanks,
Oleksii

[Robert_Wagner]

Hi,

I was able to reproduce this. We run scripts with $ErrorActionPreference = 'Stop', which means that PowerShell won’t process the next command (ie the output from tee) . You can try this yourself by setting that variable in your console.

You can change this setting within your script, but then you need to handle stopping at the right point (otherwise it will continue running the rest of the script.

Rob W

[Oleksii_Bereza]

Thank you very much! Now It’s clear.