Solved

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

Posted on 2015-01-09
10
174 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 48

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
Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

 

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 48

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 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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

632 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