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 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply