2

I have an SQL procedure that calls the command shell to write an SQL dump to a file. Take this redacted file for example:

VALUES(
  @SomeVar
  ,1
)

The procedure calls:

echo VALUES(>> C:\somefile.sql
echo @SomeVar>> C:\somefile.sql
echo ,1>> C:\somefile.sql
echo )>> C:\somefile.sql

This works fine, except for the ,1 line. If you run echo ,1>> C:\somefile.sql in CMD, you will see that C:\somefile.sql only contains ,

My theory is that echo thinks it can accept more than 1 parameter.

If you modify the command to echo ,1blah>> C:\somefile.sql, it works perfectly.

I could modify my procedure to check if the line contains a , followed by a number, not followed by anything, and prepend ^ to the number to escape it. This is a bit of a pain though.

Also, echo 1>> C:\somefile.sql writes Echo is ON

Is there a way to echo a literal string in CMD? Enclosing the string in " " outputs the " marks. Or is there any other solution you can think of?

Danny Beckett
  • 20,529
  • 24
  • 107
  • 134

4 Answers4

2

The problem is that 1 is the file descriptor for the standard output stream in batch files / CMD.EXE. You can work around this by using this line instead:

echo ,1 1>> C:\somefile.sql

Or, but more fragile, just put a space between the 1 and the >> redirection

echo ,1 >> C:\Somefile.sql

Update: If you are really concerned about the trailing whitespace for the above examples, you could also use

>> C:\Somefile.sql echo ,1

I.e. you can also put the redirection in front of the command. Personally, I think it looks a little awkward, but YMMV.

Christian.K
  • 47,778
  • 10
  • 99
  • 143
2

You have a few routes to go here:

  1. Put the redirection at the start of the line:

    > file echo foo

  2. Put the echo in parentheses:

    (echo foo) > file

In the latter case you have to escape closing parentheses as ^), in the former you might have to, depending on how the surrounding code looks like.

Joey
  • 344,408
  • 85
  • 689
  • 683
1

Escape the trailing number.

echo ,^1> C:\Somefile.sql
Ansgar Wiechers
  • 193,178
  • 25
  • 254
  • 328
  • Rephrasing my answer: that is the *only* solution for your problem. – Ansgar Wiechers Sep 04 '12 at 12:17
  • 2
    +1, I had no idea you could escape the 1 to avoid it being treated as the file descriptor. Pretty cool. But it is not the only solution. You can also get the correct output without a trailing space by putting the redirection in front: `>c:\somefile.sql echo ,1`. – dbenham Sep 04 '12 at 14:26
0

You can output a literal string. Any and all arguments to echo are redirected. The problem is you do not have a space between your string and the redirect. 1> and 1>> are special versions of > (pipe output to file, replacing if it exists) and >> (pipe output and append to file, creating it if it does not exist). They redirect the standard output (stdout in C++, Console.Out in C#). 2> and 2>> redirect the standard error output (stderr in C++, Console.Error in C#).

For example:

echo 111 > a.txt
echo 111 1> a.txt

are equivalent.

akton
  • 14,148
  • 3
  • 43
  • 47
  • The problem with this is that it writes `111[space]` to the file – Danny Beckett Sep 04 '12 at 11:11
  • 1
    Is there a particular reason why you are using echo? You can just do `copy con somefilel.sql` then enter your SQL and end it with a control-Z character/F6 (end of file). The latter part might be harder, though. – akton Sep 04 '12 at 11:18
  • This is perfect! No escaping whatsoever required!! I'm pretty sure I can just do `CHAR(26)` to get an EOF. Thanks a lot @akton - I will mark this as the accepted answer as soon as I check out the EOF. – Danny Beckett Sep 04 '12 at 11:25
  • Unfortunately, after converting all of the `echo`s in my procedure to `INSERT INTO #Temp`s and trying `copy con`, I couldn't for the life of me find a way to mirror either the CR/LF or EOF. I tried ASCII codes 10, 13, and 10 & 13 for CR/LF and 4, 26 and probably a couple more for EOF/EOT... nothing worked! :( – Danny Beckett Sep 04 '12 at 15:57
  • @DannyBeckett Can you modify your question to include the contents of our xp_cmdshell script, please? – akton Sep 04 '12 at 22:31
  • Done - it just executes and executes and executes (i.e. still waiting for input) @akton – Danny Beckett Sep 05 '12 at 07:32
  • @DannyBeckett Let's take a step back for a second. Why are we logging the SQL to a file? If you just want to trace the output, why not log the output: http://stackoverflow.com/questions/123781/logging-all-queries-on-a-sql-server-2008-express-database? – akton Sep 05 '12 at 07:40
  • The procedure is to backup all procedures, functions and triggers in a specified schema that match a specified pattern. – Danny Beckett Sep 05 '12 at 08:12
  • Why not export all the schema (see http://forums.asp.net/t/1667081.aspx/1 for an example) then filter that? It sounds like you are doing things the hard way. There is also a powershell script for it at http://www.sqlwebpedia.com/content/powershell-script-export-sql-schema-definition. xp_cmdshell is also a significant security risk and most DBAs disable. – akton Sep 05 '12 at 08:19
  • The schema is enormous - several gigs of data. This is to export procedures from a development environment to a customer's environment. It automatically adds `WITH ENCRYPTION` in the correct places etc. All of the data generated by the procedure is done in < 1 second. The procedure is run as a DBA, therefore there is no risk of xp_cmdshell being disabled. – Danny Beckett Sep 05 '12 at 09:16
  • How about using sp_helptext (http://msdn.microsoft.com/en-us/library/ms176112.aspx) to output the text of each procedure? – akton Sep 05 '12 at 09:25
  • sp_helptext is limited to 255 characters per line. Also, it uses deprecated sys tables. My solution takes the definition directly: `SELECT definition FROM sys.sql_modules WHERE object_id = OBJECT_ID('someschema.someproc')`. However, with either solution, `copy con` still does not work. – Danny Beckett Sep 05 '12 at 09:32
  • Is there a reason why this has to run as a stored procedure? Can you write a short application that connects to SQL, runs the query you provided above and writes the procs out to files? – akton Sep 05 '12 at 09:36
  • I agree that's the best solution. Unfortunately this is a case of "the customer's always right". If I could just get line feeds/end of file working, this would be perfect with `copy con` – Danny Beckett Sep 05 '12 at 10:50
  • @DannyBeckett Well, I've had a good muck around myself and I cannot get it to work. I assumed that the control-Z character is not an EOF character itself but is being interpreted by cmd.exe to end the file and this appears to be correct. The closest I could get was http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=132685 but that said it did not work. I would look at Joey's solution. – akton Sep 05 '12 at 11:58