Copy MySQL database to SQL Server using .Net

Question

I am writing a .net process that reads data from a mySQL database into a datatable, and inserts it into a SQL Server database, using TableTypes. I'm getting "The type of column 'id' is not supported. The type is 'UInt32'. I tried the exact same thing with my SQL Server backup of the mySql database and it works. Any ideas?

 

Dim mySqlConnection As New MySqlConnection(zConfig.MySQLPortalConnectionString)

mySqlConnection.Open()

SqlConnection.Open()

' this passes using SQL Server (exactly the same schema)

Dim sc2 As New SqlCommand("Select * from [Portal_Client_Lists_slave].[dbo].[gnp9l_gptwportal_companies]", SqlConnection)

sc2.CommandTimeout = zConfig.NormalDbTimeoutSeconds()

sc2.CommandType = CommandType.Text

Dim da As SqlDataAdapter = New SqlDataAdapter(sc2)

Dim dt As DataTable = New DataTable("Results")

da.Fill(dt)

Dim sc1 As New SqlCommand("dbo.Insert_Companies", SqlConnection) With {

.CommandTimeout = zConfig.NormalDbTimeoutSeconds(),

.CommandType = CommandType.StoredProcedure

}

sc1.Parameters.Add(GetParameter("@Companies", "dbo.TableType_companies", dt))

sc1.ExecuteNonQuery() ' Yay! It did the job.

Dim sc3 As New SqlCommand("dbo.Insert_Companies", SqlConnection) With {

.CommandTimeout = zConfig.NormalDbTimeoutSeconds(),

.CommandType = CommandType.StoredProcedure

}

Dim Companies As DataTable = runMySQL("Select * from gnp9l_gptwportal_companies", mySqlConnection)

sc3.Parameters.Add(GetParameter("@Companies", "dbo.TableType_companies", Companies))

sc3.ExecuteNonQuery()

--- and here I get an error - "The type of column 'id' is not supported. The type is 'UInt32'.

...... blahblahblah

Private Function GetParameter(ParameterName As String, TableType As String, Value As DataTable) As SqlParameter

Dim myParm As New SqlParameter

myParm.ParameterName = ParameterName

myParm.SqlDbType = SqlDbType.Structured

myParm.Value = Value

myParm.TypeName = TableType

Return myParm

End Function

Public Shared Function runMySQL(ByVal SQLstring As String, mySqlConnection As MySqlConnection) As DataTable

Dim sc As New MySqlCommand(SQLstring, mySqlConnection)

sc.CommandTimeout = zConfig.NormalDbTimeoutSeconds()

sc.CommandType = CommandType.Text

sc.CommandText = SQLstring

Dim da As MySqlDataAdapter = New MySqlDataAdapter(sc)

Dim dt As DataTable = New DataTable("Results")

da.Fill(dt)

Return dt

End Function

 

Thanks,

Carol.