Click here to Skip to main content
15,885,985 members
Articles / Programming Languages / Visual Basic
Tip/Trick

Copy tables between servers with smo - different collation

Rate me:
Please Sign up or sign in to vote.
5.00/5 (1 vote)
18 Jan 2012CPOL 18.5K   2   1
Copy tables between servers with smo - different collation
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):

VB
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)

' names of the tables involved
    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")

' delete table if exists
    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)

' add columns to new table
    For Each col As Column In tabell_source.Columns
    ' *** important... this changes collation of smo.table.column
        ' you copy from, the original table in the database is not changed
        col.Collation = "Danish_Norwegian_CI_AS"
        col.Alter()
    ' *** end of collation change
        Dim lCol As Column = New Column(newTable, col.Name, col.DataType)
    ' set collation to new table column
        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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Norway Norway
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
QuestionThe source table is actually modified Pin
trn1820-Aug-12 20:31
trn1820-Aug-12 20:31 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.