This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.
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?
Asked:
1 Solution
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
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
Join & Write a Comment Already a member? Login.
Featured Post
Tackle projects and never again get stuck behind a technical roadblock.
Join Now