BCP Output Issue - 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

General SQL Server Forums

Data Corruption Issues

BCP Output Issue

Author

Topic

bcpuser
Starting Member

1 Post

Posted - 2007-09-04 : 17:28:00

Maybe I should preface this by saying I basically need to get my data
from a view query into a double-quoted, comma-delimited text file.

That said, having a bit of a nightmare with BCP. Was working fine, client asked for a change,
suddenly wouldn't format correctly.
Removed the change, now it doesn't format correctly and adds an
"A" character before a http:// in a url column. Tried several
different formats including generating a format file from BCP that
doesn't seem to work.

I'm using BCP to output a quoted comma-delimited data file. The data file was created by BCP as follows:

bcp database.dbo.view out %locpath%datafile.txt -f data.fmt -U user -P pass -S server

The format file was created as follows:
bcp database.dbo.view format nul -U user -P pass -S server -n -f formatfile.fmt

This is in theory the format file that should work generated by BCP:

8.0
19
1 SQLCHAR 2 0 "" 1 col1 SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 2 5 "" 2 col1 SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 2 20 "" 3 col3 SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 2 4 "" 4 col4 SQL_Latin1_General_CP1_CI_AS
5 SQLCHAR 2 20 "" 5 col5 SQL_Latin1_General_CP1_CI_AS
6 SQLCHAR 2 30 "" 6 col6 SQL_Latin1_General_CP1_CI_AS
7 SQLCHAR 2 0 "" 7 col7 SQL_Latin1_General_CP1_CI_AS
8 SQLCHAR 2 20 "" 8 col8 SQL_Latin1_General_CP1_CI_AS
9 SQLCHAR 2 50 "" 9 col9 SQL_Latin1_General_CP1_CI_AS
10 SQLCHAR 2 6 "" 10 col10 SQL_Latin1_General_CP1_CI_AS
11 SQLCHAR 2 50 "" 11 col11 SQL_Latin1_General_CP1_CI_AS
12 SQLCHAR 2 50 "" 12 col12 SQL_Latin1_General_CP1_CI_AS
13 SQLCHAR 2 50 "" 13 col13 SQL_Latin1_General_CP1_CI_AS
14 SQLCHAR 2 607 "" 14 col14 SQL_Latin1_General_CP1_CI_AS
15 SQLCHAR 2 0 "" 15 col15 SQL_Latin1_General_CP1_CI_AS
16 SQLCHAR 2 50 "" 16 col16 SQL_Latin1_General_CP1_CI_AS
17 SQLCHAR 2 50 "" 17 col17 SQL_Latin1_General_CP1_CI_AS
18 SQLCHAR 2 0 "" 18 col18 SQL_Latin1_General_CP1_CI_AS
19 SQLCHAR 2 0 "" 19 col19 SQL_Latin1_General_CP1_CI_AS

I keep getting zero length records with it. I'm using a view to
capture the data. If I roll the version back to 7.0 in the top of
the format file it actually works but introduces some white space
which fortunately gets trimmed anyway) and an "A" before the urls in
the url column for reasons unknown. Doesn't do it anywhere else.

An example of the view is:

CREATE VIEW dbo.MyView
AS
SELECT
'' as col1,
'123' as col2,
val3 as col3,
val4 as col4,
val5 as col5,
val6 as col6,
'' as col7,
val8 as col8,
val9 as col9,
cast(val10 as varchar(6)) as col10,
val11 as col11,
CASE val12 WHEN 'N/A' THEN '' ELSE val12 END as col12,
CASE val13 WHEN 'N/A' THEN '' ELSE val13 END as col13,
('http://myserver.com/dir/'+val14+'.jpg,http://myserver.com/dir/'+val14+'(2).jpg,http://myserver.com/dir/'+val14+'(3).jpg,http://myserver.com/dir/'+val14+'(4).jpg,http://myserver.com/dir/'+val14+'(5).jpg,http://myserver.com/dir/'+val14+'(6).jpg,http://myserver.com/dir/'+val14+'(7).jpg,http://myserver.com/dir/'+val14+'(8).jpg,http://myserver.com/dir/'+val14+'(9).jpg,http://myserver.com/dir/'+val14+'(10).jpg') as col14,
'' as col15,
val16 as col16,
val17 as col17,
'' as col18,
'' as col19
from table

It was working fine but a column had to be added and then BCP started
having issues. Drives me crazy because BCP is so tempramental. I
can't see any reason why it wouldn't work since it did previously but
it's not the easiest thing to debug. Maybe I need to change some
parameters somewhere?

Any help appreciated. Thanks.

nr
SQLTeam MVY

12543 Posts

Posted - 2007-09-07 : 08:55:32

Can you post an xample of the A's.
Sounds like a translation issue - are you sure they are in the file and not being created by whatever you are using o view the file. Have you tried a hex editor to see which character the really are?
Have a look at
http://www.simple-talk.com/sql/database-administration/creating-csv-files-using-bcp-and-stored-procedures/
If you use an SP to format the rows it means you don't have to use a format file and things become much easier. Also means you can test the format in query analyser which makes development quicker too.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources