Set Xact_abort On

Mar 28, 2008

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

View 1 Replies


ADVERTISEMENT

Set XACT_ABORT ON

Dec 7, 2004

Newbie 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

View 1 Replies View Related

Set Xact_abort On

Feb 13, 2004

Hi,

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.

View 3 Replies View Related

Xact_abort

Apr 21, 2004

How can I set xact_abort to 'ON' on my server.

Thanks.

View 1 Replies View Related

Use Of Set Xact_abort On/off

Jul 23, 2005

Is there any reason to use set xact_abort on OR set xact_abort off as arule in my stored procedures?

View 1 Replies View Related

SET XACT_ABORT In C# Sp?

May 12, 2006

How do you set SET XACT_ABORT to On in a c# stored procedure?

View 4 Replies View Related

Xact_abort

Nov 13, 2007

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

View 9 Replies View Related

Xact_abort On To Commit Trans To Other Linked Sql Server

Apr 20, 2004

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

JDBC And SQL 2005 With XACT_ABORT UPDATE Statement Bug ?

Aug 17, 2007

While 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

View 1 Replies View Related

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

View 8 Replies View Related

Bulk Insert With Another Outstanding Result Set Should Be Run With XACT_ABORT On

Oct 24, 2006

All,

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

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved