If you run an ansible.windows.win_powershell task that causes an error parameterizing a [System.Data.CommandType]::StoredProcedure, the resulting task output will be 2MB of text per host. Is there a way to limit the error payload of a win_powershell task?
SQL isn’t really relevant here. I’m not sure how to generate an example that doesn’t require a SQL server connection though.
The gist of my question is - can the output/error be caught/managed in PowerShell, to return only what’s desired - not the whole script, not the whole error/exception?
Ansible Version
~/c/a/T/ansible ❯❯❯ ansible --version
ansible [core 2.18.1]
config file = /home/cruscio/code/alm/TW-DevOps-pwshwebmsi/ansible/ansible.cfg
configured module search path = ['/home/cruscio/.ansible/plugins/modules', '/usr/share/ansible/plugins/modules']
ansible python module location = /home/cruscio/.local/pipx/venvs/ansible/lib/python3.11/site-packages/ansible
ansible collection location = /home/cruscio/.ansible/collections:/usr/share/ansible/collections
executable location = /home/cruscio/.local/bin/ansible
python version = 3.11.2 (main, Nov 30 2024, 21:22:50) [GCC 12.2.0] (/home/cruscio/.local/pipx/venvs/ansible/bin/python)
jinja version = 3.1.2
libyaml = True
~/c/a/T/ansible ❯❯❯ ansible-galaxy collection list | grep "windows"
ansible.windows 2.5.0
community.windows 2.3.0
Playbook
---
- name: "Too Many MB of error log"
hosts: tw_sql_primary
tasks:
- name: Execute a failing SQL stored procedure
# Use System.Data.SqlClient so we can parameterize the call
# And for consistent behavior:
# - We don't know what version of Invoke-SQLCMD is available
# - and we're not allowed to change/update the SQL PowerShell module
ansible.windows.win_powershell:
error_action: stop
script: |
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = @(
"Server = $ServerName"
'Database = TWCommon'
'Integrated Security = True'
) -join '; '
try {
$SqlConnection.Open()
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.Connection = $SqlConnection
$SqlCmd.CommandType = [System.Data.CommandType]::StoredProcedure
$SqlCmd.CommandText = 'dbo.StoredProcedureThatDoesntExist'
#This causes a (relatively massive exception payload:
$SqlCmd.Parameters.AddWithValue('@somevar', 'Foo')
$SqlCmd.ExecuteNonQuery()
}
catch {
$Error.Clear()
# Tried to limit the output in Ansible by clearing $Error
# That didn't work
Write-Error "Well, that didn't work"
}
finally {
$SqlConnection.Close()
}
Log
2025-02-17 12:45:42,564 p=3104600 u=cruscio n=ansible INFO| PLAY [Too Many MB of error log]
2025-02-17 12:45:42,633 p=3104600 u=cruscio n=ansible INFO| TASK [Gathering Facts]
2025-02-17 12:45:42,633 p=3104600 u=cruscio n=ansible INFO| Monday 17 February 2025 12:45:42 +0000 (0:00:00.070) 0:00:00.070 *******
2025-02-17 12:45:44,556 p=3104600 u=cruscio n=ansible INFO| ok: [my.sql.server.local]
2025-02-17 12:45:44,562 p=3104600 u=cruscio n=ansible INFO| TASK [Execute a failing SQL stored procedure]
2025-02-17 12:45:44,562 p=3104600 u=cruscio n=ansible INFO| Monday 17 February 2025 12:45:44 +0000 (0:00:01.929) 0:00:02.000 *******
2025-02-17 12:45:50,383 p=3104600 u=cruscio n=ansible INFO| fatal: [my.sql.server.local]: FAILED! => changed=true
debug: []
error:
- category_info:
activity: Write-Error
category: NotSpecified
category_id: 0
reason: WriteErrorException
target_name: ''
target_type: ''
error_details: null
exception:
help_link: null
hresult: -2146233087
inner_exception: null
message: Well, that didn't work
source: null
type: Microsoft.PowerShell.Commands.WriteErrorException
fully_qualified_error_id: Microsoft.PowerShell.Commands.WriteErrorException
output: |-
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = @(
"Server = $ServerName"
'Database = TWCommon'
'Integrated Security = True'
) -join '; '
try {
$SqlConnection.Open()
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.Connection = $SqlConnection
$SqlCmd.CommandType = [System.Data.CommandType]::StoredProcedure
$SqlCmd.CommandText = 'dbo.StoredProcedureThatDoesntExist'
$SqlCmd.Parameters.AddWithValue('@somevar', 'Foo')
$SqlCmd.ExecuteNonQuery()
}
catch {
$Error.Clear()
# Tried to limit the output in Ansible by clearing $Error
# That didn't work
Write-Error "Well, that didn't work"
}
finally {
$SqlConnection.Close()
}
: Well, that didn't work
At line:22 char:3
+ Write-Error "Well, that didn't work"
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [Write-Error], WriteErrorException
+ FullyQualifiedErrorId : Microsoft.PowerShell.Commands.WriteErrorException
pipeline_iteration_info:
- 0
- 0
script_stack_trace: 'at <ScriptBlock>, <No file>: line 22'
target_object: null
host_err: ''
host_out: ''
information: []
output:
- CompareInfo:
String: None
Type: System.Data.SqlTypes.SqlCompareOptions
Value: 0
DbType:
String: String
Type: System.Data.DbType
Value: 16
Direction:
String: Input
Type: System.Data.ParameterDirection
Value: 1
ForceColumnEncryption: false
IsNullable: false
LocaleId: 0
Offset: 0
ParameterName: '@somevar'
Precision: 0
Scale: 0
Size: 3
SourceColumn: ''
SourceColumnNullMapping: false
SourceVersion:
String: Current
Type: System.Data.DataRowVersion
Value: 512
SqlDbType:
String: NVarChar
Type: System.Data.SqlDbType
Value: 12
SqlValue:
CompareInfo:
LCID: 1033
Name: en-US
Version:
FullVersion: 394243
SortId: 00000001-57ee-1e5c-00b4-d0000bb1e11e
CultureInfo:
Calendar:
AlgorithmType: 1
CalendarType: 1
Eras:
- 1
IsReadOnly: true
MaxSupportedDateTime: /Date(253402300799999)/
MinSupportedDateTime: /Date(-62135596800000)/
TwoDigitYearMax: 2049
CompareInfo:
LCID: 1033
Name: en-US
Version:
FullVersion: 394243
SortId: 00000001-57ee-1e5c-00b4-d0000bb1e11e
CultureTypes: 70
DateTimeFormat:
AMDesignator: AM
AbbreviatedDayNames:
- Sun
- Mon
- Tue
- Wed
- Thu
- Fri
- Sat
AbbreviatedMonthGenitiveNames:
- Jan
- Feb
- Mar
- Apr
- May
- Jun
- Jul
- Aug
- Sep
- Oct
- Nov
- Dec
- ''
AbbreviatedMonthNames:
- Jan
- Feb
- Mar
- Apr
- May
- Jun
- Jul
- Aug
- Sep
- Oct
- Nov
- Dec
- ''
Calendar:
AlgorithmType: 1
CalendarType: 1
Eras:
- 1
IsReadOnly: true
MaxSupportedDateTime: /Date(253402300799999)/
MinSupportedDateTime: /Date(-62135596800000)/
TwoDigitYearMax: 2049
CalendarWeekRule: 0
DateSeparator: /
DayNames:
- Sunday
- Monday
- Tuesday
- Wednesday
- Thursday
- Friday
- Saturday
FirstDayOfWeek: 0
FullDateTimePattern: dddd, MMMM d, yyyy h:mm:ss tt
IsReadOnly: true
LongDatePattern: dddd, MMMM d, yyyy
LongTimePattern: h:mm:ss tt
MonthDayPattern: MMMM d
MonthGenitiveNames:
- January
- February
- March
- April
- May
- June
- July
- August
- September
- October
- November
- December
- ''
MonthNames:
- January
- February
- March
- April
- May
- June
- July
- August
- September
- October
- November
- December
- ''
NativeCalendarName: Gregorian Calendar
PMDesignator: PM
RFC1123Pattern: ddd, dd MMM yyyy HH':'mm':'ss 'GMT'
ShortDatePattern: M/d/yyyy
ShortTimePattern: h:mm tt
ShortestDayNames:
- Su
- Mo
- Tu
- We
- Th
- Fr
- Sa
SortableDateTimePattern: yyyy'-'MM'-'dd'T'HH':'mm':'ss
TimeSeparator: ':'
UniversalSortableDateTimePattern: yyyy'-'MM'-'dd HH':'mm':'ss'Z'
YearMonthPattern: MMMM yyyy
DisplayName: English (United States)
EnglishName: English (United States)
IetfLanguageTag: en-US
IsNeutralCulture: false
IsReadOnly: true
KeyboardLayoutId: 1033
LCID: 1033
Name: en-US
NativeName: English (United States)
NumberFormat:
CurrencyDecimalDigits: 2
CurrencyDecimalSeparator: .
CurrencyGroupSeparator: ','
CurrencyGroupSizes:
- 3
CurrencyNegativePattern: 0
CurrencyPositivePattern: 0
CurrencySymbol: $
DigitSubstitution: 1
IsReadOnly: true
NaNSymbol: NaN
NativeDigits:
- '0'
- '1'
- '2'
- '3'
- '4'
- '5'
- '6'
- '7'
- '8'
- '9'
NegativeInfinitySymbol: -∞
NegativeSign: '-'
NumberDecimalDigits: 2
NumberDecimalSeparator: .
NumberGroupSeparator: ','
NumberGroupSizes:
- 3
NumberNegativePattern: 1
PerMilleSymbol: ‰
PercentDecimalDigits: 2
PercentDecimalSeparator: .
PercentGroupSeparator: ','
PercentGroupSizes:
- 3
PercentNegativePattern: 1
PercentPositivePattern: 1
PercentSymbol: '%'
PositiveInfinitySymbol: ∞
PositiveSign: +
OptionalCalendars:
- AlgorithmType: 1
CalendarType: 1
Eras:
- 1
IsReadOnly: false
MaxSupportedDateTime: /Date(253402300799999)/
MinSupportedDateTime: /Date(-62135596800000)/
TwoDigitYearMax: 2049
- AlgorithmType: 1
CalendarType: 2
Eras:
- 1
IsReadOnly: false
MaxSupportedDateTime: /Date(253402300799999)/
MinSupportedDateTime: /Date(-62135596800000)/
TwoDigitYearMax: 2049
Parent:
Calendar:
AlgorithmType: 1
CalendarType: 1
Eras:
- 1
IsReadOnly: false
MaxSupportedDateTime: /Date(253402300799999)/
MinSupportedDateTime: /Date(-62135596800000)/
TwoDigitYearMax: 2049
CompareInfo:
LCID: 9
Name: en
Version:
FullVersion: 394243
SortId: 00000001-57ee-1e5c-00b4-d0000bb1e11e
CultureTypes: 69
DateTimeFormat:
AMDesignator: AM
AbbreviatedDayNames:
- Sun
- Mon
- Tue
- Wed
- Thu
- Fri
- Sat
AbbreviatedMonthGenitiveNames:
- Jan
- Feb
- Mar
- Apr
- May
- Jun
- Jul
- Aug
- Sep
- Oct
- Nov
- Dec
- ''
AbbreviatedMonthNames:
- Jan
- Feb
- Mar
- Apr
- May
- Jun
- Jul
- Aug
- Sep
- Oct
- Nov
- Dec
- ''
Calendar:
AlgorithmType: 1
CalendarType: 1
Eras:
- 1
IsReadOnly: false
MaxSupportedDateTime: /Date(253402300799999)/
MinSupportedDateTime: /Date(-62135596800000)/
TwoDigitYearMax: 2049
[... continues on for 20000 lines ...]]