Appending a text file with & and ' in the fields and double quots
Posted on 2012-12-27
When I execute the below, my rrecords append to the correct file - they all begin and end with double quotes:
DECLARE @var1 varchar(4000);
SELECT @var1 = 'echo "' + col1 + CHAR(9) + col2 + char(9) + col3 + '" >>\\pathwhere\fileexists.txt' FROM Table1 WITH(NOLOCK);
EXEC master..xp_cmdshell @var1;
When I remove the double quotes, none of my records with ampersands and single quotes print - instead, this error is logged: 'fieldname' is not recognized as an internal or external command.
When I restore the double quotes, my fieldnames print but every row begins and ends with double quotes.
My customer can not have the double quote but still needs the data with ampersands and quotes in the tite field to print. (ex Micky & Minnie)
How can I either escape the double quotes or modify my statement so my titles with ampersands and single quotes append to my file?