Error handling for Stored procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

Error handling for Stored procedure

Author

Topic

haineelu
Starting Member

2 Posts

Posted - 2008-09-25 : 18:43:35

I have created a stored procedure like below
Create procedure mySP
Begin
insert command for excel1
Insert command for excel2
Insert Command for excel3
Insert command for Excel4
End

though which i'm inserting data into the database tables by importing certain range of data from different excelfiles. When any of the excel files are open, i get an error saying ""Microsoft.Jet.OLEDB.4.0" for linked server "(null)" reported an error" which is quite obvious.

Now the problem:
If I run this Stored procedure through SSIS and excel3 file opend by some other user then i'll get an error. But excel1 and excel2 data was already been inserted which i dont want to happen. If SP throws error at any stage i want to rollback the changes made by the stored procedure. This stored procedure should insert data into the database table from all the files at a time or data should not be imported from any of the files.

How can i achive this?

clarkbaker1964
Constraint Violating Yak Guru

428 Posts

Posted - 2008-09-26 : 00:59:37

You have begin end but you need begin tran / commit or rollback.
Hope this sample helps. Note if you begin the transaction and you have a critical error it will never reach the commit. Recoverable errors will be caught by checking the error status and redirecting past the commit going to the rollback.

Sample

insert excel1
if @@error <> 0
goto RollbackMyImport

insert excel1
if @@error <> 0
goto RollbackMyImport

...
-- if no error occures then
commit tran
return
RollbackMyImport:
Rollback
return

cheers

You can do anything at www.zombo.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-26 : 03:33:12

quote:
Originally posted by haineelu

I have created a stored procedure like below
Create procedure mySP
Begin
insert command for excel1
Insert command for excel2
Insert Command for excel3
Insert command for Excel4
End

though which i'm inserting data into the database tables by importing certain range of data from different excelfiles. When any of the excel files are open, i get an error saying ""Microsoft.Jet.OLEDB.4.0" for linked server "(null)" reported an error" which is quite obvious.

Now the problem:
If I run this Stored procedure through SSIS and excel3 file opend by some other user then i'll get an error. But excel1 and excel2 data was already been inserted which i dont want to happen. If SP throws error at any stage i want to rollback the changes made by the stored procedure. This stored procedure should insert data into the database table from all the files at a time or data should not be imported from any of the files.

How can i achive this?

you can achieve this in SSIS by enclosing all the data transfer task inside a sequence container and making transaction property of sequence container as 'Required'. This will cause the container to start an explicit transaction each time before transfers and will rollback entire transfers if some error happens in between.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-09-26 : 09:02:44

quote:
Originally posted by visakh16

quote:
Originally posted by haineelu

I have created a stored procedure like below
Create procedure mySP
Begin
insert command for excel1
Insert command for excel2
Insert Command for excel3
Insert command for Excel4
End

though which i'm inserting data into the database tables by importing certain range of data from different excelfiles. When any of the excel files are open, i get an error saying ""Microsoft.Jet.OLEDB.4.0" for linked server "(null)" reported an error" which is quite obvious.

Now the problem:
If I run this Stored procedure through SSIS and excel3 file opend by some other user then i'll get an error. But excel1 and excel2 data was already been inserted which i dont want to happen. If SP throws error at any stage i want to rollback the changes made by the stored procedure. This stored procedure should insert data into the database table from all the files at a time or data should not be imported from any of the files.

How can i achive this?

you can achieve this in SSIS by enclosing all the data transfer task inside a sequence container and making transaction property of sequence container as 'Required'. This will cause the container to start an explicit transaction each time before transfers and will rollback entire transfers if some error happens in between.

Visakh!! This is smart solution.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-26 : 09:12:25

quote:
Originally posted by sodeep

quote:
Originally posted by visakh16

quote:
Originally posted by haineelu

I have created a stored procedure like below
Create procedure mySP
Begin
insert command for excel1
Insert command for excel2
Insert Command for excel3
Insert command for Excel4
End

though which i'm inserting data into the database tables by importing certain range of data from different excelfiles. When any of the excel files are open, i get an error saying ""Microsoft.Jet.OLEDB.4.0" for linked server "(null)" reported an error" which is quite obvious.

Now the problem:
If I run this Stored procedure through SSIS and excel3 file opend by some other user then i'll get an error. But excel1 and excel2 data was already been inserted which i dont want to happen. If SP throws error at any stage i want to rollback the changes made by the stored procedure. This stored procedure should insert data into the database table from all the files at a time or data should not be imported from any of the files.

How can i achive this?

you can achieve this in SSIS by enclosing all the data transfer task inside a sequence container and making transaction property of sequence container as 'Required'. This will cause the container to start an explicit transaction each time before transfers and will rollback entire transfers if some error happens in between.

Visakh!! This is smart solution.

Thanks Sodeep

haineelu
Starting Member

2 Posts

Posted - 2008-09-26 : 16:02:59

quote:
Originally posted by visakh16

quote:
Originally posted by haineelu

I have created a stored procedure like below
Create procedure mySP
Begin
insert command for excel1
Insert command for excel2
Insert Command for excel3
Insert command for Excel4
End

though which i'm inserting data into the database tables by importing certain range of data from different excelfiles. When any of the excel files are open, i get an error saying ""Microsoft.Jet.OLEDB.4.0" for linked server "(null)" reported an error" which is quite obvious.

Now the problem:
If I run this Stored procedure through SSIS and excel3 file opend by some other user then i'll get an error. But excel1 and excel2 data was already been inserted which i dont want to happen. If SP throws error at any stage i want to rollback the changes made by the stored procedure. This stored procedure should insert data into the database table from all the files at a time or data should not be imported from any of the files.

How can i achive this?

you can achieve this in SSIS by enclosing all the data transfer task inside a sequence container and making transaction property of sequence container as 'Required'. This will cause the container to start an explicit transaction each time before transfers and will rollback entire transfers if some error happens in between.

Hey that's a good idea. Thanks alot.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-27 : 02:38:22

quote:
Originally posted by haineelu

Hey that's a good idea. Thanks alot.

You're welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources