copying tables with SMO between different instances

Hi,

I want to be able to with SMO copy one table (with a schema other than dbo) to another instance.

I can successfully do this on eth same machine (same instance) but when I try the following script to copy accross different instances. it doesn't work as the Transfer methode in SMO doesn't have a property for instance.

would be able to help

Thanks

Public Sub Main()

Dim svr_connection As ServerConnection = New ServerConnection()

'svr_connection.ServerInstance = "(local)"

svr_connection.ConnectionString = "Data Source=TECH-2L56H2J\sql2k5;Initial Catalog=creditcard;Trusted_Connection=true"

Dim dest_svr_connection As ServerConnection = New ServerConnection()

'svr_connection.ServerInstance = "(local)"

dest_svr_connection.ConnectionString = "Data Source=TECH-2L56H2J\sql2k5_2;Initial Catalog=master;Trusted_Connection=true"

Dim srv As Server = New Server(svr_connection)

Dim dest_srv As Server = New Server(dest_svr_connection)

'Reference the source database

Dim db As Database

db = srv.Databases("creditcard")

'Create a new database that is to be destination database.

Dim dbCopy As Database

dbCopy = New Database(dest_srv, "creditcardcopy")

dbCopy.Create()

'Define a Transfer object and set the required options and properties.

Dim xfr As Transfer

'Point the transfer object to the source database (to be copied from)

xfr = New Transfer(db)

'Turn off the copying of all objects - seems to be on by default

xfr.CopyAllObjects = False

'Add the schema, role and table we want to copy (as the tables isn't in dbo, the associated schema and role needs to be moved)

'xfr.ObjectList.Add(db.Roles("test"))

xfr.ObjectList.Add(db.Schemas("test"))

xfr.ObjectList.Add(db.Tables("credittest", "test"))

'We want to copy the data as well

xfr.CopyData = True

'Set other transfer options

xfr.Options.WithDependencies = False

'Set where we are copying to

xfr.DestinationDatabase = "creditcardcopy"

xfr.DestinationServer = "TECH-2L56H2J\sql2k5_2" 'dest_srv.Name

'Do the transfer

xfr.TransferData()

'return success

' Dts.TaskResult = Dts.Results.Success

End Sub

Could you confirm the error you're getting? If you have multiple instances on the same box, you may want to specify port numbers in your connection string (get this from SQL Configuration Manager)

eg Data Source=TECH-2L56H2J\sql2k5_2, 1434

HTH!|||Thanks but tried that already, you see that part works fine and it can connect to both instances and creates the database on the target machine.it fails in the process of transfering objects(tables and schemas). therefore I think it should be down to xfr.DestinationServer ( the transfer object properties )

Thanks

sql