Trying to export data into csv file using bcp stored procedure

Question

Post reply

Trying to export data into csv file using bcp stored procedure

mp88_3

SSC Veteran

Points: 256
More actions
February 19, 2019 at 1:31 pm

#370718

Can someone please help me with explaining why I am getting the error shown in the attached file?
Thank You in advance.

Viewing 13 posts - 1 through 12 (of 12 total)

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

frederico_fonseca SSCoach Points: 16025 More actions · Answer 1

have you even bothered googling for it? first hit I get has the reason for the issue and the solution.

mp88_3 SSC Veteran Points: 256 More actions · Answer 2

well obviously I did google it thank you very much before posting it on here!

frederico_fonseca SSCoach Points: 16025 More actions · Answer 3

not enough really - just on the forums here you get 99 hits
and please post the full error message, not just a screen print that does not show the remaining message

mp88_3 SSC Veteran Points: 256 More actions · Answer 4

I try executing the following
declare @sql varchar(8000) select @sql = 'bcp [sample].[dbo].[fams] out C:\Users\mp88_\OneDrive\Desktop\samplefile.txt -c -t, -T -S' + @@servername exec master..xp_cmdshell @sql
but I get the following error
SQLState = S1000, NativeError = 0 Error = [Microsoft][ODBC Driver 13 for SQL Server]Unable to open BCP host data-file NULL

frederico_fonseca SSCoach Points: 16025 More actions · Answer 5

then as per the error the file can't be opened - pretty clear - and just proves that you didn't really search for anything - just expect that we do the work for you.
Really annoying to all professionals here.

https://www.sqlservercentral.com/Forums/237238/BCP-error-Unable-to-open-BCP-host-data-file

mp88_3 SSC Veteran Points: 256 More actions · Answer 6

the link you included in your previous message I read already before I posted my query on here.
I didn't find that link useful to help me solve my problem, hence why I decided to ask on here.

Lowell SSC Guru Points: 323504 More actions · Answer 7

you are trying to access a file in your private protected space
C:\Users\mp88_\OneDrive\Desktop\samplefile.txt

if you ran bcp from the command line itself. YOU have access to that file.
but you since are using xp_cmdshell, it's not you anymore. SQL uses the SERVICE ACCOUNT to access items outside of SQL, and that account must have access to the file/folders.

That service account:
select * from sys.dm_server_services. the SQL account from that query must have access to file/folders, but that folder is private, so you get the access denied error.

the fix is to simply move the file to a non-protected location...not your desktop, not my documents, but something like C:\Data

note you cannot access files on the root of a drive either.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

mp88_3 SSC Veteran Points: 256 More actions · Answer 8

thanks Lowell for your detailed reply but unfortunately, it still not working.
Maybe I am doing something wrong but I tried moving the file into C:\Data but no luck.

When I executed this
select * from sys.dm_server_services
i then got the following service accounts showing
NT Service\MSSQL$SQL2016TRAINING NT Service\SQLAgent$SQL2016TRAINING NT Service\MSSQLFDLauncher$SQL2016TRAINING
at these locations
"C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016TRAINING\MSSQL\Binn\sqlservr.exe" -sSQL2016TRAINING "C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016TRAINING\MSSQL\Binn\SQLAGENT.EXE" -i SQL2016TRAINING "C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016TRAINING\MSSQL\Binn\fdlauncher.exe" -s MSSQL13.SQL2016TRAINING

I also right clicked on the file (i.e. samplefile.txt) > properties > security tab > edit > add > advanced > find now and I get the list shown in the attached snapshot and I tried giving these full control to the file but still no luck.

mp88_3 SSC Veteran Points: 256 More actions · Answer 9

So I guess no one here knows the solution to my problem.

I am a newbie so I wouldn’t know but it’s sad to see no one can help.

Michael L John One Orange Chip Points: 27042 More actions · Answer 10

mp88_3 - Wednesday, February 20, 2019 4:41 PM
So I guess no one here knows the solution to my problem.I am a newbie so I wouldnâ€™t know but itâ€™s sad to see no one can help.

Grant the group "everyone" full control to the folder C:\Data.

Then see if it works.

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

mp88_3 SSC Veteran Points: 256 More actions · Answer 11

did that and it still does not work.
same error Error = [Microsoft][ODBC Driver 13 for SQL Server]Unable to open BCP host data-file

mp88_3 SSC Veteran Points: 256 More actions · Answer 12

ok so I managed to get it working by changing the directory from C: to D:
But still don't understand why it does not work with C:?