Voice of the DBA

Tag: tools

Getting Started with the MSSQL AI Agent in VS Code

Recently I was working in VS Code and I saw a walkthrough for the new Copilot chat features. I decided to give those a try in trying to get some information from my SQL Server instance.

This post walks through a few things I did with this GenAI agent. There is a video walkthrough at the end.

Note: I have copilot access set on VS Code as a part of my employer’s benefit.

This is part of a series of experiments with AI systems.

First Steps

When I start VS Code, I see something like this.

One of the Walkthroughts recently mentioned copilot. If I click the “More” at the bottom right, I’ll get this image. You might see something different, but Id’ expect you have a Copilot walkthrough if you can use Copilot. I choose the 4th one down (where the mouse pointer is).

This opened a Copilot pane. There were a few items, and you can see on the left in the image below, some have checkmarks. I’d explored these before.

If I scroll up, I see the one I wanted to get, which was “chat about your code”. I picked this one. This opened a blade to the left when I clicked the blue “Chat with Copilot” button.

I had read there are these @ agents (look up at the right side) and decided to type “@”. I saw a list of things.

Lots of places to work, but I choose the @mssql agent, since this is the place I tend to work. In the lower pane, I typed a question.

Above this (still in the left blade), I got a response.

Below this, I got some text and code explaining how to access a list of databases on various platforms. Not sure why MySQL is first, but I’m assuming this is alphabetical. For SQL Server, I saw this. This is a reasonable answer, with some help on how to execute it.

I then decided to connect to my local instance. I have the MSSQL extension, so I clicked that and got a connection.

Rerunning that query produced the same response. However, when I opened a query window, I got different results. Note the little database icon on the left, below my prompt, with “Untitled-1” next to it. This is the context, which I also saw added to the lower prompt box, just above where I would enter a prompt.

However, this didn’t work. After a few minutes, I got this.

and this. The LLM is trying, but can’t seem to get a query to run. It did try.

I then decide to move on.

Getting Results Back from Questions

This isn’t really the type of thing I’d do, but I decided to try and get some info from a database. The one above isn’t that interesting, so I switched to asking the model some questions. Here’s the first one, where I don’t remember the exact table name, but I ask.

It’s queried the database, and there isn’t a player table. However, it continues to look and finds dbo.players.

Even better, once it has the answer, it also provides a little more info. Maybe good, maybe bad. This reminds me of talking with a person that gives me more information than I asked for.

I try something else. Let’s get some metadata, since I clearly don’t remember what’s in this database.

I get a nice response, with some guesses about what information is contained inside these tables.

OK, can I query for information. I’ve always been a bit more of a hitter than a pitcher, so I’ll ask a question. This isn’t asking to join specific tables, but get me an answer.

It worked, though to be fair, I tabbed over to SSMS and wrote this query in the same time (with SQL Prompt) as the Copilot agent. Cool to see as I’d forgotten Thome and Vlad were up there.

While I got the answer, I didn’t get the query. I asked for it and got it, with an apology.

I’ll do something else. Who played the longest. Might be a somewhat funny query to write for a quick answer. I’d have to join a few tables and look for a sum.

It likely remembered I wanted the query, so that was included, with an explanation. However, it only looked at the batting table.

I asked other questions about fielding and pitching and got those answers (Nolan Ryan, 27 years with fielding stats and pitching stats. So I asked that:

Below this, I see the two players who tied, which Copilot noted.

The code provided only returned one player. I checked, which is something that you should always do. I asked if I could get better code. I got a few options, and I liked the RANK one, so I tried it and it worked.

Slightly annoying, but when I think about this type of conversation with someone else, especially a junior dev, I might have the same results and iterate this way.

At this point I also asked about databases, and I got a result. Maybe I needed a query to run first? Not sure why this works now.

Summary

This was an interesting set of things I could get done with this agent in VS Code. It’s not amazing, but it was helpful. I could tackle some light query tasks or db query ones while also handling some other work. In this case I wasn’t in the zone, trying to code or decode a database schema. Instead, I had a few things to try, and I let the agent work while I tabbed over to close some emails and chats.

In a job where I might need to find info from an unfamiliar database, this could be helpful in getting things done, though it’s hard to know if it’s slower for me if I were focused on ths all the time. The agent can find some info without me, but it also failed in a few cases. I started to try and get other things done when I noticed delays in responding and some hung queries.

Learning to use an AI agent to help you is a skill, and it’s one that takes time to develop.

I’ll look at some more practical tasks in the next post.

Video Walkthrough

Here’s a video walkthrough of most of the stuff in this post. It differs slightly as working with LLMs is not deterministic.

September 8, 2025
Moving On After a Technology Fail

Recently my smartwatch broke. I was coaching volleyball and demonstrating something to a few kids when a ball hit the case of my watch just right and broke a corner there the watch band attaches. I’ve been playing and coaching with my watch in this way for many years and never had an issue, but apparently there was enough material fatigue that this time something snapped. I was very sad as this was my ten-year gift from Redgate, which I’ve loved, used, and cherished for 7 years now.

I wonder if there’s a 20-year gift …

In any case, I lived for a day before I realized I really depend on my smartwatch. I quickly realized that I constantly look at my watch to check the time, run a stopwatch at volleyball, track my heart rate and exercise for health, and most importantly, wake me up with a vibration in the morning. Before I had this I set an audible alarm, which my wife hated. At the time the case broke I was very busy, and after a quick set of searches and very minimal evaluation, I just bought a Garmin Venu 2Sq as a replacement. It was inexpensive, but appeared to do most of what I wanted. If I decided I didn’t like it, I wasn’t out a lot of money.

I contacted Garmin, but they said they wouldn’t repair or replace cases. Maybe they assume technology that is more than 5 years old is too prone to failure and not worth fixing? Maybe they just want to sell new products and not support old ones? I get all that, but this age of disposable digital tech is a bit annoying and mildly upsetting to me. I constantly fix old things on the ranch and I like keeping devices going as long as possible. Why not make cases replaceable? I feel the same way about a few devices that have embedded and built in cords. One of my Google cameras had a cord failure when a cat chewed it and the entire device is not junk because I can’t repair this tiny cable and Google won’t. But I’m getting distracted here.

This new watch is very different. Less buttons, more touch screen, and a different type of OS. I am having to learn how to use a new tool, which is both exciting and annoying. I am less productive in some ways as I learn a new tool, and I’m sure I’m missing things because I just want the tool to work rather than invest the time to learn about (potential) ways to use it. I know that there might be plenty of features I learn about that I’m uninterested in using, and I’ll have wasted time.

I see this often in technology. Many of us get used to working with tools in a certain way, and we learn to be productive. We get comfortable, develop habits, and work around annoyances. If the tool changes, or we are forced to use a new one, often we don’t like the change. We may feel lost and not see the advantages of a new tool.

I see this all the time as I work with customers. Sometimes I can see better ways they can accomplish tasks, and sometimes I see the cost of change is too high. Even a more efficient way of working can’t overcome a loss of productivity for a long period of time if too much change is required. Leaning on existing tools, skills, and habits can be efficient and comfortable. Going through the hassles of change can be worth the effort. Like most things in the database world, the devil is in the details and the answer to the value of changes is it depends. Sometimes a new tool is valuable and sometimes it is not worth the effort.

Almost a month into a new watch, I’m unsure of whether I like it or not, but in the short term, I’m too busy to spend time looking for a new tool. Unfortunately, that’s the state of the world often for many of us. We live with good enough.

Steve Jones

Listen to the podcast at Libsyn, Spotify, or iTunes.

Note, podcasts are only available for a limited time online.

February 21, 2025
The End of Azure Data Studio

I don’t know how many of you will be disappointed or impacted by this, but Azure Data Studio (ADS) is being retired, as of 6 Feb, 2025. It will be supported for a little over a year, until 28 Feb, 2026. On one hand I’m not surprised, and on the other, I’m a little shocked by this.

I have written a number of articles on ADS, and shown how things work, as well as pointed out a number of things that don’t work well in the product or its extensions. These pieces have gotten a number of reads, and people have commented on them, so I wonder if there are a lot of you that are upset by this. Is this going to change the way you work? I will say that it will lightly change my work, as I do use ADS to connect to PostgreSQL, but not so much for SQL Server.

I have tried to use ADS, but I just don’t like it. I don’t have a good reason, as it does a lot of what I need from a query tool. I think the port of the query and result experience from a real app like SSMS or Enterprise Manager or even isql/w is just a worse experience. I don’t like the ADS interface and it’s annoying to me.

I suspect that many others feel the same way (other views from Deb and Kevin). They don’t like the ADS experience and prefer SSMS or some other tool. I know there’s been no shortage of complaints over the years about, and finally MS has listened. From first trying to get everyone to leave SSMS to forcing people to install ADS alongside SSMS and now to finally retiring the tool. I think it’s a good decision as people don’t want to lose SSMS and it’s hard to maintain two tools.

We will still have VS Code, which I use often for other purposes. I haven’t spent much time with the mssql extension, but I need to as it’s been updated as of a few months ago and supposedly works better now. We’ll see.

In the meantime, I won’t mourn ADS. It was a tool that had potential. I liked the idea of notebooks, I liked the fast startup. I just wish it were better implemented as a run-a-query-and-get-results application. I wish we had a cross platform editor that was simple and fast, but not one based on VSCode. One that’s written to just manage queries. Maybe they’ll rewrite isql/w in a modern way and port it to Linux.

Steve Jones

Listen to the podcast at Libsyn, Spotify, or iTunes.

Note, podcasts are only available for a limited time online.

February 12, 2025
Connection String Confusion in SSMS
Recently I was trying to use a connection string to connect in SSMS. There are some tools that have a connection string available as an output, including some Redgate tools. ADS lets me paste in a connection string. Can I do this in Management Studio (SSMS).

Yes, but be careful.

Getting a Connection String

There are lots of ways to get a connection string. You can build one, or use a site like https://www.connectionstrings.com/sql-server/ I tend to get them for applications, as I work more with app developers.

In any case, I’ll use this as my string:
```
Server=Aristotle;Database=Sandbox;Trusted_Connection=True;
```
If I open SSMs, I get a connection dialog like this one. I see the server, but if I wanted a specific database, I’d have to go to the second tab.

I could also go to the last tab, the Additional Connection Properties, and paste my string in there.

I press Connect and that works great.

If I open a new Query Window, I’m connected to the Sandbox database.

The Problem

Let’s change the connection. I’ll press the icon to the left of the database name in the image above. Then I’ll go to the second tab and pick a database. In this case, I’ve selected the Westwind database.

If I click Connect, I see this:

The connection string in the individual parameters overrides the selection here. If this were a day later, I might remember I’d put a string in the Additional Connection Parameters. I rarely use this and when this happened, I couldn’t figure out why this wasn’t working.

I thought this also happened with changing the main dialog and the server name, but this appears fixed. At least on SSMS 20.1, the additional connection parameters are linked with any saved systems I have on the first tab, so if I change servers in the drop down, the additional connection parameters link to the last entry for that server.

On my laptop, which has 20.2, I’ll enter this as a connection string in the last tab:

In this case, I have multiple container instances running on different ports. This instance is on port 41433. On the main tab of the connection dialog, I see this:

What happens when I press Connect? I get to this server:

The default port is SQL Server 2022, but the additional connection properties overrode the front screen in this case.

Summary

I’ve never had this problem in 30+ years of SQL Server work until this summer. However, it’s the first time I’ve really been focused on using connection strings more often than just entering values in the dialog. I only noticed this as I had a deployment going to one instance, but SSMS kept connecting to the other one and I didn’t realize this.

I think it’s OK to have conflicting values in locations, but it wasn’t clear to me that these values override others. This is documented on the MSLearn site, but it’s easy to miss this.

Hence this blog.
September 25, 2024