Solved

copy table data from remote sql server to local sql server

Posted on 2008-10-26
10
449 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-
10 Comments
 
LVL 39

Expert Comment

by:BrandonGalderisi
Comment Utility
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
Comment Utility
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
Comment Utility
0
 

Author Comment

by:-Dman100-
Comment Utility
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
Comment Utility
try reinstalling
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:-Dman100-
Comment Utility
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
Comment Utility
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 500 total points
Comment Utility
also the tool is installed on  XP and Vista 32 bit systems in my environment
0
 

Author Comment

by:-Dman100-
Comment Utility
Got it working.  Thank you.
0
 
LVL 13

Expert Comment

by:sm394
Comment Utility
Glad your problem resolved
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

771 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now