5

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?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Dave Fish
  • 93
  • 1
  • 1
  • 4

2 Answers2

9

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

Jasper Schellingerhout
  • 1,070
  • 1
  • 6
  • 24
  • question: is the output file "yourfile.txt" go to the C: drive on the serve identified by the flag -S? or on the server running bcp? so if I was running thedatabase.db.myhost.com, is C:\ on my machine? or on thedatabase.db.myhost.com? Thanks – arcee123 Oct 16 '18 at 01:40
  • 1
    @arcee123. The output file is saved on the server running bcp. In fact, it would be impossible to save on the server identified by the flag -S, as the only access that is being granted to that server is database access, and not access to the windows (or whatever) environment, – gordon613 Jan 07 '20 at 12:23
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.

James Z
  • 12,209
  • 10
  • 24
  • 44