Change DataType using SMO

Category: sql server smodmo


Price Brattin on Tue, 09 Jul 2013 22:25:31

I am using the following SMO code to try to change the datatype in columns. It blows up at the Tbl.Alter statement. ADOdotNetConn is a SQLClient connection. What can I do to get the program work?

	Dim SMOConn As ServerConnection
	Dim SMOServer As Microsoft.SqlServer.Management.Smo.Server
	  SMOConn = New ServerConnection(ADOdotNetConn)
	  SMOServer = New Microsoft.SqlServer.Management.Smo.Server(SMOConn)
	  SMOServer.ConnectionContext.SqlExecutionModes = SqlExecutionModes.ExecuteSql
	  Dim DBName As String = SMOConn.DatabaseName
	  Dim DB As Database = SMOServer.Databases(DBName)

          Dim Tbl As Table
For Each Tbl In DB.Tables Dim Col As Column For Each Col In Tbl.Columns Dim ColName As String = Col.Name Dim dType As String = Col.DataType.ToString Dim CharLen As Integer = Col.DataType.MaximumLength If dType = "char" AndAlso CharLen > 9 Then Col.DataType = DataType.VarChar(CharLen) Next Tbl.Alter() Next Catch ex As Exception

Price Brattin, SQLServer & SharePoint 2010 MCP, Microsoft Dynamics SL Consultant


GladToHelpYou on Thu, 11 Jul 2013 05:29:27

seems no err, what's the err? try debugging

Price Brattin on Tue, 16 Jul 2013 20:03:19

The error msg is "Alter Failed For Table xxxxxx"

Yes, we tried debug and that is how we know it fails on the alter.

Price Brattin on Thu, 18 Jul 2013 19:00:21

I found that SMO will provide more verbose error information if the following code is used:

Catch smoex As SmoException

  Dim sb As New StringBuilder

  sb.AppendLine("This is an SMO Exception")

  'Display the SMO exception message.


  'Display the sequence of non-SMO exceptions that caused the SMO exception.

  Dim ex As Exception

  ex = smoex.InnerException

  If ex Is Nothing Then


  Do While ex.InnerException IsNot (Nothing)


    ex = ex.InnerException


  End If

  MsgBox(sb.ToString, MsgBoxStyle.Critical, "SMO Error")

  ShowStatus("SMO Error Occurred")

Catch ex As Exception

  frmErrorInfo.txtErrMsg.Text = DispMsg


End Try