Transporting tagged data to html file using SQL Server 2005 1

[PraveenSK]

DECLARE @cmd sysname, @var varchar(max)
SET @var = 'Hello world !!'
SET @cmd = 'echo ' + @var + ' > c:\pravin\test.htm'
EXEC master..xp_cmdshell @cmd

when I use above query in sql server 2005, it gives me desired result, but when I use the same query with some changes that I tried to transoport tagged data to one html file like,

DECLARE @cmd sysname, @var varchar(max)
SET @var = '[red]<[/red]Hello world !![red]>[/red]'
SET @cmd = 'echo ' + @var + ' > c:\pravin\test.htm'
EXEC master..xp_cmdshell @cmd

it gives me following error,

output
----------------------------------
> was unexpected at this time.
NULL


Will any body please help asap,

Thanks in advance,

Praveen

 

[gmmastros]

The problem with your method is that you are essentially using DOS commands to write to the file. When you have a > symbol, the DOS shell is interpretting this as a redirection operator.

Instead, you could insert the data in to a (global) temp table and then use BCP to write the contents of the table to a file. Something like this...

[green]-- Create the global temp table[/green]
Create Table ##Output(Data VarChar(8000))

[green]-- Add your data[/green]
Insert Into ##Output Values('<hello world!!>')

[green]-- Save it to a file[/green]
exec master..xp_cmdshell 'bcp ##Output out c:\pravin\test.htm -S -U -P -c '

[green]-- Remov the temp table[/green]
Drop Table ##Output

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[PraveenSK]

Thank u vey much George
It worked for me!
Thanks alot again