Copying Tables Across Servers

I'm trying to copy data from a remote server connected thru enterprise
manager. I' assuming there shld be a means to script the table structure an
d
the contents. Is this possible and how can I go about it.
--
Beaversuse import/export wizard
"Beavers" wrote:

> I'm trying to copy data from a remote server connected thru enterprise
> manager. I' assuming there shld be a means to script the table structure
and
> the contents. Is this possible and how can I go about it.
> --
> Beavers
>|||I had problems with that because of the server names. Nevertheless I have
managed to copy with the age old copy and paste function.
Thanks!
"Aleksandar Grbic" wrote:
[vbcol=seagreen]
> use import/export wizard
>
> "Beavers" wrote:
>

Copying Tables Across Servers

I'm trying to copy data from a remote server connected thru enterprise
manager. I' assuming there shld be a means to script the table structure and
the contents. Is this possible and how can I go about it.
Beavers
use import/export wizard
"Beavers" wrote:

> I'm trying to copy data from a remote server connected thru enterprise
> manager. I' assuming there shld be a means to script the table structure and
> the contents. Is this possible and how can I go about it.
> --
> Beavers
>
|||I had problems with that because of the server names. Nevertheless I have
managed to copy with the age old copy and paste function.
Thanks!
"Aleksandar Grbic" wrote:
[vbcol=seagreen]
> use import/export wizard
>
> "Beavers" wrote:

copying sql table to computer hard drive

Hi,

I use sql server management express. I have created a table on my hosts remote database and i want to copy the table (or the data) in some format or other to my hard drive. does anyone have any good ideas how i may do this either through management express or other means.

thanks a lot

nick

http://www.cryer.co.uk/brian/sqlserver/howtoexportcsv.htm
http://www.codeproject.com/aspnet/ImportExportCSV.asp

|||

thanks mike, i shall give this a whirl

nick

Copying entire databases to remote server w/o Enterprise Manager

Is there any possible way to copy an entire MSDE database from my local system to a remote server using a program like 'osql'?

Or, is there any other GUI available for MSDE?

Thanks in advance.

GrierSure. There are a few ways. A couple that come to mind are to use the sp_detach_db system stored procedure in osql, copy the database files to the server, and use sp_attach_db to reattach them. Another is to run dtsrun.exe to run a DTS package that copies it over. You could also write ADO.NET code to do it, but it would be a lot of work to get all the objects copied.

There are several tools you can use to administer MSDE:

ASP.NET Enterprise Manager, an open source SQL Server and MSDE management tool.

ASP.NET WebMatrix (which includes a database management tool) from this web site (click on the Web Matrix tab at the top of this page).

Microsoft's Web Data Administrator is a free web-based MSDE management program written using C# and ASP.NET, and includes source code.

You can also access MSDE using Access. I'm not sure if this will do what you want, though.

Any of these work for you?
Don

Copying Database table to another Database

I am using Visual Studio 2005 to connect to a database on a remote SQL2000 server. I need to copy a table in a database on my local machine up to the server. How can I do that?

Thank you,

Do you have enterprise manager or sql management studio?

If so do use the Import/Export wizard to copy the table from A to B.

|||

no - I have Access 2007 and it is nothing like Access 2000 which made this act easy

|||

Part of my problem is that I am using Vista and it is giving me fits.

|||

This is "long ago at school" kind of knowledge for me. But can't u connect the sql server to your access database and copy the data across from within access?

|||

Finally got it - the adp setup in Access 2007 is completely different but you are correct it does work and actually quite well - now that I learned how to use it. The brain always stops with learning incomplete.

THank you

Copying database files

Hi all,
I need to give remote access to a SQL Server 2000 (MSDE) dabatase, meaning
that a developer must be able to upload/dowload via FTP the mdf and ldf
files.
This isn't a production database, so there is no active connection to it
except for the said developer.
The system doesn't even allow us to copy the files, saying that they are in
use.
I've fiddled with the "Auto Close" parameter for the db, but to no avail.
The only way I've found is to stop the SQL Server, but obviously, this isn't
a viable solution.
How can this be done?
TIA
Paul Dussault, MCP
Hi Paul,
The programmer mentioned below could detach the database remotely then copy
the files and last but not least; attach the database. You can do this
without stopping the service.
Yours sincerely,
Jo Segers.
"Paul Dussault" <paulduss@.hotmail.com> schreef in bericht
news:%23Z4mQU7IEHA.2596@.TK2MSFTNGP10.phx.gbl...
> Hi all,
> I need to give remote access to a SQL Server 2000 (MSDE) dabatase, meaning
> that a developer must be able to upload/dowload via FTP the mdf and ldf
> files.
> This isn't a production database, so there is no active connection to it
> except for the said developer.
> The system doesn't even allow us to copy the files, saying that they are
in
> use.
> I've fiddled with the "Auto Close" parameter for the db, but to no avail.
> The only way I've found is to stop the SQL Server, but obviously, this
isn't
> a viable solution.
> How can this be done?
> TIA
> Paul Dussault, MCP
>
|||1. detach the database - sp_detach_db
2. copy the files
3. re-attach the database - sp_attach_db
If you do this on a regular basis, you can create a script to detach your
database, copy the files to an alternate location, then re-attach the
database on a regular basis.
Regards
Shane Brodie
"Paul Dussault" <paulduss@.hotmail.com> wrote in message
news:%23Z4mQU7IEHA.2596@.TK2MSFTNGP10.phx.gbl...
> Hi all,
> I need to give remote access to a SQL Server 2000 (MSDE) dabatase, meaning
> that a developer must be able to upload/dowload via FTP the mdf and ldf
> files.
> This isn't a production database, so there is no active connection to it
> except for the said developer.
> The system doesn't even allow us to copy the files, saying that they are
in
> use.
> I've fiddled with the "Auto Close" parameter for the db, but to no avail.
> The only way I've found is to stop the SQL Server, but obviously, this
isn't
> a viable solution.
> How can this be done?
> TIA
> Paul Dussault, MCP
>
|||Thank you both for your quick replies.
I was aware of the sprocs, but the developper won't have any other access
than HTTP and FTP (ports 80 and 21).
Should I create an ASP script to execute these sprocs and then let the
developper execute to script as needed?
Thanks again!
Paul Dussault, MCP
"Shane Brodie" <sbrodie@.decorkit.com> wrote in message
news:uqrV5b7IEHA.2556@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> 1. detach the database - sp_detach_db
> 2. copy the files
> 3. re-attach the database - sp_attach_db
> If you do this on a regular basis, you can create a script to detach your
> database, copy the files to an alternate location, then re-attach the
> database on a regular basis.
> Regards
> Shane Brodie
> "Paul Dussault" <paulduss@.hotmail.com> wrote in message
> news:%23Z4mQU7IEHA.2596@.TK2MSFTNGP10.phx.gbl...
meaning[vbcol=seagreen]
> in
avail.
> isn't
>
|||A much better option is to use the BACKUP command to backup your =
database(s) to a file. You can use these files to restore on your =
machine. The benefit with this approach is that you do not have to take =
the database offline at any point.
More information about Backup and Restore can be found within Books =
Online
http://www.microsoft.com/sql/techinf...2000/books.asp
--=20
Keith
"Shane Brodie" <sbrodie@.decorkit.com> wrote in message =
news:uqrV5b7IEHA.2556@.TK2MSFTNGP12.phx.gbl...
> 1. detach the database - sp_detach_db
> 2. copy the files
> 3. re-attach the database - sp_attach_db
>=20
> If you do this on a regular basis, you can create a script to detach =
your[vbcol=seagreen]
> database, copy the files to an alternate location, then re-attach the
> database on a regular basis.
>=20
> Regards
>=20
> Shane Brodie
>=20
> "Paul Dussault" <paulduss@.hotmail.com> wrote in message
> news:%23Z4mQU7IEHA.2596@.TK2MSFTNGP10.phx.gbl...
meaning[vbcol=seagreen]
ldf[vbcol=seagreen]
to it[vbcol=seagreen]
are[vbcol=seagreen]
> in
avail.[vbcol=seagreen]
this
> isn't
>=20
>
|||In 6 years of SQL server experience, I've never found a case where using
backup/restore was better than using detach/attach when wholesale
replacement of the database is needed. Additionally, in order to restore
from a backup, the restorer still needs to have access to the SQL server
enterprise manager and the database has to be void of any users or pending
transactions.
Paul's situation indicates only HTTP and FTP access is available. So, his
idea of exposing a script to attach and detach the relevant database files
should work OK. My question is, "What is the developer doing that requires
him/her to make regular wholesale replacements of the entire database?"
-Steve
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:eqZb2V9IEHA.700@.TK2MSFTNGP09.phx.gbl...
A much better option is to use the BACKUP command to backup your database(s)
to a file. You can use these files to restore on your machine. The benefit
with this approach is that you do not have to take the database offline at
any point.
More information about Backup and Restore can be found within Books Online
http://www.microsoft.com/sql/techinf...2000/books.asp
Keith
"Shane Brodie" <sbrodie@.decorkit.com> wrote in message
news:uqrV5b7IEHA.2556@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> 1. detach the database - sp_detach_db
> 2. copy the files
> 3. re-attach the database - sp_attach_db
> If you do this on a regular basis, you can create a script to detach your
> database, copy the files to an alternate location, then re-attach the
> database on a regular basis.
> Regards
> Shane Brodie
> "Paul Dussault" <paulduss@.hotmail.com> wrote in message
> news:%23Z4mQU7IEHA.2596@.TK2MSFTNGP10.phx.gbl...
meaning[vbcol=seagreen]
> in
avail.
> isn't
>
|||sp_detach_db takes the database offline.=20
This is unacceptable in a production environment.
BACKUP does not bring the database offline.
The developer/DBA has many options when restoring the database (WITH =
MOVE, for example)
SQL Server Enterprise Manager is not required to issue the RESTORE =
command. Anything that can execute the appropriate Transact-SQL RESTORE =
command will do. This includes osql.exe (a command line utility), Query =
Analyzer, or even a query window within a web page.
I agree with your question "What is the developer doing that requires =
him/her to make regular wholesale replacements of the entire database?"
Perhaps they are not tracking their table, data, stored procedure =
changes and it is simply "easier" to replace the whole database when =
they want to move their code to production. Most of us would agree that =
this is not the best method of code promotion. It is better to apply =
table changes as necessary, insert/update/delete any data that needs to =
be modified, and create the stored procedures that have changed since =
the latest build and promote to production.
--=20
Keith
"Steve Lupton" <nospam@.nowhere.com> wrote in message =
news:XqZfc.18538$_I3.13377@.twister.socal.rr.com...
> In 6 years of SQL server experience, I've never found a case where =
using
> backup/restore was better than using detach/attach when wholesale
> replacement of the database is needed. Additionally, in order to =
restore
> from a backup, the restorer still needs to have access to the SQL =
server
> enterprise manager and the database has to be void of any users or =
pending
> transactions.
>=20
> Paul's situation indicates only HTTP and FTP access is available. So, =
his
> idea of exposing a script to attach and detach the relevant database =
files
> should work OK. My question is, "What is the developer doing that =
requires
> him/her to make regular wholesale replacements of the entire =
database?"
>=20
> -Steve
>=20
> "Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
> news:eqZb2V9IEHA.700@.TK2MSFTNGP09.phx.gbl...
> A much better option is to use the BACKUP command to backup your =
database(s)
> to a file. You can use these files to restore on your machine. The =
benefit
> with this approach is that you do not have to take the database =
offline at
> any point.
>=20
> More information about Backup and Restore can be found within Books =
Online[vbcol=seagreen]
> http://www.microsoft.com/sql/techinf...2000/books.asp
>=20
> --=20
> Keith
>=20
>=20
> "Shane Brodie" <sbrodie@.decorkit.com> wrote in message
> news:uqrV5b7IEHA.2556@.TK2MSFTNGP12.phx.gbl...
your[vbcol=seagreen]
the[vbcol=seagreen]

Copying database files

Hi all,
I need to give remote access to a SQL Server 2000 (MSDE) dabatase, meaning
that a developer must be able to upload/dowload via FTP the mdf and ldf
files.
This isn't a production database, so there is no active connection to it
except for the said developer.
The system doesn't even allow us to copy the files, saying that they are in
use.
I've fiddled with the "Auto Close" parameter for the db, but to no avail.
The only way I've found is to stop the SQL Server, but obviously, this isn't
a viable solution.
How can this be done?
TIA
Paul Dussault, MCP
Hi Paul,
The programmer mentioned below could detach the database remotely then copy
the files and last but not least; attach the database. You can do this
without stopping the service.
Yours sincerely,
Jo Segers.
"Paul Dussault" <paulduss@.hotmail.com> schreef in bericht
news:%23Z4mQU7IEHA.2596@.TK2MSFTNGP10.phx.gbl...
> Hi all,
> I need to give remote access to a SQL Server 2000 (MSDE) dabatase, meaning
> that a developer must be able to upload/dowload via FTP the mdf and ldf
> files.
> This isn't a production database, so there is no active connection to it
> except for the said developer.
> The system doesn't even allow us to copy the files, saying that they are
in
> use.
> I've fiddled with the "Auto Close" parameter for the db, but to no avail.
> The only way I've found is to stop the SQL Server, but obviously, this
isn't
> a viable solution.
> How can this be done?
> TIA
> Paul Dussault, MCP
>
|||1. detach the database - sp_detach_db
2. copy the files
3. re-attach the database - sp_attach_db
If you do this on a regular basis, you can create a script to detach your
database, copy the files to an alternate location, then re-attach the
database on a regular basis.
Regards
Shane Brodie
"Paul Dussault" <paulduss@.hotmail.com> wrote in message
news:%23Z4mQU7IEHA.2596@.TK2MSFTNGP10.phx.gbl...
> Hi all,
> I need to give remote access to a SQL Server 2000 (MSDE) dabatase, meaning
> that a developer must be able to upload/dowload via FTP the mdf and ldf
> files.
> This isn't a production database, so there is no active connection to it
> except for the said developer.
> The system doesn't even allow us to copy the files, saying that they are
in
> use.
> I've fiddled with the "Auto Close" parameter for the db, but to no avail.
> The only way I've found is to stop the SQL Server, but obviously, this
isn't
> a viable solution.
> How can this be done?
> TIA
> Paul Dussault, MCP
>
|||Thank you both for your quick replies.
I was aware of the sprocs, but the developper won't have any other access
than HTTP and FTP (ports 80 and 21).
Should I create an ASP script to execute these sprocs and then let the
developper execute to script as needed?
Thanks again!
Paul Dussault, MCP
"Shane Brodie" <sbrodie@.decorkit.com> wrote in message
news:uqrV5b7IEHA.2556@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> 1. detach the database - sp_detach_db
> 2. copy the files
> 3. re-attach the database - sp_attach_db
> If you do this on a regular basis, you can create a script to detach your
> database, copy the files to an alternate location, then re-attach the
> database on a regular basis.
> Regards
> Shane Brodie
> "Paul Dussault" <paulduss@.hotmail.com> wrote in message
> news:%23Z4mQU7IEHA.2596@.TK2MSFTNGP10.phx.gbl...
meaning[vbcol=seagreen]
> in
avail.
> isn't
>
|||A much better option is to use the BACKUP command to backup your =
database(s) to a file. You can use these files to restore on your =
machine. The benefit with this approach is that you do not have to take =
the database offline at any point.
More information about Backup and Restore can be found within Books =
Online
http://www.microsoft.com/sql/techinf...2000/books.asp
--=20
Keith
"Shane Brodie" <sbrodie@.decorkit.com> wrote in message =
news:uqrV5b7IEHA.2556@.TK2MSFTNGP12.phx.gbl...
> 1. detach the database - sp_detach_db
> 2. copy the files
> 3. re-attach the database - sp_attach_db
>=20
> If you do this on a regular basis, you can create a script to detach =
your[vbcol=seagreen]
> database, copy the files to an alternate location, then re-attach the
> database on a regular basis.
>=20
> Regards
>=20
> Shane Brodie
>=20
> "Paul Dussault" <paulduss@.hotmail.com> wrote in message
> news:%23Z4mQU7IEHA.2596@.TK2MSFTNGP10.phx.gbl...
meaning[vbcol=seagreen]
ldf[vbcol=seagreen]
to it[vbcol=seagreen]
are[vbcol=seagreen]
> in
avail.[vbcol=seagreen]
this
> isn't
>=20
>
|||In 6 years of SQL server experience, I've never found a case where using
backup/restore was better than using detach/attach when wholesale
replacement of the database is needed. Additionally, in order to restore
from a backup, the restorer still needs to have access to the SQL server
enterprise manager and the database has to be void of any users or pending
transactions.
Paul's situation indicates only HTTP and FTP access is available. So, his
idea of exposing a script to attach and detach the relevant database files
should work OK. My question is, "What is the developer doing that requires
him/her to make regular wholesale replacements of the entire database?"
-Steve
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:eqZb2V9IEHA.700@.TK2MSFTNGP09.phx.gbl...
A much better option is to use the BACKUP command to backup your database(s)
to a file. You can use these files to restore on your machine. The benefit
with this approach is that you do not have to take the database offline at
any point.
More information about Backup and Restore can be found within Books Online
http://www.microsoft.com/sql/techinf...2000/books.asp
Keith
"Shane Brodie" <sbrodie@.decorkit.com> wrote in message
news:uqrV5b7IEHA.2556@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> 1. detach the database - sp_detach_db
> 2. copy the files
> 3. re-attach the database - sp_attach_db
> If you do this on a regular basis, you can create a script to detach your
> database, copy the files to an alternate location, then re-attach the
> database on a regular basis.
> Regards
> Shane Brodie
> "Paul Dussault" <paulduss@.hotmail.com> wrote in message
> news:%23Z4mQU7IEHA.2596@.TK2MSFTNGP10.phx.gbl...
meaning[vbcol=seagreen]
> in
avail.
> isn't
>
|||sp_detach_db takes the database offline.=20
This is unacceptable in a production environment.
BACKUP does not bring the database offline.
The developer/DBA has many options when restoring the database (WITH =
MOVE, for example)
SQL Server Enterprise Manager is not required to issue the RESTORE =
command. Anything that can execute the appropriate Transact-SQL RESTORE =
command will do. This includes osql.exe (a command line utility), Query =
Analyzer, or even a query window within a web page.
I agree with your question "What is the developer doing that requires =
him/her to make regular wholesale replacements of the entire database?"
Perhaps they are not tracking their table, data, stored procedure =
changes and it is simply "easier" to replace the whole database when =
they want to move their code to production. Most of us would agree that =
this is not the best method of code promotion. It is better to apply =
table changes as necessary, insert/update/delete any data that needs to =
be modified, and create the stored procedures that have changed since =
the latest build and promote to production.
--=20
Keith
"Steve Lupton" <nospam@.nowhere.com> wrote in message =
news:XqZfc.18538$_I3.13377@.twister.socal.rr.com...
> In 6 years of SQL server experience, I've never found a case where =
using
> backup/restore was better than using detach/attach when wholesale
> replacement of the database is needed. Additionally, in order to =
restore
> from a backup, the restorer still needs to have access to the SQL =
server
> enterprise manager and the database has to be void of any users or =
pending
> transactions.
>=20
> Paul's situation indicates only HTTP and FTP access is available. So, =
his
> idea of exposing a script to attach and detach the relevant database =
files
> should work OK. My question is, "What is the developer doing that =
requires
> him/her to make regular wholesale replacements of the entire =
database?"
>=20
> -Steve
>=20
> "Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
> news:eqZb2V9IEHA.700@.TK2MSFTNGP09.phx.gbl...
> A much better option is to use the BACKUP command to backup your =
database(s)
> to a file. You can use these files to restore on your machine. The =
benefit
> with this approach is that you do not have to take the database =
offline at
> any point.
>=20
> More information about Backup and Restore can be found within Books =
Online[vbcol=seagreen]
> http://www.microsoft.com/sql/techinf...2000/books.asp
>=20
> --=20
> Keith
>=20
>=20
> "Shane Brodie" <sbrodie@.decorkit.com> wrote in message
> news:uqrV5b7IEHA.2556@.TK2MSFTNGP12.phx.gbl...
your[vbcol=seagreen]
the[vbcol=seagreen]

Copy tables on remote servers

Hi all, I'm trying to create a script / stored proc that copies one table from a remote server to another table on a different remote server.

The approach I wanted to take was...

create a stored proc that can be called remotely

the stored proc tests to see if the table exists, if it does it is dropped, if not it is created

new table structure and data copied from remote server A to remote server B

...I feel that this should be quite easy, but I must admit I'm struggling. Any help would be really appreciated.

Cheers, Jon

Did you try to setup a linked server and then do a SELECT INTO?

How about using DTS instead?

|||

Thanks for your reply William.

I have tried SELECT INTO...

select * into remoteServer.intranetcms.dbo.woodford_bridge
from localServer.intranetcms.dbo.woodford_bridge

... but I get the error...

The object name 'remoteServer.intranetcms.dbo.' contains more than the maximum number of prefixes. The maximum is 2.

...I am familiar with DTS but what I would like to do is create a stored proc that can be executed inside an on_click event from a web page.

- Jon

|||

In Ado.net, there is a pretty good method for doing this. The api name is SqlBulkCopy.

Let me know if you want to know more about the api.

Thanks

Bei

|||

Thanks Bei, unfortunately we're not using .Net 2 so, as I understand it, can't use SQLBulkCopy. Any other ideas?

- Jon

|||

Hi, maybe very late, but I ran into the same problem and solved this by doing this:

Ex:

SELECT t.Bilagnr

FROM [172.18.165.25\ASNV].[AS_14830].[dbo].[tblSalesDetails] as t WHERE Pkey > 1

Hope this helps!

- Per S.

|||

I'll recommend the use of DTS. then

create an SP that triggers the DTS thru xp_cmdshell using dtsrun.

call the SP from ASP.net

Copy tables on remote servers

Hi all, I'm trying to create a script / stored proc that copies one table from a remote server to another table on a different remote server.

The approach I wanted to take was...

create a stored proc that can be called remotely the stored proc tests to see if the table exists, if it does it is dropped, if not it is created new table structure and data copied from remote server A to remote server B

...I feel that this should be quite easy, but I must admit I'm struggling. Any help would be really appreciated.

Cheers, Jon

Did you try to setup a linked server and then do a SELECT INTO?

How about using DTS instead?

|||

Thanks for your reply William.

I have tried SELECT INTO...

select * into remoteServer.intranetcms.dbo.woodford_bridge
from localServer.intranetcms.dbo.woodford_bridge

... but I get the error...

The object name 'remoteServer.intranetcms.dbo.' contains more than the maximum number of prefixes. The maximum is 2.

...I am familiar with DTS but what I would like to do is create a stored proc that can be executed inside an on_click event from a web page.

- Jon

|||

In Ado.net, there is a pretty good method for doing this. The api name is SqlBulkCopy.

Let me know if you want to know more about the api.

Thanks

Bei

|||

Thanks Bei, unfortunately we're not using .Net 2 so, as I understand it, can't use SQLBulkCopy. Any other ideas?

- Jon

|||

Hi, maybe very late, but I ran into the same problem and solved this by doing this:

Ex:

SELECT t.Bilagnr

FROM [172.18.165.25\ASNV].[AS_14830].[dbo].[tblSalesDetails] as t WHERE Pkey > 1

Hope this helps!

- Per S.

|||

I'll recommend the use of DTS. then

create an SP that triggers the DTS thru xp_cmdshell using dtsrun.

call the SP from ASP.net

Copy table to Remote SQL server using DTS

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

Quote:

Originally Posted by 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.

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/k...ult.asp?id=1176

But failed with the Last step with this error.

Failed to copy objects to mssql server to mssql server.|||

Quote:

Originally Posted by ajaxrand

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,introduce 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.because you have a copy of data and metadata on your remote server.|||

Quote:

Originally Posted by hoomaniraji

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,introduce 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.because 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!|||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.