Problem with newline character in SP. 1

[stapet]

SET @UploadDir = ‘\\MyServer\data\’
SET @UploadFileName = ‘test.txt’

SET @UploadData = ' echo ' + @HoldRecNo + ',' + @Name + ',' + @Terms + ',' + @Description + ',' + CONVERT(char(8), @DueDate,1) + ',' + CONVERT(char(8), @PayDate,1) + ',' + CONVERT(varchar(15), @Amount) + ',' + @InvoiceNo + ',' + CONVERT(char(8), @InvoiceDate, 1) + ',' + CONVERT(varchar(15), @Amount) + ',' + ‘ >> ' + @UploadDir + @UploadFileName
EXEC master..xp_cmdshell @UploadData



I am having a problem writing data to a text file, via a stored procedure. The problem is the variable @Name contains: ‘C&W Store’.
Once it hits the & sign, it treats it like a new line or return character. I thought about converting the & sign, but the problem is this text
file is going to be uploaded by another program and is uploading by @Name, so if I convert the & sign, the data will never be
uploaded because it won’t be able to find the matching name. Does anyone have any ideas on getting around this? Any help would be
greatly appreciated.

 

[NathanGriffiths]

If the program you are using to upload the data can handle text qualifiers, then I would use them on your text column data in your output.

e.g. use double quote text qualifiers:

SET @UploadData = ' echo ' + @HoldRecNo + ',[blue]"[/blue]' + @Name + '[blue]"[/blue],' + ...

This will stop the echo command from interpreting the & as a new line, as the command will read as;

echo <holdrecno>,"C&W Store",<name> ...

Wouldn't a DTS package be better for this though?


Nathan

----------------------------------------
Want to get a good response to your question? Read this FAQ! -> faq183-874
----------------------------------------

 

[stapet]

Thanks BlueStringPudding! It worked great! I'm not really sure if a DTS package would work or not. I haven't worked with them enough, but I will look in to it. I appreciate the suggestion.