Hello
I am trying to bulk insert a text file into SQL 2005 table. When I execute the bulk insert I get the error
"Msg 4860, Level 16, State 1, Line 1. Cannot bulk load. The file "\\ENDUSER-SQL\EnduserText\B1020063.txt" does not exist."
The text file that it is saying does not exist I recently created thru my code. I can open the file but only when I rename the file will the Bulk Insert work. After creating the text file I am moving it to the server that SQL server is running on. Also if I run sp_FileExists it also says the file does not exist unless again I rename the file then this stored procedure recognizes the file. I dont' know if I have a permission issue or what is the problem. Any help would be appreiated.
Thanks
Chris
What is your bcp command? What is the original and new name of the file?|||This is always a rights or login issue.How are you "running" the bulk insert, is it a job or a dts or a query or what? Is it "Bulk Insert" or "BCP.EXE".
Assuming it is Bulk Insert, like you said, it is running on the SERVER as the logged in user. Can the server access \\ENDUSER-SQL...?
What are you "renaming" the file too? Are you actually moving the file to another location or just renaming it? I don't see anything wrong with the name.|||
I am running the bulk insert from a query. The original file name is B120049.txt, I then rename the file to say B120049BLK.txt save it then the bulk insert runs fine.
Thanks
Chris
|||I am running the bulk insert in a query
BULK INSERT B1020063 FROM '\\ENDUSER-SQL\EnduserText\B1020063.txt' WITH (FIELDTERMINATOR = '|')
ENDUSER-SQL is the server that MS SQL 2005 is running on. I am running the query thru my Delphi 2005 code using ADO components. What I am doing is running my Delphi code to create the text file from a paradox table (don't laugh) then trying to use the bulk insert to take the text file into SQL table. I am running the code on my desktop and creating the text file on a shared folder on the SQL Server (\\ENDUSER-SQL\EnduserText\..)
When I rename the file I am taking B1020063.txt renaming to B1020063BLK.txt and it works. Also if after saving the file once I save it back to orginal name it also works. I am not moving the file from the orginal location just changing the name.
Thanks for the help.
Chris
|||BULK INSERT will be running from the server, so it doesn't matter where you ran it from.
Try changing the file name to the actual physical directory "C:\EndUser...." or whatever. See if that works.
It has to be a rights issue to the file. When you rename it, it is getting your rights, then running as you so it works.|||Do an xp_cmdshell 'dir \\ENDUSER-SQL\EnduserText' to see if SQL Server can access the folder.|||
I tried renaming the file to C:EnduserText\B1020049.txt and go the same results. Any ideas on how I can change the rights on the file so I can use it without having to renam it?
Thanks
Chis
|||I ran this stored procedure and here are the results :
Volume in drive \\ENDUSER-SQL\EnduserText has no label.
Volume Serial Number is 547D-E293
NULL
Directory of \\ENDUSER-SQL\EnduserText
NULL
10/29/2006 01:32 PM <DIR> .
10/29/2006 01:32 PM <DIR> ..
10/27/2006 03:27 PM 77,087 AcctRpts.txt
10/27/2006 03:27 PM 826,363 AcctSql.txt
10/27/2006 03:27 PM 217 AcctTypes.txt
10/27/2006 03:27 PM 121 AcctTypes2.txt
10/27/2006 03:27 PM 1,411,007 Address.txt
10/27/2006 03:27 PM 7,350 Addrlink.txt
10/27/2006 03:27 PM 185,409 AllFeat.txt
10/27/2006 03:27 PM 574 Allocation.txt
10/27/2006 03:27 PM 13,954 ANPI.txt
10/27/2006 03:27 PM 617 AREACODE.txt
10/27/2006 03:27 PM 1,882 ARSETUP.txt
10/27/2006 03:27 PM 3,613 B1020049.txt
10/27/2006 03:27 PM 7,243 B1020063.txt
10/27/2006 03:30 PM 50,497,983 B1120022.txt
10/27/2006 03:32 PM 46,926,455 B1120023.txt
10/27/2006 03:35 PM 54,188,893 B1120024.txt
10/27/2006 03:37 PM 50,094,853 B1120025.txt
10/27/2006 03:39 PM 54,233,552 B1120026.txt
10/27/2006 03:41 PM 54,900,297 B1120027.txt
10/27/2006 03:41 PM 72 BILCYCLE.txt
10/27/2006 03:41 PM 4,549 Billdate.txt
10/27/2006 03:41 PM 0 BILLERR.txt
10/27/2006 03:41 PM 3,765,367 BILLMAIN.txt
10/27/2006 03:41 PM 10,293 Billplan.txt
10/27/2006 03:44 PM 35,721,280 Billsum.txt
10/27/2006 03:41 PM 261,183 BILLTOBK.txt
10/27/2006 03:44 PM 152,994 Bilmesg.txt
10/27/2006 03:44 PM 2,574 BundledService.txt
10/27/2006 03:44 PM 1,759 BusCalendar.txt
10/29/2006 11:27 AM 49,507,562 C200110.txt
10/29/2006 11:29 AM 45,172,115 C200111.txt
10/29/2006 11:31 AM 47,562,230 C20017.txt
10/29/2006 11:33 AM 50,695,011 C20018.txt
10/29/2006 11:36 AM 56,648,282 C20019.txt
10/29/2006 11:38 AM 50,773,887 C20021.txt
10/29/2006 11:40 AM 46,550,340 C200210.txt
10/29/2006 11:42 AM 43,977,305 C200211.txt
10/29/2006 11:44 AM 47,850,286 C200212.txt
10/29/2006 11:46 AM 45,525,197 C20022.txt
10/29/2006 11:48 AM 41,312,655 C20023.txt
10/29/2006 11:50 AM 48,118,979 C20024.txt
10/29/2006 11:52 AM 45,851,116 C20025.txt
10/29/2006 11:54 AM 48,136,512 C20026.txt
10/29/2006 11:57 AM 47,693,599 C20027.txt
10/29/2006 11:59 AM 48,643,674 C20028.txt
10/29/2006 12:01 PM 49,560,178 C20029.txt
10/29/2006 12:03 PM 47,320,372 C20031.txt
10/29/2006 12:05 PM 44,242,516 C200310.txt
10/29/2006 12:07 PM 43,453,596 C200311.txt
10/29/2006 12:09 PM 48,399,934 C200312.txt
10/29/2006 12:11 PM 42,166,984 C20032.txt
10/29/2006 12:13 PM 42,449,679 C20033.txt
10/29/2006 12:15 PM 42,856,103 C20034.txt
10/29/2006 12:17 PM 44,666,989 C20035.txt
10/29/2006 12:19 PM 45,443,402 C20036.txt
10/29/2006 12:21 PM 47,432,232 C20037.txt
10/29/2006 12:23 PM 46,027,801 C20038.txt
10/29/2006 12:25 PM 49,730,365 C20039.txt
10/29/2006 12:27 PM 45,443,543 C20041.txt
10/29/2006 12:29 PM 44,342,955 C200410.txt
10/29/2006 12:31 PM 42,923,015 C200411.txt
10/29/2006 12:33 PM 43,391,572 C200412.txt
10/29/2006 12:35 PM 41,510,324 C20042.txt
10/29/2006 12:37 PM 45,968,110 C20043.txt
10/29/2006 12:38 PM 42,195,260 C20044.txt
10/29/2006 12:41 PM 47,489,289 C20045.txt
10/29/2006 12:43 PM 44,718,583 C20046.txt
10/29/2006 12:45 PM 47,564,502 C20047.txt
10/29/2006 12:47 PM 46,161,036 C20048.txt
10/29/2006 12:49 PM 47,575,248 C20049.txt
10/29/2006 12:51 PM 45,312,859 C20051.txt
10/29/2006 12:53 PM 44,072,464 C200510.txt
10/29/2006 12:55 PM 43,128,828 C200511.txt
10/29/2006 12:57 PM 42,414,577 C200512.txt
10/29/2006 01:34 PM 48,520,020 c20052.txt
10/29/2006 12:59 PM 42,046,826 C20053.txt
10/29/2006 01:00 PM 40,829,562 C20054.txt
10/29/2006 01:02 PM 46,261,908 C20055.txt
10/29/2006 01:04 PM 44,259,999 C20056.txt
10/29/2006 01:06 PM 44,248,580 C20057.txt
10/29/2006 01:08 PM 44,001,686 C20058.txt
10/29/2006 01:10 PM 44,345,187 C20059.txt
10/29/2006 01:12 PM 44,186,657 C20061.txt
10/29/2006 01:14 PM 30,604,652 C200610.txt
10/29/2006 01:16 PM 42,462,188 C20062.txt
10/29/2006 01:18 PM 44,203,999 C20063.txt
10/29/2006 01:20 PM 42,164,513 C20064.txt
10/29/2006 01:22 PM 51,782,537 C20065.txt
10/29/2006 01:24 PM 46,269,771 C20066.txt
10/29/2006 01:27 PM 51,892,690 C20067.txt
10/29/2006 01:29 PM 48,158,790 C20068.txt
10/29/2006 01:32 PM 50,942,048 C20069.txt
08/22/2006 04:10 AM 3,927,036 CASS060817done.txt
08/24/2006 12:32 PM 3,485 vod082406.txt
10/19/2006 12:13 PM 3,522 vod101306.txt
10/19/2006 12:13 PM 8,695 vod101606.txt
96 File(s) 3,236,402,958 bytes
2 Dir(s) 130,462,183,424 bytes free
NULL
It looks like it sees all the text files I have created.
Thanks for the help.
Chris
No comments:
Post a Comment