• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 460
  • Last Modified:

copy table data from remote sql server to local sql server

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
-Dman100-
Asked:
-Dman100-
1 Solution
 
BrandonGalderisiCommented:
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
 
sm394Commented:
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
 
jazzIIIloveCommented:
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
-Dman100-Author Commented:
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
 
sm394Commented:
try reinstalling
0
 
-Dman100-Author Commented:
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
 
sm394Commented:
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
 
sm394Commented:
also the tool is installed on  XP and Vista 32 bit systems in my environment
0
 
-Dman100-Author Commented:
Got it working.  Thank you.
0
 
sm394Commented:
Glad your problem resolved
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now