dbatools
dbatools copied to clipboard
dataplat
Export-DbaSysDbUserObject ignores -ScriptingOptionsObject parameter
Verified issue does not already exist?
I have searched and found no existing issue
What error did you receive?
Scripting options passed with -ScriptingOptionsObject ignored by the cmdlet.
Steps to Reproduce
It's a bit tricky to test -ScriptingOptionsObject parameter. In the code below I check if an output script creates a table only if it is not exist yet. This not-by-default behavior turns on by -ScriptingOptionsObject.
$msSqlName = 'YOUR_MSSQL_INSTANCE_NAME'
$msSqlDb = 'msdb'
$tstSchName = 'dbo'
$tstTblName = 'foo'
$conn = Connect-DbaInstance -SqlInstance $msSqlName -TrustServerCertificate -Database $msSqlDb
# Let's create a table to seek it in the output script latter.
Invoke-DbaQuery -SqlInstance $conn `
-Query ("DROP TABLE IF EXISTS [$tstSchName].[$tstTblName];" + `
"CREATE TABLE [$tstSchName].[$tstTblName] (bar INT);")
# Do the test
$scriptOpts = New-DbaScriptingOption
$scriptOpts.IncludeIfNotExists = $true
$scriptOpts.EnforceScriptingOptions = $true # Just an extra precausion.
Export-DbaSysDbUserObject -SqlInstance $conn -ScriptingOptionsObject $scriptOpts -PassThru `
| Where-Object -FilterScript { $_ -match $tstSchName -and $_ -match $tstTblName } `
| Out-Host
$conn | Disconnect-DbaInstance | Out-Null
Got
CREATE TABLE [dbo].[foo](
[bar] [int] NULL
) ON [PRIMARY]
GO
The code below exports the same table with Export-DbaScript cmdlet using -ScriptingOptionsObject parameter. It works as expected.
$msSqlName = 's-tst-sql-01'
$msSqlDb = 'msdb'
$tstSchName = 'dbo'
$tstTblName = 'foo'
$conn = Connect-DbaInstance -SqlInstance $msSqlName -TrustServerCertificate -Database $msSqlDb
# Let's create a table to seek it in the output script latter.
Invoke-DbaQuery -SqlInstance $conn `
-Query ("DROP TABLE IF EXISTS [$tstSchName].[$tstTblName];" + `
"CREATE TABLE [$tstSchName].[$tstTblName] (bar INT);")
# Do the control test
$scriptOpts = New-DbaScriptingOption
$scriptOpts.IncludeIfNotExists = $true
$scriptOpts.EnforceScriptingOptions = $true # Just an extra precausion.
Get-DbaDbTable -SqlInstance $conn -Database $msSqlDb -Schema $tstSchName -Table $tstTblName -IncludeSystemDbs `
| Export-DbaScript -ScriptingOptionsObject $scriptOpts -PassThru `
| Where-Object -FilterScript { $_ -match $tstSchName -and $_ -match $tstTblName } `
| Out-Host
$conn | Disconnect-DbaInstance | Out-Null
Got
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[foo]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[foo](
[bar] [int] NULL
) ON [PRIMARY]
END
Please confirm that you are running the most recent version of dbatools
2.1.30
Other details or mentions
I tracked down the issue to the following line of the cmdlet:
if (!(Test-Bound -ParameterName ScriptingOption)) {
It turned out Test-Bound doesn't allow partial names of script parameters.
What PowerShell host was used when producing this error
Windows PowerShell (powershell.exe)
PowerShell Host Version
Name Value
---- -----
PSVersion 5.1.19041.3031
PSEdition Desktop
PSCompatibleVersions {1.0, 2.0, 3.0, 4.0...}
BuildVersion 10.0.19041.3031
CLRVersion 4.0.30319.42000
WSManStackVersion 3.0
PSRemotingProtocolVersion 2.3
SerializationVersion 1.1.0.1
SQL Server Edition and Build number
Microsoft SQL Server 2016 (SP2-CU17) (KB5001092) - 13.0.5888.11 (X64) Mar 19 2021 19:41:38 Copyright (c) Microsoft Corporation Developer Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: ) (Hypervisor)
.NET Framework Version
Get-ChildItem 'HKLM:\SOFTWARE\Microsoft\NET Framework Setup\NDP' -Recurse | Get-ItemProperty -Name version -EA 0 | Where PSChildName -Match '^(?!S)\p{L}' | Select PSChildName, version
Got
PSChildName Version
----------- -------
v2.0.50727 2.0.50727.4927
v3.0 3.0.30729.4926
Windows Communication Foundation 3.0.4506.4926
Windows Presentation Foundation 3.0.6920.4902
v3.5 3.5.30729.4926
Client 4.8.04084
Full 4.8.04084
Client 4.0.0.0
