Voice of the DBA

Tag: tools

Parameter Fun

Recently I was editing a document about SQL Server on Linux and the author noted that if you type “sqlcmd” without any parameters, you get the list of possible parameters back. I tried that on my Windows laptop and immediately got an error that no credentials were supplied. Apparently sqlcmd on Windows attempts to connect to the default instance.

I thought the author had made an error, but sure enough, when I connected to Linux and ran just “sqlcmd”, I got a list of parameters. What was more fascinating to me was that if I ran “sqlcmd -?” on either platform, I got the parameter list, as I would expect. However, if I ran “sqlcmd /?”, this worked on Windows, but returned an error on Linux.

A long time ago I wrote some command line utilities to help our network team manage a Netware 3.x environment. I was proud of my work and knew that the team would appreciate a few of my tools. When I first showed me boss and started to explain what it did, he stopped me and ran the name of the utility with a /? at the end. Nothing returned, and he told me to redo the work and ensure that /? always returned help for the tool.

I’ve kept that habit for years and I’ve often tried that with new programs. Most have worked, including bcp and sqlcmd. Somehow, that hasn’t continued to this day. For sqlcmd.exe on Linux, only -? works. If you run Docker, you need a –help, though -help works. Other programs might be more strict, but it’s surprising to me how many different ways we’ve implemented help and parameters.

PowerShell has Get-Help, but then uses single dashes for parameters. A number of newer cli tools, especially for Linux, seem to want two dashes for parameters, though a single dash often works. I’ve seen a few tools that mix single and double dashes, depending on which parameter. I did find this note that on Unix a few single dash parameters can be combined, so the double dash indicates we are using one parameter, not multiple ones. That makes sense, though I would argue that -abc meaning -a -b -c is a fundamental design problem in and of itself.

The evolution of help and parameters seems funny to me. It’s likely caused by someone implementing the parameter short cutting in Unix at some point that now requires double dashes for multi-character parameters, which is really a case of a short-sighted design in Unix. In any case, understanding the behavior of parameters and help is a useful skill, especially in the current environment that tends to implement more scripts with command line utilities.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 4.1MB) podcast or subscribe to the feed at iTunes and Libsyn.

March 4, 2019
Evolving Our Tools

This week the next preview version of SSMS v18 was released. This is the sixth preview release, and I’m guessing that this will be one of the last. Six seems like a lot of releases, and I’d like to think that this is getting close to being ready to use by most people, but I’m not sure. I certainly have some some annoying and problematic bugs, so I can’t be sure there won’t be a seventh, eighth, or ninth preview, but it does seem to be fewer issues are being reported.

With the release of SQL Server 2016, SSMS was decoupled from the database engine, and we saw some SSMS v16 releases. I didn’t use many of those before moving to SSMS v17, which came slightly after SQL Server 2017. I’m glad we’ll start to get the versions separate from the engine as this is confusing to many people. However, I do expect that plenty of people will call this SSMS 2019 or think there’s a SQL Server 2018, etc. If we could get more people to leave the older SSMS versions that came with 2008 R2, 2012, 2014, then I’ll take a little confusion in how we talk about SSMS.

However, we don’t need to stick with SSMS these days. If you’ve been heads down and just focused on keeping your existing systems running, you might not realize that not only do we have different SSMS choices, but we also have other tools. I’m not talking about SQLCMD, bcp, and Visual Studio, but we have other ways of working with SQL Server. Visual Studio Code has an mssql extension if you write code in that IDE, which might be something you full stack developers need.

For the SQL Server people, we have a fork of VS Code in Azure Data Studio. This is a lighterweight IDE built for SQL Server work. We also have the mssql-cli tool, giving us way more control over command line work than we have with SQLCMD. I haven’t worked with it much, but it’s on my list for January to play with a bit. I don’t know how well either of these will catch on, but let me know your thoughts. Are you doing more work with either of these tools?

There are certainly plenty of other choices as well. My company (Redgate) and others make plugins for SSMS that improve how you work with SQL Server. There are even other IDEs, such as DataGrip, that you can use and abandon the Microsoft tools altogether. I’m not sure I would look to leave SSMS entirely, but perhaps I should give some of these a try at some point.

Tools matter to many professionals. Mechanics treasure their sets of wrenches, chefs love their knives, and we ought to have tools that we know, use, and are comfortable with. This includes both the actual software and the various scripts, code, and helper applications that allow us to work efficiently. If you don’t love your tools, or have a collection, maybe now is the time to start the new year building some skills with the one you use, or try a new one. Wayne Sheffield has a nice series on SSMS and I’m hoping to get some other pieces written for other tools. If you want to tackle one for SQLServerCentral, let me know.

If you’re looking for a new tool to try for SQL Server work, might I suggest some PoSh and dbatools. It’s an amazing combination for lots of tasks.

Steve Jones

December 22, 2018
Creating a Quick Dashboard Widget

I read Carlos Robles blog on creating an Azure Data Studio (ADS) insight widget and decided to try this for myself. I decided to try and get a list of object types and a count of each. Following instructions, here’s what I did.

First, I wrote a query that would gather the types of objects and counts from sys.objects.

I saved this as a file under my Documents folder. Once I had this, I ran the query and in the results, I clicked on the Chart item to the right of the results.

This displays a bar chart, which is useful at times, but that’s not what I want.

Instead of this, I’ll expand the Chart Type drop down. This gives me a list of a number of items I can choose. For this insight, I’ll use the table type.

Once this is done, I’ll see the results I want. There is a “Create Insight” button above the chart. I’ll click this to get the JSON code that creates this.

A new editor tab opens with the JSON code in it. This is just one long line of code, which isn’t as helpful or easy to work with.

CTRL+Shift+P opens the command palette. Type “format” and you’ll see the Format Document command. Once you format the code, it will be easier to read.

The next step is to open the user settings. This is also in the command palette.

In my ADS, I get the settings list. I can search for dashboard and I’ll find the one I want.

If I put the cursor in the upper left, I’ll get an edit item. I can pick this and I’ll get one of two items. If I’ve never customized anything, I’ll get the “copy to user settings” option. If I have, I get the “Replace in settings” item.

Once I’ve done this, I want to modify some code. I don’t love the search box, so I’ll delete that json widget. Then I paste in my widget code. Be sure you put this in the right place, with a comma between the widgets braces. I changed the name from “my-widget” to “User Object Count”.

Once I do this, I save it and close it. Then I double click one of my server connections and I see my widget on the dashboard.

Pretty easy to get a query to appear on your dashboard as a chart if you want to get some insights. Any query could be used, with the results returned when you open the server connection. You can also leave the dashboard open and refresh it when convenient.

November 8, 2018
Adding SQL Search to Azure Data Studio

There are a limited number of extensions available for Azure Data Studio (ADS), but one that came out early is SQL Search. This is highlighted as a recommended extension from Microsoft. Redgate worked with them early on in the lifecycle of the product to get this extension working, including helping spec out the APIs for interaction with ADS.

My extensions pane is on the right, so my details are on the left. There’s a description of SQL Search as well as a link to install the extension. As with most extensions, the link takes you to a download page. For SQL Search, this is a Redgate Foundry page with a few builds. I picked the public one.

Installing extensions is basically using File | Install Extensions from VSIX and picking the downloaded file. You need to approve the extension and then reload once it’s installed. You can see it installed if you re-open the extensions pane.

Using SQL Search

To use this extension, you can use the CTRL+Shift+P command to bring up the command palette. When you do this, you get a way to run commands at the top of the ADS window. This is a handy tool that you will use often.

If we type “SQL S”, we’ll get the SQL Search commands. There is a reindex command and a search command. These are the two items implemented at this time.

The re-index command will update the SQL Search index with information from the current database connection. If you cannot find an object you need, then you might run the reindex.

The other command allows you to search the database. If we click this one, we get a new edit box at the top of the window. Focus will be in this window, so you can type a term. For me, I know there are a number of items in this database for blogs, so let’s enter that.

The results will appear in a new tab alongside your currently open tabs.

The results show me that I have an object name along with the schema and database. We see the type of the object and then the way the object was matched. Next to the object name, there is a circle with an ellipsis. If we click this, we have a couple of choices for what to do with our results.

We can highlight this object in our object explorer, which will, well, do nothing for me. I suspect that the call from the extension to the servers Object Explorer is flaky. Even if I open the Servers pane and then click “Reveal in Object Explorer”, nothing happens. I see whatever I had opened in the Servers Pane.

If I close the server pane and instead click View Definition, I get a split pane on the right with the object definition. I can resize this as needed, but this is a view only pane. I can’t edit or execute this code. It’s for reference, which can be helpful when coding, but is of limited use.

I can copy this code for another window if I click the start and end places in the search results while holding the Shift key. Then I right click in the window and select Copy.

This is a limited use extension, but it does allow one to find code quicker than browsing through the Object Explorer. We can keep the search results up as a separate pane, so that we can use this as a pick list if we were looking for all the dependent objects for an object.

One nice touch from ADS is that if you CTRL+Shift+P again, the SQL Search commands appear at the top as recently used items.

Give SQL Search a try and let us know what you think.

November 2, 2018