Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


commit require in query analyzer?

Posted on 2006-04-28
Medium Priority
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
  • 3
LVL 75

Expert Comment

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

Expert Comment

by:Scott Pletcher
ID: 16565744
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

Anthony Perkins earned 2000 total points
ID: 16565758
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
ID: 16568917
> 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
ID: 16569309
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

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!

Question has a verified solution.

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

This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Suggested Courses

564 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