bcp from remote database to local drive

Question

I have two database servers: LIVE and DB. Both have SQL Server 2017.

If I open SMSS in DB, I can log on to LIVE databases using SQL Server authentication.

My goal is to run BCP from DB to queryout from LIVE into a file on DB.

The command I'm running is:

BCP "Select * from <<DATABASE.dbo.TABLE>>" queryout "D:\data\test.csv" -t^ -r '0x0A' 
    -U <<USER>> -P <<PASSWORD>> -S "LIVE\MSSQLSERVER" -c -C65001

The problem is that for some reason I'm not connecting successfully:

SQLState = 08001, NativeError = 87
Error = [Microsoft][ODBC Driver 13 for SQL Server]SQL Server Network Interfaces:
Connection string is not valid [87].
SQLState = 08001, NativeError = 87
Error = [Microsoft][ODBC Driver 13 for SQL Server]A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.

SQLState = S1T00, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]Login timeout expired

Am I missing something? If on DB, I attempt to connect to LIVE using SMSS, it's ok, but bcp results in not found errors?

Thanks!

marc_s marc_s · Accepted Answer · 2019-01-28T05:11:31

I see you're using the MSSQLSERVER as instance name when you're trying to connect to LIVE here:

BCP "Select * from <<DATABASE.dbo.TABLE>>" queryout "D:\data\test.csv" -t^ -r '0x0A' 
    -U <<USER>> -P <<PASSWORD>> -S "LIVE\MSSQLSERVER" -c -C65001
                                   ******************

Normally, MSSQLSERVER is the internal "instance" name of the default, unnamed instance and you connect to such a default instance without providing any instance name - e.g. using just LIVE instead of LIVE\MSSQLSERVER.

Try this BCP command:

BCP "Select * from <<DATABASE.dbo.TABLE>>" queryout "D:\data\test.csv" -t^ -r '0x0A' 
    -U <<USER>> -P <<PASSWORD>> -S "LIVE" -c -C65001

bcp from remote database to local drive

1 Answers