2012年3月27日星期二
BCP will delete '\' chars on insert of character data
equence '\' + <newline> (backslash + ASCII 13), the '\' character is strippe
d. For example, try inserting this:
"Hello\\n"
and you'll wind up with
"Hello\n"
Can I turn this behavior off, or is this a known bug in BCP?
I'm using BCP with the SQL Server 7 ODBC driver.
Thanks,
Jim FloodJim Flood wrote:
> I've discovered that when using BCP to insert character data, then for any
sequence '' + <newline> (backslash + ASCII 13), the '' character is strip
ped. For example, try inserting this:
> "Hello\\n"
> and you'll wind up with
> "Hello\n"
> Can I turn this behavior off, or is this a known bug in BCP?
> I'm using BCP with the SQL Server 7 ODBC driver.
> Thanks,
> Jim Flood|||Can you verify the version number of the ODBC driver? You can do this be
running the ODBC Administrator and looking for the Version for the "SQL
Server" driver on the Drivers tab. Or, you can check the version of
sqlsrv32.dll in your system32 directory using Explorer.
We tried this with an 8.0 ODBC Driver and it does not repro. Before I go
setup a machine with SQL 7.0 on it, I want to verify your version number, to
make sure I get the same version.
Brannon Jones
Developer - MDAC
This posting is provided "as is" with no warranties and confers no rights.
"Jim Flood" <anonymous@.discussions.microsoft.com> wrote in message
news:DA87D286-F669-4F74-A5E5-82A9CBDEE5BE@.microsoft.com...
> I've discovered that when using BCP to insert character data, then for any
sequence '' + <newline> (backslash + ASCII 13), the '' character is
stripped. For example, try inserting this:
> "Hello\\n"
> and you'll wind up with
> "Hello\n"
> Can I turn this behavior off, or is this a known bug in BCP?
> I'm using BCP with the SQL Server 7 ODBC driver.
> Thanks,
> Jim Flood|||The version number is 2000.81.9042.0. The sequence is backslash character, f
ollowed by newline character. Backslash followed by any other character does
not cause a problem.
2012年3月20日星期二
BCP out, extended chars when field is null (sometimes)
row terminator.
Now for whatever reason, when I look at the exported text file some rows
have extended characters. The extended characters appear in fields that
happen to be null. The kicker is for the same column some rows with nulls
come through just fine, and a few rows (same column) come in with the
extended characters.
I am aware of the issue of there being no way to represent a null in the bcp
generated text data file. It is ok, if the source is a null and then it
comes back in as an empy string. I am ok with this.
In a sense, it sort of sounds like data corruption, but the field value is
null. The extended chars appear between the delimiters. So how does NULL
turn into extended characters.
Thanks.
I just ran into this too.
I created a little C# console guy that opens the text file and strips out
the null chars that were in my file.
you run it from a command line passing in the source file name and an output
file name.
FileScrubber.exe IckyFile.txt Cleanfile.txt
I've attached the cs file for the Class if you can use it great.
(i'm no C# Guru so please be kind with the review)
Greg Jackson
PDX, Oregon
begin 666 Class1.cs
M=7-I;F<@.4WES=&5M.PT*=7-I;F<@.4WES=&5M+DE/.PT*#0IN86UE<W!A8V4@.
M1FEL95-C<G5B8F5R#0I[#0H)+R\O(#QS=6UM87)Y/@.T*"2\O+R!3=6UM87)Y
M(&1E<V-R:7!T:6]N(&9O<B!#;&%S<S$N#0H)+R\O(#PO<W5M;6%R>3X-"@.EC
M;&%S<R!&:6QE4V-R=6)B97(-"@.E[#0H)"2\O+R \<W5M;6%R>3X-"@.D)+R\O
M(%1H92!M86EN(&5N=')Y('!O:6YT(&9O<B!T:&4@.87!P;&EC8 71I;VXN#0H)
M"2\O+R \+W-U;6UA<GD^#0H)"5M35$%4:')E861=#0H)"7-T871I8R!V;VED
M($UA:6XH<W1R:6YG6UT@.87)G<RD-"@.D)>PT*"0D)<W1R:6YG('-);G!U=$9I
M;&4@./2!A<F=S6S!=.PT*"0D)<W1R:6YG('-/=71P=71&:6QE(#T@.87)G<ULQ
M73L-"@.D)"6EN="!I;G1">71E.PT*"0D)8GET92!B=$)Y=&4[#0 H-"@.D)"49I
M;&53=')E86T@.<W1R;4]U='!U=" ](&YU;&P[#0H)"0E&:6QE4W1R96%M('-T
M<FU);G!U=" ](&YU;&P[#0H-"@.D)"71R>0T*"0D)>PT*"0D)"49I;&5);F9O
M(&]B:DEN1FEL92 ](&YE=R!&:6QE26YF;RAS26YP=71&:6QE*3L-"@.D)"0E&
M:6QE26YF;R!O8FI/=71&:6QE(#T@.;F5W($9I;&5);F9O*'-/=71P=71&:6QE
M*3L-"@.T*"0D)"7-T<FU);G!U=" ](&]B:DEN1FEL92Y/<&5N4F5A9"@.I.PT*
M"0D)"7-T<FU/=71P=70@./2!O8FI/=71&:6QE+D]P96Y7<FET92@.I.PT*#0H)
M"0D)9F]R*&EN="!I(#T@.,#MI/'-T<FU);G!U="Y,96YG=&@.[:2LK*0T*"0D)
M"7L-"@.D)"0D):6YT0GET92 ]('-T<FU);G!U="Y296%D0GET92@.I.PT*"0D)
M"0EB=$)Y=&4@./2 H8GET92EI;G1">71E.PT*#0H)"0D)"2\O:68@.:70@.:7,@.
M82!N=6QL(&-H87)A8W1E<BP@.=V4@.9V]T=&$@.<VMI<"!I="XN+BY'04H-"@.D)
M"0D):68H8G1">71E(#X@.,"D-"@.D)"0D)>PT*"0D)"0D)<W1R;4]U='!U="Y7
M<FET94)Y=&4H8G1">71E*3L-"@.D)"0D)?0T*"0D)"7T-"@.D)"7T-"@.D)"6-A
M=&-H*%-Y<W1E;2Y%>&-E<'1I;VX@.97AP*0T*"0D)>PT*"0D)"4-O;G-O;&4N
M5W)I=&5,:6YE*")%<G(@.(B K(&5X<"Y-97-S86=E*3L-"@.D)"7T-"@.D)"69I
M;F%L;'D-"@.D)"7L-"@.D)"0ES=')M3W5T<'5T+D-L;W-E*"D[#0H)"0D)<W1R
M;4EN<'5T+D-L;W-E*"D[#0H)"0E]#0H-"@.D)"4-O;G-O;&4N5W)I=&5,:6YE
M*")0<F]C97-S960@.26YP=70Z("(@.*R!S26YP=71&:6QE*3L-"@.D)"4-O;G-O
M;&4N5W)I=&5,:6YE*")'96YE<F%T960@.3W5T<'5T.B B("L@.<T]U='!U=$9I
M;&4I.PT*"0D)0V]N<V]L92Y7<FET94QI;F4H(B(I.PT*"0D)0V]N<V]L92Y7
D<FET94QI;F4H(D=I9&1Y(%5P(2(I.PT*"0E]#0H)?0T*?0T*
`
end
2012年2月25日星期六
BCP command with arguments having special chars
The command is as follows:
BCP asdos+_reports..StageCONTACTINFO OUT C:\16E8.tmp -w -b10000 -t"|" -r\n
-Sblrkec28791d\sqlserver -Uadmin -Padmin -eC:\ERRCONTACTINFO.txt
Here, since the first argument(database name) has a special char '+' in
it, the command gives a syntax error. How can i escape it? Double quotes"",
[] didnt work!! Pls help!
TIAYour thread in microsoft.public.sqlserver.programming on this subject is
still active. See my response.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Sundar" <sundar.jammy@.gmail.com> wrote in message
news:b4d47b8e.0411040547.7c8ed064@.posting.google.c om...
> Hi all,
> The command is as follows:
> BCP asdos+_reports..StageCONTACTINFO OUT C:\16E8.tmp -w -b10000 -t"|" -r\n
> -Sblrkec28791d\sqlserver -Uadmin -Padmin -eC:\ERRCONTACTINFO.txt
> Here, since the first argument(database name) has a special char '+' in
> it, the command gives a syntax error. How can i escape it? Double
> quotes"",
> [] didnt work!! Pls help!
> TIA