-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO
Then just copy in this stored procedure. There is test code included to show you how to run it.
-- Test using this:
-- EXEC DD_AppendToFile '<ExchangeRate>','D:\test\test.xml'
-- Drop stored procedure if it already exists
IF EXISTS (
SELECT *
FROM INFORMATION_SCHEMA.ROUTINES
WHERE SPECIFIC_SCHEMA = N'dbo'
AND SPECIFIC_NAME = N'DD_AppendToFile'
)
DROP PROCEDURE dbo.DD_AppendToFile
GO
create procedure DD_AppendToFile
@vchrText varchar(max),
@vchrFileName varchar(255),
@EOL varchar(10) = NULL
as
begin
if @EOL is NULL begin
set @EOL = char(13) + char(10);
end;
-- the period allows for empty lines
declare @prefix varchar(255) = 'echo.';
-- '>>' will append, use '>' to replace
declare @suffix varchar(255) = ' >> ' + @vchrFileName;
-- Escape special characters so things work
-- But escapes work funny when in double quotes (and maybe single quotes too)
set @vchrText = (case when charindex('"', @vchrText) = 0
then replace(replace(replace(@vchrText, '|', '^|'), '>', '^>'), '&', '^&')
else @vchrText
end);
set @vchrText = replace(@vchrText,'<','^<')
while (@vchrText <> '') begin
declare @pos int = charindex(@EOL, @vchrText);
declare @line varchar(8000) = (case when @pos > 0 then left(@vchrText, @pos) else @vchrText end);
set @vchrText = (case when @pos > 0 then substring(@vchrText, @pos+2, 1000000) else '' end);
set @line = @prefix + @line + @suffix;
--write @line to file;
exec xp_cmdshell @line;
end;
end;