identify errors in SQL Server Management Studio that go with errors seen in client ms access app

Posted on 2015-01-09
Last Modified: 2015-01-10

I am new to SQL server and can figure out how identify and troubleshoot erros for the db server side.

I get back a "0 [Microsoft][SQL Server Native Client 11.0]Connection is busy with results for another command
3146 ODBC--call failed."  error on a recordset addnew called from ms access 2013 . (DAO)

I know the error is really a null value.  Is there was to find what error happened  from SQL Server Management Studio?  As in which field, which violation of contraint?
Question by:pressMac
  • 4
  • 2
  • 2
  • +1
LVL 15

Expert Comment

ID: 40541762
After looking at the error text message, I don't think the problem is a violation of a constraint. It looks like the record you are trying to get/update/delete is being updated. This can be caused because of an unfinished transaction.

Try to restart your SQL-Server and run the program again. If same error still stays, then review your program and search for connections with autocommit property set to false. If you really need to make transactions larger than one sentence, then try to put the minimun amount of SQL commanda all together with the minimum execution time (no messageBox between them, or any other element that waits for an user action) and don't forget to put the COMMIT sql sentence to finish the transaction.

Hope it helps.
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40541916
If you are doing this from Access, you can also expand on the error messages.  Add an error handler to your code, if you don't already have one, then check for error 3146 with an If Statement.  Something like:

   Dim ErrLoop as Error

   If Errors.Count > 1 then 
       For Each ErrLoop in Errors
           Debug.Print "ODBC Error"
           Debug.Print ErrLoop.Number
           Debug.Print EffLoop.Description
       Next ErrLoop
       Debug.print err.number, err.description
   End If

Open in new window

I would actually put the Dim statement at the beginning of my code segment.

Author Comment

ID: 40541933
The " "0 [Microsoft][SQL Server Native Client 11.0]Connection is busy with results for another command" message is from the following message handler.
Dim myError As Error
      MsgBox Errors.Count
      For Each myError In DBEngine.Errors
        With myError
            Debug.Print .Number & " " & .Description
          MsgBox .Number & " " & .Description
        End With
      Next myError
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.


Author Comment

ID: 40541935
The question that i need answered is how to see, on the backend sql  server, the error that is being thrown back to ms access.  Not how to fix this problem in particular.  The problem is data related.  The code is part of a recordset loop, it only fails on certain records.  very simple record set add.  fails on .update.  I am looking to see exactly which constraint is violated.  Sure i could step by step test fills records, and find the offending field, but that is not the point.
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40541949
The errors collection will frequently provide enough detail to resolve the problem.

However, when it doesn't, I will usually also print out the SQL String that was passed to the SQL Server, along with the error message.  Or, in your case, I would print out all of the values assigned to that record in your code.

Then I would open the SQL Server management console and
1. review all of the fields in the table being added to, looking for fields with NOT NULL constraints.  If that didn't resolve the issue then I would:
2.  construct a SQL Query that contains all of those assignments and attempt to run the query in SQL.

That is the only way I know how to determine what constraints are being violated.
LVL 84
ID: 40542005
I generally use ADO to interact with SQL Server, and if you do that (and create an ADO Connection), then you can also examine the Errors collection of that ADO Connection. It often tells you more about the source error (which is what you're looking for, it would seem).

Not sure if a DAO connection would have that additional info, however ...

Author Comment

ID: 40542051
Is there any kind of log or trace on the server that shows each query processed by the server.
LVL 15

Expert Comment

ID: 40542067
One of my clients use DB Artisant software, which is a tool to monitor SQL-Databases (and also other things). You can see the problems by using this software in a graphical way, and when you put the curson over a problem you see the query that is causing the problem.

for example, you can see database locks, and you can select it and see the SQL sentence that is making the lock (and also you can do some actions from this software, like kill the process).

Here is the link to the product in case you are interested:

Hope it helps.
LVL 84

Accepted Solution

Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 40542229
If you have the "full" version of Management Studio, you can use SQL Profiler to see every request sent to the server.

Author Comment

ID: 40542516

This is spot on.  I was able to see the exact error, and what caused it, including the constraint and column.  I inserted a screen shot.

It is worth mentioning that one may need to go to the trace properties and set some filters otherwise there is a mountain of data.  

I used the following filters:
i enabled errors and warnings, all events
i enabled TSQL, all events
i limited to the NTUsername that i was using.

Very helpful.image from SSMA profiler

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
changing page verifacation 1 29
HTML <font style="color:red"> 9 32
SQL Recursion schedule 13 16
2 subforms 1 main form 1 13
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question