Solved

BULK INSERT Error. Plse help!

Posted on 2004-03-30
6
811 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
Comment Utility
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
Comment Utility
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
Comment Utility
Hi Beneke,

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

Cheers,

Rajesh
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 3

Accepted Solution

by:
ctcampbell earned 250 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
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…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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.

728 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now