Solved

BULK INSERT Error. Plse help!

Posted on 2004-03-30
6
815 Views
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
   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
Comment
Question by:beneke
6 Comments
 
LVL 13

Expert Comment

by:danblake
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:
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
 
LVL 13

Expert Comment

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

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

Expert Comment

by:raje10410
ID: 10713490
Hi Beneke,

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

Cheers,

Rajesh
0
Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

 
LVL 3

Accepted Solution

by:
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.
0
 
LVL 13

Expert Comment

by:danblake
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.
0
 

Author Comment

by:beneke
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
SET QUOTED_IDENTIFIER OFF

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

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

696 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