March 31, 2007 at 3:27 am
I get a connection error when trying to create a format file using the command (in a CMD window):
bcp.exe mydatabase.mytable format nul -c -t , -f c:\formatfile.fmt -T
Error messages I receive:
"SQLState = 08001, NativeError = 2
Error = [Microsoft][SQL Native Client]Named Pipes Provider: Could not open a connection to Sql Server [2]
SqlState = HYT00, NativeError = 0
Error = [Microsoft][SQL Native Client]Login timeout expired
SQLState = 08001, NativeError = 2
Error = [Microsoft][SQL Native Client]An error has occurred while establishing a connection to the server. When
connecting to SQL Server 2005, this failure may be caused by the fact that
under the default settings SQL Server does not allow remote connections."
I have found numerous posts addressing a connection issue when "Remote Connection" is not set to affirmative via the Surface Configuration tool. I changed the settings to allow remote connections, restarted the database engine and tried the bcp.exe command again. Still, the same error appears.
The server is a named instance. Everything is located on the local box so there are no firewall or UNC path issues to deal with. I created the test database and the test table today. I am the only user.
I can intentionally misspell the database name or the table name and the same error occurs. This tells me that it's not even FINDING the server.
Any hints on what to change in order to get a connection would be appreciated.
Thanks,
Ken Wells.
March 31, 2007 at 2:07 pm
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!
April 3, 2007 at 2:55 pm
SQL2005 doesn't allow trusted connection in bcp. Use -U<power user name> -P<password> and it will work.
April 3, 2007 at 10:07 pm
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.
April 4, 2007 at 8:50 am
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)
April 4, 2007 at 11:41 am
Great! Thanks. I'll give it a try.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply