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