Tag: Powershell

Printing/Escaping a variable name in Powershell

In Powershell, if you include a variable name (like $PROFILE) in a statement that writes to the screen, it will be replaced with the contents of the variable:

PowerShell 7.4.1
PS C:\Users\Ryan> Write-Output "this is my profile: $PROFILE"
this is my profile: C:\Users\Ryan\OneDrive\Documents\PowerShell\Microsoft.PowerShell_profile.ps1

But what if you want to actually out the name of your variable instead of the contents? Like if you’re displaying an example statement to execute? In that case, you can escape it by using the backtick character (“`” – the backwards single-quote on in the upper left of your keyboard on the same key as the “~” tilde). In that case, your result will look like this:

PowerShell 7.4.1
PS C:\Users\Ryan> Write-Output "this is my profile: `$PROFILE"
this is my profile: $PROFILE

In my case, I was trying to print some instructions to the screen that included the $PROFILE, so that was the workaround.

Run Windows Troubleshooting Wizards manually (from the command line)

There are some great resources online that show you how to manually run various Windows troubleshooters from the command line (a great summary, or the details from Technet), but they didn’t include some of the newer items in Windows 10, including the Windows Store Apps Troubleshooter.

To run these manually, you can use the following commands (in some cases, you’ll be prompted to elevate to admin rights if you’re not already running CMD or Powershell as an admin):

Bluetooth:

%systemroot%\system32\msdt.exe -id  BluetoothDiagnostic

Keyboard:

%systemroot%\system32\msdt.exe -id  KeyboardDiagnostic

Speech:

%systemroot%\system32\msdt.exe -id  SpeechDiagnosticCalibrate

Video Playback:

 %systemroot%\system32\msdt.exe -id  VideoPlaybackDiagnostic

Windows Store Apps:

%systemroot%\system32\msdt.exe -id AppsDiagnostic

Additionally, here are the Troubleshooting packages listed at the Technet site in case that’s unavailable in the future:

Aero effects, like transparency:

%systemroot%\system32\msdt.exe -id  AeroDiagnostic

Troubleshoots problems connecting to a workplace network over the Internet using Direct Access:

%systemroot%\system32\msdt.exe -id  NetworkDiagnosticsDA

Troubleshoots problems using hardware and access devices connected to the computer:

%systemroot%\system32\msdt.exe -id  DeviceDiagnostic

Troubleshoots problems viewing computers or shared files in a homegroup:

%systemroot%\system32\msdt.exe -id  HomeGroupDiagnostic

Troubleshoots problems with allowing other computers to communicate with the target computer through Windows Firewall:

%systemroot%\system32\msdt.exe -id  NetworkDiagnosticsInbound

Troubleshoots problems connecting to the Internet or to a specific Web site:

%systemroot%\system32\msdt.exe -id  NetworkDiagnosticsWeb

Helps the user prevent add-on problems and optimize temporary files and connections:

%systemroot%\system32\msdt.exe -id  IEDiagnostic

Helps the user prevent malware, pop-ups, and online attacks:

%systemroot%\system32\msdt.exe -id  IESecurityDiagnostic

Troubleshoots problems with Ethernet, wireless, or other network adapters:

%systemroot%\system32\msdt.exe -id  NetworkDiagnosticsNetworkAdapter

Helps the user adjust settings to improve operating system speed and performance:

%systemroot%\system32\msdt.exe -id   PerformanceDiagnostic

Troubleshoots problems playing sounds and other audio files:

%systemroot%\system32\msdt.exe -id  AudioPlaybackDiagnostic

Helps the user adjust power settings to improve battery life and reduce power consumption:

%systemroot%\system32\msdt.exe -id  PowerDiagnostic

Troubleshoots problems printing:

%systemroot%\system32\msdt.exe -id  PrinterDiagnostic

Helps the user configure older programs so that they can run in the current version of Windows:

%systemroot%\system32\msdt.exe -id  PCWDiagnostic

Troubleshoots problems recording audio from a microphone or other input source:

%systemroot%\system32\msdt.exe -id  AudioRecordingDiagnostic

Troubleshoots problems with search and indexing using Windows Search:

%systemroot%\system32\msdt.exe -id  SearchDiagnostic

Troubleshoots problems accessing shared files and folders on other computers over the network:

%systemroot%\system32\msdt.exe -id  NetworkDiagnosticsFileShare

Helps the user clean up unused files and shortcuts and perform other maintenance tasks:

%systemroot%\system32\msdt.exe -id  MaintenanceDiagnostic

Troubleshoots problems playing a DVD using Windows Media Player:

%systemroot%\system32\msdt.exe -id  WindowsMediaPlayerDVDDiagnostic

Troubleshoots problems with adding media files to the Windows Media Player library:

%systemroot%\system32\msdt.exe -id  WindowsMediaPlayerLibraryDiagnostic

Helps the user reset Windows Media Player settings to the default configuration:

%systemroot%\system32\msdt.exe -id  WindowsMediaPlayerConfigurationDiagnostic

Troubleshoots problems that prevent Windows Update from performing update tasks:

%systemroot%\system32\msdt.exe -id  WindowsUpdateDiagnostic

To get a full list of Troubleshooters currently installed on your system, you can run the following command and then select “Additional Troubleshooters” if necessary:

%systemroot%\system32\control.exe /name Microsoft.Troubleshooting

Unexpected results from ISNULL in SQL Compact 3.5

In SQL Server, the ISNULL function takes two arguments – the first is a potentially NULL value, and the second is another potentially NULL value. If the first value is NULL, the function returns the second value instead. It’s always been this way – for example:

  SELECT V1, V2,
         ISNULL(V1, V2) as [Result],
         Descr
  FROM (SELECT 10 as V1, 20 as V2, 'Returns first' as Descr UNION
        SELECT 10 as V1, NULL as V2, 'Returns first' as Descr UNION
        SELECT NULL as V1, 20 as V2, 'Returns second' as Descr UNION
        SELECT NULL as V1, NULL as V2, 'Both NULL - return NULL' as Descr) t

In SQL Server, you get the following results:

V1      V2      Result  Descr
10      20      10      Returns first
10      NULL    10      Returns first
NULL    20      20      Returns second
NULL    NULL    NULL    Both NULL - return NULL

However, SQL Compact implements ISNULL differently, in a way that’s not compatible at all – instead, the ISNULL function returns a 0 or 1 based on whether the first parameter is NULL! It doesn’t even process the second parameter, but instead of failing with an “Invalid Syntax” error, it happily accepts it, executes, and returns unexpected results. In SQL Compact, you get the following result set:

V1      V2      Result  Descr
10      20      0       Returns first
10      NULL    0       Returns first
NULL    20      1       Returns second
NULL    NULL    1       Both NULL - return NULL

Notice the difference? The ISNULL column is returning the value based only on the first parameter (a “0” if it’s not NULL, and a “1” if it is), and if you’re not expecting it, it silently mis-calculates any formulas containing that function. If you’re relying on the function in any statements that add values together or make decisions based on the results, you’re in for some very expected behavior (in my case, I noticed totals that were wildly incorrect).

As an alternative, you should be using the COALESCE – it accepts multiple parameters and it walks down the list until it finds one that’s not null, returning a NULL value if all parameters are NULL. This function can be used in place of ISNULL in SQL Server code with exactly the same behavior (i.e. no changes), and as a bonus, it acts the same in SQL Compact, so your formulas will work properly there as well. The example above becomes:

  SELECT V1, V2,
         COALESCE(V1, V2) as [Result],
         Descr
  FROM (SELECT 10 as V1, 20 as V2, 'Returns first' as Descr UNION
        SELECT 10 as V1, NULL as V2, 'Returns first' as Descr UNION
        SELECT NULL as V1, 20 as V2, 'Returns second' as Descr UNION
        SELECT NULL as V1, NULL as V2, 'Both NULL - return NULL' as Descr) t

And on both SQL Server and SQL Compact, the result set is the same:

V1      V2      Result  Descr
10      20      10      Returns first
10      NULL    10      Returns first
NULL    20      20      Returns second
NULL    NULL    NULL    Both NULL - return NULL

+1 for consistency!

MORAL of the story:

When executing statements on different platforms, ensure the functions you use behave in the same manner on both – and even when considering multiple platforms from the same vendor, named almost identically, don’t assume things behave unless you’ve tested them 🙂

Return a list of all dates between a start and end date

In some situations, you’ll need to work with a list of numbers or dates that are between some start or end, and you don’t have a complete list handy for joining to. Using the script below, you can create one to use in your query – if you wanted to use this in-line in another SQL Statement (and assuming you’re using SQL 2005+), you could either do a subquery or a WITH CTE clause and then join directly to it.

To do it, I’m using a table that has a large number of rows in it, even in an empty database (sys.columns), and then doing a cross-join to ensure that I’ll have enough rows to satisfy my entire range. This table has about 890 rows in an empty databases (or you can use the “model” database if you don’t have any user databases handy), meaning that the cross join yields about 800,000 rows – enough for almost 2200 years of days, or 100 years of hourly increments (change the “dd” in the “DATEADD” statements below to “hh” or even “mi” to do any increment of time you want).

The code:

DECLARE @StartDate DATETIME,
	    @EndDate   DATETIME

	SET @StartDate = '2012-12-01'
	SET @EndDate   = '2015-12-31'

;WITH numberlist(number)
   AS (SELECT RANK() over(order by c1.object_id,
								   c1.column_id,
								   c2.object_id,
								   c2.column_id)
	     from sys.columns c1
   	    cross
	     join sys.columns c2)
SELECT DATEADD(dd, number-1, @StartDate)
  FROM numberlist
 WHERE DATEADD(dd, number-1, @StartDate) <= @EndDate

I’m using dates above, but if you wanted to use INT instead, it’s pretty straightforward:
DECLARE @Start INT,
		@End   INT

	SET @Start = 1500
	SET @End   = 64000

;WITH numberlist(number)
   AS (SELECT RANK() over(order by c1.object_id,
								   c1.column_id,
								   c2.object_id,
								   c2.column_id)
	     from sys.columns c1
   	    cross
	     join sys.columns c2)
SELECT @Start + number - 1
  FROM numberlist
 WHERE @Start + number - 1 <= @End