Copy table to csv file

Last Post 17 Mar 2007 05:40 AM by TRACEYSQL. 5 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
TRACEYSQL
New Member
New Member

--
16 Mar 2007 04:07 AM
i have a table that when i use bcp or dts it misses part of the records in the table if i do all 65026 rows.

If i just do it for a range of the missing records it works.

Any ideas how to export table to comma delimited in sql analyzer
instead

Thanks
TRACEYSQL
New Member
New Member

--
16 Mar 2007 05:59 AM
I Was thinking that as the rows are 65026 and excel is 65536

but the file stops in the middle it process
001
008..here it stops
016
Not checked all others but if i run just for 008 works fine.
TRACEYSQL
New Member
New Member

--
17 Mar 2007 05:40 AM
I put rowid on the table and opened in access and all records are exported...

I assuming some carriage return is missing on the records perhaps

Working on command to display this...



DECLARE @position int, @string char(100), @ASCII_CONCAT INT,
@ASCII_CHAR CHAR,
@ASCII_CONCAT_DISPLAY NVARCHAR(100),
@ASCII_CHAR_DISPLAY NVARCHAR(100)
-- Initialize the variables.
SET @position = 1
SET @string = '"00101001"'
SET @ASCII_CONCAT = ''
SET @ASCII_CONCAT_DISPLAY = ''
SET @ASCII_CHAR_DISPLAY = ''

WHILE @position <= LEN(@STRING)
BEGIN
SET @ASCII_CONCAT = ASCII(SUBSTRING(@string, @position, 1))
SET @ASCII_CHAR = CHAR(ASCII(SUBSTRING(@string, @position, 1)))
--PRINT @ASCII_CHAR
--PRINT @ASCII_CONCAT
SET @position = @position + 1
Set @ASCII_CONCAT_DISPLAY = @ASCII_CONCAT_DISPLAY + @ASCII_CONCAT
SET @ASCII_CHAR_DISPLAY = @ASCII_CHAR + ';' + @ASCII_CHAR_DISPLAY

--SET @ASCII_CHAR_DISPLAY = @ASCII_CHAR + ';' + @ASCII_CHAR_DISPLAY

--PRINT @ASCII_CONCAT_DISPLAY
--PRINT @ASCII_CHAR_DISPLAY
END
PRINT @ASCII_CONCAT_DISPLAY
PRINT @ASCII_CHAR_DISPLAY
SET NOCOUNT OFF
GO

Can't get the ascii for
Set @ASCII_CONCAT_DISPLAY = @ASCII_CONCAT_DISPLAY + @ASCII_CONCAT
to work it add the values together

so im getting 455 instead of 34;48 etc

I think thats how i going to determine if there is something not correct at the end of the columns ?
SQLUSA
New Member
New Member

--
22 Mar 2007 08:26 PM
You can just scan for white space characters ascii < 32 and print that cell.

Also there are built-in functions which may help isDate, isNumeric.

With SSIS you can actually collect failing rows.

Kalman Toth
SQLUSA: http://www.sqlusa.com
TRACEYSQL
New Member
New Member

--
23 Mar 2007 02:13 AM
I have SSIS here let me see how to capture the failing rows that be great.
SQLUSA
New Member
New Member

--
24 Mar 2007 01:08 AM
You can do IGNORE or REDIRECT with failing rows.

Have fun! SSIS is really nothing short of fantastic.

Kalman Toth, Database Architect
SQLUSA: http://www.sqlusa.com


Acceptable Use Policy
---