Voice of the DBA

Tag: SQLNewBlogger

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
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