February 12, 2022 at 6:19 am
Hi everyone
I have two stored procedures that update two tables. If one of the updates fails then I don't want either of the two stored procedures to run. If one was run then it needs to be rolled back. What is the best way to accomplish this? Should I handle that in the stored procedure? Should I handle it in the C# code? Combination of the two? C# code is part of SSIS package I am working on. Any suggestions is greatly appreciated. Thank you
foreach (string fullFileName in fileEntries)
{
string SQL1 = "dbo.UpdateTable";
SqlCommand cmd1 = new SqlCommand(SQL1, con);
cmd1.CommandType = CommandType.StoredProcedure;
SqlParameter param1;
param1 = cmd1.Parameters.Add("@FILENAME", SqlDbType.VarChar, 200);
param1.Value = fullFileName;
cmd1.ExecuteNonQuery();
string SQL2 = "dbo.UpdateImportHistory";
SqlCommand cmd2 = new SqlCommand(SQL2, con);
string fileName = fullFileName.Substring(filePathTemp.Length, fullFileName.Length - filePathTemp.Length).Trim();
fileName = (fileName.Substring(0, fileName.Length - 4) + ".zip").Trim();
cmd2.CommandType = CommandType.StoredProcedure;
SqlParameter param2;
param2 = cmd2.Parameters.Add("@FILENAME", SqlDbType.VarChar, 100);
param2.Value = fileName;
cmd2.ExecuteNonQuery();
if (File.Exists(filePathDestination + fileName))
{
bool fireAgain = false;
Dts.Events.FireInformation(0, null,
string.Format("File {0} exists in the destination folder. Further investigation is needed.",fileName),
null, 0, ref fireAgain);
}
else
{
File.Move(filePathSource + fileName, filePathDestination + fileName);
}
}
February 12, 2022 at 9:12 am
Hi
First: Use in your SP Begin try and begin catch SQL Satements.
For eq.
Begin try
Update Tabelle
Set field= 1/0
Where field2=1
--here rais an error because Division durch zero
Ende Try
Begin catch
-- here you can catch this error and throw it out
Insert into tbl_error (errornbr ) values (errornumber ())
Throw;
End catch
After you can checkout the tbl_error if there was an error
February 12, 2022 at 6:33 pm
Hi
First: Use in your SP Begin try and begin catch SQL Satements.
For eq.
Begin try
Update Tabelle
Set field= 1/0
Where field2=1
--here rais an error because Division durch zero
Ende Try
Begin catch
-- here you can catch this error and throw it out
Insert into tbl_error (errornbr ) values (errornumber ())
Throw;
End catch
After you can checkout the tbl_error if there was an error
If the error occurs in the second stored proc, how does this method roll back any changes made by the first stored proc?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
February 12, 2022 at 7:20 pm
I found this article on microsoft website. Is this what I need to do?
I am a rookie so I need some guidance to make sure I am doing the right thing. Feedback is greatly appreciated!!
February 12, 2022 at 8:25 pm
As you're already working in C#, that link looks good.
But, as always, you should test it to make sure it works the way you want.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
February 12, 2022 at 9:32 pm
Thank you for the feedback. I will definitely test it out to make sure it does what I hope it does. Thanks again for your help!
February 13, 2022 at 12:58 pm
No, is'nt.
In this case, use to begin transaction SQL Statement.
February 14, 2022 at 1:53 pm
No, is'nt.
In this case, use to begin transaction SQL Statement.
Adding that to both procedures will not solve the issue. If an error occurs in the second procedure, the first will not be rolled back.
If, however, the calls to both procs in the C# code is enclosed in a begin/commit/rollback transaction, that will rollback both procedures. The transactions inside each proc will be ignored.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
February 14, 2022 at 7:11 pm
You're using SQL Sever 2019... that means that there's little need to do such things using the likes of C#. Please see the following article and look for the 2019 stuff in it.
With the understanding that I've not touched managed code (and certainly not C# or .NET) in more than 2 decades, you wouldn't do "rollback" in this code. If a file fails to load using your "dbo.UpdateTable" call of ...
string SQL1 = "dbo.UpdateTable";
SqlCommand cmd1 = new SqlCommand(SQL1, con);
cmd1.CommandType = CommandType.StoredProcedure;
SqlParameter param1;
param1 = cmd1.Parameters.Add("@FILENAME", SqlDbType.VarChar, 200);
param1.Value = fullFileName;
cmd1.ExecuteNonQuery();
... you should have some form of error detection that identifies if the stored procedure produced an error or not. You wouldn't do a rollback because there shouldn't actually be anything to rollback. You would just handle the error as to what type of logging you were going to do and, perhaps, move the file to a "Has Errors" directory.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply