C# is returning an error when it runs a SQL Server stored procedure

[csharp4594]

Hi everyone
I need a bit of help with my c# script. It always rolls back the changes. I ran the stored procedure in SQL Server manually and it ran with no issues so there is nothing wrong with the stored procedure. So I suspect the issue is with the C# script. My questions are:
1. Why is the code always rolling back? How do I fix it?
2. Why am I getting a timeout error? I used the timeout property but then the code won't rollback when there is an error. To test this scenario, I added cmd.CommandTimeout = 0 to the C# script below and added "select 1/0" in the RunStoredProcedures logic so the stored procedure will guarantee a failure hence roll back logic kicks in. No rollback happens if I use the timeout property.

c# script:

       public void Main()
        {
            // TODO: Add your code here
            try
            {
                string connectionString = @"Data Source=localhost;Initial Catalog=testDB;Integrated Security=True";
                SqlConnection con = new SqlConnection(connectionString);
                con.Open();

                SqlCommand cmd = con.CreateCommand();

                SqlTransaction transaction;

                transaction = con.BeginTransaction();

                //cmd.Connection = con;
                //cmd.Transaction = transaction;

                try
                {
                    string SQL = "dbo.RunStoredProcedures";
                    cmd = new SqlCommand(SQL, con, transaction);
                    cmd.CommandType = CommandType.StoredProcedure;
                    int rowsAffected = cmd.ExecuteNonQuery();
                    transaction.Commit();

                }
                catch (Exception ex1)
                {
                    bool fireAgain = false;
                    Dts.Events.FireInformation(0, null, string.Format("Message: {0}", ex1.Message), null, 0, ref fireAgain);

                    try
                    {
                        transaction.Rollback();

                        fireAgain = false;
                        Dts.Events.FireInformation(0, null, string.Format("Rollback of RunStoredProcedures successful"), null, 0, ref fireAgain);
                    }
                    catch (Exception ex2)
                    {
                        fireAgain = false;
                        Dts.Events.FireInformation(0, null, string.Format("Rollback Exception Type: {0}", ex2.GetType()), null, 0, ref fireAgain);

                    }

                }
                con.Close();
                Dts.TaskResult = (int)ScriptResults.Success;

            }
            catch (Exception ex3)
            {
                Dts.Events.FireError(0, "Exception from Script Task", ex3.Message + "\r" + ex3.StackTrace, String.Empty, 0);
                Dts.TaskResult = (int)ScriptResults.Failure;

            }

        }

stored procedure:

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO


ALTER procedure [dbo].[RunStoredProcedures]

as


EXEC dbo.SP1

EXEC dbo.SP2

output:

Information: 0x0 at Run Stored Procedures: Message: Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
Operation cancelled by user.
Warning: Null value is eliminated by an aggregate or other SET operation.
Warning: Null value is eliminated by an aggregate or other SET operation.
Warning: Null value is eliminated by an aggregate or other SET operation.
The statement has been terminated.
Information: 0x0 at Run Stored Procedures: Rollback of RunStoredProcedures successful

 

[Skydiver]

Moving thread to a more appropriate database sub-forum.

 

[Skydiver]

Not related to your problem. About 95% of the time, C# code is called "code" not "scripts". The only time C# code is called "script" is when C# is used as a scripting language by another engine or framework (ex. Unity, some stock analysis software, etc.).

 

[Skydiver]

Anyway, it doesn't look to be a problem with your C# code, but rather it's taking too long for your stored procedures to finish running. I suggest following the execution time out troubleshooting steps:

Timeout expired messages when connecting to SQL Server - SQL Server

Fixes timeout expired errors when you connect to SQL Server and helps you verify and troubleshoot the errors.

learn.microsoft.com

 

[Skydiver]

I forgot to mention that the default timeout is 30 seconds. If your stored procedure doesn't finish in that time, that is the most likely reason why you are getting that exception.

 

[jmcilhinney]

Run your proc in SSMS and see how long it takes. That will give you an idea of how you need to set the CommandTimeout to allow the command to finish processing. If for instance, you expect the proc to take 2 minutes to execute, you'd want to set the timeout on the command to 150 or 180 or the like, to allow a bit of clearance.