Why does .Script and .EnumScript not accept ScriptingOptions objects as arguments?

45 views Asked by At

Background
I'm working on a tech debt project where I'm revising my team's build processes. In this, I'm working on being able to deploy database changes from a local database to our Dev database using scripts that are generated by our build process.

When researching an alternative to our current process, I came across this SO question and answer, which has an answer that includes some Powershell script fragments that use SQL Studio's Get-SqlDatabase cmdlet to get a handle on the set of tables, and call .Script(ScriptingOptions) to generate a SQL script file.

Problem
Whenever I try to run the following script...

$Database = Get-SqlDatabase -Name "MyRadDatabase" -ServerInstance "(local)\MyLocalDb"
$Options = New-Object -TypeName Microsoft.SqlServer.Management.Smo.ScriptingOptions
# For sanity check, I'm not setting any options!
$Database.Tables.Script($Options)

...I get the following error:

Cannot find an overload for "Script" and the argument count: "1".
At line:4 char:1
+ $Database.Tables.Script($Options)
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo          : NotSpecified: (:) [], MethodException
+ FullyQualifiedErrorId : MethodCountCouldNotFindBest

In addition, I did the following things before asking this question:

  • Included a set of valid options
  • Called EnumScript instead of Script
  • Attempted to create a Scripter object, which could have .Script called with options

...Which, of course, is wrong according to the MS documentation...

...Though, that documentation is also pretty woefully wrong as well. For instance, while it is possible to call $Database.Tables.Script() - which, TableCollection doesn't have a noted Script or EnumScript function in the documentation - all instances of the Script and EnumScript methods tends to include an overload that accepts a ScriptingOptions method as an argument so that the programmer has better control over the script that the SQL Server CLI generates.

Question
In what way can I control how the SMO Scripting functionality generates SQL scripts?

0

There are 0 answers