Solved

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

Posted on 2015-01-09
10
168 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 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:

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
Technology Partners: 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 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.
0
 
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 ...
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 84

Accepted Solution

by:
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.
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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sql server cross db update 2 20
VBA Delete sql "Too Few Parameters" 8 24
Delete old Sharepoint backups 2 22
calculate running total 8 15
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 ?
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
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…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

733 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