July 25, 2008 at 11:23 am
Even after digging through hundreds of articles, I could not find the solution to my problem. Here's the deal:
I want to export some data from a table into a text file using the following code in T-SQL:
EXEC XP_CMDSHELL 'bcp TestCSV.dbo.testcsvdm OUT C:\test.txt -c -T
I get the "16 rows copied..." and other output text that indicates that the operation was successful; however, there is no "test.txt" file in that location.
So, I tried changing the output directory to C:\DATA\test.txt, but now I get the error:
Error = [Microsoft][SQL Native Client]Unable to open BCP host data-file
So after reading various solutions, I concluded that changing the permissions might fix the problem. I set the 'data' folder to a full share and gave full access to Everyone (the group), gave full access to other groups & users (such as Admins, Users, etc.), and made sure no permissions were denied.
Still, I receive the preceding error.
I tried executing the bcp command from command prompt: everything worked perfectly. I could export data to any folder I wanted it to without changing permissions.
Any help would be greatly appreciated... I have done my research and I'm not some guy coming to the forums without visiting a search engine. Also, please feel free to ask me to clarify anything, as I realize that sometimes I forget details.
July 25, 2008 at 11:34 am
I just did this:
create database TestCSV
go
use TestCSV
go
create table dbo.testcsvdm (
ID int identity primary key,
Col1 varchar(100))
go
insert into TestCSV.dbo.testcsvdm (col1)
select number
from common.dbo.numbers -- Table with numbers from 0 to 10,000
go
EXEC master.dbo.XP_CMDSHELL 'bcp TestCSV.dbo.testcsvdm OUT C:\test.txt -c -T'
And I ended up with a file with the right data in it.
I also tried it with C:\Data\test.txt as the target, and that worked too.
It seems to me it's gotta be a permission issue. It is possible to set xp_cmdshell to run under a different login specifically. Have you checked if that's set up and if that might be the problem?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 25, 2008 at 12:58 pm
GSquared (7/25/2008)
I just did this:
create database TestCSV
go
use TestCSV
go
create table dbo.testcsvdm (
ID int identity primary key,
Col1 varchar(100))
go
insert into TestCSV.dbo.testcsvdm (col1)
select number
from common.dbo.numbers -- Table with numbers from 0 to 10,000
go
EXEC master.dbo.XP_CMDSHELL 'bcp TestCSV.dbo.testcsvdm OUT C:\test.txt -c -T'And I ended up with a file with the right data in it.
I also tried it with C:\Data\test.txt as the target, and that worked too.
It seems to me it's gotta be a permission issue. It is possible to set xp_cmdshell to run under a different login specifically. Have you checked if that's set up and if that might be the problem?
Tried that. I set the proxy account to my username using the SQL server configuration manager... I granted full access to the data folder. No dice.
Could it be that BCP is not allowed to access the actual database? I'm going to look into it.
July 25, 2008 at 2:02 pm
Well I solved the problem... It wasn't my SQL server and I didn't realize that it wasn't hosted on the computer I had been working on... All the files got created somewhere else, and the error called because there was not a folder called 'Data' on that machine.
Anyways thanks for the help guys.
July 28, 2008 at 7:26 am
That makes sense. Glad you sorted it out.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 5 posts - 1 through 5 (of 5 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