SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


BCP produces garbled output


BCP produces garbled output

Author
Message
cafescott
cafescott
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1048 Visits: 733
Hello,

I have created a stored procedure that produces output for specific tables in our database. The output for one of the files is garbled. I have to have it fixed.

The strategy I'm employing is to first create a view that contains a select statement that chooses all the fields in the table; i.e., select field1, field2, field3, etc. Then my routine calls BCP by selecting from the view. My command resembles this:

bcp "select * from Database.OurSchema.BCP_View" queryout "c:\temp\bcp\MyTable.CSV" -SSQLServerName -t"|" -r"" -T -c

Note: the "r" parameter is actually: -r Quote Backslash n Quote


I have been painstakingly adding one field at a time to the view until the output becomes garbled. When it does, I skip that field and try to add the one following. The same result occurs; the output is garbled.

This suggests that there is a limit to the number of fields BCP can be used for. Add one more to the limit and you get junk.

This table has a lot of null data. However, It is fine with null data up until it reaches the point where the data becomes garbled. Also, there is no unusual characters in string fields of this table at all.

I've been searching for an option to fix this matter without success. Does anyone have an idea how to use BCP to produce non-garbled output for a table with many fields?

thanks,
cafescott
cafescott
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1048 Visits: 733
Update: I can add additional, non-null fields after the point where the data is garbled. This indicates that it isn't a field number limit problem.

BCP just doesn't seem to like a group of four null fields that show up fine (i.e., as "NULL") in SSMS.
cafescott
cafescott
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1048 Visits: 733
The fields where BCP begins to fail are numeric, with null values. I have found if I change the view to use "IsNull", that BCP works fine with it. In other words, select Field1, Field2...., IsNull(Field20, 0.0) as Field20, ...

I don't know why BCP doesn't like null values in numeric fields. However, it seems that I have a work-around to this matter.
cafescott
cafescott
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1048 Visits: 733
Update: I am now using Isnull for all data of type numeric and money. I'm still getting garbled output, which begins when I add to my select list a particular field of type char (which is NULL in my table).

However, if I comment out a bunch of fields early in the select list and retain that particular field of type char, the output looks fine! w00t

So apparently BCP does have a maximum length per line issue. Does anyone have an idea how to tell BCP to accept a real wide line of output?

thanks,
bsadowsky
bsadowsky
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 2
Thanks! This saved me a load of grief
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)

Group: General Forum Members
Points: 217691 Visits: 41995
Thanks for bringing this back up. I've always meant to ask what the OP meant by "garbled" and how many characters the limit seemed to be and what was being used to determine that the resulting file was actually "garbled", mostly because I've never experienced such problems with it. I've very successfully exported more than 135 columns and more than 1,200 characters wide with it to be used for telephone ordering and billing and that was almost 2 decades ago and continue to do so.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
bsadowsky
bsadowsky
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 2
In my case, it had to do with the number of columns with NULL values. More than 4 seemed to cause the issue, not so much the number of columns (my table had 82)
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)

Group: General Forum Members
Points: 217691 Visits: 41995
bsadowsky (4/27/2016)
In my case, it had to do with the number of columns with NULL values. More than 4 seemed to cause the issue, not so much the number of columns (my table had 82)


Thanks for the feedback on that. I've got to give that a try and find out what's up on that.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search