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

Make XACT_ABORT persist?

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

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

Make a Job continue after Primary Key Violation?

i have a stored procedure that processes new incoming data. during this
procedure i have many INSERT commands that may result with a duplicate key
violation.
this kind of error should not stop the process and the algorithm continue.
when i run it manually ( EXEC SP1 ...) it behaves ok and continue even when
primary key violation occur.
BUT, when i run it via a job (single step) it quits on the 1st error!!!
WHY ?
how can i force the job/SP to continue running after data errors like key
violations '
thanks
rafiThere are errors in sql that are not trappable. Erland has some good writing
that you should take a look:
http://www.sommarskog.se/error-handling-I.html
-oj
"Rafi" <Rafi@.discussions.microsoft.com> wrote in message
news:93C834D3-71DA-48AF-B2C6-2151EC06F136@.microsoft.com...
>i have a stored procedure that processes new incoming data. during this
> procedure i have many INSERT commands that may result with a duplicate key
> violation.
> this kind of error should not stop the process and the algorithm continue.
> when i run it manually ( EXEC SP1 ...) it behaves ok and continue even
> when
> primary key violation occur.
> BUT, when i run it via a job (single step) it quits on the 1st error!!!
> WHY ?
> how can i force the job/SP to continue running after data errors like key
> violations '
> thanks
> rafi|||Hi
Why not add a where clause to the insert statement to check the PK value
does not exist? If they do exist you may want to log the fact.
It may not a good idea to SET XACT_ABORT OFF as you may miss something that
is important.
John
"Rafi" wrote:

> i have a stored procedure that processes new incoming data. during this
> procedure i have many INSERT commands that may result with a duplicate key
> violation.
> this kind of error should not stop the process and the algorithm continue.
> when i run it manually ( EXEC SP1 ...) it behaves ok and continue even whe
n
> primary key violation occur.
> BUT, when i run it via a job (single step) it quits on the 1st error!!!
> WHY ?
> how can i force the job/SP to continue running after data errors like key
> violations '
> thanks
> rafi