For SQL 2008 R2, data views.
Looking for a timeout control using Microsoft SQL Server Management Studio (SSMS) that is NOT at the Server Level, and/or is NOT dependent on query timeout as set by application initiating the query.
I have been unable to find timeout controls by View, User or Role using SSMS.
There are server level timeouts (remote query timeout http://technet.microsoft.com/en-us/library/ms189040.aspx ), but as I understand it would also impact the main applications use of the database, which lives on a different server (main application gets to define its own limits).
I found DBPROP_COMMANDTIMEOUT http://msdn.microsoft.com/en-us/library/windows/desktop/ms712980(v=vs.85).aspx but not seeing any way to control it by View. And this http://serverfault.com/questions/242300/set-command-timeout-from-sql-server-2005-rather-than-through-the-code says "Command timeouts are always set by the client"
Considerations: These are connections for reporting from the production database of a major application, where the archived datasets (midnight last night) are not sufficiently current. We have a requirement to allow some access; we have a responsibility to not let that access adversely impact the application.
- I have control over what is in the Data View Query, and any SQL settings that do not impact the main application.
- I have control over the User Permissions that can access the data view
- I do not have control over external report application(s)
- Snapshot isolation is not an option (application considerations)
- The hints NOLOCK & READPAST are not viable solutions in SQL 2008 R2 http://stackoverflow.com/questions/15770402/sql-2008-nolock-vs-readpast-considerations-for-reporting-accuracy