BCP with ASCII

People,
can I export data using BCP to ASCII file?
I'm looking for but I did not found...
ThanksYou ought to get character data if you use the -c (must be lower case) BCP switch.

-PatP|||I did a BCP test with -c parameter but It isn't correct. How I discovery the caracter table of the file?

thanks,sql

BCP will delete '\' chars on insert of character data

I've discovered that when using BCP to insert character data, then for any s
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.

BCP Problems with ISO Latin-1 Characters past ASCII 192

I am trying to export data from a table with char and varchar fields
(Non-Unicode) that contains the ISO Latin-1 Characters. The problem exist
with the character past ASCII 192 the characters like , , , etc. My
export statement looks like this as I am using the -c flag.
bcp database.dbo.table out C:\table.txt" -c -t"|" -SMACHINE -Usa -P
But I get the following:
MXICO| |MX|0|1
The data in the table looks like this
MXICO| |MX|0|1
-n (Native) did not seem to work.
-w (Unicode character) did work, geting it out, but I can not put it back
in...
Do I have some sort of code page/collation not set correct?
Thanks
Reeves
Hi
Have you tried -C RAW?
John
"Reeves Smith" <ReevesSmith@.WillTellYouLater.com> wrote in message
news:uXjvY5UVEHA.3988@.tk2msftngp13.phx.gbl...
> I am trying to export data from a table with char and varchar fields
> (Non-Unicode) that contains the ISO Latin-1 Characters. The problem exist
> with the character past ASCII 192 the characters like , , , etc. My
> export statement looks like this as I am using the -c flag.
> bcp database.dbo.table out C:\table.txt" -c -t"|" -SMACHINE -Usa -P
> But I get the following:
> MXICO| |MX|0|1
> The data in the table looks like this
> MXICO| |MX|0|1
> -n (Native) did not seem to work.
> -w (Unicode character) did work, geting it out, but I can not put it back
> in...
> Do I have some sort of code page/collation not set correct?
> Thanks
> Reeves
>
|||Reeves,
We had a similar problem that I believe was fixed when we switched to
sing -C ACP. (RAW may do for you as well, try it out.) The problem is
that the default is -C OEM, which is basically the old DOS character set and
that is trashing some characters for you.
Russell Fields
"Reeves Smith" <ReevesSmith@.WillTellYouLater.com> wrote in message
news:uXjvY5UVEHA.3988@.tk2msftngp13.phx.gbl...
> I am trying to export data from a table with char and varchar fields
> (Non-Unicode) that contains the ISO Latin-1 Characters. The problem exist
> with the character past ASCII 192 the characters like , , , etc. My
> export statement looks like this as I am using the -c flag.
> bcp database.dbo.table out C:\table.txt" -c -t"|" -SMACHINE -Usa -P
> But I get the following:
> MXICO| |MX|0|1
> The data in the table looks like this
> MXICO| |MX|0|1
> -n (Native) did not seem to work.
> -w (Unicode character) did work, geting it out, but I can not put it back
> in...
> Do I have some sort of code page/collation not set correct?
> Thanks
> Reeves
>

BCP generates 0x00 characters

Hello,
I'm using BCP with a .FMT file to generate exports of some tables.
BCP sometimes messes up the file by placing 0x00 characters (ASCII value 00)
in the export. It looks like BCP translates NULL values or empty fields like
this.
Strange enough, this doesn't happen all the time. It will happen every once
in a while (data changes all the time). When it happens, it messes up the
entire file.intGod (intGod@.discussions.microsoft.com) writes:
> I'm using BCP with a .FMT file to generate exports of some tables.
> BCP sometimes messes up the file by placing 0x00 characters (ASCII value
> 00) in the export. It looks like BCP translates NULL values or empty
> fields like this.
> Strange enough, this doesn't happen all the time. It will happen every
> once in a while (data changes all the time). When it happens, it messes
> up the entire file.
My guess the 0x00 characters are there, because they appear in the data.
How does your format file look like?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||"Erland Sommarskog" wrote:
> My guess the 0x00 characters are there, because they appear in the data.
> How does your format file look like?
Dear Erland,
I found the root of the problem but I don't know why BCP behaves like this.
The problem is: sometimes (only sometimes) BCP will treat an empty field
incorrectly and insert a 0x00 character instead of the string. By using a
CASE on that field (case fieldName when '' then ' ' else fieldName), the
problem is solved. Still, it's annoying.
I've never come across this problem but I've never used .fmt files before.
I've always used tab-delimited.|||intGod (intGod@.discussions.microsoft.com) writes:
> I found the root of the problem but I don't know why BCP behaves like
> this.
> The problem is: sometimes (only sometimes) BCP will treat an empty field
> incorrectly and insert a 0x00 character instead of the string. By using a
> CASE on that field (case fieldName when '' then ' ' else fieldName), the
> problem is solved. Still, it's annoying.
> I've never come across this problem but I've never used .fmt files before.
> I've always used tab-delimited.
Since you did not post the format file, I cannot comment much more. But
it appears that you use the queryout option. I have not use queryout myself
much, but I have the impression that it is considered buggy.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

BCP error

Hi,
Env: win2k, mssql 8.00.194, Standard Edition

Please help me to make the following bcp to work.
I want to save RS from store procedure to ascii file, using BCP. The sp creates temporary tables and the final select in sp is exec sp_executesql @.comm.
The following example demonstrates the problem:

declare @.bcpCommand nvarchar(100)
set @.bcpCommand = 'bcp "exec sp_helpdb" queryout "\\kkkk\bcp_dbs.log" -c'
exec master..xp_cmdshell @.bcpCommand

I'm getting the following error:
Password:
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]BCP host-files must contain at least one column
NULL

Thanksdeclare @.bcpCommand nvarchar(100)
set @.bcpCommand = 'bcp "set fmtonly off exec sp_helpdb" queryout "c:\test.txt" -c'
exec master..xp_cmdshell @.bcpCommand|||Damn...who was that (un)masked man??

Very nice...but it's as clear as mud

BOL

Returns only meta data to the client.

Syntax
SET FMTONLY { ON | OFF }

Remarks
No rows are processed or sent to the client as a result of the request when SET FMTONLY is turned ON.

The setting of SET FMTONLY is set at execute or run time and not at parse time.

Permissions
SET FMTONLY permissions default to all users.

Examples
This example changes the SET FMTONLY setting to ON and executes a SELECT statement. The setting causes the statement to return the column information only; no rows of data are returned.

SET FMTONLY ON
GO
USE pubs
GO
SELECT *
FROM pubs.dbo.authors
GO|||Thanks for the replay - it works .

BCP character conversion

I bcped in data into a database with SQL_Latin1_General_Cp1_CI_AS collation. The input data has an embedded character (ascii 174). I did not specify any code page using the -C parm. The data was converted to character (ascii 171). I ran the bcp trying -C1252 and -CRAW and both maintained the correct character. -C437 and -COEM change the character to .
Why did this happen? I thought that data would be converted to correctly without any code page specification.Different code pages map binary values to glyphs (the graphic symbols that humans know and love) differently. One binary value can map to many different glyphs using different code pages.

If BPC doesn't know which code page to use for translation, you get "pot luck", especially for characters that aren't well defined. Typically, you want the code page that created the data. Occaisionally, you want the code page that was intended (or at least used) to view the data. Because of the pot-pouri of mappings supported by the different code pages, the business of getting data from point A to point B has grown yet another potentially "interesting" twist to amuse those of us that do the moving!

-PatP|||Thanks for the quick reply.
BOL states:
"When bulk copying data using native or character format, bcp, by default, converts character data to:

OEM code page characters when exporting data from an instance of Microsoft SQL Server.

ANSI/Microsoft Windows code page characters when importing data into an instance of SQL Server. "

So wouldn't the bcp in use code page 1252 by default. This should be similar to -C1252.|||Well where did the data come from?|||The bcp ran from my workstation with a code page 437 - if that's your question.|||From Books Online topic bcp: "OEM Default code page used by the client. This is the default code page used by bcp if -C is not specified."
From that I suppose that SQL Server interpreted your file as being OEM 437 CP. mojza|||I guess I still don't understand why the character was changed during the bcp. I can view it correctly from my workstation which is 437, but if I bcp using -C437 or without -C(which uses default OEM code page) it gets converted. I think I'm missing something.|||In what editor can you see that character correctly? in ANSI (e.g.Notepad) or in OEM (e.g.Edit)? mojza|||Correctly in notepad or textpad, not correctly in edit. So bcp, running in a command window, is using 437 which changes the character to ?|||Then, in my opinion, your file was created in code page ANSI 1252 (notepad ok) and bcp interprets your file as cp 437 (default client OEM code page). That leads to a loss of some extended characters that are not compatible between these two pages unless you tell sql server to interpret him as 1252 or without any translation (RAW). Check out this Microsoft article. There is a good explanation and excellent examples. mojza

http://support.microsoft.com/default.aspx?scid=kb;en-us;199819|||Thanks for your help. That article definitely helped explain things. I also looked at the nls files for 437 and 1252 and character 174(offset x0178) reflects in the 1252 file and int 437 file.
Again, thanks.