Freigeben über

"SQL Execution Error: Timeout expired. The timeout period elapsed..." while opening view from SSMS

  • Artikel

Another recently answered question from a colleague. My colleague posted the following question (modified a bit to leave relevant information only):

From: <Removed_Intentionally>
Sent: <Removed_Intentionally>
To: <Removed_Intentionally>
Subject: SSMS -> Open View timeout - Help

Hi guys Sorry for the Wide Distrib but I wanted the feedback from the Dev guys also ,

I have a client who has a view joining 7 tables, one of which has a few million rows. In SSMS when they click Open View after 30 seconds he gets the below:

Microsoft SQL Server Management Studio

SQL Execution Error.

Executed SQL statement: Select <> AS ...

Error Source: .Net SqlClient Data Provider

Error Message: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

He can run the view via a Query Window and it takes 3 – 5 minutes depending on load but return the results... ...he says that they have a third party app that also uses this view and they see the same error from the application also , so I was wondering if I could get some feedback on the below:

Is this definitely not configurable and by design from SSMS ?

Is it possible that the application uses the same class or something which has the timeout hard coded at 30 seconds or is it possible that from the app this is configurable via the connection string or something?

Thanks guys

PS - (SQL 2005 SP2)

This was my initial answer:

From: Ignacio Alonso Portillo
Sent: <Removed_Intentionally>
To: <Removed_Intentionally>
Subject: RE: SSMS -> Open View timeout - Help

Sorry for late response. It took me a little while to actually find where this information is taken from.

It is actually not SSMS, but VDT related (being VDT, Visual Database Tools, which is written and owned by DevDiv BU)

This timeout setting value is configurable through the DWORD value SQLQueryTimeout at HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\90\Tools\Shell\DataProject.

For those of you who have access to DevDiv source depot, the relevant file is <Removed_Intentionally> from the Whidbey branch.

Thanks,

Nacho

And another one, to answer his second question which I forgot to answer in the first place :-)

From: Ignacio Alonso Portillo
Sent: <Removed_Intentionally>
To: <Removed_Intentionally>
Subject: RE: SSMS -> Open View timeout - Help

BTW, I forgot to answer your second question (Is it possible that the application uses the same class or something which has the timeout hard coded at 30 seconds or is it possible that from the app this is configurable via the connection string or something?). AFAIK, VDT is a component DevDiv BU only redistributes to Visual Studio (within their unit), and to SQL Server (outside their business unit). There are no classes (again afaik) from the .NET framework wrapping this functionality implemented in VDT. So customers cannot use those VDT tools outside SSMS, Whidbey or DataDude.

Now, it’s also worth recalling that 30 seconds is like a magic number, very commonly used as the default value for connection or command timeouts, across different data access technologies or products. So, it might perfectly be that the other product customer is referring to is completely independent from VDT, and only shares with it two things: 1) a similar look and feel, and 2) the same default timeout value.

Hope this answers your (and customer’s) questions. If not, ping me offline.

Thanks,

Nacho

I hope this post helps someone someday.

Comments

  • Anonymous
    April 02, 2008
    Thanks jalonso for this very nice ticket ! I had the exact same problem and your solution worked perfectly fine ! Helped me a lot !

  • Anonymous
    June 23, 2008
    worked great! For any novices or rusty users, you can modify keys by running regedit from the Run command.

  • Anonymous
    July 08, 2009
    Thanks for the tip. I did exactly what suggested - modifying SQLQueryTimeout to 300, but it still times out in 30 seconds.   I am using SSMS 2005.  Any further tip would be greatly appreciated.