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.

Converting two-column DataFrame to a dictionary in Python

If you have a Pandas dataframe like the following (if you already have a DataFrame based on a query result, you can use that):

import pandas as pd
data = [['tom', 10], ['nick', 15], ['juli', 14]]
df = pd.DataFrame(data, columns = ['Name', 'Age'])
Basic Pandas DataFrame

I wanted a dictionary where I could provide the name (“tom”, “nick”, or “juli”) and receive their age as the result. To convert your DataFrame to a dictionary like this, you’d use the following command:

my_dict = pd.Series(df.Age.values,index=df.Name).to_dict()
print(my_dict)
Python dict with three key:value pairs

Now that you have a dictionary, you can do lookups or whatever else you want. Make sure you take note above that the first parameter uses “values” where the second one doesn’t. If you use values in the second parameter, or you just try to use the “.todict()” function on the DataFrame directly, without converting it to a series first as above, you can end up with Dictionaries with an entry for each column, or dictionaries that contain dictionaries, or a number of other things that don’t work.

Short and to the point – this took me about an hour to find the answer to and hopefully you find it before losing that much time!

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

“There is no data in the database for @catalogitemid” in Power BI Report Server

After updating from the May 2020 release of Power BI Report Server to the January 2021 release, we started to receive this error during our report refresh schedules:

An error has occurred. There is no data in the database for @CatalogItemId

This was happening when multiple reports were refreshing at once, during our overnight schedules, as well as when multiple reports were manually refreshed at the same time. I found a post that seemed to confirm this (https://community.powerbi.com/t5/Report-Server/Scheduled-Refresh-error-quot-There-is-no-data-in-the-database/m-p/1505277) and suggested that you change your schedules so multiple reports don’t refresh at once – since were using shared schedules (including a schedule that we trigger via the API when our ETL finishes), that wasn’t an option for us.

To work around it, we had to refresh our reports one at a time, waiting until the currently one had completed before starting the next one – if you started the next one before the previous was complete, it seemed like about a 50/50 chance that the second report would fail with this error. This left a 0% chance that the overnight schedules that were attached to dozens of reports would even refresh 2-3 successfully.

I came across a post in the Power BI Community forum (https://community.powerbi.com/t5/Report-Server/There-is-no-data-in-the-database-for-catalogitemid/m-p/1721489) where somebody shared that it was caused by a code change to the code that loads the data model from the SQL database in the October 2020 release – switching a query hint in a Stored Procedure (GetCatalogExtendedContentData) from READPAST to NOWAIT (it was even annotated with a comment from the PBIRS developer that made it – nice work, whoever this was! This is a great example of a comment that has the “Why” and not the “What”, so it’s actually useful):

FROM [CatalogItemExtendedContent] WITH (NOWAIT) -- DevNote: Using NOWAIT here because for large models the row might be locked for long durations. Fail fast and let the client retry.

You can read more about query hints in the Microsoft Docs, but at a high level, READPAST (the previous code) causes a query to skip past and locked rows as if they’re not even in the table – NOWAIT, however, causes a query to fail with a lock timeout error as soon as it encounters any locked rows that it wants to read. The code change results in a failure any time we attempted to refresh a report while a previous refresh has any data model contents locked.

I couldn’t force this error to happen to one of our test environments, no matter how many reports I refreshed at once or large the data models were – given this, I wasn’t sure that fixing this code would resolve our error since the test environment had the code with the NOWAIT hint. I wanted to be sure, so I ran a SQL Profiler trace while refreshing some reports in production so I could see the error happen (WARNING – Profiler in production is usually a bad idea as it drags down performance, so proceed with caution here). Here were the three lines I saw that confirmed it:

SQL Profiler trace showing Lock Request Timeout exception followed by the SP call that’s failing

This was exactly where the forum said the code had changed – I was seeing the stored procedure throw an immediate Lock Timeout, which was the confirmation that I needed. I ran the following script with SQL Management Studio in the Power BI Report Server database to revert the code to the May 2020 query hint (red is the code I commented out and blue is the code I added):

 USE [PBIReportServer]
 GO
 ALTER PROCEDURE [dbo].[GetCatalogExtendedContentData]
     @CatalogItemID UNIQUEIDENTIFIER,
     @ContentType VARCHAR(50)
 AS
 BEGIN
     SELECT
         DATALENGTH([Content]) AS ContentLength,
         [Content]
     FROM
         [CatalogItemExtendedContent] WITH (READPAST) -- Reverting this back to May 2020 code. Commented line below is original in January 2021 version.
 --        [CatalogItemExtendedContent] WITH (NOWAIT) -- DevNote: Using NOWAIT here because for large models the row might be locked for long durations. Fail fast and let the client retry.
     WHERE
         [ItemID] = @CatalogItemID AND ContentType = @ContentType
 END
 GO

With this change deployed, we didn’t have a single report refresh failure over the weekend when our shared schedules ran! I’m not sure why I couldn’t force the issue in our lower environments, though I suspect it has something to do with either lower total data model size stored there, or index maintenance, or something else related to the data storage. I checked and all the SQL Server settings I could see where the same (as they should be) so I can’t confirm what additional situation causes this error (since clearly it doesn’t happen in every installation).

If you are able to use this successfully or have issues, please weigh in on the community post above or in the comments below!