Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

BCP produces garbled output Expand / Collapse
Author
Message
Posted Monday, January 21, 2013 8:20 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 9:47 AM
Points: 159, Visits: 688
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,
Post #1409578
Posted Monday, January 21, 2013 8:35 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 9:47 AM
Points: 159, Visits: 688
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.
Post #1409584
Posted Monday, January 21, 2013 8:46 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 9:47 AM
Points: 159, Visits: 688
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.

Post #1409594
Posted Monday, January 21, 2013 9:48 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 9:47 AM
Points: 159, Visits: 688
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!

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,
Post #1409623
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse