SQL Server How to set a column's default value using Microsoft SMO?

dsekswqp 于 2023-06-21 发布在其他

关注(0)|答案(2)|浏览(120)

I'm trying to set the default value of a non nullable bit column using Microsoft's SMO (C#) but I get an error on table creation.

Currently what I have is

if (!string.IsNullOrWhiteSpace(dynamicColumn.DefaultValue))
{
    column.Default = dynamicColumn.DefaultValue;
}

However when trying to create the table I get the following error:

One or more errors occured.
Create failed for Table'CustomDocument.TestDef9_Data'. An exception occurred while executing a Transact-SQL statement or batch. The default '((0))' does not exist.

I've tried ((0)), (0), and 0, and I get the same error every time.

Is there a different property I need to set on the column object or some other way to do it?

sql-server

来源：https://stackoverflow.com/questions/76443665/how-to-set-a-columns-default-value-using-microsoft-smo

2条答案

按热度按时间

2q5ifsrm1#

I was able to do it thusly (in powershell, but it's conceptually the same):

$t = Get-DbaDbTable -SqlInstance . -Database tempdb -Table foo;
# $t now holds a Table object
$df = $t.columns['a'].AddDefaultConstraint('DF_foo_a');
$df.Text = 0;
$df.Create();

赞(0）回复(0）举报 2023-06-21

w41d8nur2#

You have to use DefaultConstraint property instead. Here a snippet

if (!string.IsNullOrWhiteSpace(dynamicColumn.DefaultValue))
{
    column.DefaultConstraint = new DefaultConstraint(table, "DF_TestDef9_Data_ColumnName");
    column.DefaultConstraint.Text = "(0)";
}

赞(0）回复(0）举报 2023-06-21

我来回答

SQL Server How to set a column's default value using Microsoft SMO?

2条答案

相关问题

热门标签

最新问答