SMO 2012 disregards default constraint - by Puterdo Borato

Status : 

  Fixed<br /><br />
		This item has been fixed in the current or upcoming version of this product.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 745566 Comments
Status Closed Workarounds
Type Bug Repros 6
Opened 6/1/2012 12:54:33 AM
Access Restriction Public


We are experiencing issue with MicrosoftR SQL ServerR 2012 Shared Management Objects.
Executing the same code snippet gives us different results for SMO 2008 and 2012.
            Microsoft.SqlServer.Management.Smo.Server server = new Microsoft.SqlServer.Management.Smo.Server("(local)");
            Microsoft.SqlServer.Management.Smo.Database db = server.Databases["mydbname"];
            Microsoft.SqlServer.Management.Smo.Table table = db.Tables["mytable1", "dbo"];
            var column = new Microsoft.SqlServer.Management.Smo.Column();
            column.Parent = table;
            column.Name = "col1";
            column.Nullable = false;
            column.DataType = Microsoft.SqlServer.Management.Smo.DataType.NVarChar(10);
            if (column.DefaultConstraint == null)
            column.DefaultConstraint.Text = "('aaa')";
-- from SMO 2008
ALTER TABLE [dbo].[mytable1] ADD [col1] [nvarchar](10) NOT NULL CONSTRAINT [DF_mytable1_col1]  DEFAULT ('aaa')
--from SMO 2012
ALTER TABLE [dbo].[mytable1] ADD [col1] [nvarchar](10) NOT NULL
Sign in to post a comment.
Posted by Quint on 7/12/2017 at 2:15 AM
When you install CU(*) of 2014 or 2016 you will get the same kind of error.
Workaround : Uninstall CU
Posted by Dave TechNet on 6/29/2017 at 1:50 AM
I can confirm that this behavior is back in SMO 2016 (SP1) AND in SMO 2017.
The problem is not there in SMO 2014 however.
Please reopen
Posted by Josef_Blattler on 4/10/2017 at 6:55 AM
Hi everyone!
I would like to note that this situation is occurring with SQL 2016 SP1.
Could someone inform me if this issue will be fixed?
Or some behavior has changed since SQL2014 that my code is not updated?
Posted by ToyMaster458 on 12/21/2015 at 9:54 AM
This is still an issue when adding a column that is a DataType of SmallInt
Posted by Robert Bastien on 1/30/2014 at 10:54 AM
Is there a time line on when SP2 for SQL12 will be released ? Haven't found anything... anywhere concerning SP2.... a lot of hype around SQL14.. that's all
Posted by Puterdo Borato on 8/30/2013 at 1:13 PM
Doesn't dirty hack i described in initial post work?
>>Thus very dirty workaround is possible with setting the flag scriptDefaultConstraintEmbedded via reflection and swallowing the consequent exception but the official fix would be much better.
Posted by Jeffrey Hales on 8/21/2013 at 9:04 AM
Really still looking for answers for this. Has anyone found a hotfix or SP2 as Microsoft claimed a year ago. Or has anyone got a work around?
Posted by Josef_Blattler on 5/22/2013 at 10:46 AM
I had the same problem! Has anyone got any response on this case? Any release date? Thanks!
Posted by willbond on 1/11/2013 at 12:01 PM
Can this be made available in a hotfix so we don't have to wait until SP2 is released?

It is very frustrating to have other tools (such as SSDT) install SQL Server 2012 components which end up subtly breaking existing power shell scripts.
Posted by Microsoft on 9/4/2012 at 10:15 AM

I just wanted to update you that this issue was resolved and you should see the fix in next major reelase. In addition we will provide this fix in SP2 for SQL12.

Let me know if you have any questions,

Thank you
Alex Grach [MSFT]
Posted by SteveK - Citrix on 8/30/2012 at 10:19 AM
We are encountering this issue too and would like to understand when a package resolving the issue will be delivered (e.g. be available for download). If there is a beta program that will make this available, please let me know and I will see if our TAM can help us participate.

Steve (
Posted by Microsoft on 8/4/2012 at 6:24 PM
A recent change to SMO scripting features support caused default constraints to added in a seperate query when creatng a new column. This is not an issue unless the column being added is cannont be null and the table the column being added is not empty. This problem has been resolved and will release on the next build.
Posted by Jeffrey Hales on 7/31/2012 at 2:17 PM
Is there any new information on this issue, it is preventing us from supporting sql2012?
Posted by Microsoft on 6/20/2012 at 2:58 PM
Thank you for reporting this issue - we are investigating and we will get back to you shortly.


Alex Grach
Posted by Puterdo Borato on 6/7/2012 at 6:05 AM
I took a look at sources of Column class in SMO library.
There is this method:
    private void ScriptDefaultConstraint(StringBuilder sb, ScriptingPreferences sp)
     if (this.DefaultConstraint == null || this.DefaultConstraint.IgnoreForScripting && !sp.ForDirectExecution || (this.Parent is Table && !this.DefaultConstraint.scriptDefaultConstraintEmbedded || sb.Length <= 0))
     this.DefaultConstraint.scriptDefaultConstraintEmbedded = false;

In debug i can see that this.DefaultConstraint.scriptDefaultConstraintEmbedded == false all the time and there is no way to set it true.
I use reflection to set this property to true and column got added with constraint but SMO has generated also separate statement for adding default constraint and call table.Alter() failed with exception.
Thus very dirty workaround is possible with setting the flag scriptDefaultConstraintEmbedded via reflection and swallowing the consequent exception but the official fix would be much better.
Can we expect fix in the nearest future please?