none
problem with bulk insert RRS feed

  • Question

  • I have the following code:

    CREATE TABLE CSVTest

    (ID INT,

    FirstName VARCHAR(40),

    LastName VARCHAR(40),

    BirthDate SMALLDATETIME)

    GO 



    BULK

    INSERT CSVTest

    FROM 'c\data\tempfile.txt'

    WITH

    (

    FIELDTERMINATOR = ',',

    ROWTERMINATOR = '\n'

    )

    GO

     

    but i get an error when i try to use it.
    the error is as follows:
    Msg 4861, Level 16, State 1, Line 1
    Cannot bulk load because the file “c\data\tempfile.txt” could not be opened. Operating system error code 3(The system cannot find the path specified.).

    i tried adding the name of the computer to the address hoping it will work but i get the following error:
    Msg 4861, Level 16, State 1, Line 1
    Cannot bulk load because the file “\\LTSR397941\c\data\tempfile.txt” could not be opened. Operating system error code 53(The network path was not found.).

    we are using SQL server 2008 with windows authentication.
    sql server resides on a remote server to which my i have access.

    can anyone tell me what is wrong with the situation.

    thanks
    Vijaya

    Friday, September 30, 2011 11:16 PM

Answers

  • can you suggest any alternative to bulk load? i'll be very thankful.

     

     

    Another methid is the BCP command-line utility.  The functionality is similar identical to the BULK INSERT T-SQL statement except that the process runs on the client so you can specify the local path.  Below is an untested example.  See http://msdn.microsoft.com/en-us/library/ms162802.aspx.

     

    BCP MyDatabase.dbo.CSVTest in 'c\data\tempfile.txt' -S"MyServer" -T -c -t"," -r"\n"

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    • Marked as answer by KJian_ Monday, October 10, 2011 8:52 AM
    Tuesday, October 4, 2011 2:54 AM
    Moderator

All replies

  • i tried adding the name of the computer to the address hoping it will work but i get the following error:
    Msg 4861, Level 16, State 1, Line 1
    Cannot bulk load because the file “\\LTSR397941\c\data\tempfile.txt” could not be opened. Operating system error code 53(The network path was not found.).

    we are using SQL server 2008 with windows authentication.

    Have you created a share (named "c" in you example) and assigned share permissions so that your account has access to the share and file?

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    Saturday, October 1, 2011 1:42 AM
    Moderator
  • the c drive is shared on my computer and it's name is c$ in the properties.

    i tried the following:

    'c:\data\myfile.text'

    'c$\data\myfile.text' and 

    'c\data\myfile.text'

    but i get the same error.

    any idea what i'm missing?

    thanks for trying to help.

    vijaya

    Sunday, October 2, 2011 7:25 PM
  • "C$" is the administrative share name so you can specify the UNC path \\LTSR397941\c$\data\tempfile.txt as long as the account you are using has permissions to the share.  If you still have problems, logon to another computer with the same account and verify the path is remotely accessible.  For example, type "DIR \\LTSR397941\c$\data\" at a command prompt.

     

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    Sunday, October 2, 2011 8:08 PM
    Moderator
  • thanks a lot for the answer.

    you are right.  my C drive is not visible on the network.

    the properties window said C$ is shared but i cant change it or see where it is shared because i get a message saying it is for administrative purposes and i cant change it.

    i tried sharing it with a different name CS$.

    i gave my login name full permissions on it. but still it is not visible from another computer on the network.

    i tried mapping it in network connections.

    but there also i get the same error message.

    can you suggest any alternative to bulk load? i'll be very thankful.

    i spent a lot of time on this already and accusations are coming my way.

    thanks a lot.

     

    vijaya

    Monday, October 3, 2011 4:45 PM
  • Hello Vijaya

    From Your Query What I See is that  You Have Not Mentioned  " : " after driver letter Try Following

     

    CREATE TABLE CSVTest
    
    (ID INT,
    
    FirstName VARCHAR(40),
    
    LastName VARCHAR(40),
    
    BirthDate SMALLDATETIME)
    
    GO 
    
    
    
    
    
    BULK
    
    INSERT CSVTest
    
    FROM 'c:\data\tempfile.txt'
    
    WITH
    
    (
    
    FIELDTERMINATOR = ',',
    
    ROWTERMINATOR = '\n'
    
    )
    
    GO
    


     


    Mark as Answer If Reply Was Helpful
    Thanks
    Kuldeep Bisht
    Technical Lead @ Simplion Technologies
    Blog : www.dbsimplified.com
    Tuesday, October 4, 2011 2:33 AM
  • can you suggest any alternative to bulk load? i'll be very thankful.

     

     

    Another methid is the BCP command-line utility.  The functionality is similar identical to the BULK INSERT T-SQL statement except that the process runs on the client so you can specify the local path.  Below is an untested example.  See http://msdn.microsoft.com/en-us/library/ms162802.aspx.

     

    BCP MyDatabase.dbo.CSVTest in 'c\data\tempfile.txt' -S"MyServer" -T -c -t"," -r"\n"

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    • Marked as answer by KJian_ Monday, October 10, 2011 8:52 AM
    Tuesday, October 4, 2011 2:54 AM
    Moderator
  • I am having the identical problems as the original poster.  I see that your post is highlighted in green with a checkmark, so I'm assuming your post is the accepted answer, even though no response has been made by the original poster to confirm.

    Since this is a working alternative, is it possible to run this BCP command-line utility using a temp table instead?

    Thanks.

    Thursday, November 17, 2011 10:29 PM
  • Since this is a working alternative, is it possible to run this BCP command-line utility using a temp table instead?


    Yes, but it must be a global temp table ('##' preifx) in order to be visible on a different connection.

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    • Proposed as answer by shamilii Tuesday, January 8, 2013 7:18 AM
    Friday, November 18, 2011 2:20 AM
    Moderator
  • Since this is a working alternative, is it possible to run this BCP command-line utility using a temp table instead?


    Yes, but it must be a global temp table ('##' preifx) in order to be visible on a different connection.


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

    Just an FYI, that also means that jobs, scripts, or batch files using the same global temp table cannot be executed concurrently.

    --Jeff Moden


    • Edited by Jeff Moden Tuesday, January 14, 2014 10:28 PM spelling
    Tuesday, January 14, 2014 10:27 PM