SQLvariations: SQL Server, a little PowerShell, maybe some Hyper-V Rotating Header Image

PSSnapin

Basic SQL Querying From PowerShell

Feb 22nd, 2010
by Aaron Nelson.
1 comment

I promise to make this worth your time.  (Oops, I better make this quick.)

Alright so with PowerShell if you open it up and just run this code you’ll be able to query your database right from the start.  Anyone who has worked with data systems for a while will notice right away the first line of this script kinda looks like a connection string for an application.  I’m sure the fact that PowerShell was built on .Net has something to do with that.  (But I’m not a developer so I don’t really know, it could be just a coincidence for all I know.  

$conn = New-Object System.Data.SqlClient.SqlConnection(“Data Source=YourMachineName\YourInstanceName; InitialCatalog=master; Integrated Security=SSPI”)
$conn.Open()
$cmd1 = $conn.CreateCommand()
$cmd1.CommandType = [System.Data.CommandType]::StoredProcedure
$cmd1.CommandText =“sp_databases”
$data = $cmd1.ExecuteReader()
$dt = new-object “System.Data.DataTable”
$dt.Load($data)
$dt | format-table #PipeLining is Awesome!
$conn.Close()

image

But this code is so much Nicer!

If you’ve got SSMS 2008 installed on your machine all you need to do is fire up the ISE and add the SnapIn like I talked about in my last blog.  Then you can use invoke-sqlcmd and pass it your query.  As you see this is a lot easier and cleaner than all of the code above was.

#Go ahead and add the SQL Snapins
add-pssnapin SqlServerCmdletSnapin100

#sp_databases | format-table
invoke-sqlcmd -query “sp_databases” -database master -serverinstance WIN7\Kilimanjaro | format-table

image

Conclusion:

Unless you really like writing 6 or 7 extra lines of code every time you query your database, load up the Snapin and use invoke-sqlcmd.

SQLServer:\SQL\Databases\Tables> Dir

Feb 17th, 2010
by Aaron Nelson.
1 comment
Getting started talking to your SQL Servers in the PowerShell 2.0 ISE

By now hopefully you’ve noticed that SQL Server 2008 comes with PowerShell all over the place.  Just about anything you click on in Object Explorer has “Start PowerShell” and you can even execute PowerShell steps in SQL Agent Jobs. 

You may be wondering why on Earth you would want to “Start PowerShell” in the first place.  I’ll show you one thing today and then expand on that in later blog posts but today I wanted to tell you how to run PowerShell in something a little more organized than a command prompt window.  The first thing I’d like you to do is open up SSMS, connect to an instance and Right-Click the Databases folder > then select Start PowerShell.  A command prompt window should open up and from there you can talk to your SQL Server instance as if was a drive on your machine:

LaunchSQLPS

The SQLPS window that just opened up is PowerShell 1.0 with a special ‘expansion pack’ that allows it to talk to SQL Server in a way that PowerShell alone can’t.  So now that we’ve got that covered I want to show you how to do the same thing in PowerShell 2.0’s ISE. 

Go to Start > All Programs > Accessories > Windows PowerShell > Windows PowerShell ISE

Copy the code from the bottom and step through it like I did in this picture.  When you’re done you will be able to drill down to tables like they were just folders directories on your hard drive.  If you aren’t running Windows 7 (upgrade already!) check this post and see if you’re operating system is eligible.

ISE_SQL

When you get to the part where you do “get-psdrive” for the second time you should now see the SQL Server on your local machine.  If you haven’t figured out yet, this is only going to work if you have SQL Server installed on your local machine. 

#Before
get-psdrive

#Snapins that are running
get-pssnapin
#Snapins that you can load
get-pssnapin -registered

#Go ahead and add the SQL Snapins
add-pssnapin SqlServerCmdletSnapin100
add-pssnapin SqlServerProviderSnapin100

#New Resource
get-psdrive

#What can we do with that?
cd SQLSERVER:\SQL\YourComputerName\YourInstanceName\DATABASES\ADVENTUREWORKS\TABLES

Now that we have done all of that we can do something that I have seen mentioned several places but I think that Allen White’s blog post explains it the best (here’s my version of it).  You can now create a variable and populate it with one the DDL structures in your database and from there generate a Create script for it like so:

$PTH = get-item Production.TransactionHistory
$PTH.Script()

Your output should look something like this:
PS SQLSERVER:\sql\WIN7\DATABASES\ADVENTUREWORKS\TABLES> $PTH = get-item Production.TransactionHistory
$PTH.Script()
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Production].[TransactionHistory](
 [TransactionID] [int] IDENTITY(100000,1) NOT NULL,
 [ProductID] [int] NOT NULL,
 [ReferenceOrderID] [int] NOT NULL,
 [ReferenceOrderLineID] [int] NOT NULL,
 [TransactionDate] [datetime] NOT NULL,
 [TransactionType] [nchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
 [Quantity] [int] NOT NULL,
 [ActualCost] [money] NOT NULL,
 [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]

And that’s it for this edition of how to do something when you open up the PowerShell 2.0 ISE.  Next we’ll do something more useful I promise.