Voice of the DBA

Tag: SQLNewBlogger

Batch Scripting SQLCMD–#SQLNewBlogger
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I wouldn’t do this anymore, but I ran across a post where someone couldn’t use PowerShell in their organization. A poor decision, IMHO, for the Microsoft platform, but it is a restriction. In this case, the user wanted to get a batch file to run a SQLCMD script. This post shows how.

Two Files

Let’s suppose I have two files in a folder. In this case, I have a version.sql file that contains this:
```
select @@version
```
The other is my batch file, which I’ll name runsql.cmd. In this file, I’ll do a few things. First, suppress the code with this:
```
@@echo off
```
That’s just a good habit, though you might leave this out until things are working. Now, I will use a loop to get a list of files with an extension. I use the FOR loop in this way:
```
for %%x in (*.sql) do (
```
This gets a list of all .sql files in the current folder. For each one, we will process all statements inside the parenthesis. The open is on the line above, the close will be below.

The next lines are my sqlcmd call and the various items I need. In my case, I’ll get the instance name as a parameter and use trusted authentication.
```
  sqlcmd -S "%1" -E -i %%x

)
```
I could use other parameters (%2, %3, etc.) to get a user and password if I wanted to. Instead, I’ll get the instance as a parameter, and then pass the filename in to sqlcmd with the –i parameter.

When I run this, with my single .sql file, I see this:

Easy to do, and I could add other .sql files in here if I wanted them to run.

SQLNewBlogger

This was a quick post to write in answer to someone asking a question. I knew about the %1, %2, and searched to find a quick SO post on getting filenames into a variable. It actually took about 5 minutes to research and test (and post) and then about 10 minutes to write this up.

You could do this, showing some knowledge of learning and creating a solution. For extra credit, how can I capture output of this?
November 13, 2019
Removing SSMS Completion Time–#SQLNewBlogger

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

If you’ve upgraded your SSMS lately, you might have seen this. Queries now have a completion time on the Messages window.

On one hand, I think this is great, because I can tell that I’ve run this recently. On the other hand, I might have multiple batches, so I can get confused or mis-informed. I also just don’t like this.

Microsoft didn’t agree, because at first, I couldn’t remove this. That was really annoying. However, with SSMS v18.3.1, I can.

If I go to the SSMS Options, and then go to Query Execution | SQL Server | Advanced, I see this:

At the bottom of the first column is the Show completion time checkbox. Uncheck this, and suddenly, I’m happy.

One caveat, I do need to open a new window. That old window, even if I change the connection, still shows the time. Fortunately, CTRL+A, CTRL+C, CTRL+V help me move on.

SQLNewBlogger

This was one of those annoying things I needed to fix because it impacted my productivity. It’s a perfect post of my finding a way to be more productive and learning on my own.

October 30, 2019
Arrays in PowerShell–#SQLNewBlogger
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

This isn’t necessarily an expert post, but more a recognition that I learned something. I knew about objects in PowerShell, but I’d never actually used an array. Never had a need.

However, while writing my article about striped backups, I ended up using arrays. This was mainly from the example shown by Anthony Notencino in his Restoring Backups from Azure Blob with dbatools.

Here’s a way to initialize an array:
```
$a = @()
```
If I look at this, I get nothing back, which makes sense. There’s nothing in there.

I can “add” values to this array with +, as in:

$a += 1
$a += 2

If I now look at the variable, I see this:

One interesting thing here is that += is different than Add(). Look at this:

So why does += work? In the array doc, there’s this quote:

When you use the += operator, PowerShell actually creates a new array with the values of the original array and the added value. This might cause performance issues if the operation is repeated several times or the size of the array is too big.

Worth knowing this. I think for most scripts this isn’t a big issue, but be aware that you are allocating a new array.

Working with arrays is a topic for another day, but this is a short look at how you might store something like a list of files in a single place.

SQLNewBlogger

While working on solving the problem for a customer, I realized this was a little thing I learned. A quick post to show that here, just about 10 minutes or writing and structuring code.

A good chance to give an interviewer something to ask you. Write your own post on arrays.
October 23, 2019
Setting Permissions for a SQL Server backup folder–#SQLNewBlogger

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

While testing a script recently, I needed to set a few backup folders for my instance. This was a striped backup, and using one folder wouldn’t make sense. In modern OSes, we can’t just create a folder and expect that our processes can read it. We need to explicitly set permissions.

It’s fairly easy for SQL Server, but since it wasn’t obvious, I decided to take a minute and document this.

If I create a folder, say c:\sqlbackup, I see this kind of thing in properties on my Window 10 machine.

If I click “Edit”, I get a similar view.

Now, my SQL Server process can’t access this. If I try to restore a backup from here, I’ll get a permissions error.

That’s fine. In the Permissions, I can click Add and I’ll get this dialog.

From here, I can enter “NT Service\MSSQLServer” or “NT ServiceMSSQL$SQL2017” for a named instance. My named instance is SQL2017. Note the space in “NT Service”

If I click the “Check Names”, this will resolve for the built in service account.

Then I can click OK and set the appropriate permissions.

If your service account is something not built in, it’s usually easy to find and add, but for build in accounts, you need the “NT Service”.

SQLNewBlogger

You can take a simple thing here that you needed to solve and write about it. This took my about 5 minutes to solve, playing with different names, and then about 5 minutes to write.

Showcase your knowledge today.

October 16, 2019