Learn about Windows PowerShell
Summary: Microsoft Scripting Guy, Ed Wilson, helps a reader with a Windows PowerShell and SQL Server scripting issue.
Microsoft Scripting Guy, Ed Wilson, is here. This morning I am sipping a nice cup of Darjeeling tea. I added a bit of peppermint leaves, spearmint leaves, orange peel, licorice root, and a cinnamon stick. The result is very refreshing, with just a little bit of a kick to help jump start one's scripting. I was reviewing my email@example.com email this morning, and I ran across the following question:
Hey, Scripting Guy! Is there good training content available for Windows PowerShell basics and an introduction to Windows PowerShell for SQL Server?
I engaged in a rather long conversation. This post is the result of that conversation.
Sure! There are lots of great resources for learning Windows PowerShell and SQL Server scripting. Check out these Hey, Scripting Guy! Blog posts. I have more than three dozen posts there.
I went through some of the links and they were very useful. But I am looking for some good training content for Windows PowerShell basics and an introduction to Windows PowerShell for SQL Server.
My Windows PowerShell Scripting page in the Script Center has over a dozen hours of training for Windows PowerShell basics, but there is nothing specific to SQL Server and Windows PowerShell other than the information I gave you earlier.
I went through your links and learned a quite a bit. Thanks for the good references. I’m running into an issue when reading data from a data table. Can you please help me find what I’m doing wrong here:
foreach ($Row in $SrvList.Rows)
write-host ("value is : ", $Row)
$srvlist.rows contains a collection of rows. $row is your enumerator, and you should be able to list $row instead of trying to index into the collection. Inside your loop, $row will have members, you can use $row | Get-Member to find those members, which will probably include column headings. Access the items via the members instead of by index number.
You can also directly access rows by using $srvlist. Use $srvlist.rows for the first row, and pipe this to Get-Member to see the members. Hope this helps.
I tried $SrvList.Rows | Get-Member, and it is giving the following error message:
Here’s the full script:
$sqlConnection = new-object System.Data.SqlClient.SqlConnection "Server=localhost;Database=SSISPROTOSERVERS;Integrated Security=sspi"
#Create a command object
$sqlCommand = $sqlConnection.CreateCommand()
$sqlCommand.CommandText = "select * from ServerList"
$adapter = New-Object System.Data.SqlClient.SqlDataAdapter $sqlcommand
$dataset = New-Object System.Data.DataSet
$adapter.Fill($dataSet) | out-null
# Close the database connection
$dataTable = new-object "System.Data.DataTable" "ServerList"
$dataTable = $dataSet.Tables
$SrvList = GetServerList
Write-Host("There are ", $SrvList.Count, " SQL Servers in the list")
$SrvList | format-table -AutoSize
$SrvList.Rows | Get-Member
What does $dataTable return? What happens when we pipe it to Get-Member? What does $rows return?
You should be able to type $dataTable, and it will return your DataTable object. $rows should return the Rows object. If they don't, you are not making the connection and returning proper data. You need to backtrack all the way to $sqlconnection, which should return a connection object. Pipe it to Get-Member also.
Remember your scoping, so that $sqlconnection only exists inside the function. You need to add a line after you make the connection:
$sqlconnection | Get-Member
You do not need Return because Windows PowerShell always returns an object from a function, but Return makes the script easier to read, so it is no problem leaving it there.
These two statements return fine with the actual data:
But I can’t display the rows.
write-host ("Rows : ")
Pipe SrvList to Get-Member. You will see at least two properties: ServerName and InstanceName. To access the name, you need to use $row.ServerName.
Thanks a lot Ed! Yup. Using $row.ServerName worked!
That is all there is to using Windows PowerShell to query from a SQL Server database. Join me tomorrow when I will talk about more cool stuff.
I invite you to follow me on Twitter and Facebook. If you have any questions, send email to me at firstname.lastname@example.org, or post your questions on the Official Scripting Guys Forum. See you tomorrow. Until then, peace.
Ed Wilson, Microsoft Scripting Guy
Good conversation. Related to the formal training on SQL Server + PowerShell, one can look at Robert Cain's PluralSight course -
There are numerous places to learn Powershell + SQL Server but here is another link if you like "reading" instead of "watching" PluralSight videos
I did the two Powershell courses on the Microsoft Virtual Academy. They were very informative.
Right now I am doing virtual course Querying SQL Server 2012 Databases.
I set up a test environment on some Virtual machines on a private network as per the Test Lab Guides, and installed the AdventureWorks sample database. I want to read the employee table and add them to the active directory. The department table would determine
which organizational unit they belong to. One drawback would be a common password for each account? Is that possible to totally script in Powershell? Is it possible/preferable to script it in the Exchange Powershell console?