Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SQL BCP exporting to ASCII

Posted on 2014-07-25
3
Medium Priority
?
525 Views
Last Modified: 2014-07-25
Hi

I am trying to export data from an SQL table using BCP using the script below:

select @sql = 'bcp "exec bloc700dummy.dbo.reptoolGetCustomers @Salesman = MS" queryout "C:\Reptool\CustomerMS.txt" -T -c -S'

The resultant datafile is then used as data for a dropdown within a VB.Net application. I have an issue where if a data column within the SQL table contains nothing, it is exported as what apperas to be 0X0 which I believe is HEX for a space. The problem I have is that when I try to concatenate fields of data together (address details), as soon as an empty field is encountered, any data after that field is lost!

Can anyone advise if there is a different switch combination I can use within BCP to stop this happening or can I code around this within my application?

Many thanks

Steve
0
Comment
Question by:abmcsltd
  • 2
3 Comments
 
LVL 36

Expert Comment

by:ste5an
ID: 40219430
Nope. 0x0 is NUL. This has an undefined meaning in the context of an ASCII file. It has not character representation. E.g. in many languages the byte 0x0 indicates the end of a string. Thus your "data loss".

Use COALESCE(yourColumn, '') to return non-null, but empty values.
0
 

Author Comment

by:abmcsltd
ID: 40219449
Hi

Where do I use this....is it part of the BCP script?

Thanks

Steve
0
 
LVL 36

Accepted Solution

by:
ste5an earned 2000 total points
ID: 40219452
Well, no. You need either to adjust the procedure or embed in a SQL script used for BCP.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question