Troubleshooting a PowerShell SQL Server Script

Troubleshooting a PowerShell SQL Server Script

  • Comments 5
  • Likes

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 scripter@microsoft.com email this morning, and I ran across the following question:

Hey, Scripting Guy! Question Hey, Scripting Guy! Is there good training content available for Windows PowerShell basics and an introduction to Windows PowerShell for SQL Server?

—PB

I engaged in a rather long conversation. This post is the result of that conversation.

Round one:

  Hello PB,

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.

Round two:

Thanks Ed!

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.

Round three:

Hi Ed,

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[0][1])

}

  $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[0] for the first row, and pipe this to Get-Member to see the members. Hope this helps.

Round four:

Hi Ed,

I tried $SrvList.Rows[0]  | Get-Member, and it is giving the following error message:

Image of command output

Here’s the full script:

Function GetServerList

{

$sqlConnection = new-object System.Data.SqlClient.SqlConnection "Server=localhost;Database=SSISPROTOSERVERS;Integrated Security=sspi"

$sqlConnection.Open()

 

#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

$sqlConnection.Close()

 

$dataTable = new-object "System.Data.DataTable" "ServerList"

$dataTable = $dataSet.Tables[0]

 

return $dataTable

}

$SrvList = GetServerList

 

Write-Host("There are ", $SrvList.Count, " SQL Servers in the list")

$SrvList | format-table -AutoSize

 

foreach ($Row in $SrvList.Rows)

{

  $SrvList.Rows[0]  | 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.

Round five:

Hi Ed,

These two statements return fine with the actual data:

Write-Host("There are ", $SrvList.Count, " SQL Servers in the list")

2

$SrvList | format-table -AutoSize

Image of command output

But I can’t display the rows.

foreach ($Row in $SrvList.Rows)

{

      write-host ("Rows : ")

      $Row

}

  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.

Round six:

Thanks a lot Ed! Yup. Using $row.ServerName worked!

~PB

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 scripter@microsoft.com, or post your questions on the Official Scripting Guys Forum. See you tomorrow. Until then, peace.

Ed Wilson, Microsoft Scripting Guy 

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment
  • Good conversation. Related to the formal training on SQL Server + PowerShell, one can look at Robert Cain's PluralSight course - http://pluralsight.com/training/courses/TableOfContents?courseName=powershell-and-sql-server&highlight=robert-cain_powershell-and-sql-server-m6-sqlpsexample*3!robert-cain_powershell-and-sql-server-m1-dbatasks#powershell-and-sql-server-m6-sqlpsexample

  • There are numerous places to learn Powershell + SQL Server but here is another link if you like "reading" instead of "watching" PluralSight videos
    http://www.sqlservercentral.com/stairway/91327/

  • 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?

  • thank you