Unable to Open host BCP Data file

Question

Post reply

Unable to Open host BCP Data file

ekknaveen

SSCrazy

Points: 2317
More actions
September 16, 2009 at 1:28 am

#138199

I am trying to extract the data from the table to text file with the below script.
DECLARE @FileName varchar(50),
@bcpCommand varchar(2000)
SET @FileName = 'd:aveen.txt'
SET @bcpCommand = 'bcp "SELECT * FROM Onepoint..navtest ORDER BY cf1" queryout "'
SET @bcpCommand = @bcpCommand + @FileName + '" -c -SWSCEXTID0073\DN001 -T'
print @bcpcommand
EXEC master..xp_cmdshell @bcpCommand
When I am running this in query analyser it is getting the error unable to open host bcp data file.
When I ran the bcp statement after printing in command prompt it is executed successfully.
Can anyone tell me why it is not running in query analyser.
Thanks in advance
Naveen

Viewing 4 posts - 1 through 3 (of 3 total)

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

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 1

2 things:

- there is a faulty path in your script !

SET @FileName = 'd:aveen.txt'

Should be SET @FileName = 'd:\aveen.txt'

- has your sqlserver instance service account been granted read access to your D:\ folder ?

BTW

- xp_cmdshell is off by default ! And there's a reason to it !

- avoid xp_cmdshell ! There are more elegant ways of making backups using SQLAgent jobs !

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

ekknaveen SSCrazy Points: 2317 More actions · Answer 2

Thanks I got by changing the permissions. But I feel when we give it to users, they may not hav the full permisssions and in that case it will not work.

Can you tell me what is the better approach for exporting the data from query to text file.

Thanks in advance

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 3

As always, it depends 😉

- For small files, rarely requested, you could use a dbmail solution and mail the query result as attachement.

- another alternative is to use a sqlagent job to do the bcp for you at the frequency you require.

(you can even build that job as dynamic as you want) The advantage will be users nolonger need the authority.

Keep in mind all path info used with your bcp command needs to be available with respect to where the command actually runs ! If you do it using xp_cmdshell, that will be on the server itself, or if you use UNC, your sql service account needs write authority to the UNC location.