Appending a text file with & and ' in the fields and double quots
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.t
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?
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.t
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?
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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.t
EXEC master..xp_cmdshell @var1;
Like this:
DECLARE @var1 varchar(4000);
SELECT @var1 = 'echo ' + replace(col1, '&', '^&') + CHAR(9) + replace(col2, '&', '^&')
+ char(9) + replace(col3, '&', '^&') + ' >>\\pathwhere\fileexists.t
EXEC master..xp_cmdshell @var1;
Optionally you can use SQLCMD ......
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
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;
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
Premium Content
You need an Expert Office subscription to comment.Start Free Trial