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.
Voice of the DBA 