Click here to Skip to main content
12,882,493 members (26,453 online)
Click here to Skip to main content
Add your own
alternative version

Tagged as


2 bookmarked
Posted 18 Jan 2012

Copy tables between servers with smo - different collation

, 18 Jan 2012 CPOL
Rate this:
Please Sign up or sign in to vote.
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):

	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
        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"
	    ' *** 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
        ConnSource = New SqlConnection(Me.ConnSource)
        ConnReciever = New SqlConnection(Me.ConnReciever)
        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
        Catch exSQL As SqlClient.SqlException
            MessageBox.Show(exSQL.ToString, "Bulk Copy Error")
        Catch ex As Exception
            MessageBox.Show(ex.ToString, "Bulk Copy Error - General")
        End Try


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


About the Author

No Biography provided

You may also be interested in...

Comments and Discussions

QuestionThe source table is actually modified Pin
trn1820-Aug-12 20:31
membertrn1820-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.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.170422.1 | Last Updated 18 Jan 2012
Article Copyright 2012 by Morten Lossius
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid