none
Bulk Insert error

    Question

  • Using a standalone windows 7 pc, when I execute the following using the SSMS 2016 on my local database, I have no issues inserting the records.  But when I try to do the same using my Azure database I receive the error message below... Could this be a bulk insert security issue???  I am able to copy and paste the records directly into the azure db table.....

    BULK INSERT Clients From "C:\Temp\TXT\01_Clients.txt" with (FIELDTERMINATOR = '\t', ROWTERMINATOR = '\n')

    error message below...

    Msg 4861, Level 16, State 1, Line 31

    Cannot bulk load because the file "C:\Temp\TXT\01_Clients.txt" could not be opened. Operating system error code (null).


    • Edited by John SegK20 Wednesday, March 15, 2017 10:23 PM
    Wednesday, March 15, 2017 10:22 PM

All replies

  • Make sure that your Azure account user is able to access to your file to be used.

    A Fan of SSIS, SSRS and SSAS

    Wednesday, March 15, 2017 10:27 PM
  • When you do bulk inserts like this it is looking for the file on the azure vm, not on your machine.

    Does this file exist on your machine or the azure machine?

    Wednesday, March 15, 2017 11:27 PM
  • Hi John,

    Just tested and confirmed what Hilary says. In this case, I would suggest you use BCP to insert into Azure database. Here’s a example:
    BCP DBNAME.SCHEMANAME.CLIENT IN C:\Temp\TXT\01_Clients.txt -S AZUREDBNAME.database.windows.net -U USERNAME@AZUREDBNAME -P PASSWORD -r -t
    

    Replace the underlined part to fit your requirement see how it goes.

    If you have any other questions, please let me know.

    Regards,
    Lin

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.



    Thursday, March 16, 2017 5:15 AM
    Moderator
  • Thanks for the help with this….  Starting to work now but it is asking for the storage type and file terminator for each column.  Is there a way around this? 

    Thursday, March 16, 2017 10:28 PM
  • if you use the native mode you will not have to do this

    bcp "DatabaseName.dbo.TableName" out  Tablename.dat -S. -n -T

    that bcp's it out then do this

    bcp "AzuredatabaseName.dbo.AzureTableName" in Tablename.dat -SAzureSQLServer -n -E -b1000 -UAzureLoginAccount -PAzurePassword

    Thursday, March 16, 2017 10:31 PM
  • I am using native mode as I tried to import a txt file into an azure database.  But now receiving an unexpected eof error.  I was able to use this same file on a local database with the tsql Bulk Insert clause....  any ideas?? Thanks!

    Friday, March 17, 2017 2:34 AM
  • Native mode is not for text files.

    Your initial BULK INSERT statement had tab as field separator and newline as record terminator. So you should use the -c option with BCP, not -n. As it happens, tab and newlined are the default for field and record terminators.

    Friday, March 17, 2017 10:57 PM