How do I copy tables between SQL Servers when one is collocated?
Posted on 2010-01-12
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:
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)".