Checking flag sometimes false\positive result

Question

Checking flag sometimes false\positive result

SSCrazy

Points: 2107

August 14, 2024 at 1:00 pm

I have this script checking a SQL table looking for a result of True in a table. 99% of the times it works as designed, but on certain occasions it does not. I check this with a scheduler every 5 minutes. It sometimes produces a result of true in which hasn't been set to that condition. What else can I do in script to ensure accuracy every time?

Flag False result:

Connection Information:

Connection to database successful.

0

Still Waiting

On Occasions I get this result which is the false\postive I need to try and fix

Connection Information:

Connection to database successful.

Found

Normal is flag is true:

Connection Information:

Connection to database successful.

1

Found

isPending

---------

1

In the example of the false\positive you see it only returns message "FOUND"

Thanks ..

$Connection = new-object system.data.sqlclient.sqlconnection #Set new object to connect to sql database
$Connection.ConnectionString ="server=$DBServer;database=$databasename;trusted_connection=True"

$SqlCmd = New-Object System.Data.SqlClient.SqlCommand #setting object to use sql commands

$SqlQuery = @"

Select
*
from
crtIsPending
where
Ispending = 1

"@


$Connection.open()
Write-host "Connection to database successful." 
$SqlCmd.CommandText = $SqlQuery
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$SqlCmd.Connection = $Connection
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$Connection.Close()
$DataSet.Tables[0]



  if ($DataSet.Tables[0] -ne 0) 
      {
      $DataSet.Tables[0]   
      write-host 'Found'
      }   
  else 
      {
      write-host 'Still Waiting' 
      }

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

ratbak Default port Points: 1411 More actions · Answer 1

What is the transaction isolation level for the database? I haven't found yet the default used by Powershell.

Bruin SSCrazy Points: 2107 More actions · Answer 2

Bruin

SSCrazy

Points: 2107

August 14, 2024 at 2:18 pm

#4444905

read-commited

Bruin SSCrazy Points: 2107 More actions · Answer 3

Any ideas on how I can maybe put try\catch to make sure nothing is hanging up?

Bruin SSCrazy Points: 2107 More actions · Answer 4

Or could I put a loop and check it twice to validate switch?

Thanks.

Checking flag sometimes false\positive result

Cookies on SQLServerCentral