commit require in query analyzer?

Posted on 2006-04-28
Last Modified: 2012-05-05
The SP is much bigger then this but here is the top few lines.

--clear out the old values
delete from table where field_id = @id;

--copy all the new values into the table
insert into table (field_id, field_code, field_value)
select @id, field_code, 0
from table2;

If I use the query analyzer the above two queries will not work unless I put the word commit under each but if I call this SP from VB it works. I have a problem with the SP and it is very difficult to trouble shoot it unless I can get it to work in both environment which are query analyzer and in VB.
Question by:lunchbyte
    LVL 75

    Expert Comment

    by:Anthony Perkins
    All statement are implicitly COMMITed using SQL Query Analyzer.  So something else must be going on.
    LVL 68

    Expert Comment

    If the code has a BEGIN TRAN, then you must do a COMMIT [or ROLLBACK] to complete the transaction.

    If you want to be sure that implicit transactions are on in QA -- which will commit each statement automatically after it is executed -- use this statement first in your QA code:

    LVL 75

    Accepted Solution

    The problem is that you are using debug mode and by default "Auto roll back" is on.  Try unchecking it.

    P.S. You will find that most experts here do not use SQL Query Analyzer's debug mode.
    LVL 40

    Expert Comment

    by:Vadim Rapp
    > P.S. You will find that most experts here do not use SQL Query Analyzer's debug mode.

    but some do, and very productively.
    LVL 75

    Expert Comment

    by:Anthony Perkins
    Most definitely.  My comment was more in reference to the questioner's other thread where no one appears to have picked up on the fact that he/she is using SQL Query Analyzer in debug mode and that it does not (by default) commit changes.

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Join & Write a Comment

    Introduced in Microsoft SQL Server 2005, the Copy Database Wizard ( is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
    When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
    Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
    Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

    729 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

    14 Experts available now in Live!

    Get 1:1 Help Now