473,853 Members | 1,839 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

How to copy tables from local machine to remote SQL server

We currently have a PPTP connection set up for our developers to
access our development SQL server through a VPN tunnel. When they
need to copy tables up to the dev SQL from their local machine they
simply do a DTS copy.

However, we are now moving to a thin client solution where they will
be working on a terminal server. They will have access to the
development SQL servers and SQL tools such as EM and QA. However,
they will not have access to their local SQL server and, therefore,
will not be able to directly perform DTS copies. We have explored
several possibilities such as exporting tables to a .csv or .mdb file
and then importing them on the development SQL server but this is not
ideal because things are lost in that process (e.g. primary keys,
field names, data types, etc.)

My question is this: Is there a way to export and then import SQL
tables without losing dependent objects such as primary keys and data
types in the process? If any of you are working with a similar
situation I would really like to hear how your remote users copy
objects from their remote location to your SQL servers. Thanks!

Ryan

--
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/General-Disc...ict211310.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=722630
Jul 23 '05 #1
4 4764
I'm not sure how your developers will get their files to the server
(FTP?), but in any case they can easily create object definition
scripts from Enterprise Manager or Query Analyzer.

Assuming they're maintaining their SQL code in a source control system,
then they will be creating these scripts anyway. So they could recreate
(or perhaps just ALTER) a table with a script, then reload the data
from a csv file. Or perhaps make a copy of the original table, modify
it, insert the data from the copy, and then drop the copy.

If this doesn't help, you might want to clarify what the issue is -
problems creating scripts, running them etc.

Simon

Jul 23 '05 #2
"Simon Hayes" wrote:
I'm not sure how your developers will get their files to the
server
(FTP?), but in any case they can easily create object
definition
scripts from Enterprise Manager or Query Analyzer.

Assuming they're maintaining their SQL code in a source
control system,
then they will be creating these scripts anyway. So they could
recreate
(or perhaps just ALTER) a table with a script, then reload the
data
from a csv file. Or perhaps make a copy of the original table,
modify
it, insert the data from the copy, and then drop the copy.

If this doesn't help, you might want to clarify what the issue
is -
problems creating scripts, running them etc.

Simon


Thanks for your reply.

The developers can copy files from there local hard drives via their
terminal server session. The problem is that they cannot use DTS to
copy SQL objects because there are firewalls between their local
machine and the terminal server.

It would be possible for them to script the table in EM and then use
bcp to insert the data but I was hoping for a simpler solution with
fewer steps involved. Some of the developers are not so SQL saavy and
Iím afraid that we will get stuck supporting them. The extra time
spent on these activities will also give them an excuse to bill us for
more hours (they are contractors).

I have been testing the export of data to .csv and .mdb files and it
is not always straightforward to do. If anyone has any experience
with this your advice is very welcome. Thanks!

--
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/General-Disc...ict211310.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=723562
Jul 23 '05 #3
Here's a workaround solution if you'd like.

1. Create a backup of the database you want to send to the SQL Server.
Let's say your DBName is Performer.
2. Restore as another database...Perf ormer1
3. Delete the tables/other objects you don't want to move up.
4. Do another backup of Performer1...ma ybe into Performer1.bak
5. Upload it to Production.
6. Restore as Performer1.
7. DTS the tables over or if you simply need to move the data, DTS only
the data to the existing tables.

....and 8. Good luck :o)

Hope this helps,

-Umar Farooq.

Jul 23 '05 #4
1.DTS out to disk the table(s) you need (using DTS for this is much less
prone to error than BCP)
2. Upload files to production
3. DTS Back in (once again DTS is easier than BCP)

To avoid foreign key confilicts you need to DTS any parent data in before
child data (if you need to move new tables you will need to script and copy
them and recreate at other end before loading data)

You can make the task completely automatic by doing steps 1 and 2 in a DTS
package locally, then do step 3 on production and pole for the files, only
upload when they are there an then delete them (or move them) when the
import is complete.

This will mean that your contractors do nothing or just initiate an automate
process.

Hope this helps
Julian

"Umar Farooq" <Um**********@g mail.com> wrote in message
news:11******** *************@o 13g2000cwo.goog legroups.com...
Here's a workaround solution if you'd like.

1. Create a backup of the database you want to send to the SQL Server.
Let's say your DBName is Performer.
2. Restore as another database...Perf ormer1
3. Delete the tables/other objects you don't want to move up.
4. Do another backup of Performer1...ma ybe into Performer1.bak
5. Upload it to Production.
6. Restore as Performer1.
7. DTS the tables over or if you simply need to move the data, DTS only
the data to the existing tables.

...and 8. Good luck :o)

Hope this helps,

-Umar Farooq.

Jul 23 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
3270
by: mbailey | last post by:
Hello- I have a Sql Server 2000 database offsite that I would like to back up to a local machine. I am using Enterprise Manager on a local machine to administer the remote db. Whats the best way to schedule backups so the remote db is backed up to the local machine? Can this be done in Enterprise Manager? Would you recommend any 3rd party software?
4
2309
by: ShyGuy | last post by:
I have a routine that creates a new database and then copies a couple of tables to the new database as backups. It works fine but I want to split the database and run this routine from a machine that does not have the actual tables on it. I am using the following DoCmd.TransferDatabase acExport, "Microsoft Access", "Backup.mdb", acTable, "TableName", "TableName" Is there a way to use this and get the tables from a different machine?
1
3903
by: POnfri | last post by:
Hi, I have a problem in a peace of code were i'm doing a file copy using File.Copy. The Source is local and the target is a remote machine. Example: File.Copy(C:\temp\hi.txt, \\myremote\c$\temp\hi.txt, true); This API is run from a web service that exposes it. The problem is as follows: 1) I request a page (On the web service machine) from my web server (Note
3
2195
by: Johnny | last post by:
Hi, I have created an ASP.NET application (let's call it FooBar) with VS.NET on my local machine, residing in http://localhost/FooBar. Deploying it to another folder on my machine works well with FrontPage Server Extensions, but I don't manage to deploy the application to my web provider http root folder. When I run Copy Project, I get an error message just saying 'Unable to create Web project 'myname'. Not found.'.
2
1980
by: camp | last post by:
I use VS.Net 2003 on Win XP Pro SP2. I created an ASP.Net app on local IIS. Now I just wanted to "Copy Project" to remote server, but I get this error: "An error occured while copying the project 'http://localhost/MyApp/MyApp.csproj'. The web sever reported the following error when attemptimg to create or open the web project located at the following URL: 'http://www.mysite.com'. 'HTTP/1.1 500 Internal Sever Error' More details:
2
1650
by: Steve Franks | last post by:
The Copy Web tool provided with VS.NET 2005 is very convenient. However every once in a while it seems to think the files on the remote server have changed, which they have not. Then when I use the Copy Web Site feature of Copy Web to just copy the entire web site over, it thinks it has to re-upload the entire site which takes a very long time - when in fact those files on the remote have not changed. Does anyone know why this occurs...
5
4629
ak1dnar
by: ak1dnar | last post by:
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!
0
1097
by: Derek Hart | last post by:
Having trouble using dotnet 2.0 code to move a dataset filled with data to a remote server. Works perfectly locally, but always times out with a remote server. Is there some property or something I am missing. Here is the code I have been working with. Public Function CopyTable(ByVal TableName As String) As String Dim LocalTableName As String = ".." Dim wsTest As New net.mikebrumley.WSTest1 Dim dsSource As DataSet Dim cn As New...
5
17783
by: =?Utf-8?B?QWRyaWFuTW9ycmlz?= | last post by:
Hello! I'm trying to copy a file from another computer on the network that I do not have permission with my current logon details to access. If I open the folder using the Windows file manager with the path "\\ 192.168.2.2\temp" (where temp is a shared directory on server \\192.168.2.2), windows prompts for a User Name and password of a user who has permission on that computer to access that directory. If I enter valid details, the...
0
9898
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, weíll explore What is ONU, What Is Router, ONU & Routerís main usage, and What is the difference between ONU and Router. Letís take a closer look ! Part I. Meaning of...
0
10670
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10735
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10358
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
7907
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Duprť who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7074
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5736
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
4549
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
3180
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.