• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 825
  • Last Modified:

BULK INSERT Error. Plse help!

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
   FROM ''C:\TESTFILE.PRN''
   WITH
     (
        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,

Rgards
0
beneke
Asked:
beneke
1 Solution
 
danblakeCommented:
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:
from:
|\n

to:
| \n

if at all possible.

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

I believe this is a bug with bulk insert.
0
 
danblakeCommented:
You can try the bcp, an alternative in this particular case.

The space between the | \n applies to every column (unfortunatly).
0
 
raje10410Commented:
Hi Beneke,

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

Cheers,

Rajesh
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
ctcampbellCommented:
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.
0
 
danblakeCommented:
To my earlier post, I've seen this occur even when all columns are NULL, and varchar(50) with a similar data-file.
0
 
benekeAuthor Commented:
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
SET QUOTED_IDENTIFIER OFF

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

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now