BULK INSERT inserts 2 rows - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

BULK INSERT inserts 2 rows

Author

Topic

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2008-05-15 : 12:04:51

Greetings

Got bulk insert thing going real nice thanks to your feedback! But now I notice that the BULK INSERT creates 2 identical rows while the flat file has the column headers and then corresponding row, just one row.
Why would it do that. Interestingly if the data file and format file are residing on a shared folder on SQL 2005, and I ran the BULK INSERT from studio it only inserts one row. But when I do BULK INSERT via user interface it creates 2 rows. What is going here.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-15 : 12:13:30

user interface? From where? What command are you using through it?

X002548
Not Just a Number

15586 Posts

Posted - 2008-05-15 : 12:18:53

The interface blows?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2008-05-15 : 12:21:18

I apologize if this is in the wrong post. Access Adp, resides on user machine. calls via cmd object that calls my stored procedure:dbo.usp_BulkInsert.

usp_BulkInsert
BULK INSERT dbo.StagingTable
FROM '\\remoteserver\shareddrive\DataFile.txt'
WITH (FIRSTROW = 2, FORMATFILE = '\\remoteserver\shareddrive\Format.txt')

X002548
Not Just a Number

15586 Posts

Posted - 2008-05-15 : 12:33:48

Explain

What 2 rows?

If you're using a sproc, then you're golden

post the sproc

If the sproc runs in ssms or QA without a problem, then it's the app

IF the table (staging?) needs to be cleared out before the sproc, then are you doing that?

Tell us, step by step what you are trying to accomplish

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2008-05-15 : 12:41:28

Brett,
hope this is more detailed :O)
Here is the content of the DataFile.txt, tab delimited

Material Width Length Qty Under OrderNo
818164 12.65 26.5 25 23 PRL14

here is the content of the format.txt
8.0
6
1 SQLCHAR 0 50 "\t" 1 Material SQL_Latin1_General_CP437_BIN
2 SQLCHAR 0 50 "\t" 2 Width SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 50 "\t" 3 Length SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 50 "\t" 4 Qty SQL_Latin1_General_CP1_CI_AS
5 SQLCHAR 0 50 "\t" 5 Under SQL_Latin1_General_CP1_CI_AS
6 SQLCHAR 0 50 "\r\n" 6 OrderNo SQL_Latin1_General_CP1_CI_AS

Staging table is identical to datafile as far structure same fields
as you notice I only have "one row" on the data file the first row is just header information which I bypass in my sproc by saying FIRSTROW = 2 as on of the parameters in the syntax.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2008-05-15 : 12:50:35

same thing happens when I change my sproc from BULK INSERT _to_ INSERT INTO syntax
2 rows created when sproc called from app with datafile and format file residing on a seperate server's remote shared folder
1 row created when sproc called from management studio with datafile and format file residing on shared drive on SQL Server
try it if you do not believe me , is this a SQL bug or security setting problem on our SQL server

>>dbo.usp_BulkInsert
INSERT INTO dbo.StagingTable
SELECT DISTINCT Material,
Width,
Length,
Qty,
Under,
OrderNo
--SELECT *
FROM OPENROWSET(BULK '\\remoteserver\shareddrive\TitanUnderReport.txt',
FORMATFILE = '\\remoteserver\shareddrive\format.txt',
FIRSTROW = 2)
AS Q

X002548
Not Just a Number

15586 Posts

Posted - 2008-05-15 : 12:50:51

Ahhh...all char data...

Of you have a carriage return in the data that could screw you up

Do this as a test

BULK insert the file to a test table with 1 row varchar(8000) and see what happens

If you get 1 row, CRLF is your prob

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2008-05-15 : 12:54:13

ok I will try as you say. but why would it do it differently when running it from management studio and identical format and data file reside on sql server shared folder. does it maybe have to do with ANSI mansi stuff?

X002548
Not Just a Number

15586 Posts

Posted - 2008-05-15 : 13:08:11

quote:
Originally posted by yosiasz

ok I will try as you say. but why would it do it differently when running it from management studio and identical format and data file reside on sql server shared folder. does it maybe have to do with ANSI mansi stuff?

Don't know...if you are saying that it works via SSMS, then it's the app

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2008-05-15 : 18:19:00

ok it does seem like it is in the app. how can I trace it, is it possible to trace this problem. obviously it seems like it is running twice. but who how where when? is there a debugger or tracer in SQL 2005?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources