none
Bulk Insert error file does not exist

    Question

  • 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

    Wednesday, October 25, 2006 8:24 PM

Answers

  • 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.
    Thursday, October 26, 2006 1:41 PM
  • 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.

    Friday, October 27, 2006 9:51 PM
  • Dear Chris:

    You can try the following:

    -- BULK INSERT : FROM FILE (NOTEPAD) TO TEMPORARY TABLE
     
    DECLARE @SqlScript      NVARCHAR(1000)
        
     
     SET @SqlScript = 'BULK INSERT #Log' +
         'FROM ''' + @LOG_PATH + '''
          WITH ( FIELDTERMINATOR = ' + CHAR(39) + CHAR(32) + CHAR(39) + ', ' + 'ROWTERMINATOR = ' + CHAR(39) + '\n' + CHAR(39) + ' )'
    
    PRINT @SqlScript 

    Thanks
    Md. M arufuzzaman
    Don't forget to click [Vote] / [Good Answer] on the post(s) that helped you.
    I will not say I have failed 1000 times; I will say that I have discovered 1000 ways that can cause failure – Thomas Edison.
    Sunday, December 27, 2009 3:49 PM

All replies

  • What is your bcp command? What is the original and new name of the file?
    Thursday, October 26, 2006 12:30 AM
  • 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.
    Thursday, October 26, 2006 1:41 PM
  • 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

    Thursday, October 26, 2006 4:37 PM
  • 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

     

    Thursday, October 26, 2006 4:46 PM
  • 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.

    Friday, October 27, 2006 9:51 PM
  • Do an xp_cmdshell 'dir \\ENDUSER-SQL\EnduserText' to see if SQL Server can access the folder.
    Saturday, October 28, 2006 12:03 AM
  • 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

    Sunday, October 29, 2006 3:35 PM
  • 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

    Monday, October 30, 2006 2:53 PM
  • Dear Chris:

    You can try the following:

    -- BULK INSERT : FROM FILE (NOTEPAD) TO TEMPORARY TABLE
     
    DECLARE @SqlScript      NVARCHAR(1000)
        
     
     SET @SqlScript = 'BULK INSERT #Log' +
         'FROM ''' + @LOG_PATH + '''
          WITH ( FIELDTERMINATOR = ' + CHAR(39) + CHAR(32) + CHAR(39) + ', ' + 'ROWTERMINATOR = ' + CHAR(39) + '\n' + CHAR(39) + ' )'
    
    PRINT @SqlScript 

    Thanks
    Md. M arufuzzaman
    Don't forget to click [Vote] / [Good Answer] on the post(s) that helped you.
    I will not say I have failed 1000 times; I will say that I have discovered 1000 ways that can cause failure – Thomas Edison.
    Sunday, December 27, 2009 3:49 PM