Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Kyle Doouss
Yak Posting Veteran
64 Posts |
 Posted - 2011-11-28 : 10:29:12
|
| I have an issue where the txt files the following bcp creates have null charactures when I want them blank because it is messing up an import to our website.How do I go about getting rid of these. In the view some fields are '' as FieldName.exec master..xp_cmdshell 'bcp "SELECT[RecordType],[CustomerGroup],[User],[CustomerNumber],[AlfaDebtors],[CodeAssortment],[Authorisation],[ManualMaintenance],[AdressGroup],[Supplier],[CustomerCodeAlfa],[DeleteFlag],[AdressGroup_Long],[CodeAssortment_Long],[ERPCustomerGroup],[FreeForUse]FROM [HBSLIVE].[dbo].[UDEF_WEB_UPDATE_RASS_ASR]" queryout "D:\WebFiles\Web Update File Out\ASS Update Files\RASS_ASR_ALL.txt" -c -Uusername -Ppassword -SAppserver' |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-11-28 : 10:34:12
|
| use ISNULL(field,'') to make NULLs blank------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
Kyle Doouss
Yak Posting Veteran
64 Posts |
Posted - 2011-11-28 : 10:41:32
|
| Would you put this around the field name in the BCP.The fields which return '' from the view are coming up as null characters?When you open the txt file up it is formated in straight columns not staggered.Sorry I am not explaining myself very well |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-11-28 : 10:45:47
|
quote:
Originally posted by Kyle Doouss
Would you put this around the field name in the BCP.The fields which return '' from the view are coming up as null characters?When you open the txt file up it is formated in straight columns not staggered.Sorry I am not explaining myself very well
hmm...they wont come as null if source field is blank.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
Kyle Doouss
Yak Posting Veteran
64 Posts |
Posted - 2011-11-28 : 10:49:03
|
| I think it must be something to do with the format.If I run my view and copy the data out of sql paste into excel and save as tab delimited it is fine?? |
|
|
|
Kyle Doouss
Yak Posting Veteran
64 Posts |
Posted - 2011-11-28 : 18:23:54
|
| It does seem to be the format. If you open up the file in excel and save it then it is fine.Anyone know what I need to do to get the format right????Glad of any pointers or help |
|
|
|
|
|
|
|
|
Wrong text format using BCP.