Copy table to Remote SQL server using DTS

[ak1dnar]

Hi,
I need to copy some tables from my local MsSQL(2000) database to remote MSSQL server.I think I should use DTS for this purpose.
I can Connect to remote server and I cam copy the tables to it.but the problem is getting here Once I copied it Local Table Constraints not applying to the Remote Host SQL Tables. May be I am doing wrong here.
How to transfer my Table(s) with the same structure as my local host sql table(s).

Thanks!

[Motoma]

If you are copying on a table by table basis, you constraints do not get copied; the server has no way of knowing if the tables referenced by the constraints are the same tables in the destination DB...The table which is referenced by constraints may not even exits!
The only way to preserve all of that information is to backup the database, and restore it on your target machine.

[ak1dnar]

If you are copying on a table by table basis, you constraints do not get copied; the server has no way of knowing if the tables referenced by the constraints are the same tables in the destination DB...The table which is referenced by constraints may not even exits!
The only way to preserve all of that information is to backup the database, and restore it on your target machine.

Thanks Motoma,
Its correct, but I don't have the access to the Logical drives of the remote server. Its acctully a Hosting package.

I tried this

http://www.intermedia.net/support/kb/default.asp?id=1176

But failed with the Last step with this error.

Failed to copy objects to mssql server to mssql server.

[hoomaniraji]

Hi,
I need to copy some tables from my local MsSQL(2000) database to remote MSSQL server.I think I should use DTS for this purpose.
I can Connect to remote server and I cam copy the tables to it.but the problem is getting here Once I copied it Local Table Constraints not applying to the Remote Host SQL Tables. May be I am doing wrong here.
How to transfer my Table(s) with the same structure as my local host sql table(s).

Thanks!

if you do not want to programm in DTS ,then you can use enterprise manager of sql server with the following steps :
1-you should register your remote server in enterprise manager
2-you should register local server and attach the local database in the local server

3-if you have not any database on your remote server.you or your host administrator must create a database for your use.also you must have a user for accessing this database.

4-right click on your local database and click 'Export Data' in 'All Tasks' sub menu .then carry on with the wizard.

5-when wizard asks you the destinition database,introd uce your database in your remote server.

6-in this method you replicate your database completly to your remote database server.i think you will not have such a problem for constraints.bec ause you have a copy of data and metadata on your remote server.

[ak1dnar]

if you do not want to programm in DTS ,then you can use enterprise manager of sql server with the following steps :
1-you should register your remote server in enterprise manager
2-you should register local server and attach the local database in the local server

3-if you have not any database on your remote server.you or your host administrator must create a database for your use.also you must have a user for accessing this database.

4-right click on your local database and click 'Export Data' in 'All Tasks' sub menu .then carry on with the wizard.

5-when wizard asks you the destinition database,introd uce your database in your remote server.

6-in this method you replicate your database completly to your remote database server.i think you will not have such a problem for constraints.bec ause you have a copy of data and metadata on your remote server.

The wizard is same as DTS import/export data that I tried already.

If you can remember these steps:

1.Select Local host database.
2. Select remote host database (alread created on the hosting packaged)

On this prompt you will ask to select on of the option.
with 1st option constraints are not copying.(there might be other stuffs also that not appliying to the remote server after exporting)



Once I tried the last option, it will display this.



I tried with deselecting the Options >>> Security option and didnt change the table options, but its giving the same error.

thanks!

[ak1dnar]

Heya Guys,

It was a permission problem. when creating the user account for me on the SQL server, they've forgotten to assign the dbo. permission on it.The steps that I used, absolutely correct. That stupid error message which came earlier, doesn't tell anything about permissions issues. If so we could figured it out easily.
anyway, Thanks for coming here.