System.Data.DataRowCollection Rows not functional on some computers

All Replies

• Thursday, February 14, 2013 10:40 PM

   

   
  

  0

  On my computer I can use the following function to access data on an sql server, but when run on other computers the "System.Data.DataRowCollection Rows" property appears to be blank.

  The function works fine until I get to $MyDataSet.Tables.rows. On an XP computer with .net 3.5 sp1 and powershell 2.0 installed the property is blank and on another Win7 computer with .net 4.0 client profile installed it is also blank. Are there other requirements for this?

  function Read-SQLDB(	
      $BEServer = "mydom\myserver",
      $SQLDBName = "mydb",
      $SqlQuery = 'my query'
  )
  {
  #Function to connect to BE servers
  function Connect-SQLDB ($SQLServer,$Database,$Query) {
      $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
      $SqlConnection.ConnectionString = "Server = $SQLServer; Database = $Database; Integrated Security = True"
      $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
      $SqlCmd.CommandText = $Query
      $SqlCmd.Connection = $SqlConnection
      $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
      $SqlAdapter.SelectCommand = $SqlCmd
      $DataSet = New-Object System.Data.DataSet
      $SqlAdapter.Fill($DataSet) | Out-Null
      $SqlConnection.Close()
      $DataSet
  }
  
  $MyDataSet = Connect-SQLDB -SQLServer $BEServer -Database $SQLDBName -Query $SqlQuery
  
  $MyDataSet.Tables.rows
  }

  
  

  The account you are using has no read rights to the data.

  Remove teh Out-Null to see what you are getting back.

  $SqlAdapter.Fill($DataSet) | Out-Null

  
  

  Parameters

  dataSet

  Type: System.Data.DataSet

  A DataSet to fill with records and, if necessary, schema.

  Return Value

  Type: System.Int32
  The number of rows successfully added to or refreshed in the DataSet. This does not include rows affected by statements that do not return rows.
  

  ---

  ¯\_(ツ)_/¯

  • Reply
  • Quote

   
• Friday, February 15, 2013 1:34 PM

   

   
  

  0

  The account definatly has read access as it is the same domain account I'm using from all computers.

  When I get rid of out-null I get back "180" and this is what the dataset returns:

  RemotingFormat          : Xml
  SchemaSerializationMode : IncludeSchema
  CaseSensitive           : False
  DefaultViewManager      : {System.Data.DataViewManagerListItemTypeDescriptor}
  EnforceConstraints      : True
  DataSetName             : NewDataSet
  Namespace               :
  Prefix                  :
  ExtendedProperties      : {}
  HasErrors               : False
  IsInitialized           : True
  Locale                  : en-US
  Site                    :
  Relations               : {}
  Tables                  : {System.Data.DataRow System.Data.DataRow System.Data.
                            DataRow System.Data.DataRow System.Data.DataRow Syste
                            m.Data.DataRow....}
  Container               :
  DesignMode              : False

  When I look at $MyDataSet.Tables it returns all the data from the rows, but when I try $MyDataSet.Tables.Rows I get no output.

  When I look at the data set on my computer where this works I notice it has a property not present on other computers: "ContainsListCollection  : True"

  What am I missing on other computers that would cause the dataset to not have this?

  Is this a powershell 3.0 thing? because the other computers only have 2.0.

  • Edited by red888 Friday, February 15, 2013 1:38 PM
  • Edited by red888 Friday, February 15, 2013 1:40 PM
  •  
  • Reply
  • Quote

   
• Friday, February 15, 2013 2:38 PM

   

   
  

  0

  Ok so I installed powershell 3.0 on the other win7 computer and now its working.

  Now my question is how can I get around using the dataset like this to avoid having to install ps 3.0?

  Here is the way I am currently using this:

  #using the previously posted function:
  $MyDataSet = Connect-SQLDB -SQLServer $BEServer -Database $SQLDBName -Query $SqlQuery
  
  foreach ($row in $MyDataSet.Tables.rows)
  {
  $row.SomeColumn #this is how I am using it
  }
  
  

  
  

  • Reply
  • Quote

   
• Friday, February 15, 2013 5:42 PM

   

   
  

  1

  $MyDataSet.Tables[0].Rows

  Your issue is likely 32/64 bit.  Try running both versions of PowerShell 2.0.

  Unfortunately all of my Win 7 and later system have only PowerShell 3 installed.  I do remember that ther was an issue like this.  I believe it requires you to access the tables as above because a dataset is a collection of tables.

  ---

  ¯\_(ツ)_/¯

  • Marked As Answer by red888 Tuesday, February 19, 2013 3:23 PM
  •  
  • Reply
  • Quote

   
• Tuesday, February 19, 2013 3:22 PM

   

   
  

  0

  "$MyDataSet.Tables[0].Rows" what a simple solution, I feel dumb for not figuring this out.

  The arch of the win7 machines are x86 (they were actually both built from the same image) and running this on a 2008 R2 server had the same results, but just doing "$MyDataSet.Tables[0].Rows" solves my issue across all machines. This now runs on my PS 2.0 and 3.0 boxes without any problems- even the XP computer. Thanks a lot for your help.

  
  

  • Edited by red888 Tuesday, February 19, 2013 3:28 PM
  •  
  • Reply
  • Quote