Voice of the DBA

Tag: SQLNewBlogger

Installing PowerShell for SQL Server – #SQLNewBlogger
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

PowerShell is the hot new scripting language for working with processes outside of an application. It’s cross platform, and it has a lot of capabilities for working with SQL Server. The way that this gets installed has changed, so this is a quick post to ensure others know how to do this.

Running a Command

I know there is an Invoke-SqlCmd cmdlet in PowerShell. On a new laptop, I tried to run it and had issues.

SQLPS is the old module, and SqlServer is the new one. The error message says to try importing the module, so let’s do that.

That doesn’t work either. Hmmm. I think I need to install this. To do that, I need an elevated command prompt. I’m using ConEmu, and I can restart this session as an admin. Or you can use the start menu to find cmd and start this as an admin:

Once you have an elevated prompt, start PowerShell and then run this:
```
Install-Module SqlServer
```
This will install the module if you answer the prompts correctly. I had to use the -AllowClobber parameter as I had some conflicting things installed from dbatools. I’ll likely update those after this with their own AllowClobber.

Once this is done, you should be able to use the cmdlets. First you import the module with this:
```
Import-Module SqlServer
```
Then you can run code:

SQLNewBlogger

After getting a new laptop, I needed to set a few things up. PowerShell was one of those. As I started to do this, I grabbed screenshots to document the process for my blog. I then built this post in about 10 minutes.

You could do this as well, to round out the knowledge you gain as you do something similar, and show you’re familiar with these concepts.
December 27, 2019
Using a PoSh variable in a string- #SQLNewBlogger
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

This is something I haven’t quite understood or used often, but I’ve been aware of it and wanted to learn more.

A member at SQLServerCentral wanted to embed a value in a string, and was having issues. In this case, they had this code:
```
$dt = get-date -format "_yyyyMMMdd_HHmss"
Invoke-Sqlcmd -Query "SELECT * FROM [Sandbox].[dbo].[Customer]" -ServerInstance "Plato\SQL2017" |
Export-Csv -Path E:\Documents\sql\$dt.csv -NoTypeInformation
```
In this case, there was an error with the Export-Csv cmdlet, with a syntax issue near the period. I suspected this was some variable expansion that didn’t work.

I found this post that helped me understand a bit more and decided to experiment a bit. Let’s try some things. First, I used to do this type of code:
```
$dt = Get-Date –format “yyyyMMdd”
write-host(“Today is “ + $dt)
```
I then see this:

However, I can use this code:
```
 write-host("Today is $dt")
```
That gives me the same result. Apparently, I can include the variable in the string and it gets expanded. This works with just a string, as shown here:
```
PS C:\Users\Steve> write-host("Today is $dt.csv")
Today is 20191202.csv
PS C:\Users\Steve>
```
Not the error I expected, but this makes more sense with a value that’s needed in a parameter. The blog helps explain this with the following code:
```
PS C:\Users\Steve> $directory = Get-Item 'c:\windows'
PS C:\Users\Steve> $message = "Time: $directory.CreationTime"
PS C:\Users\Steve> $message
Time: C:\windows.CreationTime
PS C:\Users\Steve>
```
An issue. However, if I use the expression evaluation of $() inside, I get this:
```
PS C:\Users\Steve> $message = "Time: $($directory.CreationTime)"
PS C:\Users\Steve> $message
Time: 09/15/2018 00:09:26
PS C:\Users\Steve>
```
That’s the trick I needed for Export-Csv. I used this code in the last line:
```
Export-Csv -Path E:\Documents\sql\$($dt).csv –NoTypeInformation
```
And the code worked as expected.

There’s likely more I should know, but I will start to use varaiables inside strings when I just need the value of the variable as a string. If I need this to better work with some property, method, or parameter value, I’ll use $() around the variable.

SQLNewBlogger

This post was about 20 minutes of me experimenting with a few things and slowly working out how some variables worked. I somewhat wrote this as I was experimenting, adding in the code that ran.

A good example of writing while learning. You could do this on your blog as you learn to work through some code or a feature.
December 18, 2019
Exporting a Table to CSV in PoSh–#SQLNewBlogger
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I saw someone struggling with exporting some data from a table in a CSV, and decided to give it a try. I think there were a few things that were broken, but here is the basic idea.

The Scenario

I want to get all the data from a table into a CSV. As a setup, I have a table that looks like this:

What I’d like is this data in a CSV, with a format like this:
```
"CustomerKey","CustomerName","CustomerNameLen"
"1","Steve","5"
"2","Andy","4"
"3","Brian","5"
"5","Acme, Inc.","10"
"6","Apple","5"
"7","IBM","3"
"1","Steve","5"
"2","Andy","4"
"3","Brian","5"
```
Let’s make that happen.

PoSh to the Rescue

If you have SQL Server and the Powershell module installed, you can use the Invoke-SqlCmd cmdlet. This takes parameters for a query and a server that you want to use. There are other parameters as well, but I’ll keep this simple.

The parameters I’ll use are a query with three part naming and then an instance. Here is the command:
```
Invoke-Sqlcmd -Query "SELECT * FROM [Sandbox].[dbo].[Customer]" -ServerInstance "Plato\SQL2017"
```
Now we want to get that data out to a CSV, and we can use the Export-Csv cmdlet for this. For me, I often want to avoid extra work, though in the past, I’d have set the output of the Invoke-SqlCmd to a variable. I don’t need to here, so I can use pipe the output to the cmdlet like this:
```
Invoke-Sqlcmd -Query "SELECT * FROM [Sandbox].[dbo].[Customer]" -ServerInstance "Plato\SQL2017" |
Export-Csv -Path E:\Documents\sql\Customer.csv –NoTypeInformation
```
This will create a new file for me, called Customer.csv.
In here, we have our data.

Easy, and quick. You could even type this from the command line, without saving the code.

SQLNewBlogger

A quick look at a common task. In this case, I was helping someone, but I’ve had the need to do this myself, and fixing someone else’s code actually taught me something here. I’d use this as a story of solving a problem in an interview with new technology (PoSh).

Solving the problem was about 10 minutes work, and really, this took about 15 minutes to set up, get working, and clean the code a bit. I also had to change a few of the other issues to make this simpler, but those will be good posts in the future.
December 11, 2019
Setting Certificate Backup Permissions for an Instance–#SQLNewBlogger

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

Recently I was testing some security change, and I made a backup of my certificate from one instance. I did this on a share that I’ll use to move the files to another instance. In this case, I had these files in the folder.

The problem is that by default, SQL Server locks down permissions, as it should. That means that the other instance couldn’t access the files, as its service account didn’t have permissions.

I fixed this by opening the security tab for the file. I had to do this for each, but I’ll show one here.

I clicked “Advanced” to get a more detailed view of permissions.

Again, I really need to approve this view of the security settings. Once I acknowledge the UAC dialog, I can see this:

What I needed to do here was add permissions for another service account. Clicking Add lets me select a principal.

For a local account, I need to give the “NT Service” prefix to my account, despite this not being shown as part of the service account settings.

When you click Check Names, this will shorten. If you enter this short version, it won’t work.

In my case, this account really just needs Read permissions.

Click OK, and I see it listed.

That’s it. Now my SQL2017 instance can access the backup and create the certificate.

SQLNewBlogger

This is something I’d expect most people working with SQL Server on Windows would be easily able to do, but showing some knowledge here gives confidence in your abilities.

This took longer to get screenshots than to write. You could easily do something similar.

November 20, 2019