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 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...

Copy Wizard Failure when coping a database on the same machine

I am trying to copy the a database on the same machine (source and destination are the same). The wizard fails using the SMO method. I have attemped to turn on the "Save Transfer logs", but it does not appear to save a file: I have looked in C:\Documents and Settings\<user-name>\Local Settings\Application Data\Microsoft\SQL Server\SMO for all the users on the server. None of them have the SMO directory in the that path nor can I find a SMO directory on the server.

The SQL Server agent account is set to Local System.

I have made the Local Administrator a Sysadmin in SQL server.

Job History after failure:

Date 6/15/2007 8:25:10 PM
Log Job History (CDW_YOUR-BQEHG1XTVQ_YOUR-BQEHG1XTVQ_9)

Step ID 1
Server YOUR-BQEHG1XTVQ
Job Name CDW_YOUR-BQEHG1XTVQ_YOUR-BQEHG1XTVQ_9
Step Name CDW_YOUR-BQEHG1XTVQ_YOUR-BQEHG1XTVQ_9_Step
Duration 00:00:48
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0

Message
Executed as user: YOUR-BQEHG1XTVQ\SYSTEM. ...GUE SQL USE [Tango_QA] GO IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[strpcd_firewall_lan_objects_to_add]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[strpcd_firewall_lan_objects_to_add] GO IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[stpcd_call_view_your_account_firewall_policies]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[stpcd_call_view_your_account_firewall_policies] GO IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[stpcd_call_view_your_account_firewall_objects]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[stpcd_call_view_your_account_firewall_objects] GO IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[stpcd_GetSIPNetworkLocation]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[stpcd_GetSIPNetworkLocation] GO IF EXISTS (SELECT * FROM sys.objects WHERE obje... The package execution fa... The step failed.

Stumped as what what to try next or what the issue is?

What is the level of service pack on SQL Server?

BOL refers:
The destination server must be running SQL Server 2005 Service Pack 2 or a later version. The computer on which the Copy Database Wizard runs may be the source or destination server, or a separate computer. This computer must also be running SQL Server 2005 Service Pack 2 or a later version to use all the features of the wizard.

To use the Copy Database Wizard, you must be a member of the sysadmin fixed server role on the source and destination servers. To transfer databases by using the detach-and-attach method, you must have file system access to the file-system share that contains the source database files.

|||The source and the destination server are one and the same. It is running SQL 2005 SP2. I am using SA to make a copy of the database. The wizard starts to copy the database but then fails before coping all of the tables.|||Ensure SA login is not disabled, you might try using SQL SErver service account in this case.