Hi All,

HELP! I am getting non-unique key values returned from my PrimaryKey generator!

This happens consistently with ONE client on ONE set of data. They are using an NT4.0 file server which holds the Exe and many sets of data. (When they log in to the application, they choose which set of data to use)

The PrimaryKey generator (Get_New_Id) is stored in the DBC. Each set of data has an identical copy of the DBC.

It appears that rlock is lying, or the update to the key table (Mc_Key) is failing w/o generating an error. Mc_Key is NOT buffered. I use the table, lock the record, update the record and close the table.

We have done the following:
- Upgraded the server to SP6 from SP4.
- Rebuilt all indexes.
- Copied the Mc_Key table from a working dataset and recreated the data.
- Copied all the data to another directory.
- Run "Scandisk"
- Added logging to Get_New_Id. It verifys that Get_New_Id is returning duplicate values.

Thanks in advance for ANY ideas!

Get_New_Id follows:

function Get_New_Id
   * Returns a new Id for many tables
   lparameters tcKeyType, tuMisc
   local lcKeyType, lcMsg, lcOldSetDeleted, lcTbl, ;
      llContinue, llUsed, llMcKeyUsed, llGotRLock, ;
      lnId, lnMcKeyRecNo, lnNbrOfKeys, lnOldWorkArea, lnRecNo, luId

   if type("tcKeyType") <> "C"
      MessageBox("Invalid type '" + MakeStr(tcKeyType) + "' Passed to Get_New_Id", ;
                   MB_OK, "Invalid Function Call")
      return .F.
   endif

   if type("tuMisc") = "N"
      lnNbrOfKeys = tuMisc
   else
      lnNbrOfKeys = 1
   endif

   lnOldWorkArea = select()
   lcKeyType = upper(tcKeyType)
   llGotRLock = .F.
   llContinue = .T.
   do while llGotRLock = .F. and llContinue
      if used("Mc_Key")
         llMcKeyUsed = .T.
         select Mc_Key
      else
         select 0
         use Mc_Key
      endif
      if seek(lcKeyType, "Mc_Key", "Key_Type")
         lnMcKeyRecNo = recno("Mc_Key")
         set reprocess to 0
         if rlock(str(lnMcKeyRecNo), "Mc_Key")
            llGotRLock = .T.
            set reprocess to 5 seconds
            if inlist(lcKeyType, "CLAIM", "REFER", "URRVW")
               luId = GregToJul(date())
               if luId = int(Mc_Key.Key_Value / 10000)
                  luId = Mc_Key.Key_Value + 1
               else
                  ** Date has changed -- This may be valid OR NOT!  We need to check against the table(s).
                  lnId = luId * 10000
                  luId = lnId + 1
                  lcOldSetDeleted = set("DELETED")
                  set deleted off
                  do case
                     case lcKeyType = "CLAIM"
                        SELECT MAX(Clm_Id) AS Max_Id FROM Mc_MdClm ;
                         WHERE Clm_Id BETWEEN lnId AND (lnId + 9999) ;
                         INTO CURSOR csrGetNewIdMax NOFILTER
                        if reccount("csrGetNewIdMax") > 0
                           luId = csrGetNewIdMax.Max_Id + 1
                        endif
                        SELECT MAX(Clm_Id) AS Max_Id FROM Mc_UbClm ;
                         WHERE Clm_Id BETWEEN lnId AND (lnId + 9999) ;
                         INTO CURSOR csrGetNewIdMax NOFILTER
                        if reccount("csrGetNewIdMax") > 0 ;
                         and csrGetNewIdMax.Max_Id >= luId
                           luId = csrGetNewIdMax.Max_Id + 1
                        endif
                        use in csrGetNewIdMax

                     case lcKeyType = "REFER"
                        SELECT MAX(Ref_Id) AS Max_Id FROM Mc_Refer ;
                         WHERE Ref_Id BETWEEN lnId AND (lnId + 9999) ;
                         INTO CURSOR csrGetNewIdMax NOFILTER
                        if reccount("csrGetNewIdMax") > 0
                           luId = csrGetNewIdMax.Max_Id + 1
                        endif
                        use in csrGetNewIdMax

                     case lcKeyType = "URRVW"
                        SELECT MAX(Rvw_Id) AS Max_Id FROM Mc_UrRvw ;
                         WHERE Rvw_Id BETWEEN lnId AND (lnId + 9999) ;
                         INTO CURSOR csrGetNewIdMax NOFILTER
                        if reccount("csrGetNewIdMax") > 0
                           luId = csrGetNewIdMax.Max_Id + 1
                        endif
                        use in csrGetNewIdMax
                  endcase
                  if lcOldSetDeleted = "ON"
                     set deleted on
                  endif
                  if luId < Mc_Key.Key_Value
                     MessageBox("Is today REALLY '" + dtoc(date()) + "'?" + chr(13) + chr(13) ;
                      + "The new Key '" + ltrim(str(luId)) ;
                      + "' is older than one that was previously used '" ;
                      + ltrim(str(Mc_Key.Key_Value)) + "' ." + chr(13) + chr(13) ;
                      + "If not, CANCEL and correct your system date.", ;
                      MB_OK + MB_ICONSTOP, "Verify System Date")
                  else
                     if luId <> (GregToJul(date()) * 10000) + 1
                        MessageBox("Is today REALLY '" + dtoc(date()) + "'?" + chr(13) + chr(13) ;
                         + "The new Key '" + ltrim(str(luId)) ;
                         + "' is not the first one for this date."+ chr(13) + chr(13) ;
                         + "If not, CANCEL and correct your system date.", ;
                         MB_OK + MB_ICONSTOP, "Verify System Date")
                     endif
                  endif
               endif
            else
               luId = Mc_Key.Key_Value + 1
            endif
            replace Key_Value with luId + lnNbrOfKeys - 1 in Mc_Key
            unlock record lnMcKeyRecNo in Mc_Key
            if lcKeyType = "TOSCD"
               if luId < 260000
                  luId = substr("ABCDEFGHIJKLMNOPQRSTUVWXYZ", int(luId / 10000) + 1, 1) ;
                   + padl(ltrim(str(mod(luId, 10000))), 4, "0")
               else
                  luId = "     "
               endif
            endif
         else && rlock(str(lnMcKeyRecNo), "Mc_Key")
            set reprocess to 5 seconds
            wait window "Unable to Lock Mc_Key for " + lcKeyType + " - Retrying" + chr(13) ;
             + "Press ESC to Abort" nowait noclear
            if inkey(.2, "H") <> 27
               wait clear
            else
               wait clear
               llContinue = .F.
            endif
         endif && rlock(str(lnMcKeyRecNo), "Mc_Key")
      else && seek(lcKeyType, "Mc_Key", "Key_Type")
         MessageBox("Invalid type '" + lcKeyType + "' Passed to Get_New_Id", ;
          MB_OK, "Invalid Function Call")
         luId = .F.
      endif && seek(lcKeyType, "Mc_Key", "Key_Type")
		
      if used("Mc_Key") ;
       and !llMcKeyUsed
         use in Mc_Key
      endif
      select (lnOldWorkArea)

      if file("New_Ids.Dbf")
         if type("g_cUserId") <> "C"
            g_cUserId = "UnKnown"
         endif
         if type("g_cMainGroupName") <> "C"
            g_cMainGroupName = "UnKnown"
         endif
         INSERT INTO New_Ids (Dbc_Vsn, Key_Time, User_Id, Sys_Info, Group_Name, ;
          Key_Type, Key_Misc, Key_Value) ;
          VALUES (DbGetProp(justfname(dbc()), "Database", "Comment"), datetime(), g_cUserId, sys(0), g_cMainGroupName, ;
          tcKeyType, transform(tuMisc), iif(llGotRLock, transform(luId), iif(llContinue, "Retrying", "Aborting")))
      endif
   enddo

   return luId