?
Solved

copy table data from remote sql server to local sql server

Posted on 2008-10-26
10
Medium Priority
?
455 Views
Last Modified: 2012-06-27
I need to move some updated table data from a table on a remote sql server to my local development instance of sql server.

Both the remote sql server and my local version are running Sql Server Management Studio Express 2005.

I'm not able to register the remote server locally because of an issue with the firewall and VPN connection.  Our network admins are working on that issue.

So, in the meantime, I need to copy some table data from the remote sql server to my local sql server.

Is BCP the best way to accomplish this?

Export using BCP on the remote server to a comma delimited file, truncate the table on my local dev environment and import using BCP?
0
Comment
Question by:-Dman100-
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
10 Comments
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22808338
You can use BCP, but I would do native format not character format.  The difference is in the use of the -n instead of the -c command line option.
0
 
LVL 13

Expert Comment

by:sm394
ID: 22808394
you can also have a choice of  Microsoft Data publishing tool for sql server

Download this freel tool by Microsoft for publishing Data
http://www.codeplex.com/sqlhost/Wiki/View.aspx?title=Database%20Publishing%20Wizard

Then
in Wizard if you just want to generate data insert statements
set the following options

uncheck Script all object
Next
Schema qualify= False
Types of data to publish =Data only

end result is data script which you can execute on another Database

The tool can also generate scripts for other objects in the database ie. Tables

Hope that helps
0
 
LVL 12

Expert Comment

by:jazzIIIlove
ID: 22808427
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

Author Comment

by:-Dman100-
ID: 22808628
I tried using the Microsoft Publishing tool, but got the following error:

TITLE: Microsoft SQL Server
------------------------------

This wizard will close because it encountered the following error:

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server+Database+Publishing+Wizard&ProdVer=1.1.1.0&EvtSrc=Microsoft.SqlServer.Management.UI.WizardFrameworkErrorSR&EvtID=UncaughtException&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

Could not load file or assembly 'Microsoft.SqlServer.BatchParser, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified. (Microsoft.SqlServer.ConnectionInfo)

------------------------------
BUTTONS:

OK
------------------------------
0
 
LVL 13

Expert Comment

by:sm394
ID: 22808661
try reinstalling
0
 

Author Comment

by:-Dman100-
ID: 22808679
Do I need to install the following:
Microsoft SQL Server 2005 Management Objects Collection (I'm not sure if this is installed already)

I have a x64 system.  Would this cause a problem?
0
 
LVL 13

Expert Comment

by:sm394
ID: 22808739
i use this tool everyday without any crash so can't really give you the exact answer about the reason of this exception but i do have SQL SERVER Managment studio installed in my environment both on remote SQL SERVERS and Local
but you can check the Known Issues section here
http://www.codeplex.com/sqlhost/Wiki/View.aspx?title=DPW%20Known%20Issues&referringTitle=Database%20Publishing%20Wizard
0
 
LVL 13

Accepted Solution

by:
sm394 earned 2000 total points
ID: 22808763
also the tool is installed on  XP and Vista 32 bit systems in my environment
0
 

Author Comment

by:-Dman100-
ID: 22808843
Got it working.  Thank you.
0
 
LVL 13

Expert Comment

by:sm394
ID: 22808861
Glad your problem resolved
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are some very powerful Dynamic Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a di…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question