Export data in SQL 2008
-
Hi, I just used the Import Export functionality in sql 2008 to export a table having about one million rows. I have exported to a flat file, and delimited with pipes. For some reason, about 23,000 rows below, the one of the fields expands and as a result pulls down to the next row, creating a blank row, the data hence after is fine. Now and then we are getting these blank rows. I have confirmed this data is of length 40 characters long or less. What could be causing the odd line breaks in the flat file? any ideas appreciated!! Thanks!
-
Hi, I just used the Import Export functionality in sql 2008 to export a table having about one million rows. I have exported to a flat file, and delimited with pipes. For some reason, about 23,000 rows below, the one of the fields expands and as a result pulls down to the next row, creating a blank row, the data hence after is fine. Now and then we are getting these blank rows. I have confirmed this data is of length 40 characters long or less. What could be causing the odd line breaks in the flat file? any ideas appreciated!! Thanks!
Is there a carriage-return or line break embedded in the field? If you can isolate one of the affected rows, try dumping the characters from the affected field:
DECLARE @FieldValue varchar(100);
SELECT @FieldValue = AffectedField FROM AffectedTable WHERE Key = @AffectedKey;WITH E1(N) AS
(
SELECT 1
UNION ALL SELECT 1
UNION ALL SELECT 1
UNION ALL SELECT 1
UNION ALL SELECT 1
UNION ALL SELECT 1
UNION ALL SELECT 1
UNION ALL SELECT 1
UNION ALL SELECT 1
UNION ALL SELECT 1
),
cteNumbers (N) As
(
SELECT
ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM
E1 a, E1 b -- Cross-join = 100 rows
)
SELECT TOP(LEN(@FieldValue))
N,
Ascii(Substring(@FieldValue, N, 1)) As AsciiCode,
Substring(@FieldValue, N, 1) As Character
FROM
cteNumbers
ORDER BY
N
;You're looking for either a
10or a13in theAsciiCodecolumn.
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
