Do you have a comment or suggestion to improve SQL Server? We’d love to hear it!

sql server smo generating inline defaultconstraint when adding a column to a table containing data

I am experiencing unwanted changes between different versions of smo.
With versie 12.0.5000 everything works as used before with older version like 10.x or 11.x.
But with version 12.0.5540 (cu4) and 12.0.5546 (cu5) the output is changed to 2 lines of code to add column AND another for adding default constraint. I know there have been some issues regarding smo with the november hotfix for different sql server versions.
With powershell script below, i tried different smo versions.
#12.0.5000.10 , Gives expected result column add with default constraint
#12.0.5540.0 , does not work!
#12.0.5546.0 , does not work!
#SqlException: ALTER TABLE only allows columns to be added that can contain nulls, or have a DEFAULT definition specified, or the column being added is an identity or timestamp column, or alternatively if none of the previous conditions are satisfied the table must be empty to allow addition of this column. Column cannot be added to non-empty table because it does not satisfy these conditions.

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo");
$srv = new-Object Microsoft.SqlServer.Management.Smo.Server("SQL1")

$db = $srv.Databases.Item("SomeDatabase")

$tb = $db.Tables.Item("ExistsContainingData")

$col1 = new-object Microsoft.SqlServer.Management.Smo.Column($tb, "ColWithConstraint", [Microsoft.SqlServer.Management.Smo.DataType]::NVarChar(50))

$constraint = $col1.AddDefaultConstraint()
$constraint.Text ="('')"
$col1.Nullable = $false

$tb.Columns.Add($col1)
$tb.Alter()

0 votes
Sign in
Check!
(thinking…)
Reset
or sign in with
  • facebook
  • google
    Password icon
    Signed in as (Sign out)

    We’ll send you updates on this idea

    AdminMicrosoft SQL Server (Admin, Microsoft Azure) shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

    1 comment

    Sign in
    Check!
    (thinking…)
    Reset
    or sign in with
    • facebook
    • google
      Password icon
      Signed in as (Sign out)
      Submitting...

      Feedback and Knowledge Base