--=_NextPart_000_000C_01C55544.13F07530
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Hi,
I want my transactions to abort when a runtime error (i.e. foreign = key constraint violation) occurs. I currently accomplish this by = prefixing all of my queries with:
SET XACT_ABORT ON
Is there a way to set this option globally so that I don't have to = continually set this option on each query? My first attempt to do this = was:
EXEC sp_dboption 'MyDatabaseName', 'xact_abort', 'true'
...which returned the following error message:
Database option 'xact_abort' does not exist.
Any help would be appreciated. Thanks.
Sincerely,
Vincent Vega
--=_NextPart_000_000C_01C55544.13F07530
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Hi,
I want my = transactions to abort when a runtime error (i.e. foreign key constraint violation) = occurs. I currently accomplish this by prefixing all of my queries = with:
SET XACT_ABORT ON
Is there a way to = set this option globally so that I don't have to continually set this = option on each query? My first attempt to do this was:
EXEC sp_dboption = 'MyDatabaseName', 'xact_abort', 'true'
...which returned = the following error message:
Database option = 'xact_abort' does not exist.
Any help would be = appreciated. Thanks.
Sincerely,
Vincent Vega
--=_NextPart_000_000C_01C55544.13F07530--This is a multi-part message in MIME format.
--=_NextPart_000_0048_01C5559A.040E7CB0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Just turning that on will eventually help you with your present =problems, but will make a lot of trouble when leaving this as a global =setting:
"Specifies whether Microsoft=AE SQL ServerT automatically rolls back the =current transaction if a Transact-SQL statement raises a run-time =error."
I would rather do some error handling than just pushing in all data with =ignoring the error about that.
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Vincent Vega" <Pulp@.Fiction.com> schrieb im Newsbeitrag =news:aJadna7pIuwCfh3fRVn-3w@.giganews.com...
Hi,
I want my transactions to abort when a runtime error (i.e. foreign =key constraint violation) occurs. I currently accomplish this by =prefixing all of my queries with:
SET XACT_ABORT ON
Is there a way to set this option globally so that I don't have to =continually set this option on each query? My first attempt to do this =was:
EXEC sp_dboption 'MyDatabaseName', 'xact_abort', 'true'
...which returned the following error message:
Database option 'xact_abort' does not exist.
Any help would be appreciated. Thanks.
Sincerely,
Vincent Vega
--=_NextPart_000_0048_01C5559A.040E7CB0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Just turning that on will eventually =help you with your present problems, but will make a lot of trouble when leaving this =as a global setting:
"Specifies whether Microsoft=AE SQL Server=99 automatically rolls =back the current transaction if a Transact-SQL statement raises a run-time =error."
I would rather do some error handling =than just pushing in all data with ignoring the error about that.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Vincent Vega"
Hi,
I want my =transactions to abort when a runtime error (i.e. foreign key constraint violation) =occurs. I currently accomplish this by prefixing all of my queries =with:
SET XACT_ABORT ON
Is there a way =to set this option globally so that I don't have to continually set this =option on each query? My first attempt to do this was:
EXEC sp_dboption 'MyDatabaseName', 'xact_abort', 'true'
...which =returned the following error message:
Database option ='xact_abort' does not exist.
Any help would =be appreciated. Thanks.
Sincerely,
Vincent Vega
--=_NextPart_000_0048_01C5559A.040E7CB0--|||I fully agree with Jens. If you still want to turn this on, you can at the instance level using
sp_configure. Note that many tools etc are not designed to use this setting, so be aware.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jens Süßmeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in message
news:eDuF9kYVFHA.3312@.TK2MSFTNGP10.phx.gbl...
Just turning that on will eventually help you with your present problems, but will make a lot of
trouble when leaving this as a global setting:
"Specifies whether Microsoft® SQL ServerT automatically rolls back the current transaction if a
Transact-SQL statement raises a run-time error."
I would rather do some error handling than just pushing in all data with ignoring the error about
that.
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Vincent Vega" <Pulp@.Fiction.com> schrieb im Newsbeitrag
news:aJadna7pIuwCfh3fRVn-3w@.giganews.com...
Hi,
I want my transactions to abort when a runtime error (i.e. foreign key constraint violation)
occurs. I currently accomplish this by prefixing all of my queries with:
SET XACT_ABORT ON
Is there a way to set this option globally so that I don't have to continually set this option
on each query? My first attempt to do this was:
EXEC sp_dboption 'MyDatabaseName', 'xact_abort', 'true'
...which returned the following error message:
Database option 'xact_abort' does not exist.
Any help would be appreciated. Thanks.
Sincerely,
Vincent Vega
No comments:
Post a Comment