RSS

Monthly Archives: December 2016

Query SQL Server from PowerShell

Consistency is king. Either consistently broken, or consistently working, automation will help you get there.

PowerShell has come a long way. Early on I avoided it and I’m happy I did that. Still to this day, we don’t have a consistent version of PowerShell across our servers. Execution policies are broken. All the code examples online didn’t provide me with a simple way to do the garbage in, garbage out data processing I needed. Time and time again, my first couple attempts would work, then I would find that some servers just wouldn’t cooperate. Error handling was a bear. But there were so many blind fans out there who would look down their nose at me I started to wonder if I was in the wrong.

Fast forward five more years and PowerShell has gotten a lot better. Working with rest APIs, tight integration with VMware, SharePoint, Exchange, and easy .csv manipulation got me started and learning the language. But even today, we still don’t have an elegant way to manage data with SQL Server.

The power is in the “|” for most things PowerShell. With great power, comes terrible error handing. Most things I will end up doing don’t really need to pipe data from one cmdlet to another so I’ve tossed in the towel for now.

Getting Started…

First, get administrator on your windows machine.

Then, lets get the latest bits. SQL Server 2016 Developer Edition is now free. That really helps us bloggers and speakers not have to load up a 180 day evaluation edition and then find out it has run out right before our talk. SSMS is also free

After checking out your local instance and connecting with SSMS to make sure you did everything right, lets enable powershell by doing the dreadful execution policy changes and module import.

Run powershell as administrator and these two commands. The first one giving you some information to switch it back if needed.

Get-ExecutionPolicy
Set-ExecutionPolicy unrestricted

For the remainder we can run without administrator.  I’d suggest opening up the Windows PowerShell ISE and you can get some syntax highlighting in the top Script window. To do anything with SQL we’ll want to run this command

Import-Module SqlServer

Phew! That was a lot of setup.

Lets actually write some code!

I am not a fan of one liners. Its great in a conversation to say that was 1 line of code to trivialize the complexity but we are realist here and we are going to use some variables so we don’t have to feel like we are reading the worlds longest run-on sentence.

$ConnectionString = 'Server=localhost;Database=VMDB;Trusted_Connection=true'
$sql = 'SELECT name,physical_name FROM sys.database_files'
$results = Invoke-Sqlcmd -ConnectionString $ConnectionString -Query $sql

If the stars have aligned, after you run those three commands, you will have some results in the $results variable. Lets take a look at the results.

$results.name[0]

Taking a look at the query, we are getting 4 cells back if the database you have in your connection string has the default of 2 files, we get 2 rows in our $results variable. We also get a file “name” and a “physical_name” column. So we can reference the column by name, and the row, assuming we know the number of the row we want with an integer.

Making it useful by accepting variables…

Lets take a look at adding a dynamic where clause. I’d like to filter on the name column by whatever the user wants me to. In this example we will build the query string by asking for input with read-host.

$filename = read-host "Enter database file logical name"
$ConnectionString = 'Server=localhost;Database=VMDB;Trusted_Connection=true'
$sql = "SELECT name,physical_name FROM sys.database_files WHERE name = '$filename'"
$results = Invoke-Sqlcmd -ConnectionString $ConnectionString -Query $sql
$results

This is what I love about blogging. I can act like I got that right on the first try! I did not. Not even the second try, or third. I’m not going to mention how many tries it took. That said it is fairly easy with one catch, the quotes.

This particular problem(debatable) with PowerShell makes it rather tricky to build a string with a variable. Notice my two strings $ConnectionString and $sql are either surrounded in single quotes or double quotes? The double quotes allow me to include single quotes in the string. I also get to include the variable $filename and not get the literal “$filename” but instead the variable value.

Summary

This code is crap. You should not allow a user to free form enter text into a query string. That allows for SQL injection. In my case, the scripts I need will be protected and I will be the user so it is not a concern.

Appendix

I changed my mind, I am going to include the embarrassing details which is my PowerShell command window I used to write this blog. I find it hilarious to make fun of my coworkers when I see their screen full of red text on a blue background.

typical_powershell_window

I figured I’d include the text too in case someone is search for errors.

PS C:\Users> $ConnectionString = ‘Server=localhost;Database=VMDB;Trusted_Connection=true’
$sql = ‘SELECT name,physical_name FROM sys.database_files’
Invoke-Sqlcmd -ConnectionString $ConnectionString -Query $sql
Invoke-Sqlcmd : The ‘Invoke-Sqlcmd’ command was found in the module ‘SqlServer’, but the module could not be loaded. For more information, run ‘Import-Module SqlServer’.
At line:3 char:1
+ Invoke-Sqlcmd -ConnectionString $ConnectionString -Query $sql
+ ~~~~~~~~~~~~~
+ CategoryInfo : ObjectNotFound: (Invoke-Sqlcmd:String) [], CommandNotFoundException
+ FullyQualifiedErrorId : CouldNotAutoloadMatchingModule

PS C:\Users> Add-PSSnapin SqlServerCmdletSnapin100
Add-PSSnapin SqlServerProviderSnapin100
Add-PSSnapin : The Windows PowerShell snap-in ‘SqlServerCmdletSnapin100’ is not installed on this computer.
At line:1 char:1
+ Add-PSSnapin SqlServerCmdletSnapin100
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidArgument: (SqlServerCmdletSnapin100:String) [Add-PSSnapin], PSArgumentException
+ FullyQualifiedErrorId : AddPSSnapInRead,Microsoft.PowerShell.Commands.AddPSSnapinCommand

Add-PSSnapin : The Windows PowerShell snap-in ‘SqlServerProviderSnapin100’ is not installed on this computer.
At line:2 char:1
+ Add-PSSnapin SqlServerProviderSnapin100
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidArgument: (SqlServerProviderSnapin100:String) [Add-PSSnapin], PSArgumentException
+ FullyQualifiedErrorId : AddPSSnapInRead,Microsoft.PowerShell.Commands.AddPSSnapinCommand

PS C:\Users> import-module SqlServer
import-module : File C:\Program Files\WindowsPowerShell\Modules\SqlServer\SqlServer.ps1 cannot be loaded because running scripts is disabled on this system. For more
information, see about_Execution_Policies at http://go.microsoft.com/fwlink/?LinkID=135170.
At line:1 char:1
+ import-module SqlServer
+ ~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : SecurityError: (:) [Import-Module], PSSecurityException
+ FullyQualifiedErrorId : UnauthorizedAccess,Microsoft.PowerShell.Commands.ImportModuleCommand

PS C:\Users> Set-ExecutionPolicy unsigned
Set-ExecutionPolicy : Cannot bind parameter ‘ExecutionPolicy’. Cannot convert value “unsigned” to type “Microsoft.PowerShell.ExecutionPolicy”. Error: “Unable to match
the identifier name unsigned to a valid enumerator name. Specify one of the following enumerator names and try again:
Unrestricted, RemoteSigned, AllSigned, Restricted, Default, Bypass, Undefined”
At line:1 char:21
+ Set-ExecutionPolicy unsigned
+ ~~~~~~~~
+ CategoryInfo : InvalidArgument: (:) [Set-ExecutionPolicy], ParameterBindingException
+ FullyQualifiedErrorId : CannotConvertArgumentNoMessage,Microsoft.PowerShell.Commands.SetExecutionPolicyCommand

PS C:\Users> Get-ExecutionPolicy
Restricted

PS C:\Users> Set-ExecutionPolicy unrestricted
Set-ExecutionPolicy : Access to the registry key ‘HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\PowerShell\1\ShellIds\Microsoft.PowerShell’ is denied. To change the execution
policy for the default (LocalMachine) scope, start Windows PowerShell with the “Run as administrator” option. To change the execution policy for the current user, run
“Set-ExecutionPolicy -Scope CurrentUser”.
At line:1 char:1
+ Set-ExecutionPolicy unrestricted
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : PermissionDenied: (:) [Set-ExecutionPolicy], UnauthorizedAccessException
+ FullyQualifiedErrorId : System.UnauthorizedAccessException,Microsoft.PowerShell.Commands.SetExecutionPolicyCommand

PS C:\Users> import-module SqlServer

PS C:\Users> Add-PSSnapin SqlServerCmdletSnapin100
Add-PSSnapin SqlServerProviderSnapin100
Add-PSSnapin : The Windows PowerShell snap-in ‘SqlServerCmdletSnapin100’ is not installed on this computer.
At line:1 char:1
+ Add-PSSnapin SqlServerCmdletSnapin100
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidArgument: (SqlServerCmdletSnapin100:String) [Add-PSSnapin], PSArgumentException
+ FullyQualifiedErrorId : AddPSSnapInRead,Microsoft.PowerShell.Commands.AddPSSnapinCommand

Add-PSSnapin : The Windows PowerShell snap-in ‘SqlServerProviderSnapin100’ is not installed on this computer.
At line:2 char:1
+ Add-PSSnapin SqlServerProviderSnapin100
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidArgument: (SqlServerProviderSnapin100:String) [Add-PSSnapin], PSArgumentException
+ FullyQualifiedErrorId : AddPSSnapInRead,Microsoft.PowerShell.Commands.AddPSSnapinCommand

PS C:\Users> $ConnectionString = ‘Server=localhost;Database=VMDB;Trusted_Connection=true’
$sql = ‘SELECT name,physical_name FROM sys.database_files’
Invoke-Sqlcmd -ConnectionString $ConnectionString -Query $sql

name physical_name
—- ————-
VMDB C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\VMDB.mdf
VMDB_log C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\VMDB_log.ldf

PS C:\Users> $results=Invoke-Sqlcmd -ConnectionString $ConnectionString -Query $sql

PS C:\Users> $results

name physical_name
—- ————-
VMDB C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\VMDB.mdf
VMDB_log C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\VMDB_log.ldf

PS C:\Users> $results.name
VMDB
VMDB_log

PS C:\Users> $results.name[0]
VMDB

PS C:\Users> $ConnectionString = ‘Server=localhost;Database=VMDB;’

PS C:\Users> Invoke-Sqlcmd -ConnectionString $ConnectionString -Query $sql
Invoke-Sqlcmd : Login failed for user ”.
At line:1 char:1
+ Invoke-Sqlcmd -ConnectionString $ConnectionString -Query $sql
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (:) [Invoke-Sqlcmd], SqlException
+ FullyQualifiedErrorId : SqlExectionError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

Invoke-Sqlcmd :
At line:1 char:1
+ Invoke-Sqlcmd -ConnectionString $ConnectionString -Query $sql
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : ParserError: (:) [Invoke-Sqlcmd], ParserException
+ FullyQualifiedErrorId : ExecutionFailureException,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

PS C:\Users> $filename = read-host “Enter database file logical name: ”
Enter database file logical name: : VMDB_log

PS C:\Users> $filename
VMDB_log

PS C:\Users> $filename = read-host “Enter database file logical name”
$ConnectionString = ‘Server=localhost;Database=VMDB;Trusted_Connection=true’
$sql = ‘SELECT name,physical_name FROM sys.database_files WHERE name = $filename’
$results = Invoke-Sqlcmd -ConnectionString $ConnectionString -Query $sql
Enter database file logical name: VMDB_log
Invoke-Sqlcmd : Invalid pseudocolumn “$filename”.
At line:4 char:12
+ $results = Invoke-Sqlcmd -ConnectionString $ConnectionString -Query $ …
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (:) [Invoke-Sqlcmd], SqlPowerShellSqlExecutionException
+ FullyQualifiedErrorId : SqlError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

PS C:\Users> $filename = read-host “Enter database file logical name”
$ConnectionString = ‘Server=localhost;Database=VMDB;Trusted_Connection=true’
$sql = ‘SELECT name,physical_name FROM sys.database_files WHERE name = ‘ + $filename
$results = Invoke-Sqlcmd -ConnectionString $ConnectionString -Query $sql
Enter database file logical name: VMDB_log
Invoke-Sqlcmd : Invalid column name ‘VMDB_log’.
At line:4 char:12
+ $results = Invoke-Sqlcmd -ConnectionString $ConnectionString -Query $ …
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (:) [Invoke-Sqlcmd], SqlPowerShellSqlExecutionException
+ FullyQualifiedErrorId : SqlError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

PS C:\Users> $sql
SELECT name,physical_name FROM sys.database_files WHERE name = VMDB_log

PS C:\Users> $sql = ‘SELECT name,physical_name FROM sys.database_files WHERE name = ”$filename”
The string is missing the terminator: ‘.
+ CategoryInfo : ParserError: (:) [], ParentContainsErrorRecordException
+ FullyQualifiedErrorId : TerminatorExpectedAtEndOfString

PS C:\Users> $sql = ‘SELECT name,physical_name FROM sys.database_files WHERE name = ”’$filename”’
The string is missing the terminator: ‘.
+ CategoryInfo : ParserError: (:) [], ParentContainsErrorRecordException
+ FullyQualifiedErrorId : TerminatorExpectedAtEndOfString

PS C:\Users> $sql = ‘SELECT name,physical_name FROM sys.database_files WHERE name = `’$filename`’
At line:1 char:74
+ … name,physical_name FROM sys.database_files WHERE name = `’$filename`’
+ ~~~~~~~~~~~
Unexpected token ‘$filename`” in expression or statement.
At line:1 char:83
+ … name,physical_name FROM sys.database_files WHERE name = `’$filename`’
+ ~~
Unexpected token ‘`” in expression or statement.
+ CategoryInfo : ParserError: (:) [], ParentContainsErrorRecordException
+ FullyQualifiedErrorId : UnexpectedToken

PS C:\Users> $sql = ‘SELECT name,physical_name FROM sys.database_files WHERE name = `’$filename`”
The string is missing the terminator: ‘.
+ CategoryInfo : ParserError: (:) [], ParentContainsErrorRecordException
+ FullyQualifiedErrorId : TerminatorExpectedAtEndOfString

PS C:\Users> $temp = ‘this is ” a quote’

PS C:\Users> $temp
this is ‘ a quote

PS C:\Users> $sql = ‘SELECT name,physical_name FROM sys.database_files WHERE name = ”$filename”
The string is missing the terminator: ‘.
+ CategoryInfo : ParserError: (:) [], ParentContainsErrorRecordException
+ FullyQualifiedErrorId : TerminatorExpectedAtEndOfString

PS C:\Users> $sql = ‘SELECT name,physical_name FROM sys.database_files WHERE name = ”$filename”’

PS C:\Users> $sql
SELECT name,physical_name FROM sys.database_files WHERE name = ‘$filename’

PS C:\Users> $filename = read-host “Enter database file logical name”
$ConnectionString = ‘Server=localhost;Database=VMDB;Trusted_Connection=true’
$sql = ‘SELECT name,physical_name FROM sys.database_files WHERE name = ”$filename”’
$results = Invoke-Sqlcmd -ConnectionString $ConnectionString -Query $sql
Enter database file logical name: VMDB

PS C:\Users>
PS C:\Users> $results

PS C:\Users> $sql
SELECT name,physical_name FROM sys.database_files WHERE name = ‘$filename’

PS C:\Users> $filename = read-host “Enter database file logical name”
$ConnectionString = ‘Server=localhost;Database=VMDB;Trusted_Connection=true’
$sql = ‘SELECT name,physical_name FROM sys.database_files WHERE name = ”’ + $filename + ”’
$results = Invoke-Sqlcmd -ConnectionString $ConnectionString -Query $sql
The string is missing the terminator: ‘.
+ CategoryInfo : ParserError: (:) [], ParentContainsErrorRecordException
+ FullyQualifiedErrorId : TerminatorExpectedAtEndOfString

PS C:\Users> $sql = “SELECT name,physical_name FROM sys.database_files WHERE name = ‘$filename'”

PS C:\Users> $sql
SELECT name,physical_name FROM sys.database_files WHERE name = ‘VMDB’

PS C:\Users> $results = Invoke-Sqlcmd -ConnectionString $ConnectionString -Query $sql

PS C:\Users> $results

name physical_name
—- ————-
VMDB C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\VMDB.mdf

PS C:\Users>

 
Leave a comment

Posted by on December 6, 2016 in Uncategorized

 

#sqlsat552 Lincoln, NE recap

Before you call me crazy SQL guy (I think that name is already taken) I had several motives for driving 10 hours to Nebraska. In no particular order:

  • My wife and I work in different states, Lincoln happens to be on the way.
  • We like nature, this one was in a State Park
  • There was a cabin available for 3 nights and at a decent rate that allowed dogs
  • SQL Saturdays are awesome
  • Free sessions and networking
  • Free volunteering (sounds weird but if you have done it, you understand)
  • I’m considering coordinating my own SQL Saturday

However, with every opportunity there is a cost

  • All-in I probably spent $700 I would not have spent otherwise
  • I missed some football games (no big deal)
  • I missed opening day of hunting season (kind of a big deal)

Here are some of my notes from the sessions I attended

Getting Started with Extended Events – Andy Galbraith
Switching from a working solution to something new when you don’t necessarily have to can cause some anxiety. This anxiety causes paralysis. Sometimes we all need a little nudge to really get going.
I was a little confused about the statement that extended events fire synchronously which causes them to be lightweight. It is a little bit of a contradiction I still have to sort out but I think I understand that some parts are always running, and you can asynchronously choose to record them.
The key bits are packages which contain events, actions which is the info you track, and predicates which filters like a where clause
The event file is a .xel file and is least impactful one of a few ways to save off the information
System_health is like the default trace and always running, don’t modify this.
Use caution which what events you pick and where you store the events, query plans can cause a lot of overhead and storing in the buffer can bloat in memory
Set state=start to make the XE work

Get Your Optimizer to Give up All Its Secrets – Brian Hansen
Brian had a nice organized flow which made me compare his style to more of a college professor
Logical order of operation is important and starts with From to ON
Intellisense doesn’t work until you have FROM
He pointed out some confusion with select meaning something different in Algebra(it’s a filter). In Algebra a projection is like a select
The query tree is the output of the algebrizer and he had a nifty app to visual query trees
Distinct is like a group by
Contant scans are usually good because they can help avoid data access – ex: where clause A>50 and A<50

Tips and Tricks for Performance Troublshooting – Phillip Podhrasdsky
Phillip had a faster pace and covered a lot of ground for an intro session.
Query Plans are key along with statistics. Stats have 200 levels, so you might not get stats for every item in a “colors” table. Creating additional filtered stats can help with this issue

Query Plan Deep Dive – Kevin Boles
Kevin is a great speaker and had a well-organized slide deck.
Ref: Paul White – cross apply blog post
Spot Nested loop joins can be inefficient if a large data pipe is on top
The optimizer will sometimes choose to sort the data to do some joins
Merge join works well if there is order to both tables
Hash joins – the table is built in memory and will spill to tempdb, works well if not ordered
Stream aggregates require order (non-blocking)
Fast first – can cause more overall work, but create a better user experience for poorly tuned query
Asserts- check things like True or False conditions (ex: finding a key to delete)
Pick fill factor intelligently
Tsqlt.org
ADO.net timeout is 60sec ish
Optimize for unknown or recompile are options if you run into sniffing issues
UDFs will generate warnings in the query plans and cause some bad estimates

Dammit Jim! Dr McCoy’s Field Guide to system_health (and the default trace) – Ed Leighton-Dick
Finding answers to “who done it” with the things that record data by default
System_health is XE and trace if profiler – they both get trimmed after a while

Afterwards we met up at one of the Peter Kiewit Lodge cabins for some drinks and food. I talked to the organizer a bit and got some great information from volunteering and the after party to help me consider organizing my own SQLSaturday.
I had a great time and look forward to attending more SQLSaturday events next year.

 

 
Leave a comment

Posted by on December 3, 2016 in Uncategorized