[dba-SQLServer] Really Interesting puzzle

Hi Arthur:

Here is a suggestion that is a bit of a stretch. 

I ran into a similar problem when manipulating data that came from a UNIX
server. The End-of-row symbol is different from the standard
<carridge-return> symbol used in the Windows environment. Linux uses LF (chr
10) and Windows uses CR (chr 13).

Jim

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Arthur
Fuller
Sent: Friday, October 03, 2008 7:47 AM
To: Discussion concerning MS SQL Server
Subject: [dba-SQLServer] Really Interesting puzzle

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/0
1/08
11:58:43
678605355511,Summary.Message,Summary.Status.Security.Allow.Trades,false,10/0
1/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
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com