How do I copy tables between SQL Servers when one is collocated?

I need to copy tables from a remotely hosted SQL Server to my local server.  Both are SQL Server 2008.

I tried linking to the server, but there is no way to identify where the remote server is located in that utility.  I cannot bcp the data out of the server for the same reason.  I exported the data to a CSV file, but BULK INSERT is failing on the identity data when I try to import with the following error:

bulk insert
Attendees
from 'C:\\attendees.txt'
with (keepidentity)

Msg 4866, Level 16, State 1, Line 1
The bulk load failed. The column is too long in the data file for row 1, column 1. Verify that the field terminator and row terminator are specified correctly.
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".
dtburdickAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Reza RadConsultant, TrainerCommented:
why you don't use SSIS?
you can do this with a simple Import / Export wizard
and copy tables from source database to destination
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dtburdickAuthor Commented:
Thanks.

Anyone else reading this thread should know that you install SSIS separately and that the Import/Export wizard is not under the SSIS menu the Start menu, but at the root of the SQLServer installation and is titled "Import and Export Data (32-bit)".
0
Reza RadConsultant, TrainerCommented:
Glad to help,
Regards,
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.