Creating a multi-line text file

[TiltingCode]

I want to create a text file of a stored procedure. Here is some code:

exec master..xp_cmdshell 'echo USE MyDB > c:\file.txt'
exec master..xp_cmdshell 'echo SET ANSI_NULLS ON  >> c:\file.txt'
exec master..xp_cmdshell 'echo GO  >> c:\file.txt'
exec master..xp_cmdshell 'echo SET QUOTED_IDENTIFIER ON  >> c:\file.txt'
exec master..xp_cmdshell 'echo GO >> c:\file.txt'

This works but I don't think I should have to keep appending to the file. I tried this:

Declare @SQLQuery varchar(3000)

Set @SQLQuery = 'echo USE [FSAI-DEV] ' + CHAR(13) + CHAR(10) ;
Set @SQLQuery = @SQLQuery + 'SET ANSI_NULLS ON ' + CHAR(13) + CHAR(10);
Set @SQLQuery = @SQLQuery + 'SET QUOTED_IDENTIFIER ON ' + CHAR(13) + CHAR(10);
Set @SQLQuery = @SQLQuery + 'GO ' + CHAR(13) + CHAR(10);

exec master..xp_cmdshell 'echo ' + @SQLQuery + ' > c:\file.txt'

This creates a Msg 102, Level 15, State 1, Line 9 Incorrect syntax near '+'. error.

I've tried other variations using the carriage return and newline but nothing works. Can some please help?

 

[gmmastros]

Test Post

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

Add another line where you concatenate so you simply have a variable with no concatenation on the last line.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom