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

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

copying a table from one database to another

Hey

in query analyzer, how do you copy a table form one db to another db

i thort it was something like

select * into dbo.databaseA.tableNew from dbo.databaseB.tableOld

cheers

insert into databaseA..tableNewselect *From databaseb..tableOld

|||

The difference between SELECT INTO and INSERT INTO is that with INSERT the table must already exist. SELECT INTO creates a new table.

Your original query looked okay, assuming that you wanted a new table tableNew. What error were you getting? You might also have a permissions problem since you are going from one database to another.

Don

|||

hey

yeah... thats why i 'd like to use select into or otherwise i'll have to create the other table ( not as fast )

when i try to run

select * into dbo.databaseA.tableNew from dbo.databaseB.tableOld

i get

Server: Msg 208, Level 16, State 1, Line 1
Invalid object name dbo.databaseB.tableOld

i've tripple checked the spelling and tried the same thing with other databases on other computers and got the same error so i'm sure its not a permission error or anything, must be syntax

cheers

|||Use this:select * into databaseA..tableNew from databaseB..tableOld|||

Matt-dot-net:

Use this:select * into databaseA..tableNew from databaseB..tableOld

<groan> I HATE when I miss things like that!

Don

|||

cheers bruva, just what i needed