[dba-SQLServer] Really Interesting puzzle

Hi Arthur

Have you tested this with 

  ROWTERMINATOR='\r\n'

?

/gustav

>>> fuller.artful at gmail.com 03-10-2008 16:46 >>>
I've just come upon a truly interesting puzzle. It's a problem but the app
doesn't have to be done today so we have a window to explore. I have a
couple of colleagues to whom I have showed this and so far no one has any
idea what is going wrong -- which in a way makes it even more fun than your
usual nightmare.

I'm reading an ascii file which looks like this:

<incoming data>
SecurityIDBSID,MessageName,FieldName,DataValue,TimeOfUpdate
678605355511,Summary.Message,Summary.Status.Security.Allow.Quotes,false,10/01/08 11:58:43
678605355511,Summary.Message,Summary.Status.Security.Allow.Trades,false,10/01/08 11:58:43
</incoming data>

I'm using the bulk insert command:

<sql>
USE BPOD
BULK INSERT dbo.BPOD_Message_Staging2
FROM 's:\arthur files\test\bpod_35A.csv'
WITH (FIRSTROW=2,FIELDTERMINATOR=',',ROWTERMINATOR='\n')
</sql>

I was initially having a problem dealing with the TimeOfUpdate values in the
incoming file, so I changed the destination table's corresponding column to
varchar. Now the result is really interesting. Here is a paste of the first
returned row. (The binary data column is a timestamp and irrelevant to this
discussion.)


  678605355116 Summary.Message Summary.ValueTraded 930752738.810806 10/01/08 11:58:44 678605355116 <Binary data>

Notice that the TimeOfUpdate column contains the same number as the first
column. It got the date right but then appended the entire second row to
that column, failing to see the row delimiter. I've run it a few times now
and the pattern is consistent. I'm getting every other row in the
destination table. I have checked with our c++ guy to see if he was doing
anything out of the ordinary at end of line, and no.

I am completely baffled by this one. Anyone got any ideas?

TIA,
Arthur