March 28, 2008 at 9:37 am
I am trying to do a bcp out from a table to a textfile on my local machine
I execute following command in the command window
bcp "Select field1 from WMSFSTD42.dbo.activation"
queryout -o"c:\mytest.txt" -T -c
I get the following error
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 enabled the named pipes protocol in the sql server configuration manager and restarted the service => same result.
I also looked at the surface area configuration, and the 'remote connections' are on TCP/IP only.
but since I am running on my local machine, this should not be a problem, right?
Who has an idea of what is going wrong?
I want to do a simple bcp out from a query tot a file, no more, no less.
March 28, 2008 at 12:33 pm
The windows account that is executing BCP at the command does not have a windows logon to the SQL server.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 31, 2008 at 1:25 am
Well, I can login on my DB using windows authentication.
Is there a difference then??
March 31, 2008 at 2:31 am
found the error : since it was SQLexpress, It seems I have to enter the servername
syntax will look like this :
bcp "SELECT line from WMSFSTD42.dbo.t_conversion_lines order by line_number" queryout "c:\output.sql" -T -c -Sxxxxx\SQLEXPRESS -r
with xxxxx being the name of the local machine.
If I execute this in the command window, I get a file with the data I need.
So that part is solved.
However when I want to take it a step further, which is executing it from out SQL' I keep on getting errors :
syntax :
exec master..xp_cmdshell 'bcp "SELECT line from WMSFSTD42.dbo.t_conversion_lines order by line_number" queryout "c:\output.sql" -T -c -Sxxxxx\SQLEXPRESS -r'
gives the following resultset:
SQLState = HY000, NativeError = 0
Error = [Microsoft][SQL Native Client]Unable to open BCP host data-file
NULL
anyone an idea??
April 1, 2008 at 4:29 pm
It appears that you need to use the -S command to name the server you are logging into.
Thank you,
Marty Galvan
April 1, 2008 at 4:34 pm
correct, i needed to enter my servername. although i thought it wasn't needed (because it is the local machine), i had to enter it as it SQLExpress
furthermore yoou have to enable the write permissions on the target folder if you'd like to execute it using cmdshell
April 2, 2008 at 3:36 am
patcherke (3/31/2008)
found the error : since it was SQLexpress, It seems I have to enter the servernamesyntax will look like this :
bcp "SELECT line from WMSFSTD42.dbo.t_conversion_lines order by line_number" queryout "c:\output.sql" -T -c -Sxxxxx\SQLEXPRESS -r
with xxxxx being the name of the local machine.
If I execute this in the command window, I get a file with the data I need.
So that part is solved.
However when I want to take it a step further, which is executing it from out SQL' I keep on getting errors :
syntax :
exec master..xp_cmdshell 'bcp "SELECT line from WMSFSTD42.dbo.t_conversion_lines order by line_number" queryout "c:\output.sql" -T -c -Sxxxxx\SQLEXPRESS -r'
gives the following resultset:
SQLState = HY000, NativeError = 0
Error = [Microsoft][SQL Native Client]Unable to open BCP host data-file
NULL
anyone an idea??
hi
it seems your bcp command is wrong
i am using sybase, infact sybase and sql server is mostly same. you can try with the following command
bcp WMSFSTD42.dbo.t_conversion_lines out "c:\output.sql" -c -t"|"
-SSQLEXPRESS -U'username' -P'password'
no need to include machine name. only sql server name is enough
this is what i do
thanks
shamsudheen
April 11, 2008 at 12:15 am
My command is this
bcp testdatabase.dbo.[dimension_72] in E:\HR.txt -S xxxxx\SQL2005 -USA -PSA -f E:\HR_unq.fmt -e E:\HR_unq_error.log -o E:\HR_unq_bcp.log -t "" -r
where xxxxx is server name
THe error is as follows:
SQLState = 08001, NativeError = -1
Error = [Microsoft][SQL Native Client]SQL Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].
SQLState = HYT00, NativeError = 0
Error = [Microsoft][SQL Native Client]Login timeout expired
SQLState = 08001, NativeError = -1
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 set SQL server 2005 to accept remote connections as instructed in microsoft site.
then also same error
April 11, 2008 at 2:53 am
Hi siddiq
Did you try my previous post. i hope it will help you
April 11, 2008 at 3:31 am
I think your way will work out, but I will leave the code I have, as it is working at this moment.
(never change a winning team)
April 11, 2008 at 3:58 am
Dear Shamsudeen,
I am connecting from another machine in the network and doing bcp.
SO I should mention the server machine name.Pls help
April 11, 2008 at 4:07 am
siddiq,
Let me clearly explain, when connect the database through query analyser .you are giving user name ,password and servername only, i am right? here you are not mentioning the machine of the server .since your machine is configured to connect the server machine
similar way you do not worry about machine name instead give servername same as while you connect through query analyser
May 27, 2008 at 9:38 pm
declare @cmd nvarchar(1000)
set @cmd = 'bcp Northwind..customers out d:\w3root\test\atest.txt -T -w '
exec master..xp_cmdshell @cmd
I run the above query in sqlserver 2005 , i can be run at the first time, and
create the output file successfully, but when I run it again ,
it appears the following error
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Unable to open BCP host data-file
It can't be overwrited , why? pls help
August 12, 2010 at 7:20 pm
I also resolve my issue with bulk copy command using xp_cmdshell by adding additional option for servername -S ServerName.
Thanks for your help!
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply