title: Powershell - Invalid attempt to call FieldCount - Reader Closed?
body: <p>I have a PowerShell script that I use to loop over a list of SQL Servers and pull extended event data from those servers. It works great on 95% of my SQL Servers but about 5% of my SQL Servers I get this error:  </p>
  
  <blockquote>
    <p>Write-DataTable:System.Management.Automation.MethodInvocationException:
    Exception calling "WriteToServer" with "1" argument(s): </p>
    
    <p>"Invalid attempt to call FieldCount when reader is closed."
    System.InvalidOperationException: Invalid attempt to call FieldCount
    when reader is closed.</p>
    
    <p>at System.Data.SqlClient.SqlDataReader.get_FieldCount() at
    System.Data.SqlClient.SqlBulkCopy.WriteToServer(IDataReader reader) at
    CallSite.Target(Closure , CallSite , Object , Object )
    --- End of inner exception stack trace --- at System.Management.Automation.ExceptionHandlingOps.CheckActionPreference(FunctionContext
    funcContext, Exception exception) at
    System.Management.Automation.Interpreter.ActionCallInstruction`2.Run(InterpretedFrame
    frame) at
    System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame
    frame) at
    System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame
    frame).Message</p>
  </blockquote>
  
  <p>I don't understand why my script works on most SQL Servers but not all.</p>
  
  <p>My PowerShell script:</p>
  
  <pre><code>#Create your SQL connection string to your central collection database  
  $ServerAConnectionString = "Data Source=SourceServer;Initial Catalog=SourceDatabase;Integrated Security=True;" $ServerAConnection = new-object system.data.SqlClient.SqlConnection($ServerAConnectionString);
  
  #Create a Dataset to hold the DataTable from DBServers 
  $dataSet = new-object "System.Data.DataSet" "DBServers" $query = "SET NOCOUNT ON;" $query = $query + " SELECT ServerName, FilePath " $query = $query
          + " FROM dbo.SourceTable " $query = $query + " WHERE Domain = 'domain'  " $query = $query + " AND XEventQueryEnabled = 1 "
          #write-host $query;
  
  #Create a DataAdapter which youll use to populate the DataSet with the instances to collect from 
  $dataAdapter = new-object "System.Data.SqlClient.SqlDataAdapter" ($query, $ServerAConnection) $dataAdapter.Fill($dataSet) #| Out-Null   $ServerAConnection.Open() $sourceConnection  = New-Object System.Data.SqlClient.SQLConnection($SourceConnectionString)
  
  
  $CollectionTime = date;    foreach($a in $dataSet.Tables[0].Rows) {   write-host "DBServer: " $a.ServerName;       # set connection string for the remote server
      $SourceConnectionString = "Server="+$a.Servername+"; Initial Catalog = master; integrated security = true"    # set connection string for the central collection server
      $DestinationConnectionString = "Data Source=RepositoryServer;Initial Catalog=RepositoryDatabase;Integrated Security=True"
       try
      {
      $sql =  "        WITH cte (event_data) as "
      $sql =  $sql + " (SELECT CAST(event_data AS XML) AS event_data "
      $sql =  $sql + " FROM sys.fn_xe_file_target_read_file(N'"+$a.FilePath+"MyXevents*.xel', NULL, NULL, NULL)) "
      $sql =  $sql + " SELECT @@SERVERNAME, * FROM cte "
      $sql =  $sql + " WHERE  SWITCHOFFSET(event_data.value('(event/@timestamp)[1]', 'datetimeoffset'),DATEPART(Tzoffset, SYSDATETIMEOFFSET())) &gt; '2/1/16' "
         try{
              write-host "Collecting XEvent XML Data from Remote SQL Server.";
              $tableName = "RepositoryTable"
              $sourceConnection.ConnectionString = $SourceConnectionString
              $sourceConnection.open()
              $commandSourceData  = New-Object system.Data.SqlClient.SqlCommand($sql,$sourceConnection)
  
              $reader = $commandSourceData.ExecuteReader()
  
              $commandSourceData.CommandTimeout = '300'
          }
          Catch
          {
              $ex1 = $_.Exception.Message
  
          }  # end CATCH block for bad file/offset
              write-host "Inserting XEvent XML Data to Repository.";
              $bulkCopy = new-object ("Data.SqlClient.SqlBulkCopy") $DestinationConnectionString
              $bulkCopy.DestinationTableName = $tableName
              $bulkCopy.BatchSize = 5000
              $bulkCopy.BulkCopyTimeout = 0
              $bulkCopy.WriteToServer($reader)
  
          $reader.Close()    }
      catch
      {
          $ex = $_.Exception
          Write-Host "Write-DataTable$($connectionName):$ex.Message"
      }
      finally
      {
          write-host "Finished Collecting XEvent XML Data.";
         # $reader.close()
      }
      $sourceConnection.close() }
  #Close the connection as soon as you are done with it $ServerAConnection.Close()
  </code></pre>