carol maley

carol maley

  • NA
  • 5
  • 394

Copy MySQL database to SQL Server using .Net

Jan 30 2020 8:26 PM
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.

Answers (2)