How to write using BCP to a remote SQL Server?

Question

I have a remote SQL Server with a hostname I am using to connect to.

BCP suggests to use

bcp DBName.dbo.tablename in C:\test\yourfile.txt -c -T -t

However when I try this it does not connect to DBName as that is not a valid alias. I get native error 2.

How to I run BCP but specify an internet / network address to connect to, not an MSSQL server name?

Answer 1

How to I run BCP but specify an internet / network address to connect to, not an MSSQL server name?

You can specify the IP address (here just 127.0.0.1) instead of the server name.

bcp DBName.dbo.tablename in "C:\test\yourfile.txt" -c -T -t -S"127.0.0.1\instance"

If you truly want to go IP\port only then use IP address and port separated by comma

bcp DBName.dbo.tablename in "C:\test\yourfile.txt" -c -T -t -S"127.0.0.1,60905"

Of course in that case you would need to know the port the instance is using, and your instance should be set to use a fixed port

Answer 2

The DBName is the name of the database, you need to use -S option to provide the server (or server\instance) name.

From MSDN:

-S server_name [\instance_name] Specifies the instance of SQL Server to which to connect. If no server is specified, the bcp utility connects to the default instance of SQL Server on the local computer. This option is required when a bcp command is run from a remote computer on the network or a local named instance. To connect to the default instance of SQL Server on a server, specify only server_name. To connect to a named instance of SQL Server, specify server_name\instance_name.