troubleshooting Question

Need to invoke (NOLOCK) feature for some SELECT statements in SQL 2000 - Not Clear with Access frontend

Avatar of dmk1gor
dmk1gor asked on
Microsoft SQL Server
5 Comments1 Solution702 ViewsLast Modified:

The problem is SQL 2000 cannot resolve distributed Access front end record locking.

I see in SQL Profiler trace a few recoccuring patterns:

trace 1                                                                                                                     >>>duration time/reads          
SELECT "CustNo" ,"ShipName" ,"Address" ,"Susp" ,"OOW"  FROM "dbo"."ICE_CUSMS_LOOKUP" ORDER BY "dbo"."ICE_CUSMS_LOOKUP"."ShipName"                                                               >>> 1593703/ 34323    
SELECT "CustNo" ,"ShipName" ,"Address" ,"Susp" ,"OOW"  FROM "dbo"."ICE_CUSMS_LOOKUP" ORDER BY "dbo"."ICE_CUSMS_LOOKUP"."ShipName"

and

from Locks in Enterprise I see a sample below:

Process ID     Lock Type     Mode     Status     Owner     Index     Resource
64     PAG     S     GRANT     Xact     PK_ICE_CUSMS_LOOKUP     1:469181        
64     PAG     S     GRANT     Xact     PK_ICE_CUSMS_LOOKUP     1:469180        
64     PAG     S     GRANT     Xact     PK_ICE_CUSMS_LOOKUP     1:469179        
64     PAG     S     GRANT     Xact     PK_ICE_CUSMS_LOOKUP     1:469177        
64     PAG     S     GRANT     Xact     PK_ICE_CUSMS_LOOKUP     1:469176        
61     PAG     S     GRANT     Xact     ICL_ShipName_Ind     1:463325        
61     PAG     S     GRANT     Xact     ICL_ShipName_Ind     1:463324


The ACCESS APPLICATION is Linked Tables to SQL  ( I did not write this & it is very complex - over 250 forms, 100 reports and many modules)
------------------------------------------------------------------------------------------------------------------------------------
Public Function LinkTablesProduction()
    On Error Resume Next
    Dim NameDB As Database, TNames As Recordset, TableIs As String, LinkedTab As TableDef
   
    Set NameDB = CurrentDb
    With NameDB
   
        Set TNames = NameDB.OpenRecordset("LTabs", dbOpenTable)
        With TNames
            Do While Not .EOF
            TableIs = !TName.Value
         
             If DCount("*", "MSysObjects", "[Flags]= 537919488 AND [Name] = '" & TableIs & "'") = 1 Then
                NameDB.TableDefs.Delete TableIs
             End If
             
             If DCount("*", "MSysObjects", "[Flags]= 537919488 AND [Name] = '" & TableIs & "'") = 0 Then
                DoCmd.TransferDatabase acLink, "ODBC Database", "ODBC;DSN=ICExxx;UID=ICExxx;PWD=usexxx;LANGUAGE=us_english;" & "DATABASE=ICE", acTable, TableIs, TableIs, , True
             End If
             'MsgBox TableIs
            .MoveNext
            Loop
        End With
    End With
    TNames.Close
    Set NameDB = Nothing
   
    ListConnections ("Production")
    MsgBox "Table links to PRODUCTION database refreshed!", vbOKOnly, "Done!"
   
End Function

  -----------------------------------------------------------------------------------------------------------------------

rowsource for combobox LookUp( which he used many & recordsource for forms).....................

SELECT DISTINCTROW ICE_CUSMS_LOOKUP.CustNo AS [Cust#], ICE_CUSMS_LOOKUP.ShipName AS Name, ICE_CUSMS_LOOKUP.ShipCity AS City, ICE_CUSMS_LOOKUP.Susp, ICE_CUSMS_LOOKUP.OOW
FROM ICE_CUSMS_LOOKUP
ORDER BY ICE_CUSMS_LOOKUP.CustNo;

He also used many PassThru examples..................

Dim CCQdf As QueryDef, CCDb As Database, CCSQL As String, CCRst As Recordset
    Dim ConSource As String, CardFor As String
    ConSource = DLookup("[RefreshSource]", "LTabs", "[TName] = 'ASCNums'")
        If ConSource = "Test" Then
        ConSource = "ODBC;DATABASE=ICE;UID=ICExxx;PWD=usexxx;DSN=ICETEST"
        Else
        ConSource = "ODBC;DATABASE=ICE;UID=ICExxx;PWD=usexxx;DSN=ICEUser"
        End If
   
    CCSQL = "EXECUTE CTS_CCEnc @CCCNO = " & Card2Ck & ", @CCCEX = '" & CardMo & CardYr
    CCSQL = CCSQL & "', @Prime = " & GetPrime() & ", @CCCTP = '" & CardType & "', "
    CCSQL = CCSQL & "@CCCNM = ''"
   
    Set CCDb = CurrentDb
    Set CCQdf = CCDb.CreateQueryDef("")
        CCQdf.ODBCTimeout = 300
    With CCQdf
        .Connect = ConSource
        '.Prepare = dbQUnprepare
        .SQL = CCSQL
        .ReturnsRecords = False
        .Execute dbSQLPassThrough
    CCSQL = "EXECUTE CTS_CCL4 @Prime = " & GetPrime()
       
        .SQL = CCSQL
        .ReturnsRecords = True
        Set CCRst = CCQdf.OpenRecordset
        CCRst.MoveLast
        Me![CardView] = CCRst![Last4]
        CardFor = CCRst![Last4]
        Me![TypeOfCard] = CCRst![CType]
        Me![CCValid] = True
        Me![CardEdit].Caption = "Change Card #"
       
    CCRst.Close
    CCQdf.Close
   
    CCSQL = "INSERT INTO RepairTrail ( PrimeID, Source, AuditData, AudID ) "
    CCSQL = CCSQL & " SELECT " & GetPrime() & " AS PrimeID, "
    CCSQL = CCSQL & "'Promote Repair' AS Source, "
    CCSQL = CCSQL & "'Credit Card " & "" & " promoted to primary repair.' AS AuditData, """
    CCSQL = CCSQL & GetCurrUser() & """ AS AudID;"
    Set CCQdf = CCDb.CreateQueryDef("", CCSQL)
    CCQdf.Execute dbSeeChanges
    CCQdf.Close



    End With
    Set CCDb = Nothing

End Sub

...........................................................................................................................................................

And many Stored Procedure Calls....
Dim PromDB As Database, PromQdf As QueryDef, PromSQL As String
    PromSQL = "EXECUTE ICE_Promote @GetPrime = " & GetPrime() & ", @CurrISR = " & PIsr
   
    Set PromDB = CurrentDb
    Set PromQdf = PromDB.CreateQueryDef("")
        PromQdf.ODBCTimeout = 300
    With PromQdf
        .Connect = ConSource
        '.Prepare = dbQUnprepare
        .SQL = PromSQL
        .ReturnsRecords = False
        .Execute dbSQLPassThrough
    PromQdf.Close
   
    PromSQL = "INSERT INTO RepairTrail ( PrimeID, Source, AuditData, AudID ) "
    PromSQL = PromSQL & " SELECT " & GetPrime() & " AS PrimeID, "
    PromSQL = PromSQL & "'Promote Repair' AS Source, "
    PromSQL = PromSQL & "'Problem Line " & PIsr & " promoted to primary repair.' AS AuditData, """
    PromSQL = PromSQL & GetCurrUser() & """ AS AudID;"
    Set PromQdf = PromDB.CreateQueryDef("", PromSQL)
    PromQdf.Execute dbSeeChanges
    PromQdf.Close

    End With
    Set PromDB = Nothing
   
DoCmd.OpenForm "ARADetailPromote"

End Sub

      .........................................................................................

My dilema IS I need to make SQL 2000 server byPass the lock system ON some SELECT statements that it locking... BUT I do not where - NOT SURE where to do this Access wont take NOLOCK ???
- and there are about 100 Stored Procedures residing in SQL 2000 database also.    
Please help - this is a handme down without any knowledge transfer......                
ASKER CERTIFIED SOLUTION
nmcdermaid

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 5 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros