Write to .html file with exec master..xp_cmdshell

Question

Below is my small code:

Scenario 1

declare @DataToWrite varchar(8000)

set @DataToWrite='<html><body><h1>This is a Heading</h1></body></html>'

DECLARE @CommandL1 varchar(8000)

SET @CommandL1 = 'echo'+@DataToWrite+'> D:\logs\Logs\HTMLFile.html'

print CommandL1 

exec master..xp_cmdshell @CommandL1

After executing it in the SSMS Query Window below are results:

Results Tab of SSMS:

**< was unexpected at this time.**

Messages Tab of SSMS:

echo<html><body><h1>This is a Heading</h1></body></html>> 
D:\logs\Logs\HTMLFile.html

(2 row(s) affected)

Why it is not allowing me to write this HTML CODE TO .html file ?

Scenario 2

Below code runs fine and generates the HTML file in the desired location:

declare @DataToWrite varchar(8000)
set @DataToWrite='t'
DECLARE @CommandL1 varchar(8000)

SET @CommandL1 = 'echo '+@DataToWrite+'> D:\logs\Logs\HTMLFile.html'

print @CommandL1 

exec master..xp_cmdshell @CommandL1

Note:

Please don't get confused with "This Is a Heading" thing. Stackoverflow editor is converting it.

Welcome to SO! Please read [mcve] and edit your post accordingly — Dev, Mar 14 '20 at 13:50

sacse · Accepted Answer · 2020-03-15T03:33:44.020

there are two issues:

You have missed space between echo and the string variable @DataToWrite.

You can't refer to a variable without @

print CommandL1

it should be

print @CommandL1

You also need to escape (using ^) special characters (tags) in your string to print them in the command line or write those into a file.

try the following:

declare @DataToWrite varchar(8000)
set @DataToWrite='<html><body><h1>This is a Heading</h1></body></html>'
DECLARE @CommandL1 varchar(8000)
SET @CommandL1 = 'echo '+REPLACE(REPLACE(@DataToWrite, '<', '^<'), '>', '^>')+' > D:\logs\Logs\HTMLFile.html'
print @CommandL1 
exec master..xp_cmdshell @CommandL1

Write to .html file with exec master..xp_cmdshell

1 Answers1