none
BCP utility uploads 0 rows to SQL Server Azure

    Question

  • bcp azure_db.dbo.db_table in calendar_dates.txt -f ../../../scripts/format_files/calendar_dates.fmt -F 2 /S tcp:azure_server /U username@servername /P password


    When I issue this command through a PowerShell prompt, I get the following response:

    starting copy..
    Network packet size 4096
    Clock 546 ms
    0 rows copied.

    When I look at the table in Azure, there are no rows are reported.  I even tried removing all entries in the text file leaving just the first two lines, all to no avail.

    If I get the input file name wrong or format file name wrong, or even the first  line indicating the SQL Server version in the format file wrong, an appropriate error message is returned from BCP.  With correct parameters, or it seems, no error is reported, yet nothing happens.  Anyone seen this?

    • Edited by Klaus Nji Wednesday, March 11, 2015 11:24 PM
    Wednesday, March 11, 2015 12:53 PM

Answers

  • Just figured out that problem is the way bcp is interpreting line terminating characters.  Notepad says the routes.txt, which I will call "bad-file" has no line terminating characters and it looks like this:

    route_id,route_short_name,route_long_name,route_desc,route_type,route_url 1-184,1,,,3, 2-184,2,,,3,

    4-184,4,,,3,  5-184,5,,,3, 6-184,6,,,3, 7-184,7,,,3, 8-184,8,,,3, 9-184,9,,,3,

    Notepad++, however, see this:

    route_id,route_short_name,route_long_name,route_desc,route_type,route_url
    1-184,1,,,3,
    2-184,2,,,3,
    4-184,4,,,3,
    5-184,5,,,3,

    NOTE: even the editor on this site recognizes the line terminators.  I had to deliberately remove the line terminators.

    So, using Notepad++, I create a new blank file, routes1.txt, which I will call "good-file" selected all text from the bad file using CTRL + A and copied the content from clipboard into the new file using CTRL+V.

    Opened new file in Notepad and it looks well formed.  If I just make a copy of the bad-file, bcp runs into the same problem as Notepad.

    Seems like bcp is using similar code to Notepad to parse files....

    Saturday, March 14, 2015 3:02 PM

All replies

  • Hi,

    Thank you for your question.

    I am trying to involve someone familiar with this topic to further look at this issue.

    In the meantime, please have a check on the below links and check if it helps:

    http://blogs.msdn.com/b/cesardelatorre/archive/2010/06/04/importing-exporting-data-to-sql-azure-databases-using-bcp-and-sql-scripts.aspx

    http://www.databasejournal.com/features/mssql/windows-azure-sql-database-uploading-data-by-using-the-bcp-utility.html

    Regards,

    Mekh.

    Wednesday, March 11, 2015 4:09 PM
    Moderator
  • Thanks for the response.

    I have visited both links and so far cannot see what is fundamentally wrong with my command.  I also am currently specifying an error file but nothing is being written to it.   Again, I am getting 0 rows updated without errors.

    Thursday, March 12, 2015 12:12 AM
  • Hi Klaus Nji,

    Below are the steps that I used to attempt to repro your situation. I, unfortunately, was not able to successfully repeat the error, or lack thereof, that you received. My intention in posting these steps is that you can use them as a comparison for troubleshooting.  

    1) Create and populate a table:

    CREATE TABLE calendar_dates ([begin] datetime, [end] datetime)
    
    CREATE UNIQUE CLUSTERED INDEX index_name
    ON calendar_dates ([begin])
    
    INSERT INTO calendar_dates VALUES (GETUTCDATE(), dateadd(day, 1, GETUTCDATE()))
    INSERT INTO calendar_dates VALUES (GETUTCDATE(), dateadd(day, 1, GETUTCDATE()))
    INSERT INTO calendar_dates VALUES (GETUTCDATE(), dateadd(day, 1, GETUTCDATE()))
    
    SELECT * FROM calendar_dates 
    2) BCP the data to your local machine to to get the proper format file

    bcp "SELECT * FROM [dbo].[calendar_dates]" queryout C:\<path>\dateout.txt -S <serverName>.database.windows.net -U <userName> -P <password> -d <database>

    3) Create a second table to input data

    CREATE TABLE calendar_dates_input ([begin] datetime, [end] datetime)
    
    CREATE UNIQUE CLUSTERED INDEX index_name_input
    ON calendar_dates_input ([begin])

    4) BCP the data into the second table

    BCP dbo.calendar_dates_input in C:\<path>\dateout.txt -f C:\<path>\formatDate.txt -S <serverName>.database.windows.net -U <userName> -P <password> -d <database>

    5) I receive the following output:

    Starting copy...

    6 rows copied.
    Network packet size (bytes): 4096
    Clock Time (ms.) Total     : 344    Average : (17.44 rows per sec.)

    A couple of other things to consider is the version of the BCP client that you are running (bcp -v).  For my attempted repro, I am running the following version:

        PS C:\> bcp -v
        BCP - Bulk Copy Program for Microsoft SQL Server.
        Copyright (C) Microsoft Corporation. All Rights Reserved.
        Version: 12.0.2000.8

    To get the latest version of BCP (and SSMS), please download CU5 for SQL Server 14 [Blog][Download]


    Friday, March 13, 2015 12:55 AM
  • Casey, thanks for the detailed response.  I already have a format file, called routes.fmt which looks like this:

    11.0
    6
    1       SQLCHAR             0       0     ","   2     RouteId                Latin1_General_CI_AS
    2       SQLCHAR             0       0     ","   4     Name                   Latin1_General_CI_AS
    3       SQLCHAR             0       0     ","   5     LongName               Latin1_General_CI_AS
    4       SQLCHAR             0       0     ","   6     Description            Latin1_General_CI_AS
    5       SQLCHAR             0       0     ","   7     RouteType              ""
    6       SQLCHAR             0       0     "\r\n"   8     RouteUrl               Latin1_General_CI_AS

    Then I started playing with the data files and decided to try importing against my local SQLEXPRESS first.  What I have now is two "identical" data files as far as my eyes can tell.  When I run bcp against one, it import 0 rows.  When I run bcp against the other is imports 152 rows.  If there is an easy way to post attachments here I will be glad to do so.  The command is as follows:

    PS C:\dev\tut\data\scripts> bcp oc_transpo.dbo.busroutes in c:\dev\tut\data\oc_transpo\google_transi
    t\latest\routes.txt -f c:\dev\tut\data\oc_transpo\google_transit\format_files\routes.fmt -S localhost\SQLEXPRE
    SS -T -k -F 2


    • Edited by Klaus Nji Saturday, March 14, 2015 12:18 PM
    Saturday, March 14, 2015 11:07 AM
  • Just figured out that problem is the way bcp is interpreting line terminating characters.  Notepad says the routes.txt, which I will call "bad-file" has no line terminating characters and it looks like this:

    route_id,route_short_name,route_long_name,route_desc,route_type,route_url 1-184,1,,,3, 2-184,2,,,3,

    4-184,4,,,3,  5-184,5,,,3, 6-184,6,,,3, 7-184,7,,,3, 8-184,8,,,3, 9-184,9,,,3,

    Notepad++, however, see this:

    route_id,route_short_name,route_long_name,route_desc,route_type,route_url
    1-184,1,,,3,
    2-184,2,,,3,
    4-184,4,,,3,
    5-184,5,,,3,

    NOTE: even the editor on this site recognizes the line terminators.  I had to deliberately remove the line terminators.

    So, using Notepad++, I create a new blank file, routes1.txt, which I will call "good-file" selected all text from the bad file using CTRL + A and copied the content from clipboard into the new file using CTRL+V.

    Opened new file in Notepad and it looks well formed.  If I just make a copy of the bad-file, bcp runs into the same problem as Notepad.

    Seems like bcp is using similar code to Notepad to parse files....

    Saturday, March 14, 2015 3:02 PM