Set Xact_abort On
Mar 28, 2008I am running update on huge table in batches. If I kill the process, will it roll back the last batch or the entire batches if I have used xact_abort on?
Thanks!
ADVERTISEMENT
Set XACT_ABORT ON
Dec 7, 2004Newbie question
I have an Update trigger and an Insert trigger on a table both of which use a variant of the code below. The trigger takes information from the table and updates information in another database configured as a linked server.
The update trigger works fine without the Set XACT_ABORT ON statement.
The insert trigger crashes unless I put this line in.
The update trigger runs 10 times faster without the statement.
Is there an alternative to this that will allow the insert trigger to run without crashing? and run faster. The insert trigger takes forever to run now. (Forever being about 20 seconds). The Update trigger takes about 2 seconds without the Set XACT_ABORT ON statement and 20 seconds with it.
Any help would be appreciated.
Tom
Set XACT_ABORT ON
Select @pos=COUNT(*)From SQLPSPRD.DTEFSPRD.dbo.PS_GCC_CONTACT_DTL
where UPPER(GCC_CONTACT_ID)=UPPER(RTRIM(@tmpcompid2)) and GCC_CONTACT_SEQ=@maxseq2
if(@pos>0)
begin
Update SQLPSPRD.DTEFSPRD.dbo.PS_GCC_CONTACT_DTL
set GCC_BUSINESS_UNIT=UPPER(@tmpbus),GCC_CONTACT_NAME= isnull(@nameout,' '),
GCC_CONTACT_ID=@tmpcompid,GCC_CONTACT_SEQ=@maxseq,
GCC_CONTACT_CMPY=isnull(UPPER(@compout),' '),GCC_CONTACT_ADDR1=isnull(@add1out,' '),
GCC_CONTACT_ADDR2=isnull(@add2out,' '),GCC_CONTACT_ADDR3=isnull(@add3out,' '),
GCC_CONTACT_CITY=isnull(@tmpcity,' '),GCC_CONTACT_STATE=isnull(UPPER(@tmpstate),' '),
GCC_CONTACT_ZIP=isnull(UPPER(@zipout),' '),GCC_CONTACT_CNTRY=isnull(UPPER(@ctryout),' '),
GCC_CONTACT_PHONE=isnull(UPPER(@tel1out),' '),GCC_CONTACT_FAX=isnull(UPPER(@tel2out),' '),GCC_CONTACT_CELL=isnull(UPPER(@tel3out),' '),
GCC_CONTACT_EMAIL=isnull(@emailout,' '),GCC_EFF_STATUS=@outstatus,GCC_OPRID=isnull(@mgr out,' '),GCC_DTTM_STAMP=CONVERT(Char, GetDate(), 101)
where UPPER(RTRIM(@tmpcompid2))=UPPER(GCC_CONTACT_ID) and @maxseq2=GCC_CONTACT_SEQ
end
else
begin
Select @rcount=count(GCC_CONTACT_SEQ)
From SQLPSPRD.DTEFSPRD.dbo.PS_GCC_CONTACT_DTL
where UPPER(GCC_CONTACT_ID)=UPPER(RTRIM(@tmpcompid)) AND GCC_CONTACT_SEQ=@maxseq AND UPPER(RTRIM(@nameout))=UPPER(RTRIM(GCC_CONTACT_NAM E))
if(@rcount<1)
begin
Insert into SQLPSPRD.DTEFSPRD.dbo.PS_GCC_CONTACT_DTL
(GCC_BUSINESS_UNIT,GCC_CONTACT_ID,GCC_CONTACT_SEQ, GCC_EFFDT,GCC_CONTACT_NAME,GCC_CONTACT_CMPY,GCC_CO NTACT_TYPE,GCC_CONTACT_ADDR1,GCC_CONTACT_ADDR2,GCC _CONTACT_ADDR3,GCC_CONTACT_ADDR4,GCC_CONTACT_CITY, GCC_CONTACT_CNTY,GCC_CONTACT_STATE,GCC_CONTACT_ZIP ,GCC_CONTACT_CNTRY,GCC_CONTACT_PHONE,GCC_CONTACT_F AX,GCC_CONTACT_CELL, GCC_CONTACT_EMAIL,GCC_EFF_STATUS,GCC_OPRID,GCC_DTT M_STAMP)
values(UPPER(@tmpbus),UPPER(@tmpcompid),@maxseq,CO NVERT(Char, GetDate(), 101),isnull(@nameout,' '),
isnull(UPPER(@compout),' '),'E',isnull(@add1out,' '),isnull(@add2out,' '),isnull(@add3out,' '),' ',isnull(@tmpcity,' '),' ',
isnull(UPPER(@tmpstate),' '),isnull(UPPER(@zipout),' '),isnull(UPPER(@ctryout),' '),isnull(UPPER(@tel1out),' '),isnull(UPPER(@tel2out),' '),isnull(UPPER(@tel3out),' '),
isnull(@emailout,' '),@outstatus,isnull(@mgrout,' '),CONVERT(Char, GetDate(), 101))
end
end
end
Set Xact_abort On
Feb 13, 2004Hi,
How to set "SET XACT_ABORT ON" Property in SQL SERVER GLobally, such that any connection to MS SQL SERVER has this property ON?
Note : By Default this property is OFF.
Xact_abort
Apr 21, 2004How can I set xact_abort to 'ON' on my server.
Thanks.
Use Of Set Xact_abort On/off
Jul 23, 2005Is there any reason to use set xact_abort on OR set xact_abort off as arule in my stored procedures?
View 1 Replies View RelatedSET XACT_ABORT In C# Sp?
May 12, 2006How do you set SET XACT_ABORT to On in a c# stored procedure?
View 4 Replies View RelatedXact_abort
Nov 13, 2007Hi,
Anyone knows how to set the xact_abort attribute on in SSIS? I get an error message "Bulk Insert with another outstanding result set should be run with XACT_ABORT on" when I'm trying to enforce transactions in my package-my package uses distributed queries.
Thanks,
Lakshmi
Xact_abort On To Commit Trans To Other Linked Sql Server
Apr 20, 2004To commit a transaction on SQL server A database A that also inserts a record to SQl Server B database B (this is a linked sql server) do I need the xact_abort set on before the commit trans statement. Or is doing this not possible
View 1 Replies View RelatedJDBC And SQL 2005 With XACT_ABORT UPDATE Statement Bug ?
Aug 17, 2007While troubleshooting a problem at one of our customers, we noticed the following consistent erratic behaviour of our product with the 1.1.105 JDBC driver (as well as the 1.2 CTP) when the XACT_ABORT user option has been set on the SQL server:
Set the user option XACT_ABORT on a database server with the following SQL query:
exec sp_configure 'user options', '16384'
reconfigure
Now run the following
Code Snippet
import java.sql.*;
import java.util.*;
public class Test {
public static void main(String[] args) {
try {
// Load the JDBC driver.
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
// Establish the connection to the database.
String url = "jdbc:sqlserver://server:1433";
Properties props = new Properties() ;
props.setProperty("user","user");
props.setProperty("password","password");
props.setProperty("DatabaseName","databasename");
props.setProperty("selectMethod","cursor");
Connection conn = DriverManager.getConnection(url, props);
// Set up simple prepared Update statement
PreparedStatement pstmt = conn.prepareStatement("UPDATE table SET field=0 where field=1", ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
Boolean isresultset = pstmt.execute();
System.out.println("IsUpdatecount? " + !(isresultset));
Integer updatedrecords=pstmt.getUpdateCount();
/*updatedrecords should be 0 or higher if isresultset is false
if updatedrecords=-1, the result should be a resultset, which it cannot be, but seems to be*/
System.out.println(updatedrecords+ " row(s) affected");
} catch (Exception e) {
System.err.println("Got an exception! ");
System.err.println(e.getMessage());
}
}
}
You can revert the database server back to the correct behavior bij executing the following SQL query
exec sp_configure 'user options', '0'
reconfigure
As you can see, when the SQL server user option XACT_ABORT is set, prepared UPDATE statements through the SQL JDBC driver will fail consistently when selectMethod=cursor and ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY are set, with the execute method indicating an UpdateCount is returned, but the UpdateCount itself claiming a resultset has been returned. On checking that, there is no resultset.
Somehow the option, which should do the following:
xact abort
Rolls back a transaction if a Transact-SQL statement raises a run-time error.
seems to roll back the results of an update statement in place ?
Is this a bug or something I seem to be doing wrong ?
Kind regards,
Niels
PS: With the 1.0 JDBC driver with this setting another problem occured, which pointed to a rollback done within a transaction without any exception being raised through JDBC, consistent with Angel Saenz-Badillos post on http://www.mombu.com/microsoft/sql-server-jdbc-driver/t-behavior-of-connectioncommit-249105.html
We had hoped the 1.1 driver would at least raise the exception through JDBC, but alas, the result mentioned above happened, which in the end we could track back to the server setting.
--Update--
When an SQL Trace is active on the server the following two error messages appear in the log, none of which are visible through the JDBC driver error handler:
Exception Error: 156, Severity: 15, State: 1
Exception Error: 16954, Severity: 10, State: 1
Bulk Insert With Another Outstanding Result Set Should Be Run With XACT_ABORT On
Apr 1, 2008 Before today when I run the package, everything is fine. Today when we use similiar database with small schema change, my package can not be run successfully. Always got '
Bulk Insert with another outstanding result set should be run with XACT_ABORT on
' error.
Any help will be appreciated. Thanks
Bulk Insert With Another Outstanding Result Set Should Be Run With XACT_ABORT On
Oct 24, 2006All,
I have an SSIS package which calls several other SSIS packages. The "mother" package has TrasactionOption set to "Supported". There is a sequence container and an imbedded Execute Package Task for each of my embedded packages. Each of the sequence containers has TransactionOption = Required. DTC is running.
When I run my mother package I get the following error within the first package executed "Bulk Insert with another outstanding result set should be run with XACT_ABORT on." I've looked high and low for others with this problem, but haven't found any resolution. Can anyone tell me how to resolve this error? I have tried using BEGIN TRANSACTION instead of using Required and I turn on XACT_ABORT, but it doesn't seem to help either. Any help on this would be greatly appreciated.
Thanks