{"id":926,"date":"2014-07-28T00:01:00","date_gmt":"2014-07-28T00:01:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2014\/07\/28\/troubleshooting-a-powershell-sql-server-script\/"},"modified":"2014-07-28T00:01:00","modified_gmt":"2014-07-28T00:01:00","slug":"troubleshooting-a-powershell-sql-server-script","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/troubleshooting-a-powershell-sql-server-script\/","title":{"rendered":"Troubleshooting a PowerShell SQL Server Script"},"content":{"rendered":"

Summary<\/b>: Microsoft Scripting Guy, Ed Wilson, helps a reader with a Windows PowerShell and SQL Server scripting issue.<\/span><\/p>\n

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<\/a> email this morning, and I ran across the following question:<\/p>\n

\"Hey, Hey, Scripting Guy! Is there good training content available for Windows PowerShell basics and an introduction to Windows PowerShell for SQL Server?<\/p>\n

—PB<\/p>\n

I engaged in a rather long conversation. This post is the result of that conversation.<\/p>\n

Round one:<\/h3>\n

\"<\/a> Hello PB,<\/p>\n

Sure! There are lots of great resources for learning Windows PowerShell and SQL Server scripting. Check out these Hey, Scripting Guy! Blog posts<\/a>. I have more than three dozen posts there.<\/p>\n

Round two:<\/h3>\n

Thanks Ed!<\/p>\n

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.<\/p>\n

\"<\/a> My Windows PowerShell Scripting<\/a> 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.<\/p>\n

Round three:<\/h3>\n

Hi Ed,<\/p>\n

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:<\/p>\n

foreach ($Row in $SrvList.Rows)<\/p>\n

{<\/p>\n

  write-host ("value is : ", $Row[0][1])<\/p>\n

}<\/p>\n

\"<\/a> $srvlist.rows<\/b> contains a collection of rows. $row<\/b> is your enumerator, and you should be able to list $row<\/b> instead of trying to index into the collection. Inside your loop, <\/span>$row<\/b> will have members, you can use <\/span>$row<\/b> | <\/span>Get-Member<\/b> to find those members, which will probably include column headings. Access the items via the members instead of by index number. <\/span><\/p>\n

You can also directly access rows by using <\/span>$srvlist<\/b>. Use <\/span>$srvlist.rows[0]<\/b> for the first row, and pipe this to <\/span>Get-Member<\/b> to see the members. <\/span>Hope this helps.<\/span><\/p>\n

Round four:<\/h3>\n

Hi Ed,<\/p>\n

I tried $SrvList.Rows[0]  | Get-Member<\/b>, and it is giving the following error message:<\/p>\n

\"Image<\/a><\/p>\n

Here’s the full script:<\/p>\n

Function GetServerList<\/strong><\/p>\n

{<\/p>\n

$sqlConnection = new-object System.Data.SqlClient.SqlConnection "Server=localhost;Database=SSISPROTOSERVERS;Integrated Security=sspi"<\/p>\n

$sqlConnection.Open()<\/p>\n

 <\/p>\n

#Create a command object<\/p>\n

$sqlCommand = $sqlConnection.CreateCommand()<\/p>\n

$sqlCommand.CommandText = "select * from ServerList"<\/p>\n

 <\/p>\n

$adapter = New-Object System.Data.SqlClient.SqlDataAdapter $sqlcommand<\/p>\n

$dataset = New-Object System.Data.DataSet<\/p>\n

 <\/p>\n

$adapter.Fill($dataSet) | out-null<\/p>\n

 <\/p>\n

# Close the database connection<\/p>\n

$sqlConnection.Close()<\/p>\n

 <\/p>\n

$dataTable = new-object "System.Data.DataTable" "ServerList"<\/p>\n

$dataTable = $dataSet.Tables[0]<\/p>\n

 <\/p>\n

return $dataTable<\/p>\n

}<\/p>\n

$SrvList = GetServerList<\/p>\n

 <\/p>\n

Write-Host("There are ", $SrvList.Count, " SQL Servers in the list")<\/p>\n

$SrvList | format-table -AutoSize<\/p>\n

 <\/p>\n

foreach ($Row in $SrvList.Rows)<\/p>\n

{<\/p>\n

  $SrvList.Rows[0]  | Get-Member <\/p>\n

}<\/p>\n

What does $dataTable<\/b> return? What happens when we pipe it to Get-Member<\/b>? What does $rows<\/b> return?<\/p>\n

\"<\/a> You should be able to type $dataTable<\/b>, and it will return your DataTable<\/b> object. $rows<\/b> should return the Rows<\/b> object. If they don't, you are not making the connection and returning proper data. You need to backtrack all the way to <\/span>$sqlconnection<\/b>, which should return a connection object. Pipe it to <\/span>Get-Member<\/b> also.<\/span><\/p>\n

Remember your scoping, so that $sqlconnection<\/b> only exists inside the function. You need to add a line after you make the connection:<\/p>\n

$sqlconnection | Get-Member<\/p>\n

You do not need Return<\/b> because Windows PowerShell always returns an object from a function, but Return<\/b> makes the script easier to read, so it is no problem leaving it there.<\/p>\n

Round five:<\/h3>\n

Hi Ed,<\/p>\n

These two statements return fine with the actual data:<\/p>\n

Write-Host("There are ", $SrvList.Count, " SQL Servers in the list")<\/p>\n

2<\/p>\n

$SrvList | format-table -AutoSize<\/p>\n

\"Image<\/a><\/p>\n

But I can’t display the rows.<\/p>\n

foreach ($Row in $SrvList.Rows)<\/p>\n

{<\/p>\n

      write-host ("Rows : ")<\/p>\n

      $Row<\/p>\n

}<\/p>\n

\"<\/a> Pipe SrvList<\/b> to Get-Member<\/b>. You will see at least two properties: ServerName<\/b> and InstanceName<\/b>. To access the name, you need to use $row.ServerName<\/b>.<\/p>\n

Round six:<\/h3>\n

Thanks a lot Ed! Yup. Using $row.ServerName<\/b> worked!<\/p>\n

~PB<\/p>\n

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.<\/p>\n

I invite you to follow me on Twitter<\/a> and Facebook<\/a>. If you have any questions, send email to me at scripter@microsoft.com<\/a>, or post your questions on the Official Scripting Guys Forum<\/a>. See you tomorrow. Until then, peace.<\/p>\n

Ed Wilson, Microsoft Scripting Guy<\/b> <\/span><\/p>\n","protected":false},"excerpt":{"rendered":"

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 […]<\/p>\n","protected":false},"author":596,"featured_media":87096,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1],"tags":[3,45],"class_list":["post-926","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-scripting-guy","tag-windows-powershell"],"acf":[],"blog_post_summary":"

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 […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/926","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/users\/596"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/comments?post=926"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/926\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media\/87096"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media?parent=926"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=926"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=926"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}