0

In my PowerShell script, I will query the database using the select statement and it will run every one hour. It will took a quite longer period for completing all the process each time the scheduler executed, so when the next scheduler executed, it might query the old data where its still processing by the previous scheduler. May I know whether any method to prevent this? From what I read (this link) there is a method to achieve it.

Even I changed my query+PowerShell into the following, I found it still not able to achieve the things that I want

$queryAuth2="select * from [StagingDB].[dbo].[UnixAccResetPassword] WITH (HOLDLOCK,READPAST)"
#write-host "The command is : "$queryAuth
$command2=$connection.CreateCommand()

$command2.CommandText=$queryAuth2
$reader2 = $command2.ExecuteReader()
#write-host "The reader is : "$reader
#start-sleep -seconds 180
while ($reader2.read())
{
# write-host "In reader 1"
 for ($i = 0; $i -lt $reader2.FieldCount; $i++)
    {
 #          write-host "In reader 2"
    $pfno=$reader2.GetValue($i)
    write-host "pfno :"$pfno
    }
}

The thing I want to achieve is when the select statement query the 5 records(item1,item2,item3,item4,item5) from table, it will locked until those records finished processed and remove away from table.

Dale K
  • 25,246
  • 15
  • 42
  • 71
Heart Break KID
  • 119
  • 1
  • 2
  • 11
  • Typically you add a flag (bit column) to the table, and when you extract some records for processing the first thing you do is set the flag showing they are "being processed". Then you filter any out with that flag set for future selects. You do need to be careful about setting the flag to ensure 2 processes can't try and set the same flag at the same time. – Dale K Mar 22 '22 at 09:16
  • _[HOLDLOCK](https://learn.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-table?view=sql-server-ver15#arguments) Is equivalent to SERIALIZABLE. For more information, see SERIALIZABLE later in this topic. HOLDLOCK applies only to the table or view for which it is specified and only for the duration of the transaction defined by the statement that it is used in_. Where's your transaction? – AlwaysLearning Mar 22 '22 at 09:35
  • @AlwaysLearning May I know what is a transaction ? From what I know the select statement or every single MSSQL query is a transaction – Heart Break KID Mar 22 '22 at 09:51

0 Answers0