BULK INSERT Error. Plse help!

Posted on 2004-03-30
Last Modified: 2008-02-01
Hi there,

SQL 7.0
I would like to upload the following file to a sql table. Here is the data of the file TESTFILE.PRN':

RZGB|FT|12| |\n
RZGB|FT|12| |\n
RZGB|FT|12| |\n
RZGB|FT|12| |\n
RZGB|FT|12|This is a test|\n
RZGB|FT|12| |\n
RZGB|FT|12|Zone 1      Zone 2|\n
RZGB|FT|12|Hello World||\n
RZGB|FT|12|     5 leading spaces|\n
RZGB|FT|12|      Tab(10) - Hello|\n

Here is the SQL file layout:

CREATE TABLE [dbo].[ArgiefTransaksies] (
      [TakDepotKodeRef] [char] (4) NOT NULL ,
      [HoofraamNommer] [varchar] (2) NOT NULL ,
      [Dokumentnommer] [decimal](1, 0) NOT NULL ,
      [DataLyn] [varchar] (133) NOT NULL )

Here is the SQL query that I try to use:

BULK INSERT ArgiefTransaksies
        DATAFILETYPE = 'char',
        FIELDTERMINATOR = '|',
        ROWTERMINATOR = '|\n',
        MAXERRORS = 0

When I run this query it return this error:

Server: Msg 1038, Level 15, State 1, Line 2
Cannot use empty object or column names. Use a single space if necessary.

Plse help,

Question by:beneke
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 13

Expert Comment

ID: 10713455
Server: Msg 1038, Level 15, State 1, Line 2
Cannot use empty object or column names. Use a single space if necessary.

Try increasing the length of the data in your last field, in your file:

| \n

if at all possible.

Where a space is between the | and the \n.

I believe this is a bug with bulk insert.
LVL 13

Expert Comment

ID: 10713480
You can try the bcp, an alternative in this particular case.

The space between the | \n applies to every column (unfortunatly).

Expert Comment

ID: 10713490
Hi Beneke,

Try removing the NOT NULL constraint from the column [DataLyn]


Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!


Accepted Solution

ctcampbell earned 250 total points
ID: 10714448
You defined [Dokumentnommer] as [decimal](1, 0), but the data file has the value "12".  You need to make it decimal(2,0).  After fixing the Dokumentnommer, it works fine in SQL Server 2000.  Not much help for you though.
LVL 13

Expert Comment

ID: 10721840
To my earlier post, I've seen this occur even when all columns are NULL, and varchar(50) with a similar data-file.

Author Comment

ID: 10738656
The problem was the flatfile:

No need for a line terminator! Only a field terminator will do.

This is a sample from earlier:
RZGB|FT|12|This is a test|\n
This now should changed to:
"RZGB"|"FT"|"12"|"This is a test"

ALTER   PROCEDURE vkbsp_insArgief (@PathFileName varchar(200))AS

Declare @SQL varchar(2000)
Select @SQL = "BULK INSERT ArgiefTransaksies
               FROM '"+@PathFileName+"'
               WITH (
                     FIELDTERMINATOR = '|',
                     MAXERRORS = 0
                    ) "

Featured Post

How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

Question has a verified solution.

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

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

688 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