CODEWITHSUNDEEP
sqlsql-serverpowershellnocount
user1536849
user1536849

Reputation: 11

Powershell and SQL Server - set nocount

I am writing a powershell function to retrieve a list of students from our database but the first item returned is always the number of records. I can't figure out a way to incorporate the set nocount on into the the script - any ideas?

The function so far is:

Function CheckMIS{
  $Table = new-object System.Data.DataTable
  $sqlConn = new-object System.Data.SqlClient.SqlConnection("Server=blah blah")
  $adapter = new-object System.Data.SqlClient.SqlDataAdapter(
     "select txtSchoolID,
             intSystemStatus,
             txtForename, txtSurname,
             txtForm
      from TblPupils
      where intSystemStatus = 1 ",$sqlConn)
  $adapter.Fill($Table)
  $sqlConn.Close()
  write-output $table
}

It returns a lovely table - but the first line is always the number of records first. I just need to suppress that output.

Upvotes: 1

Views: 1512

Answers (2)

Bruce
Bruce

Reputation: 1623

You could catch the rowcount for later use.

$rowCount = $adapter.Fill($Table)

Or just ignore it.

$adapter.Fill($Table)  | Out-Null

Adding "Set Nocount On; select txtSchoolID,"... didn't have any effect in my test.

Upvotes: 1

dpw
dpw

Reputation: 1586

You should be able to just add SET NOCOUNT ON to your SQL.

i.e. SET NOCOUNT ON select txtSchoolId, intSystemStatus, txtForename, txtSurname, txtForm from TblPupils where intSystemStatus = 1

Upvotes: 0

Related Questions