How to copy a sql table from one instance to another

[ivanrossouw2@gmail.com]

Can anyone please tell me how to copy a sql table from my server to my syncronizing client if the schemas dont match

I have tried and came up with this code but my output string is wrong

Dim ds1 As New DataSet
Dim Changes As String = "Success"

Try
Dim cmd1 As New SqlCommand("select name,xtype,length from syscolumns where id=" & AnalyseTables(TableName).Database1ID, GlobalVars.ServerConnection)
Dim da1 As New SqlDataAdapter(cmd1)
da1.Fill(ds1)
Catch ex As Exception
Changes = "Reading table columns from Server Database Failed - " & ex.Message
Return Changes
Exit Function
End Try

Dim PartOfSqlCommand As String = Nothing
Dim PrimaryKey As String = Nothing
For Each dr1 As DataRow In ds1.Tables(0).Rows
Dim Type As String = Nothing
If dr1("length") = Nothing Then
Type = dr1("xtype")
Else
Type = dr1("xtype") & "(" & dr1("length") & ")"
End If

If PartOfSqlCommand = Nothing Then
PartOfSqlCommand = dr1("Name") & " " & Type
PrimaryKey = dr1("Name")
Else
PartOfSqlCommand = PartOfSqlCommand & ", " & dr1("Name") & " " & Type
End If
Next
GlobalVars.ClientConnection.Open()
Dim Command As New SqlCommand("CREATE TABLE " & TableName & "(" & PartOfSqlCommand & ", PRIMARY KEY (" & PrimaryKey & " ) )", GlobalVars.ClientConnection)
Command.ExecuteNonQuery()
Return Changes
End Function

The output I get for Command is : "CREATE TABLE Countries(Country_Id 36(16), Name 231(200), FullName 231(400), TelephoneCode 231(20), AreaCode 231(20), Region 231(200), TimeZone 239(30), Currency 239(200), CurrencySymbol 239(20), VatRate 106(9), Image 34(16), CreatedBy 231(510), Created 61(8), ModifiedBy 231(510), Modified 61(8), PRIMARY KEY (Country_Id ) )"

and it sould be: CREATE TABLE Countries(Country_Id uniqueidentifier, Name nvarchar(100) NULL,FullName nvarchar](200),TelephoneCode nvarchar(10),AreaCode nvarchar(10), Region nvarchar(100), TimeZone nchar(15), Currency nchar(100), CurrencySymbol nchar(10), VatRate decimal(18, 2), Image image, CreatedBy nvarchar(255), Created datetime NULL, ModifiedBy nvarchar(255), Modified datetime, PRIMARY KEY (Country_Id )

 

[jmcilhinney]

If I wanted to get schema information from a SQL Server database, I'd use the SqlConnection.GetSchema method.