Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Appending a text file with & and ' in the fields and double quots

Posted on 2012-12-27
3
Medium Priority
?
310 Views
Last Modified: 2012-12-30
Hello.

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?
0
Comment
Question by:programmher
3 Comments
 
LVL 9

Expert Comment

by:shorvath
ID: 38725807
sample data?
0
 
LVL 12

Accepted Solution

by:
sachitjain earned 600 total points
ID: 38725931
I guess issue is only with ampersand character and not with single quotes and that could be resolved by replacing each & character with ^&.

Like this:

DECLARE @var1 varchar(4000);
SELECT @var1 = 'echo ' + replace(col1, '&', '^&') + CHAR(9) + replace(col2, '&', '^&')
            + char(9) +  replace(col3, '&', '^&') + ' >>\\pathwhere\fileexists.txt' FROM Table1;
EXEC master..xp_cmdshell @var1;
0
 
LVL 12

Expert Comment

by:Saurabh Bhadauria
ID: 38726779
Optionally you can use SQLCMD ......


Create table  temp (Col1 nvarchar(20),col2 nvarchar(20)) 

insert into temp
select 'Saurabh & bhadauria' , 'Saurabh " bhadauria '

DECLARE @var1 varchar(4000);
SELECT @var1 = 'SqlCMD  -S.   -E   -h    -1  -q " set nocount on select col1 + CHAR(9) + col2  from   test_sa.dbo.temp ; "    -o D:\33.txt'
EXEC master..xp_cmdshell @var1; 

Open in new window


Here I have suppressed headers with  ( -h -1)  ..... and  row-count  summary with  Set NoCount ON ...

This will produced desired result...

look at below link for full details...
http://msdn.microsoft.com/en-us/library/ms165702(v=sql.105).aspx
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

877 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question