February 26, 2009 at 1:08 am
VS VB 2008
SQL server 2008
Im trying to create a column in a table. I have been able to drop and rename columns but can not create. Can anyone see whats wrong with the code.
I'm getting the error 'Create failed for Table 'dbo.employees' on tb.create()
Thanks in advance.
Imports Microsoft.SqlServer.Management.Smo
Imports Microsoft.SqlServer.Management.Common
Function AddSQLColumn(ByVal Name As String) As Boolean
Dim svr As New Server(SERVER_NAME)
svr.ConnectionContext.LoginSecure = False
svr.ConnectionContext.Login = DB_USER
svr.ConnectionContext.DatabaseName = DATABASE_NAME
svr.ConnectionContext.Password = DB_PASSWORD
svr.ConnectionContext.Connect()
Dim db As Database = svr.Databases(DATABASE_NAME)
Dim tb As New Table(db, "employees")
Dim col As New Column(tb, Name, DataType.Int)
col.Nullable = True
tb.Columns.Add(col)
tb.Create()
End Function
February 26, 2009 at 6:15 am
I'm assuming the table already exists? If so, you're doing this the wrong way as you're trying to create the table which will fail as the table already exists.
try:
Dim db As Database = svr.Databases("MyDatabase")
Dim tb As Table = db.Tables("MyTable")
Dim col As New Column(tb, Name, DataType.Int)
col.Nullable = True
tb.Columns.Add(col)
tb.Alter()
February 26, 2009 at 6:21 am
Thanks for the reply but I have tried this also and even resorted to desperations such as refresh.
I can create the column no problems with TSQL but I wish to migrate to SMO.
I need to ship the beta within the next few days so I guess I'm stuck with TSQL.
I'm guess I'm just getting frustrated but I thank you for your help.
February 26, 2009 at 6:23 am
What's the inner exception when you use the code I posted? Definitely works in my environment
February 26, 2009 at 6:26 am
This is what I get
'Alter failed for Table 'dbo.employees' on tb.Alter()
February 26, 2009 at 6:36 am
That looks like the outer exception, what's the inner exception?
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply