How to escape Newline in ECHO command with sql

Question

i have a Format file for exporting data from SQL Server to local hdd. I have to create this Format file on the remote machine via SQL command.

Server srv = new Server(conn);    
server.ConnectionContext.ExecuteNonQuery("xp_cmdshell 'ECHO 10.0 1 1       SQLBINARY           8       0       \"\"   1     blob                 \"\">> " + destinationPathFormat + "'");

Without the Newline escaping the Format file is not valid and exporting the data isn't working. The string has to be escaped here: 10.0 [ESCPAPE] 1 [ESCPAPE] 1

Thanks in Advance.

Answer 1

Since echo appends a newline, you can issue multiple echo commands.

server.ConnectionContext.ExecuteNonQuery(
    "xp_cmdshell 'ECHO line1 >> " + destinationPathFormat + "'");
server.ConnectionContext.ExecuteNonQuery(
    "xp_cmdshell 'ECHO line2 >> " + destinationPathFormat + "'");
server.ConnectionContext.ExecuteNonQuery(
    "xp_cmdshell 'ECHO line3 >> " + destinationPathFormat + "'");

You're already using >>, which appends to a file, instead of >, which creates a file.