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 table from one database to another

I would like to copy a table from one database to another. These two
databases reside in two different servers/instances? Is this possible? If so,
how can I code it in m C++ application? Thanks!
You have 2 choices, Database solution or client based solution.
Database: Have your DBA create a linked server connection to server2 on
server1. Now you can execute this statement from server1
INSERT MyDest (column_list) SELECT column_list from
server2.mydb.dbo.MySource where ...
Client based solution:
Setup ADO connection to both servers. Populate a recordset from the source db
Iterate through this recordset and insert records in destination using a
procedure that has your insert logic.
HTH
Arun
"luv2travel" wrote:

> I would like to copy a table from one database to another. These two
> databases reside in two different servers/instances? Is this possible? If so,
> how can I code it in m C++ application? Thanks!

copying table from one database to another

I would like to copy a table from one database to another. These two
databases reside in two different servers/instances? Is this possible? If so,
how can I code it in m C++ application? Thanks!You have 2 choices, Database solution or client based solution.
Database: Have your DBA create a linked server connection to server2 on
server1. Now you can execute this statement from server1
INSERT MyDest (column_list) SELECT column_list from
server2.mydb.dbo.MySource where ...
Client based solution:
Setup ADO connection to both servers. Populate a recordset from the source db
Iterate through this recordset and insert records in destination using a
procedure that has your insert logic.
HTH
Arun
"luv2travel" wrote:
> I would like to copy a table from one database to another. These two
> databases reside in two different servers/instances? Is this possible? If so,
> how can I code it in m C++ application? Thanks!

copying table from one database to another

I would like to copy a table from one database to another. These two
databases reside in two different servers/instances? Is this possible? If so
,
how can I code it in m C++ application? Thanks!You have 2 choices, Database solution or client based solution.
Database: Have your DBA create a linked server connection to server2 on
server1. Now you can execute this statement from server1
INSERT MyDest (column_list) SELECT column_list from
server2.mydb.dbo.MySource where ...
Client based solution:
Setup ADO connection to both servers. Populate a recordset from the source d
b
Iterate through this recordset and insert records in destination using a
procedure that has your insert logic.
HTH
Arun
"luv2travel" wrote:

> I would like to copy a table from one database to another. These two
> databases reside in two different servers/instances? Is this possible? If
so,
> how can I code it in m C++ application? Thanks!

copying selective tables with SMO(VB)

Hi ,

I’m using following VB script (SMO) to copy tables between instances and it works fine. At the moment

xfr.CopyAllTables = True

but I would like to be able to be selective (list of table) that would be transferred.

Dim svr_connection As ServerConnection = New ServerConnection()

svr_connection.ServerInstance = "TECH-2L56H2J\sql2k5"

'svr_connection.ConnectionString = "Data Source=TECH-907WL2J;Initial Catalog=CustomerDM_Control;Trusted_Connection=true"

Dim dest_svr_connection As ServerConnection = New ServerConnection()

dest_svr_connection.ServerInstance = "TECH-2L56H2J\sql2k5_2"

'dest_svr_connection.ConnectionString = "Data Source=TECH-907WL2J;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("adventureworks")

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

Dim dbCopy As Database

dbCopy = New Database(dest_srv, "adventureworkscopy")

dbCopy.Create()

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

Dim xfr As Transfer

xfr = New Transfer(db)

xfr.CopyAllTables = True

'xfr.ObjectList.Add("Shaunt")

xfr.DestinationDatabase = "adventureworkscopy"

xfr.DestinationServer = "TECH-2L56H2J\sql2k5_2"

'xfr.DestinationLoginSecure = True

xfr.CopySchema = True

xfr.CopyData = True

xfr.CopySchema = True

xfr.Options.WithDependencies = True

'Script the transfer. Alternatively perform immediate data transfer with TransferData method.

'xfr.ScriptTransfer()

xfr.TransferData()

can you give me any idea?

Thanks

Moving thread...

Copying data between database tables

Hi. I need to move data from one database table to

another across database instances. A simple example of the typical

move would be:

[CODE]

INSERT into destination_db.dbo.table1

SELECT column1, column2, column3, column4 from source_db.dbo.table2

[/CODE]

My options are:

1. Create an SSIS package to perform the move.

2. Create sprocs and schedule the data move as jobs.

3. Write .NET code using sprocs to perform the move.

I'll have to move hundreds of thousands of records, so I want the

option that provides the best performance. I'm guessing that option 3

will be the slowest.

Thanks for the help!
To tell you the truth, you could just try it and find out which runs faster. We are probably talking seconds here at the 100,000 records level. One thing you might want to look into is partitions. If the table you are pulling to and from are identical then the switch option of the partion method with tables makes this happen in a second regardless of the number of records. It is just swapping data pages.|||

BSHOE wrote:

To tell you the truth, you could just try it and find out which runs faster. We are probably talking seconds here at the 100,000 records level. One thing you might want to look into is partitions. If the table you are pulling to and from are identical then the switch option of the partion method with tables makes this happen in a second regardless of the number of records. It is just swapping data pages.

BSHOE,

That won't work if the tables are on different instances. Which in this case they are.

To the original poster - if pure performance is your consideration then SSIS is the way to go.

-Jamie

|||

Jamie Thomson wrote:

BSHOE wrote:

To tell you the truth, you could just try it and find out which runs faster. We are probably talking seconds here at the 100,000 records level. One thing you might want to look into is partitions. If the table you are pulling to and from are identical then the switch option of the partion method with tables makes this happen in a second regardless of the number of records. It is just swapping data pages.

BSHOE,

That won't work if the tables are on different instances. Which in this case they are.

To the original poster - if pure performance is your consideration then SSIS is the way to go.

-Jamie

I concur with Jamie. Write a simple SSIS job to extract and then load the data. I can pull 100,000 records a second from SQL Server 2005 on my laptop into a flat file. Look on my website for details on how I do this and just substitute a table for the flat file in my demo.