Troubleshooting a PowerShell SQL Server Script

Doctor Scripto

July 28th, 20140 0

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:

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")

$SrvList | format-table -AutoSize

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