automatic or manual ROLLBACK

Hi,
I have a job that executes the TSQL below. Do I need to do the
errorchecking myself (as below) or does the transaction automaticaly
rollback if a statement fails?
Feel free to comment on the script. (Its never too late to learn).
Yours sincerely,
Jo
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
DECLARE @.MYERROR
BEGIN TRANSACTION
INSERT INTO sequencetoolingbak
(
orderid,
partno,
jobno,
deliverydate,
time_of_creation
)
SELECT orderid, partno, jobno, deliverydate, time_of_creation
FROM dbo.sequencetooling AS seq
WHERE deliverydate < DATEADD(d, -14, GETDATE())
AND NOT EXISTS
(
SELECT orderid
FROM dbo.sequencetoolingbak AS bak
WHERE bak.orderid = seq.orderid
)
SET @.MYERROR = @.@.ERROR
IF @.MYERROR <> 0
BEGIN
PRINT 'INSERT ERROR ' + CONVERT(VARCHAR, @.@.ERROR)
ROLLBACK TRANSACTION
RETURN
END
DELETE FROM dbo.sequencetooling
WHERE DATEDIFF(d, deliverydate, GETDATE()) >= 14
SET @.MYERROR = @.@.ERROR
IF @.MYERROR <> 0
BEGIN
PRINT 'DELETE ERROR ' + CONVERT(VARCHAR, @.@.ERROR)
ROLLBACK TRANSACTION
RETURN
END
COMMIT TRANSACTION
Jo SegersHi
Relying on default error handling to be the same in future versions of SQL
Server is not good at all as it could change.
You should always handle the error yourself (like in your code) as the
execution will be predictable.
Regards
Mike
"Jo Segers" wrote:

> Hi,
> I have a job that executes the TSQL below. Do I need to do the
> errorchecking myself (as below) or does the transaction automaticaly
> rollback if a statement fails?
> Feel free to comment on the script. (Its never too late to learn).
> Yours sincerely,
> Jo
> SET NOCOUNT ON
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
> DECLARE @.MYERROR
> BEGIN TRANSACTION
> INSERT INTO sequencetoolingbak
> (
> orderid,
> partno,
> jobno,
> deliverydate,
> time_of_creation
> )
> SELECT orderid, partno, jobno, deliverydate, time_of_creation
> FROM dbo.sequencetooling AS seq
> WHERE deliverydate < DATEADD(d, -14, GETDATE())
> AND NOT EXISTS
> (
> SELECT orderid
> FROM dbo.sequencetoolingbak AS bak
> WHERE bak.orderid = seq.orderid
> )
> SET @.MYERROR = @.@.ERROR
> IF @.MYERROR <> 0
> BEGIN
> PRINT 'INSERT ERROR ' + CONVERT(VARCHAR, @.@.ERROR)
> ROLLBACK TRANSACTION
> RETURN
> END
> DELETE FROM dbo.sequencetooling
> WHERE DATEDIFF(d, deliverydate, GETDATE()) >= 14
>
> SET @.MYERROR = @.@.ERROR
> IF @.MYERROR <> 0
> BEGIN
> PRINT 'DELETE ERROR ' + CONVERT(VARCHAR, @.@.ERROR)
> ROLLBACK TRANSACTION
> RETURN
> END
> COMMIT TRANSACTION
> --
> Jo Segers
>|||The transaction will not be automatically rolled back, unless you SET
XACT_ABORT ON first. Some errors will terminate the batch, which means that
your error handler won't be executed though.
For a complete explanation of errorhandling in SQL Server, see the articles
by SQL Server MVP Erland Sommarskog on www.sommarskog.se .
Jacco Schalkwijk
SQL Server MVP
"Jo Segers" <jo.segers@.alro.be> wrote in message
news:eSgVzgsAFHA.4008@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have a job that executes the TSQL below. Do I need to do the
> errorchecking myself (as below) or does the transaction automaticaly
> rollback if a statement fails?
> Feel free to comment on the script. (Its never too late to learn).
> Yours sincerely,
> Jo
> SET NOCOUNT ON
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
> DECLARE @.MYERROR
> BEGIN TRANSACTION
> INSERT INTO sequencetoolingbak
> (
> orderid,
> partno,
> jobno,
> deliverydate,
> time_of_creation
> )
> SELECT orderid, partno, jobno, deliverydate, time_of_creation
> FROM dbo.sequencetooling AS seq
> WHERE deliverydate < DATEADD(d, -14, GETDATE())
> AND NOT EXISTS
> (
> SELECT orderid
> FROM dbo.sequencetoolingbak AS bak
> WHERE bak.orderid = seq.orderid
> )
> SET @.MYERROR = @.@.ERROR
> IF @.MYERROR <> 0
> BEGIN
> PRINT 'INSERT ERROR ' + CONVERT(VARCHAR, @.@.ERROR)
> ROLLBACK TRANSACTION
> RETURN
> END
> DELETE FROM dbo.sequencetooling
> WHERE DATEDIFF(d, deliverydate, GETDATE()) >= 14
>
> SET @.MYERROR = @.@.ERROR
> IF @.MYERROR <> 0
> BEGIN
> PRINT 'DELETE ERROR ' + CONVERT(VARCHAR, @.@.ERROR)
> ROLLBACK TRANSACTION
> RETURN
> END
> COMMIT TRANSACTION
> --
> Jo Segers|||Mike Epprecht (SQL MVP) wrote:
> Hi
> Relying on default error handling to be the same in future versions of SQL
> Server is not good at all as it could change.
> You should always handle the error yourself (like in your code) as the
> execution will be predictable.
> Regards
> Mike
OK,
Thanks for your reply.
Jo Segers

Automated restore of a user database

I am trying to restore a user database in a scheduled Job I created that executes the following command "restore database sqleligibilitydatabase from disk= 'd:\dbfrom01\sqleligibilitydatabase_db_%.bak'". I get an error 3101 "unable to get exclusive use".
Absolutely no one is on this server when I do this
From which database are you executing the restore command? Make sure it is the master database. you can run an
sp_who just before and get the output from the jobstep to a file and examine that file to see who is using the
database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"cbriscoejr" <cbriscoejr@.discussions.microsoft.com> wrote in message
news:C9D0496B-0E92-48B2-A3EE-860048654DE4@.microsoft.com...
> I am trying to restore a user database in a scheduled Job I created that executes the following command
"restore database sqleligibilitydatabase from disk= 'd:\dbfrom01\sqleligibilitydatabase_db_%.bak'". I get an
error 3101 "unable to get exclusive use". Absolutely no one is on this server when I do this
|||Hi,
Turn the database to single user before restore and make it multiuser after
restore:-
Change the script to do below :-
Alter database <dbname> set single_user with rollback immediate
go
restore database dbname from disk=...........
go
Alter database <dbname> set multi_user
Thanks
Hari
MCDBA
"cbriscoejr" <cbriscoejr@.discussions.microsoft.com> wrote in message
news:C9D0496B-0E92-48B2-A3EE-860048654DE4@.microsoft.com...
> I am trying to restore a user database in a scheduled Job I created that
executes the following command "restore database sqleligibilitydatabase from
disk= 'd:\dbfrom01\sqleligibilitydatabase_db_%.bak'". I get an error 3101
"unable to get exclusive use". Absolutely no one is on this server when I
do this
|||ALTER DATABASE sqleligibilitydatabase SET OFFLINE WITH ROLLBACK IMMEDIATE
ALTER DATABASE sqleligibilitydatabase SET ONLINE
RESTORE DATABASE......
That will clear all users out of the database. Also, make sure your job
step does not execute in the context of the target database.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"cbriscoejr" <cbriscoejr@.discussions.microsoft.com> wrote in message
news:C9D0496B-0E92-48B2-A3EE-860048654DE4@.microsoft.com...
> I am trying to restore a user database in a scheduled Job I created that
executes the following command "restore database sqleligibilitydatabase from
disk= 'd:\dbfrom01\sqleligibilitydatabase_db_%.bak'". I get an error 3101
"unable to get exclusive use". Absolutely no one is on this server when I
do this

Automated restore of a user database

I am trying to restore a user database in a scheduled Job I created that exe
cutes the following command "restore database sqleligibilitydatabase from di
sk= 'd:\dbfrom01\sqleligibilitydatabase_db_%
.bak'". I get an error 3101 "un
able to get exclusive use".
Absolutely no one is on this server when I do thisFrom which database are you executing the restore command? Make sure it is t
he master database. you can run an
sp_who just before and get the output from the jobstep to a file and examine
that file to see who is using the
database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"cbriscoejr" <cbriscoejr@.discussions.microsoft.com> wrote in message
news:C9D0496B-0E92-48B2-A3EE-860048654DE4@.microsoft.com...
> I am trying to restore a user database in a scheduled Job I created that executes
the following command
"restore database sqleligibilitydatabase from disk= 'd:\dbfrom01\sqleligibil
itydatabase_db_%.bak'". I get an
error 3101 "unable to get exclusive use". Absolutely no one is on this serv
er when I do this|||Hi,
Turn the database to single user before restore and make it multiuser after
restore:-
Change the script to do below :-
Alter database <dbname> set single_user with rollback immediate
go
restore database dbname from disk=...........
go
Alter database <dbname> set multi_user
Thanks
Hari
MCDBA
"cbriscoejr" <cbriscoejr@.discussions.microsoft.com> wrote in message
news:C9D0496B-0E92-48B2-A3EE-860048654DE4@.microsoft.com...
> I am trying to restore a user database in a scheduled Job I created that
executes the following command "restore database sqleligibilitydatabase from
disk= 'd:\dbfrom01\sqleligibilitydatabase_db_%
.bak'". I get an error 3101
"unable to get exclusive use". Absolutely no one is on this server when I
do this|||ALTER DATABASE sqleligibilitydatabase SET OFFLINE WITH ROLLBACK IMMEDIATE
ALTER DATABASE sqleligibilitydatabase SET ONLINE
RESTORE DATABASE......
That will clear all users out of the database. Also, make sure your job
step does not execute in the context of the target database.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"cbriscoejr" <cbriscoejr@.discussions.microsoft.com> wrote in message
news:C9D0496B-0E92-48B2-A3EE-860048654DE4@.microsoft.com...
> I am trying to restore a user database in a scheduled Job I created that
executes the following command "restore database sqleligibilitydatabase from
disk= 'd:\dbfrom01\sqleligibilitydatabase_db_%
.bak'". I get an error 3101
"unable to get exclusive use". Absolutely no one is on this server when I
do this