SQL 2005 Bulk Insert: Connection problem using bcp.exe.

Question

SQL 2005 Bulk Insert: Connection problem using bcp.exe.

Viewing 5 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Ken Wells Old Hand Points: 326 More actions · Answer 1

There were two issues involved with this problem. One was found documented, the other was not.

After much reading, I ran across the switch that allows a named instance of SQL Server to be addressed from the command line. That is -S Machine\InstanceName. That allowed a successful connection.

The second issue (after solving the connection problem) was Invalid Object error on the "database.table" parameter. I reviewed example after example after example...but never found any reference to my ultimate solution. I had taken the command line format 'database.table' to mean just that, the database qualifier and the table qualifier separated by a dot. This is NOT correct. Not sure why this is not pointed out in the explanation of the usage of bcp.exe.

Is it me, or is just plain diffucult to find answers in the documentation to even fairly simple issues?

This forum has some great info!

Markus Veretin SSCommitted Points: 1595 More actions · Answer 2

SQL2005 doesn't allow trusted connection in bcp. Use -U<power user name> -P<password> and it will work.

Ken Wells Old Hand Points: 326 More actions · Answer 3

I actually DID get it to work with the trusted connection. I can connect to a named instance of SQL Server 2005 with bcp.exe from the command line (running through a DOS .bat file since I got soooooo tired of typing the same commands over and over) and create the format file. Immediately on the next line of the batch file I successfully import the data to the table from the csv file using the format file. It works great. I'd like to be able to do this from within SQL Server, though. I ran across a post somewhere where someone had done it but I can't find it again.

Thanks for the reply.

Markus Veretin SSCommitted Points: 1595 More actions · Answer 4

First - In SQL Server Configuration Manager create an alias for your named instance with port number.

And second - to run it from sql server

Declare @sql varchar(500)

set @sql = 'exec master..xp_cmdshell ''bcp dbname..viewname out \\servername\c$\filename.bcp /n /Ubcpuser /Ppassword /Sservername''' + ', no_output'

exec (@sql)

Ken Wells Old Hand Points: 326 More actions · Answer 5

Ken Wells

Old Hand

Points: 326

April 4, 2007 at 11:41 am

#698615

Great! Thanks. I'll give it a try.