Solved

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

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

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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

706 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now