none
procedure property of SqlException class returns empty RRS feed

  • Question

  • Hi

    I have a typed dataset,I created dataset with dataset designer and simply placed a storedprocedure into designer and it created a datatable for me then I have a fill command and I placed it into a try...catch. like this :

      static void Main(string[] args)
            {
                vdsTableAdapters.sp_testErrorTableAdapter da = new vdsTableAdapters.sp_testErrorTableAdapter();
                
                try
                {
                    
                    vds.sp_testErrorDataTable dt = da.GetData();
                    
                }
                catch (SqlException ex1)
                {
                    Console.Write(ex1.Procedure);
                }
                catch (Exception ex)
                {
                    Console.Write(ex.Message);
                }
               
                
            }

    the problem is when it hits a timeout error ,it goes to sqlexcpetion catch but procedure property is always empty.


    Monday, April 16, 2018 9:48 AM

All replies

  • Hi arash ghasemi,

    >>the problem is when it hits a timeout error ,it goes to sqlexcpetion catch but procedure property is always empty.

    Based on your description and related code, I create a simple demo and reproduce your issue, I think that ado.net could not connect your database, so it could not get related store produce name on your database.

    Best regards,

    Zhanglong


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, April 17, 2018 3:00 AM
    Moderator
  • thanks for reply ,the connection is correct but my problem is not why the error occurs ,because I am placing a code in stroedprocedure to produce timeout in sqlserver with an infinite loop ,the problem is here:

    catch (SqlException ex1)
                {
                    Console.Write(ex1.Procedure);
                }

    why ex1.Procedure is empty and returns empty string , it supposed to return the name of my storedprocedure  


    Tuesday, April 17, 2018 5:39 AM
  • Hi arash ghasemi,

    As you said, connection is timeout, which mean that ado.net could not connect your database. so I think it could not retrieve related SP name.

    Best regards,

    Zhanglong


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, April 20, 2018 8:44 AM
    Moderator
  • Hi arash ghasemi,

    As you said, connection is timeout, which mean that ado.net could not connect your database. so I think it could not retrieve related SP name.

    Well, @Zhanlong that's not totally true. It wasn't a connection timeout, it's an Execution Timeout. @Arash has connected to the database just fine.

    @Arash ... I tested this two different ways. First, I created a Stored Procedure that simply had an error in it (in this case, a Divide By Zero error). The catch correctly shows the Stored Procedure name. But, with a long loop and an Execution Timeout, it does not show the Stored Proc name. I don't know why, but at least now you know you're not going crazy!!  ;0)


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com


    • Edited by BonnieBMVP Friday, April 20, 2018 2:49 PM fixed typo
    Friday, April 20, 2018 2:30 PM
  • that's what I am talking about, I think there is something wrong with SQL server 
    Saturday, April 21, 2018 4:34 AM
  • There's nothing wrong with SQL Server ... the Execution Timeout exception is not caused by SQL Server, it's happening on the client side (your ADO.NET code: SqlCommand or SqlDataAdapter). Still, I would think that the SqlExeception.Procedure property should correctly show your Stored Proc name. The default SqlCommand.CommandTimeout is 30 seconds. You can change that if you need to.

    I'm wondering how you're using this. If you've just executed a Stored Procedure, you should already know the name of the Stored Procedure you just called. It would be in your code. Can you explain how/why you need to use the SqlException.Procedure property? Can you post some code where this is actually causing you a problem? (Not the test code you posted, but something you're actually using.)


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Saturday, April 21, 2018 2:05 PM
  • thanks for your reply

    Consider this scenario, you have a lot of fill operation in a one try catch block like this:

      

    DataAdapter1.Fill(DataTable1);
    DataAdapter2.Fill(DataTable2);
    DataAdapter3.Fill(DataTable3);
    DataAdapter4.Fill(DataTable4);

    and that 's where it is useful, when one of them times out then you know which one is causing the problem and you can log it in SqlException catch block and solve the problem ,putting try catch for each fill operation wouldn't be wise , it makes code dirty and it might be a lot of fill operation.

    Saturday, April 21, 2018 2:28 PM
  • Good point, Arash. The only suggestion I have is to also log the SqlException.StackTrace, which should show the line number of the Fill() statement that caused the Timeout.

    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Saturday, April 21, 2018 4:34 PM
  • don't you think this is the bug in ADO.net?
    Sunday, April 22, 2018 6:42 AM
  • don't you think this is the bug in ADO.net?

    Yes, I agree. But, the workaround that I suggested is better than nothing. Who knows when/if that bug will ever get fixed!! BTW, the best way to handle that exception so that you don't have unnecessary StackTraces, is to only log a StackTrace if ex1.Procedure is empty (because you could have other types of SqlExceptions). And then, if that bug ever does get fixed, your code will still work optimally!

    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Sunday, April 22, 2018 1:39 PM