Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2015-01-09
10
Medium Priority
?
214 Views
Last Modified: 2015-01-10
Hello,

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?
0
Comment
Question by:pressMac
  • 4
  • 2
  • 2
  • +1
10 Comments
 
LVL 15

Expert Comment

by:gplana
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.
0
 
LVL 49

Expert Comment

by:Dale Fye
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:

ProcError:
   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
   Else
       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.
0
 

Author Comment

by:pressMac
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
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:pressMac
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.
0
 
LVL 49

Expert Comment

by:Dale Fye
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.
0
 
LVL 85
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 ...
0
 

Author Comment

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

Expert Comment

by:gplana
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: http://www.embarcadero.com/es/products/dbartisan

Hope it helps.
0
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 2000 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.
0
 

Author Comment

by:pressMac
ID: 40542516
Scott,

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
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

824 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