SSMS Error when refreshing a Power BI Table: Paramter name already exists

2 weeks ago I talked about A No-Code Method to Refresh One Table From a Power BI Dataset in the Service. I recently ran into an error using this method, so I thought it was worth sharing this error, and the solution 😀, with you.

TL;DR

Don't use the initial catalog when you're connecting to a Power BI dataset with SQL Server Management Studio (SSMS).

If you want to now more details about the exact problem and solution, please read on below.

Problem

In my previous post I mentioned:

"With some tools it might be necessary to also provide the Initial Catalog (the dataset to connect to in your workspace). The datasets in your workspace will eventually show as databases under your AS-server:"

I did specifically say some tools, and might. 😁

So, to be even more specific, when using SSMS ánd the option for initial catalog, you run into the below error message:

I tried a few things before I got asked for some (external) help. A little bit of context around the problem:

• Note that the message itself also has a typo in it (paramter 😂).
• It happens to every table in every dataset (I tried so far) in this workspace. I tried another workspace and that refreshed fine from the UI.
• A colleague is getting the same error message in the UI for this particular workspace.
• Looking at the error I checked if there are params called name in the datasets, but there aren't.
• It's a test workspace in a deployment pipeline.

Solution

Luckily the solution is very simple! 

• Don't use the Initial Catalog option in combination with SSMS, leave it at <default>
• Or, if for any reason you need the above option selected, install SSMS 19 Preview 4
• This preview version is a side-by-side installation, so it installs next to SSMS 18.x

I hope this helped you if you encountered the same problem.

A No-Code Method to Refresh One Table From a Power BI Dataset in the Service

Context

A few weeks back I was working on a dataset at a client where I needed to import Excel files from a folder into said dataset. I filtered the files on a prefix and loaded around 30 files of the same structure to a table in my dataset. The Excel files are exports from a budgetting system (I know, right?) that have to be updated multiple times in the next coming weeks on an ad-hoc basis.

After the Excel files are updated I currently have 2 choices:

• Open the pbix-file and refresh the specific table that loads the files, and then publish to the service. This however again triggers a refresh in the service after publishing. Depending on the connection I have in the pbix (subset of rows from dev/test/prod) I might have to wait for the refresh in the service anyhow
• Refresh the dataset in the service and wait for that to finish

And it's not that the dataset is thát big, but of course waiting (for 20 to 30 minutes) for a refresh would be a waste of time. And it doesn't really matter how long it exactly takes, if you're waiting for something, it always takes too long, right?

Solution

But as you might have noticed from the title of this blog, there is also another solution 😀.
Spoiler alert: you do need a Premium license.

Marc Lelijveld previously talked about the automation of triggering a single table to refresh in the Power BI Service, with PowerShell and a TMSL script. 

But what if you're not (that) familiar with PowerShell and / or TMSL? And maybe it's not going to be part of an automated schedule, but you just want to be able to execute it ad-hoc, when necessary?

Luckily there's the XMLA endpoint to the rescue. You can connect to any Premium (Capacity or Per User) workspace via the XMLA endpoint with SQL Server Management Studio (SSMS) to do a (single) table refresh.

There's a few things you'll have to consider before being able to do this..

• Enable XMLA read-write
• Get the XMLA endpoint connection string
• Connect via SSMS
• Refresh your table(s)
• Check the outcome of the refresh in SSMS
• Or alternatively check the refresh history in the service

Let's look at the individual steps in more detail.

Enable XMLA read-write

The first thing you need to do is to enable the option Read Write on the XMLA Endpoint of your Premium capacity.

This setting is available in the Admin Portal, either under Capacity settings or Premium Per User, depending on your license.

XMLA Endpoint connection

You can get the connection URL from the workspace Settings page, under the Premium tab.

More info on the documentation page.

Note: Connecting to a My Workspace by using the XMLA endpoint is currently not supported.

Connect via SSMS

Use SQL Server Management Studio to connect to the URL obtained in the previous step. Just be aware that you need version 18.9 or higher to do so.

Make sure you use the following option for Authentication:

• Azure Active Directory - Universal with MFA

Next to connecting to and looking at properties of your Power BI models, this method also supports executing DAX, MDX, and XMLA queries.

SSMS isn't the only tool supported by the XMLA endpoint, you can pick any of the tools mentioned here, e.g. Excel, SQL Server Profiler, DAX Studio or Tabular Editor to name a few of them.

With some tools it might be necessary to also provide the Initial Catalog (the dataset to connect to in your workspace). The datasets in your workspace will eventually show as databases under your AS-server:

UPDATE December 19th 2022:

When using SSMS and the option to use an Initial Catalog, you (might) run into an error, read my update post how to avoid this.

Refresh your table(s)

Now onto the actual refreshing of my tables. 

• Right-click the table you want to refresh and select Process Table

• Select the right processing option depending on your needs, I used a full refresh
• Optionally select other tables to refresh
• Click OK and wait for the refresh to complete.

Next to refreshing your table(s) in SSMS, you could also script out the refresh command and use it to automate the process. Because I only need to refresh it on an ad-hoc basis I'm good with my solution for now.

Refresh history

This type of refresh shows as a Via XMLA Endpoint in the Refresh History of your dataset properties in the service.

In any case you ever want to cancel a running refresh, also with Premium and the XMLA endpoint set to Read/Write, have a look at this post how to Cancel a refresh with a SessionID (instead of SPID) and DAX Studio.

Conclusion

In this post I provided a UI-only solution to refreshing a single table in your Power BI dataset. Hopefully this post gave you some insights.
Have you already used this method before?
Do you use it often?
Do you use other methods to refresh a table in a dataset?

I'd love it when you provide more details in the comments!

SSMS Status Bar Colors

I want to share this quick post for setting the color of the status bar in Management Studio (SSMS).

This post is also part of the #SQLNewBlogger challenge that Ed Leighton-Dick (@eleightondick | b) started back in April 2015:

TL;DR – In April, I’m challenging myself to write (and publish!) here regularly, and I’m extending an open challenge to other new bloggers to do the same.

You can read about his idea and the origin in his blog post, for my post on SSMS keep on reading here!

In SSMS, you can go to Tools > Options > Text Editor > Editor Tab and Status Bar.

In the Status Bar Layout and Colors part, you can set the default colors for Single server connections (the standard) and Group connections. The colors you set here are for all connections when you don’t explicitly set another color for the current connection

One-time Connection Color

When you want to set a different color for a one-time connection you can do so in the Connect to Server dialog:

Click on Options >> and go to the Connection Properties tab.

In the bottom, select User custom color: and click on Select… You can select your own color there
Just keep in mind that setting the property here will keep it until you deselect it the next time. So if you don’t want this color to be applied to all subsequent connections you make, you’ll have to uncheck the box the next time you connect to a database via this connection box.

Registered Server connection

Instead of connecting to a specific database via the Connect to Server dialog, you can “save” a server/connection as a registered server. You can do this via Registered Servers > New Server Registration. On the Connection Properties tab you can also apply a custom color here as you see in the picture below, so you don’t have to undo applying the color when you’re connecting to other servers.

If you like this type of basic posts be sure to check out the #SQLNewBlogger hashtag on twitter.

@NickyvV

Tips & Tricks

Last week I saw Cathrine Wilhelmsen's (b|t) slides of her session at SQLBits XIV: Tools and Tips: From accidental to efficient DWH developer. If you haven't watched the session, go scroll through the slide deck at slideshare right away! It was really useful and though I already knew several of the tips, I got some new ones out of it that I'm using on a daily basis now.

I especially liked the Query shortcuts, where you can set specific queries under e.g. CTRL + [3-9]. If for example you set SELECT TOP 1000 * FROM under CTRL + 3, that way you can select the first 1000 rows of a table by only selecting the table and pressing the shortcut.

I'd like to add a few tips & tricks of my own, mostly being keyboard shortcuts I frequently use.

In SSMS, with nothing selected you can use the following:

• CTRL + C selects the whole row at your cursor
• CTRL + X cuts the whole row at your cursor (also works with SHIFT + DEL)
• CTRL + V pastes the just copied or cut rows at the cursor (also works with SHIFT + INS)

Other tips:

• CTRL + Click anywhere in a word selects the whole word. If you move the cursor after clicking (and still holding CTRL) you can select more words or even sentences
• (SHIFT +) F6 scrolls through the available panes of the current tab taking the following into account: current tab, results, messages, execution plan, client statistics.
• CTRL + SHIFT + U changes the selection to UPPER CASE
• CTRL + SHIFT + L changes the selection to lower case
• As Cathrine mentioned: CTRL + U changes the database while you're typing, but it can also be done with ALT + D. I like this shortcut a little more because it can be done with one hand in case you're mousing at the same time.

I hope this was useful to you and you maybe learned something new!

Error on opening a database connection in SSMS

Yesterday I ran into an issue with SQL Server Management Studio (2012). I was using it on my laptop with Windows 7 installed.

Whenever I wanted to connect to a database I got the following pop-up and nothing could be done with the database I connected to. Opening databases or security also gave me the same pop-up with an error in Microsoft.VisualStudio.Platform.WindowManagement:

After a thorough search I came to an MSDN message that suggested the following:

• Try installing the following Microsoft Visual Studio 2010 Shell (Isolated) Redistributable Package
• Then apply SP1 for Visual Studio.

Installing the redistributable already failed with me so I searched through my recently installed programs to see what has changed the last days.. This monday I installed Vault Client for source control, and after uninstalling this the problem had disappeared. I still don't know why this was happening though..