Home Dashboard Directory Help
Search

BCP table out with format file does not work if file specifies less columns than source table by amihanov


Status: 

Active


3
0
Sign in
to vote
Type: Bug
ID: 584001
Opened: 8/6/2010 11:16:59 AM
Access Restriction: Public
0
Workaround(s)
view
0
User(s) can reproduce this bug

Description

In a nutshell, the problem is that SQL bcp accepts column skipping for import operation, but not for export (this is true at least since SQL 2000 up until now) .These are the errors you get:

Error1 = [Microsoft][SQL Server Native Client 10.0]Host-file columns may be skipped only when copying into the Server
Error2 = [Microsoft][SQL Server Native Client 10.0]The number of fields provided for bcp operation is less than the number of columns on the server.

It’s important to note 1) there’s no public documentation explicitly stating what the first error reads, and 2) it has not been included in migration document from Sybase (even if there were documentation on this, spending more than 2 hours searching in BOL or bing does not qualify for “public documentation”). Finally, 3) Sybase’s bcp does support this so from the customer’s point of view (and honestly I don’t blame him) this is plain and simply a SQL bug (no, no design bug.. just bug). This issue simply appeared out of the blue. Simply put, there was no way of knowing this in advance for proper planning and estimation of migration efforts.

Customer understands that there's no standard for BCP, but he believes is completely counter intuitive to think that import works but not the export...
Details
Sign in to post a comment.
Posted by Shura Balaganov on 1/7/2013 at 2:40 PM
Hi Jimmy Wu at Microsoft SQL Server,

In this post:

    http://connect.microsoft.com/sqlserver/feedback/details/584001/bcp

on 9/8/2010 at 1:10 PM you posted the following:

"... In addition, the BCP.exe utility also supports executing query statements, so you can SELECT the exact set of columns to export data from. ..."


I tried to do just exactly that - to use a SELECT query - and it did not work for me.
This is the sequence of steps I performed:


1. I created the table"tblGRP":

    CREATE TABLE Grouper.dbo.tblGRP (RECNUM BIGINT IDENTITY(1,1),
                             APG varchar(432))

populated it with data and than "indexed" field RECNUM:

    CREATE UNIQUE INDEX IX_RECNUM
        ON Grouper.dbo.tblGRP(RECNUM)



2. I checked how my data looks like using this query:

    SELECT TOP 1000 RECNUM, APG FROM Grouper.dbo.tblGRP

and the data looked just as I expected.


3. I ran the following query:

    SELECT APG + REPLACE(STR(RECNUM,21),' ','0') FROM Grouper.dbo.tblGRP ORDER BY RECNUM

and the result looked just as I expected.


4. I ran BCP utility to create a format file:

    bcp GRP_DAT_INP_HDD_2012_APD210 format nul -SHCF18 -dGrouper -T -c -f C:\Grouper\BCP\Extract.fmt -t

and here is format file:


Extract.fmt
--------------------------------------------------------------------------------------------------------
10.0
2
1     SQLCHAR             0     21     ""     1     RECNUM         ""
2     SQLCHAR             0     432     "\r\n" 2     APG             SQL_Latin1_General_CP1_CI_AS


5. Eventualy, I ran BCP utility to extract data from my test table into flat file GRP_DATA.txt using format file Extract.fmt:


bcp "SELECT APG + REPLACE(STR(RECNUM,21),' ','0') FROM HCF18.Grouper.dbo.tblGRP ORDER BY RECNUM" queryout C:\Grouper\DAT_INP\GRP_DATA.txt -SHCF18 -dGrouper -T -fC:\Grouper\BCP\Extract.fmt -b1000000

and here is the result:

C:\Grouper\BCP>bcp "SELECT APG + REPLACE(STR(RECNUM,21),' ','0') FROM HCF18.Grou
per.dbo.tblGRP ORDER BY RECNUM" queryout C:\Grouper\DAT_INP\GRP_DATA.txt -SHCF18
-dGrouper -T -fC:\Grouper\BCP\Extract.fmt -b1000000
SQLState = S1000, NativeError = 0
Error = [Microsoft][SQL Server Native Client 10.0]Host-file columns may be skipp
ed only when copying into the Server

Can you please tell what did I do wrong?

Thank you,
Shura Balaganov
Posted by Microsoft on 9/8/2010 at 1:10 PM
Thank-you for your feedback regarding this scenario you are interested in. Please remember that the BCP.exe utility also support executing a table out against a view, which will avoid having to skip any columns. In addition, the BCP.exe utility also supports executing query statements, so you can SELECT the exact set of columns to export data from.

We will continue to evaluate if the enhancement you are looking for can be achieved. In the meantime, please let us know if the above workarounds works for you.

Thanks,
Jimmy Wu
Microsoft SQL Server
Sign in to post a workaround.