Solved

BCP and Identity columns

Posted on 1997-12-11
2
1,578 Views
Last Modified: 2006-11-17
I am using BCP to inport an ASCII text file into an existing SQL Server database. The SQL Server Database has an identity column, the ASCII text file does not. I have read about the /E switch for BCP and my system does not seem to follow what the /E switch information says. My .fmt file is as follows.
6.0
13
1       SQLCHAR       0       0       ""        1       ID
2       SQLCHAR       0       255     "\t"      2       JOBNUMBER
3       SQLCHAR       0       4       "\t"      3       COSTELEMENT
4       SQLCHAR       0       4       "\t"      4       CLASS
5       SQLCHAR       0       1       "\t"      5       REVOREXP
6       SQLCHAR       0       30      "\t"      6       DESCRIPTION
7       SQLCHAR       0       10      "\t"      7       ABBREVIATION
8       SQLCHAR       0       25      "\t"      8       STANDARDAMOUNT
9       SQLCHAR       0       25      "\t"      9       STANDARDHOURS
10      SQLCHAR       0       1       "\t"      10      ISLABOR
11      SQLCHAR       0       1       "\t"      11      BILLABLE
12      SQLCHAR       0       1       "\t"      12      APPLYFEE
13      SQLCHAR       0       1       "\r\n"    13      APPLYFEEBURDEN

Where ID is the Identity column. Note that I have set the length to 0 and the terminator to null "" so that it isn't looking in the ASCII file for ID information.

The bcp command line looks like:

bcp mait.dbo.CELMWorkfile in e:\mait\copy\CELM.txt /f e:\mait\copy\celm.fmt  /e e:\mait\copy\ercelm.log /F 2 /U sa /P xxxxx S garfield

The error message I keep getting is:

#@ Row 1, Column 1: Attempt to bulk-copy a NULL value into a Server column which does not accept NULL values. @#
      TC7N      LABN      E      TECHNICAL STAFF ASSISTANT      TCH ST AST      0.00      0.00      N      Y      Y      N      <Unable to display>      <Unable to display>

The Identity column is the only one defined in my table as not being able to accept null values.

Any ideas?

Thanks,

Tim      
0
Comment
Question by:ttheis
2 Comments
 
LVL 1

Accepted Solution

by:
mikkon earned 50 total points
ID: 1089588
I have successfully BCP'd to an identity column but in my case I had an ID column in the text file as well. BCP simply ignored the ID's in the text file and assigned new IDs as the table identity column.

You might also try settings a length (4) for your blank ID line in the FMT file.
0
 

Author Comment

by:ttheis
ID: 1089589
In further research on my own, I discovered that this is a recognized bug by Microsoft in SQL Versions 6.0 and 6.5. Their solution was to add an ID column to the input ASCII file for the BCP utility to ignore. Indeed, this solution worked.
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question