Special Characters in parameters (i.e. $)

[nduckste]

I'm trying to automate the collection of perfmon counters for SQL Server. I have this all working with cmd files but want to take it to the next level with Powershell. This is all open source so I'm happy to share the tools with anybody.

The problem I'm running into is with named instances of SQL Server because they contain a '$' in the instance name.

So the following works fine for a default instance of SQL Server. "SQLServer" is always the instance name for the default instance.

typeperf -q "SQLServer:Access Methods" -s localhost

However, named instances of perfmon counters have a different format as follows.

MSSQL$<named instance>

My named instance is call R2, so the typeperf command needs to be the following.

typeperf -q "MSSQL$R2:Access Methods" -s localhost

This command works fine from the CMD line. However, it generates an error in powershell because the '$' indicates the text following should be translated as a variable. Providing a backtick in front of the '$' solves the problem:

typeperf -q "MSSQL`$R2:Access Methods" -s localhost

However, what I really want to do is store the SQL instance name in an xml config file. Storing backticks in the file represents a couple problems:

-I can't really expect users to know when to put back ticks in a variable when they install and configure these utilities.
-I use this value as part of the naming convention for a lot of other things, i.e. files on disk, data collector names, etc. Having a backtick in all those places complicates things.


I can get this to work by including just one backtick in my config value and using Invoke-Expression as follows. But the nature of creating a dynamic command with all the extra quotes, backticks, etc, and joining it with the necessary findstr command makes this really cumbersome when I have hundreds of typeperf/findstr combinations.

$SQLCounter = $SQLInstance + ":Access Methods"
$tp = "typeperf -q `"$SQLCounter`" -s `"$Server`" "
Invoke-Expression -Command $tp | findstr /I /C:"Forwarded Records/sec" | Out-File -FilePath $tempCounterFile -Append


Ideally, I'd like this all in one line:

typeperf -q "$SQLCounter" -s "$Server" | findstr /I /C:"Forwarded Records/sec" | Out-File -FilePath $tempCounterFile -Append

Thanks for your help in advance.

Nick

 

[mrmovie]

sorry, i really dont know what i am talking about but just want to get in on this thread, the type of issue you are having in powershell confuses me.
i read about the different between " and ' and got a bit confused.
i have been using Invoke-Command pretty successfully like vbscripts Execute() method, but this is more for building the code dynamically

have you tried

typeperf -q 'MSSQL$R2:Access Methods' -s localhost

or

typeperf -q "'MSSQL$R2:Access Methods'" -s localhost

 

[GrimR]

I'm no powershell expert but can you not create separate .psm1 files [PowerShell Modules] and import them when needed.

As mrmovie said single quotes should sort out the variable issue.


MCITP:EA/SA, MCSE, MCSA, MCDBA, MCTS, MCP+I, MCP