3

I have a powershell script that will run a query and export the results to Excel. I want to hook this into SQL Studio Management Studio (2008)'s external tools. SSMS will not allow powershell scripts so I am using a batch file to get it started.

In the external tools section you can specify some predefined arguments. The argument that I want is called "current text". This argument passes whatever is highlighted to the tool (batch file). My batch file then passes that argument on to a powershell script.

The problem is that if the user has a query that spans multiple lines highlighted then the powershell script fails because of the linebreaks. It seems it would be relatively easy to strip them out, or better yet, replace them with a space?

Here is my batch file:

echo %~1
call powershell ^& 'c:\temp\ExportSQL.ps1' -query "%~1"

My question is how can I replace newlines and carriage returns from %1 with a space before passing it to the powershell script? TIA.

Malk
  • 11,107
  • 4
  • 31
  • 32

1 Answers1

2

First, I can't believe that you have really linefeeds in your parameter %1, as it is possible, but a bit complex to achieve this.
To control this you could use

echo on
rem # %1 #

To handle CR and linefeed characters you have to use the delayed expansion, as percent expansion of such a content is not possible.
With percent expansion CR's are always removed, linefeeds removes the rest of the line or in a block context they append a new command line.

So it's not possible to handle content with linefeeds in a parameter like %1 ..%9.
You need it stored in a variable.

Assuming you have content with newlines in a variable, you could replace it with spaces.
The empty lines in this example are important for the result. 

@echo off
set text=This contains a ^

newline
setlocal EnableDelayedExpansion
set ^"result=!text:^

= !"
echo Content of text is   "!text!"
echo ----
echo Content of result is "!result!"

--- Output --- 

Content of text is   "This contains a
newline"
----
Content of result is "This contains a  newline"
jeb
  • 70,992
  • 15
  • 159
  • 202
  • Thank you for your help. You were correct to question my having linefeeds in %1. The query I was using to test was actually when highlighting 2 similar select statements, and I was seeing the echo command followed by the actual echo which my brain processed as echoing both statements... doh. In reality it was just echoing the first line. Thank you for taking the time to answer this question. Your solution works nicely given what you had to work with! – Malk Apr 06 '11 at 16:56
  • 1
    Thanks for this. Your technique of escaping a newline gave me the prod I need to solve my problem: http://stackoverflow.com/a/9626257/412335 – kybernetikos Mar 08 '12 at 22:53
  • Yes, the FOR/F token splitting is one of the useful things you can do with a `` [How dos-batch newline variable hack works](http://stackoverflow.com/a/6379861/463115) – jeb Mar 09 '12 at 09:28