Re: Strange Problems with ODBC connection to SQL Server

• From: "Mary Chipman [MSFT]" <mchip@xxxxxxxxxxxxxxxxxxxx>
• Date: Wed, 01 Feb 2006 17:07:48 -0500

I'd like to suggest a different approach, for the following reasons.

-- DAO is not the best API to work with SQL Server from Jet. If you
must create a recordset, use ADO. The general rule of thumb is, when
accessing SQLS data, use ADO. When accessing Jet data, use DAO.

-- Any kind of recordset is the absolute slowest, worst, and
potentially buggiest way to modify data by causing potential blocking
and deadlocking problems. Pessimistic locking compounds the problem.

-- The code you are writing now will be difficult to maintain even
if/when you do get it to work.

Your best bet is to use passthrough queries. You can modify them at
runtime by using DAO QueryDef objects, supplying the .SQL property,
which contains your T-SQL statements (or even better, calls a stored
procedure). In addition, you don't need to use a DSN--you can supply
an ODBC connection string at runtime to dynamically connect to the
server. This way you are not exposing connection information in clear
text, which could leave your server vulnerable to attack.

--Mary

On Wed, 1 Feb 2006 07:41:43 -0800, "Informate-Rod"
<InformateRod@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

>Hi,
>
>I am having problems with an app I thought was working!!!
>
>Basically the app executes within an NT4 domain. The MSAccess (Access2K) app 
>accesses an SQL Server 2000 repository via ODBC (System DSN) data source.
>
>I use a Workspace and Connection object to access the SQLSvr2K based tables 
>etc
>
>Code fragment for "initialising the Connection" and the "problem code" are 
>included at the end of this msg. The problem code has been annotated to 
>identify the 4 invocations mentioed below.
>
>In a nutshell ...The problem is I receive an ODBC Call Failed msg (3146) 
>when executing "Invocation D" (see listing) - basically attempting to open a 
>table via the OpenRecordset method on the Connection obj. This recordset is 
>being used to add further records. As the code looks ok (and I'm sure it 
>previously executed ok as well??) and the returned error was saying nothing 
>of value I've turned on ODBC tracing via the control panel and examined the 
>log files. 
>
>First thing I noticed is that there seems to be a reported error for 
>Invocations "A", "B" and "D" (not "C") from SQLExecDirect  ..."DIAG [37000] 
>[Microsoft][ODBC SQL Server Driver][SQL Server]The request for procedure 
>'dtbSks' failed because 'dtbSks' is a table object. (2809) . The severity of 
>this error appears minor but you never know??
>
>
>However I know from debugging that Invocations "A", "B" and "C" are working. 
>However when  I single step through Invocation "D" I also get a further 
>error....
>
>DIAG [S1008] [Microsoft][ODBC SQL Server Driver]Operation canceled. (0)
>
>My Questions
>1. What is causing this [37000] - 2809 error mentioned above. It seems to 
>occur on all my opening of tables - successful and erroneous. Do I need to be 
>concerned about it and what fix is needed.
>2. My observable error of the ODBC call failed (3146) seems to be related to 
>this [S1008] error I am seeing in the ODBC log file. What is causing this and 
>how can I fix it.
>3. Are there are more sophisticated tools for debugging ODBC DAO calls.
>
>MANY THANKS
>
>
>
>The code to iniitalise the workspace and connection objects...
>
>Set XWorkspace = CreateWorkspace("ODBCWorkspace", "dbo", "", dbUseODBC)
>
>Set ConnectionSQLSrvr = XWorkspace.OpenConnection("ConnectionToSQLSrvr", 
>dbDriverCompleteRequired, False, 
>"ODBC;DATABASE=ProfServ;UID=dbo;PWD=;DSN=Repository")
>
>
>
>Then within a workspace based transaction the following code fragment 
>executes...
>
>            Rem INVOCATION "A"
>            Set KnownSkRecordSet = ConnectionSQLSrvr.OpenRecordset("dtbSks", 
>dbOtpenDynamic, 0, dbPessimistic)
>            KnownSkRecordSet.AddNew
>            KnownSkRecordSet.Fields("SkDescription") = 
>SelectedPotSksToAction(SkIndex).SkName
>            KnownSkRecordSet.Fields("SkAreaId") = SelectedAreaId
>            KnownSkRecordSet.Update
>            KnownSkRecordSet.Bookmark = KnownSkRecordSet.LastModified
>            StoredSkId = KnownSkRecordSet.Fields("SkId")
>            KnownSkRecordSet.Close
>            
>            If (StoredSkId < 0) Then
>                ExecutionState = False
>            Else
>                Rem INVOCATION "B"
>                Set KnownSkRecordSet = 
>ConnectionSQLSrvr.OpenRecordset("dtbVersionedSks", dbOpenDynamic, 0, 
>dbPessimistic)
>                KnownSkRecordSet.AddNew
>                KnownSkRecordSet.Fields("VersionText") = "default"
>                KnownSkRecordSet.Fields("SkId") = StoredSkId
>                KnownSkRecordSet.Update
>                KnownSkRecordSet.Bookmark = KnownSkRecordSet.LastModified
>                StoredVersionedSkId = KnownSkRecordSet.Fields("VersionedSkId")
>                KnownSkRecordSet.Close
>            End If
>            
>            If (ExecutionState = False) Then
>            ElseIf (StoredVersionedSkId < 0) Then
>                ExecutionState = False
>            Else
>                Rem INVOCATION "C"
>                strSQL = "SELECT CnId, OccurencesInVC, FirstFound, "
>                strSQL = strSQL & "LastFound, AccumulatedWeight, 
>DateOfRegistration "
>                strSQL = strSQL & "FROM crsCnPotentialSks WHERE 
>((PotentialSkId)="
>                strSQL = strSQL & Format(SelectedPotSksToAction(SkIndex).Id) 
>& ");"
>                Set SkSearchRecordSet = 
>ConnectionSQLSrvr.OpenRecordset(strSQL, dbOpenSnapshot)
>                If SkSearchRecordSet.BOF = False Then
>                    SkSearchRecordSet.MoveLast
>                    SkXRefCount = SkSearchRecordSet.RecordCount
>                    SkSearchRecordSet.MoveFirst
>                    If (SkXRefCount > 0) Then
>                        Rem INVOCATION "D"
>                        Set KnownSkXRefRecordSet = 
>ConnectionSQLSrvr.OpenRecordset("crsCnSks", dbOpenDynamic, 0, dbPessimistic)
.

• References:
  • Strange Problems with ODBC connection to SQL Server
    • From: Informate-Rod

• Prev by Date: Strange Problems with ODBC connection to SQL Server
• Next by Date: System Data Source via GPO
• Previous by thread: Strange Problems with ODBC connection to SQL Server
• Next by thread: System Data Source via GPO
• Index(es):
  • Date
  • Thread

• Windows
• Science
• Usenet

• Archive
• About
• Privacy
• Search
• Imprint