Solved

BCP and Identity columns

Posted on 1997-12-11
2
1,571 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

809 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