Home Dashboard Directory Help
Search

SMO 2012 disregards default constraint by Puterdo Borato


Status: 

Closed
 as Fixed Help for as Fixed


7
0
Sign in
to vote
Type: Bug
ID: 745566
Opened: 6/1/2012 12:54:33 AM
Access Restriction: Public
0
Workaround(s)
view
5
User(s) can reproduce this bug

Description

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.

Code:
            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.AddDefaultConstraint();
            column.DefaultConstraint.Text = "('aaa')";

            table.Columns.Add(column);
            table.Alter();

Results:

-- 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
Details
Sign in to post a comment.
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
Hello,

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.

Regards,
Steve (Steven.Keller@citrix.com)
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.

Thanks,

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))
        return;
     this.DefaultConstraint.scriptDefaultConstraintEmbedded = false;
     sb.Append(this.DefaultConstraint.ScriptDdl(sp));
    }

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?
Sign in to post a workaround.