Allen White

Accessing SQL Server data through PowerShell

I've been working with Dan, who handles our ADS administration, on extracting data from our employee tables, which are fed from an extract from our HR system, to populate the contact information in ADS from the HR data directly. This allows us to disable accounts in AD when the employees have been terminated and create accounts for new employees automatically. PowerShell is the ideal tool for this task, and Dan figured out how to populate the ADS data from a spreadsheet extracted from the database table containing the required data, but didn't know how to query the database directly. I spent a bit of time polling the web for the "best" way to do this, and here's the script I sent him:

$cn = new-object system.data.SqlClient.SqlConnection("Data Source=MyServer;Integrated Security=SSPI;Initial Catalog=AdventureWorks");
$ds = new-object "System.Data.DataSet" "dsPersonData"
$q = "SELECT [ContactID]"
$q = $q + "      ,[FirstName]"
$q = $q + "      ,[LastName]"
$q = $q + "      ,[EmailAddress]"
$q = $q + "      ,[Phone]"
$q = $q + "  FROM [AdventureWorks].[Person].[Contact]"
$da = new-object "System.Data.SqlClient.SqlDataAdapter" ($q, $cn)
$da.Fill($ds)

$dtPerson = new-object "System.Data.DataTable" "dtPersonData"
$dtPerson = $ds.Tables[0]
$dtPerson | FOREACH-OBJECT { " " + $_.ContactID + ": " + $_.FirstName + ", " + $_.LastName + ", " + $_.EmailAddress + ", " + $_.Phone }

Of course, it's been modified to extract data from AdventureWorks, and I built a stored procedure to return the data he wanted rather than code the query directly in the script, but I just wanted to share how easy it is to pull data directly from a database table in PowerShell. Dan can now pipe the resultset directly into the ADS database, and when our coworkers use Outlook they can view properties for anyone in our organization and see their address, phone number, etc.

In another post I'll demonstrate how to use PowerShell to manage servers via SMO.

Allen

PowerShell and SMO

I started seriously trying to learn the new Windows PowerShell scripting language this week. I started with the TechNet Webcast: An Overview of Windows PowerShell (Level 200) that Don Jones did in December. I worked with Don at Microsoft on the MCITP:Database Developer Objective Domain, and he's got a great presentation style, so that complicated things become clear quickly. I then started searching for sites that specifically used SMO and PowerShell together.

After remembered having read about using SMO from PowerShell in Bob Beauchemin's blog, I downloaded his sample code and found the directory with his scripts. I had difficulty getting them to work, so started searching again.

The next thing I found was in Dan Sullivan's blog, where he used a script to initialize the environment and create some functions he calls PowerSMO. His code disallows its use in production code, though, so I continued my search.

I then found Rob Farley's blog, and he presented a much simpler script, which I was able to play with and actually start extracting information about servers other than my local server using SMO.

That, in fact, is my litmus test of a good demo - does it work against a server other than my local server? In my presentation at PASS on SMO I cautioned the attendees that my examples were in VB.Net because VBScript only allows you to connect with your local default server. Peter Ward had a session on SMO two days later where he told those present that there was "no problem" using VBScript to script SMO tasks, and then proceeded to demonstrate his code, connecting to his local default server. Don't get me wrong, I like Peter, and have learned from his sample code, but I felt he did a disservice to those present in his session.

So, now I'm working on building some useable scripts using PowerShell and SMO. I hope to put together a presentation once I get comfortable with the new language because I see amazing possibilities with it.

Allen

Go Buckeyes!!!

I've been working on developing, and now implementing, automated database maintenance plans that regenerate every cycle based on the existing databases on my server, but today that's not important.

GO BUCKS!

Stomp them Gators!

That is all.

Allen