Make XACT_ABORT persist?

Hi,
I want my transactions to abort when a runtime error (i.e. foreign key const
raint 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 continual
ly 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 VegaJust turning that on will eventually help you with your present problems, bu
t will make a lot of trouble when leaving this as a global setting:
"Specifies whether Microsoft SQL ServerT automatically rolls back the curre
nt 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 ign
oring the error about that.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Vincent Vega" <Pulp@.Fiction.com> schrieb im Newsbeitrag news:aJadna7pIuwCfh
3fRVn-3w@.giganews.com...
Hi,
I want my transactions to abort when a runtime error (i.e. foreign key const
raint 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 continual
ly 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|||I fully agree with Jens. If you still want to turn this on, you can at the i
nstance 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 Smeyer" <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, bu
t will make a lot of
trouble when leaving this as a global setting:
"Specifies whether Microsoft SQL ServerT automatically rolls back the curre
nt 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 ign
oring 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 const
raint 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 continual
ly 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