Я выполняю экспорт BCP:
bcp "exec SourceDatabase.dbo.usp_Select_CLIENT_DailyFile_BCP" queryout "outbox\CLIENT_%DATE:~10,4%%DATE:~4,2%%DATE:~7,2%.dat" -f dailyfile.fmt -S SQLSVR10 -T -q >>%DATE:~10,4%%DATE:~4,2%.log
Вот файл формата, о котором идет речь:
9.0
80
1 SQLCHAR 0 20 "|" 1 xxxxxxxxxxxxxxx1 SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 20 "|" 2 xxxxxxxxxxxxxxx2 SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 2 "|" 3 xxxxxxxxxxxxxxx3 SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 20 "|" 4 xxxxxxxxxxxxxxx4 SQL_Latin1_General_CP1_CI_AS
5 SQLCHAR 0 20 "|" 5 xxxxxxxxxxxxxxx5 SQL_Latin1_General_CP1_CI_AS
6 SQLCHAR 0 3 "|" 6 xxxxxxxxxxxxxxx6 SQL_Latin1_General_CP1_CI_AS
7 SQLCHAR 0 2 "|" 7 xxxxxxxxxxxxxxx7 SQL_Latin1_General_CP1_CI_AS
8 SQLCHAR 0 8 "|" 8 xxxxxxxxxxxxxxx8 SQL_Latin1_General_CP1_CI_AS
9 SQLCHAR 0 8 "|" 9 xxxxxxxxxxxxxxx9 SQL_Latin1_General_CP1_CI_AS
10 SQLCHAR 0 8 "|" 10 xxxxxxxxxxxxxx10 SQL_Latin1_General_CP1_CI_AS
11 SQLCHAR 0 8 "|" 11 xxxxxxxxxxxxxx11 SQL_Latin1_General_CP1_CI_AS
12 SQLCHAR 0 8 "|" 12 xxxxxxxxxxxxxx12 SQL_Latin1_General_CP1_CI_AS
13 SQLCHAR 0 8 "|" 13 xxxxxxxxxxxxxx13 SQL_Latin1_General_CP1_CI_AS
14 SQLCHAR 0 8 "|" 14 xxxxxxxxxxxxxx14 SQL_Latin1_General_CP1_CI_AS
15 SQLCHAR 0 8 "|" 15 xxxxxxxxxxxxxx15 SQL_Latin1_General_CP1_CI_AS
16 SQLCHAR 0 8 "|" 16 xxxxxxxxxxxxxx16 SQL_Latin1_General_CP1_CI_AS
17 SQLCHAR 0 8 "|" 17 xxxxxxxxxxxxxx17 SQL_Latin1_General_CP1_CI_AS
18 SQLCHAR 0 8 "|" 18 xxxxxxxxxxxxxx18 SQL_Latin1_General_CP1_CI_AS
19 SQLCHAR 0 8 "|" 19 xxxxxxxxxxxxxx19 SQL_Latin1_General_CP1_CI_AS
20 SQLCHAR 0 8 "|" 20 xxxxxxxxxxxxxx20 SQL_Latin1_General_CP1_CI_AS
21 SQLCHAR 0 5 "|" 21 xxxxxxxxxxxxxx21 SQL_Latin1_General_CP1_CI_AS
22 SQLCHAR 0 50 "|" 22 xxxxxxxxxxxxxx22 SQL_Latin1_General_CP1_CI_AS
23 SQLCHAR 0 1 "|" 23 xxxxxxxxxxxxxx23 SQL_Latin1_General_CP1_CI_AS
24 SQLCHAR 0 2 "|" 24 xxxxxxxxxxxxxx24 SQL_Latin1_General_CP1_CI_AS
25 SQLCHAR 0 50 "|" 25 xxxxxxxxxxxxxx25 SQL_Latin1_General_CP1_CI_AS
26 SQLCHAR 0 5 "|" 26 xxxxxxxxxxxxxx26 SQL_Latin1_General_CP1_CI_AS
27 SQLCHAR 0 1 "|" 27 xxxxxxxxxxxxxx27 SQL_Latin1_General_CP1_CI_AS
28 SQLCHAR 0 25 "|" 28 xxxxxxxxxxxxxx28 SQL_Latin1_General_CP1_CI_AS
29 SQLCHAR 0 25 "|" 29 xxxxxxxxxxxxxx29 SQL_Latin1_General_CP1_CI_AS
30 SQLCHAR 0 50 "|" 30 xxxxxxxxxxxxxx30 SQL_Latin1_General_CP1_CI_AS
31 SQLCHAR 0 5 "|" 31 xxxxxxxxxxxxxx31 SQL_Latin1_General_CP1_CI_AS
32 SQLCHAR 0 50 "|" 32 xxxxxxxxxxxxxx32 SQL_Latin1_General_CP1_CI_AS
33 SQLCHAR 0 50 "|" 33 xxxxxxxxxxxxxx33 SQL_Latin1_General_CP1_CI_AS
34 SQLCHAR 0 50 "|" 34 xxxxxxxxxxxxxx34 SQL_Latin1_General_CP1_CI_AS
35 SQLCHAR 0 50 "|" 35 xxxxxxxxxxxxxx35 SQL_Latin1_General_CP1_CI_AS
36 SQLCHAR 0 11 "|" 36 xxxxxxxxxxxxxx36 SQL_Latin1_General_CP1_CI_AS
37 SQLCHAR 0 11 "|" 37 xxxxxxxxxxxxxx37 SQL_Latin1_General_CP1_CI_AS
38 SQLCHAR 0 50 "|" 38 xxxxxxxxxxxxxx38 SQL_Latin1_General_CP1_CI_AS
39 SQLCHAR 0 50 "|" 39 xxxxxxxxxxxxxx39 SQL_Latin1_General_CP1_CI_AS
40 SQLCHAR 0 50 "|" 40 xxxxxxxxxxxxxx40 SQL_Latin1_General_CP1_CI_AS
41 SQLCHAR 0 50 "|" 41 xxxxxxxxxxxxxx41 SQL_Latin1_General_CP1_CI_AS
42 SQLCHAR 0 25 "|" 42 xxxxxxxxxxxxxx42 SQL_Latin1_General_CP1_CI_AS
43 SQLCHAR 0 2 "|" 43 xxxxxxxxxxxxxx43 SQL_Latin1_General_CP1_CI_AS
44 SQLCHAR 0 10 "|" 44 xxxxxxxxxxxxxx44 SQL_Latin1_General_CP1_CI_AS
45 SQLCHAR 0 20 "|" 45 xxxxxxxxxxxxxx45 SQL_Latin1_General_CP1_CI_AS
46 SQLCHAR 0 50 "|" 46 xxxxxxxxxxxxxx46 SQL_Latin1_General_CP1_CI_AS
47 SQLCHAR 0 25 "|" 47 xxxxxxxxxxxxxx47 SQL_Latin1_General_CP1_CI_AS
48 SQLCHAR 0 60 "|" 48 xxxxxxxxxxxxxx48 SQL_Latin1_General_CP1_CI_AS
49 SQLCHAR 0 50 "|" 49 xxxxxxxxxxxxxx49 SQL_Latin1_General_CP1_CI_AS
50 SQLCHAR 0 35 "|" 50 xxxxxxxxxxxxxx50 SQL_Latin1_General_CP1_CI_AS
51 SQLCHAR 0 35 "|" 51 xxxxxxxxxxxxxx51 SQL_Latin1_General_CP1_CI_AS
52 SQLCHAR 0 35 "|" 52 xxxxxxxxxxxxxx52 SQL_Latin1_General_CP1_CI_AS
53 SQLCHAR 0 35 "|" 53 xxxxxxxxxxxxxx53 SQL_Latin1_General_CP1_CI_AS
54 SQLCHAR 0 25 "|" 54 xxxxxxxxxxxxxx54 SQL_Latin1_General_CP1_CI_AS
55 SQLCHAR 0 2 "|" 55 xxxxxxxxxxxxxx55 SQL_Latin1_General_CP1_CI_AS
56 SQLCHAR 0 10 "|" 56 xxxxxxxxxxxxxx56 SQL_Latin1_General_CP1_CI_AS
57 SQLCHAR 0 10 "|" 57 xxxxxxxxxxxxxx57 SQL_Latin1_General_CP1_CI_AS
58 SQLCHAR 0 50 "|" 58 xxxxxxxxxxxxxx58 SQL_Latin1_General_CP1_CI_AS
59 SQLCHAR 0 50 "|" 59 xxxxxxxxxxxxxx59 SQL_Latin1_General_CP1_CI_AS
60 SQLCHAR 0 35 "|" 60 xxxxxxxxxxxxxx60 SQL_Latin1_General_CP1_CI_AS
61 SQLCHAR 0 35 "|" 61 xxxxxxxxxxxxxx61 SQL_Latin1_General_CP1_CI_AS
62 SQLCHAR 0 35 "|" 62 xxxxxxxxxxxxxx62 SQL_Latin1_General_CP1_CI_AS
63 SQLCHAR 0 35 "|" 63 xxxxxxxxxxxxxx63 SQL_Latin1_General_CP1_CI_AS
64 SQLCHAR 0 25 "|" 64 xxxxxxxxxxxxxx64 SQL_Latin1_General_CP1_CI_AS
65 SQLCHAR 0 2 "|" 65 xxxxxxxxxxxxxx65 SQL_Latin1_General_CP1_CI_AS
66 SQLCHAR 0 10 "|" 66 xxxxxxxxxxxxxx66 SQL_Latin1_General_CP1_CI_AS
67 SQLCHAR 0 10 "|" 67 xxxxxxxxxxxxxx67 SQL_Latin1_General_CP1_CI_AS
68 SQLCHAR 0 50 "|" 68 xxxxxxxxxxxxxx68 SQL_Latin1_General_CP1_CI_AS
69 SQLCHAR 0 20 "|" 69 xxxxxxxxxxxxxx69 SQL_Latin1_General_CP1_CI_AS
70 SQLCHAR 0 50 "|" 70 xxxxxxxxxxxxxx70 SQL_Latin1_General_CP1_CI_AS
71 SQLCHAR 0 35 "|" 71 xxxxxxxxxxxxxx71 SQL_Latin1_General_CP1_CI_AS
72 SQLCHAR 0 35 "|" 72 xxxxxxxxxxxxxx72 SQL_Latin1_General_CP1_CI_AS
73 SQLCHAR 0 20 "|" 73 xxxxxxxxxxxxxx73 SQL_Latin1_General_CP1_CI_AS
74 SQLCHAR 0 2 "|" 74 xxxxxxxxxxxxxx74 SQL_Latin1_General_CP1_CI_AS
75 SQLCHAR 0 5 "|" 75 xxxxxxxxxxxxxx75 SQL_Latin1_General_CP1_CI_AS
76 SQLCHAR 0 10 "|" 76 xxxxxxxxxxxxxx76 SQL_Latin1_General_CP1_CI_AS
77 SQLCHAR 0 50 "|" 77 xxxxxxxxxxxxxx77 SQL_Latin1_General_CP1_CI_AS
78 SQLCHAR 0 8 "|" 78 xxxxxxxxxxxxxx78 SQL_Latin1_General_CP1_CI_AS
79 SQLCHAR 0 8 "|" 79 xxxxxxxxxxxxxx79 SQL_Latin1_General_CP1_CI_AS
80 SQLCHAR 0 1 "\r\n" 80 xxxxxxxxxxx80 SQL_Latin1_General_CP1_CI_AS
Хранимая процедура выполняет следующие преобразования для всех полей:
поля char/varchar обрабатываются RTrim(IsNull(FieldName, ''))'d поля datetime обрабатываются Convert(VarChar(8), [DateField], 112)'d
Происходит следующее: пустые поля даты экспортируются как «||», а нулевые поля char/varchar экспортируются как «| |» где пробел — нулевой символ (00). Я хочу, чтобы в файле не было нулевых символов, и я не могу понять, почему BCP преобразует пустое значение в 00. Есть ли способ исправить это поведение?
