none
SqlClient.SqlConnection.GetSchema and Timeout issue RRS feed

  • Question

  •  

    Hello,

    I have an app that uses SqlConnection.GetSchema to get schema information such as tables, views, etc. It works great, except on some systems (that are outside of my control) I am getting reports of sporadic timeout errors. Now I know I can set the connection's ConnectionTimeout for the initial connection, and I can set the CommandTimeout for SQLCommands, but where can I set the equivalent of command timeout for the .GetSchema method? Or am I forced to query sysobjects directly?

     

    TIA,

    John

    Tuesday, August 12, 2008 8:34 PM

Answers

All replies

  • First you need to check exception stack to see if you get connection or command timeout. Can you post stack here?
    Wednesday, August 13, 2008 9:50 AM
    Moderator
  • No, unfortunately I can't (for now - may be able to soon) - but I can tell you for sure that the connection has been established at an earlier point. The time-out occurs when the GetSchema methods are called... so that points to a command timeout, does it not?

     

    Thanks!

    John

     

    Wednesday, August 13, 2008 7:19 PM
  • Yes, it looks like, but in this case there is no explicit command to set timeout. Did you try to run SQL Profiler to see what is going on between client and server? Since it fails on some clients, I would suspect some network issues.

    Wednesday, August 13, 2008 9:24 PM
    Moderator
  • "in this case there is no explicit command to set timeout"

     

    ... I think you answered my question. Just to verify: when using the GetSchema method on the SqlConnection object, there is no way to set command timeout from it's default (which is 30 seconds, I believe) - if you want to set the timeout, you must query the schema tables directly and use a SqlCommand object. Correct?

     

    Thanks!

    John O

     

    Saturday, August 16, 2008 2:24 PM
  • Hello John

     

    SqlConnection.GetSchema should use the SqlConnection's ConnectionTimeout value. However, at present it is always using 180 secs as the timeout value. This is a bug. Please report this bug at http://connect.microsoft.com/site/sitehome.aspx?SiteID=210. Or if you prefer I can create the bug for you.

     

     

    Thanks

    Himanshu

    Sunday, August 17, 2008 5:54 PM
  • Himanshu,

    Thanks - that's interesting about the ConnectionTimeout, and I can certainly report it - but I was really inquiring about the CommandTimeout as well - it also appears that you cannot set this from default either with GetSchema - is this also a bug?

     

    Thanks!

    John O

     

     

     

    Monday, August 18, 2008 12:47 PM
  • John

     

    You are right. There is no way for you to specify the timeout value used by the SqlCommand that is created inside GetSchema method. However, this is not a bug.

     

    If you think about the scenario, what an application would really want is to be able to specify the timeout value for GetSchema method. How the timeout is implemented inside GetSchema method is an implementation detail. It could implement it by setting the CommandTimeout value for the command it internally uses, or it could use another mechanism for implementing the timeout.

     

    Since, GetSchema method is defined on SqlConnection, it is designed to use the timeout value specified for the connection.

     

    Hope that helps!

    Himanshu

    Tuesday, August 19, 2008 1:05 AM