SQL Server bulk Insert Issue?

PKTG
PKTG used Ask the Experts™
on
I try to insert small flat file data into SQL Server with Bulk Insert but i am getting below error.
"Msg 4860, Level 16, State 1, Line 1
Cannot bulk load. The file "c:\test.txt" does not exist."
But file is there in my C drive. Hope SQL Server account doesn't have permission to access this file. So i try with Windows Authentication still i am getting same error.
So pease clarify me the below questions.
1. How to verify SQL Server account has permisssion to access this file or not?
2. In case file data has text qualfier "" (Double quoted ) then what are the option i need to give in Bulk Insert command?
------------------------
BULK
INSERT Test
FROM 'c:\test.txt'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO
---------------------------------------------------
Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
please confirm that the file c:\test.txt is on the sql server box, and not on the client which runs the BULK statement...
Commented:
change the file location

BULK
INSERT Test
FROM '\\hostname\sharename\test.txt'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO

Author

Commented:
Files is client machine not sql srver box. I tried  '\\hostname\sharename\test.txt'. but i got Access denied error. So i put the file is SQL Server box then it is working fine.
And i have Quoted text qualifier file . when i try to load data using Bulk insert , the column value stored along with text qualifier. To avoid this what kind of parameter , i need to give . PLs advice me.
 
Billing Engineer
Most Valuable Expert 2014
Top Expert 2009
Commented:
you mean, for some (all) columns you have " as field value delimiter in addition to the comma?

in that case, you will need to use a format file.
sql 2000:http://msdn.microsoft.com/en-us/library/aa173859(SQL.80).aspx
sql 2005+: http://www.mssqltips.com/tip.asp?tip=1060

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial