May 22, 2008 at 8:33 pm
I use domain account to login query analyser, i have admin right , and I can access the folder
"d:\W3Root\Test", but after run the following query, there is a error message.
ps: I can execute the query : exec master..xp_cmdshell 'dir d:\W3Root\Test\*.*'
anyone pls help.
declare @cmd nvarchar(1000)
select @cmd = 'bcp "select top 10 * from Northwind..Customers" queryout "d:\W3Root\Test\Atest.txt" '
exec master..xp_cmdshell @cmd
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Unable to open BCP host data-file
May 24, 2008 at 7:43 pm
Does folder "d:\W3Root\Test\" exist on the server where SQL Server is running?
_____________
Code for TallyGenerator
May 26, 2008 at 8:58 pm
yes, it does exist
May 26, 2008 at 11:02 pm
What account starts SQL Server?
Does that accout have write privileges on that folder?
_____________
Code for TallyGenerator
May 27, 2008 at 1:24 am
use my domain account , it has the administrator's right
but I found the answer, I missed the parameter -T -c at the end of the statement ,
but there is a another problem , when i run the bcp command with parameter -T -c,
it can output the result to the file , but when I excute it again , it show the error again.
why? is it can't overwrite the file?
May 27, 2008 at 8:47 am
That seems likely.
[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]
May 27, 2008 at 5:14 pm
My bcp overwrites existing files with no questions asked.
Are you sure you have "Modify" or "Full Control" permission, not only "Read" and "Write" permissions on that folder?
_____________
Code for TallyGenerator
May 27, 2008 at 9:14 pm
yes, i have full control in this folder and I am running the query in a sql 2005
it only can run it once and create the output file, and it can't overwrite the file when i run the query again .
following is the output error :
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Unable to open BCP host data-file
May 28, 2008 at 12:00 am
Can you print and post here command string you're executing?
Command
bcp "SELECT 'WHATEVER'" queryout "C:\TestFile.csv" -c -t"," -r -S"ServerName" -T
overwrites existing files without problems.
_____________
Code for TallyGenerator
May 28, 2008 at 1:06 am
this is my string query
declare @cmd nvarchar(1000)
set @cmd = 'bcp "select * from northwind..customers" queryout d:\w3root\test\atest.txt -S"testserver" -T -w'
exec master..xp_cmdshell @cmd
when excute it at first time , it runs ok , and generate a output file "atest.txt" in the destination folder
NULL
Starting copy...
NULL
91 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.): total 1
NULL
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
NULL
October 29, 2008 at 4:25 am
As an additional thought: any such job/extract that stops working after being ok previously (happened to me today!) may be due to a destination (eg file share) running out of space. In my case, once I cleared a bit of room the job ran fine. Deceptive because explorer may well show gigs free; the more limited file share disk space allocation not being shown via explorer.
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy