BULK INSERT in 2005 does not understand old BCP format file (cretaed in 2000)

I am not able to BULK INSERT data files in SQL 2005 because it is
complaining about the format of the format description file (.fmt) (it says
something about invalid xml character ...) I am aware of the new XML format
definition and obviously by default SQL assumes that the format file
specified in the BULK INSERT is the new XML one. Everything works fine if I
use command line BCP utility which allows me to specify the format of the
format file. How do I specify it with BULK INSERT statement?
//Misha
Misha,
Could you please show the first 5 lines of the format file? Maybe seeing it
would give a clue.
RLF
"Misha" <mishka_mishka@.inbox.ru> wrote in message
news:eeqSdL8dIHA.4476@.TK2MSFTNGP06.phx.gbl...
>I am not able to BULK INSERT data files in SQL 2005 because it is
> complaining about the format of the format description file (.fmt) (it
> says
> something about invalid xml character ...) I am aware of the new XML
> format
> definition and obviously by default SQL assumes that the format file
> specified in the BULK INSERT is the new XML one. Everything works fine if
> I
> use command line BCP utility which allows me to specify the format of the
> format file. How do I specify it with BULK INSERT statement?
> //Misha
>
|||Here are the first 5 lines of my .fmt file:
--cut here--
8.0
26
1 SQLCHAR 0 4 " " 0 Timestamp ""
2 SQLCHAR 0 8000 " " 1 Company ""
--cut here--
The same lines in HEX:
00000000: 38 2E 30 0D 0A 32 36 0D 0A 31 09 53 51 4C 43 48
8.0
26
1SQLCH
00000010: 41 52 09 30 09 34 09 22 07 22 09 30 09 54 69 6D
AR04" "0Tim
00000020: 65 73 74 61 6D 70 09 22 22 0D 0A 32 09 53 51 4C
estamp""
2SQL
00000030: 43 48 41 52 09 30 09 38 30 30 30 09 22 07 22 09
CHAR08000" "
00000040: 31 09 43 6F 6D 70 61 6E 79 09 22 22 0D 0A 33 09
1Company""
3
//Misha
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:%23vOhYw8dIHA.3756@.TK2MSFTNGP06.phx.gbl...
> Misha,
> Could you please show the first 5 lines of the format file? Maybe seeing
> it would give a clue.
> RLF
> "Misha" <mishka_mishka@.inbox.ru> wrote in message
> news:eeqSdL8dIHA.4476@.TK2MSFTNGP06.phx.gbl...
>
|||Misha,
I really do not know the answer. I also get XML errors on "invalid
character", but when I replace your separator character with a "," it all
works. (I tried saving your .fmt file as both ANSI and UNICODE. I get
different errors, but I still get errors.)
It makes me wonder if the format file, when being read is turned into
internal XML and the XML does not allow your separator character (which is
0x07) to exist.
Can you readily change to another separator character?
FWIW,
RLF
"Misha" <mishka_mishka@.inbox.ru> wrote in message
news:%23VY38aEeIHA.5984@.TK2MSFTNGP06.phx.gbl...
> Here are the first 5 lines of my .fmt file:
> --cut here--
> 8.0
> 26
> 1 SQLCHAR 0 4 " " 0 Timestamp ""
> 2 SQLCHAR 0 8000 " " 1 Company ""
> --cut here--
> The same lines in HEX:
> 00000000: 38 2E 30 0D 0A 32 36 0D 0A 31 09 53 51 4C 43 48 8.0
> 26
> 1 SQLCH
> 00000010: 41 52 09 30 09 34 09 22 07 22 09 30 09 54 69 6D AR 0 4 " " 0
> Tim
> 00000020: 65 73 74 61 6D 70 09 22 22 0D 0A 32 09 53 51 4C estamp ""
> 2 SQL
> 00000030: 43 48 41 52 09 30 09 38 30 30 30 09 22 07 22 09 CHAR 0 8000
> " " 00000040: 31 09 43 6F 6D 70 61 6E 79 09 22 22 0D 0A 33 09 1 Company
> ""
> 3
> //Misha
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:%23vOhYw8dIHA.3756@.TK2MSFTNGP06.phx.gbl...
>
|||Thanks a lot for your help Russell,
I have switched the application onto the new XML format file format and it
works fine. Obviously MS has a bug in SQL 2005 with the old fmt files.
//Misha
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:%23%23jgRkJeIHA.288@.TK2MSFTNGP02.phx.gbl...
> Misha,
> I really do not know the answer. I also get XML errors on "invalid
> character", but when I replace your separator character with a "," it all
> works. (I tried saving your .fmt file as both ANSI and UNICODE. I get
> different errors, but I still get errors.)
> It makes me wonder if the format file, when being read is turned into
> internal XML and the XML does not allow your separator character (which is
> 0x07) to exist.
> Can you readily change to another separator character?
> FWIW,
> RLF
>
> "Misha" <mishka_mishka@.inbox.ru> wrote in message
> news:%23VY38aEeIHA.5984@.TK2MSFTNGP06.phx.gbl...
>