I struggled to find a way to copy tables with different collation between two servers, with the use of VB.NET and smo.
Here's my solution (I've underlined the two lines that I've added, the rest of the code I found on the internet):
Dim source_server_conn As New ServerConnection("OMNIOSLSSQ006\A1")
source_server_conn.LoginSecure = False
source_server_conn.Login = "username"
source_server_conn.Password = "password"
Dim source_server As New Server(source_server_conn)
Dim reciever_server_conn As New ServerConnection("OMNIOSLSSQ001\A2")
reciever_server_conn.LoginSecure = False
reciever_server_conn.Login = "username"
reciever_server_conn.Password = "password"
Dim reciever_server As New Server(reciever_server_conn)
Dim newTable_name As String = "name_of_table_to_copy_to"
Dim tabell_source_name As String = "name_of_table_to_copy_from"
Dim db_source As New Database
db_source = source_server.Databases("from_database")
Dim db_reciever As New Database
db_reciever = reciever_server.Databases("to_database")
If db_reciever.Tables.Contains(newTable_name) Then
db_reciever.Tables(newTable_name).Drop()
End If
Dim newTable As Table = New Table(db_reciever, newTable_name)
Dim tabell_source As Table = db_source.Tables(tabell_source_name)
For Each col As Column In tabell_source.Columns
col.Collation = "Danish_Norwegian_CI_AS"
col.Alter()
Dim lCol As Column = New Column(newTable, col.Name, col.DataType)
lCol.Collation = "Danish_Norwegian_CI_AS"
lCol.Nullable = col.Nullable
newTable.Columns.Add(lCol)
Next
newTable.Create()
ConnSource = New SqlConnection(Me.ConnSource)
ConnSource.Open()
ConnReciever = New SqlConnection(Me.ConnReciever)
ConnReciever.Open()
Dim CommandSource As SqlCommand = New SqlCommand("Select * FROM " & tabell_source_name, ConnSource)
CommandSource.CommandTimeout = 1600
Dim DataReaderSource As SqlDataReader = CommandSource.ExecuteReader
Dim BulkCopyReciever As SqlBulkCopy = New SqlClient.SqlBulkCopy(Me.ConnReciever, SqlBulkCopyOptions.KeepIdentity)
BulkCopyReciever.DestinationTableName = newTable_name
BulkCopyReciever.BulkCopyTimeout = 1600
Try
BulkCopyReciever.WriteToServer(DataReaderSource)
Catch exSQL As SqlClient.SqlException
MessageBox.Show(exSQL.ToString, "Bulk Copy Error")
Catch ex As Exception
MessageBox.Show(ex.ToString, "Bulk Copy Error - General")
Finally
DataReaderSource.Close()
BulkCopyReciever.Close()
End Try