Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

On several of my servers, I have "Setting database option Compatibility_Level" messages filling up the SQL log and application event log to the tune of twice every 5 seconds. This makes finding anything else in the SQL log nearly impossible.

I'm aware that this is a known bug in MS Reporting Services (marked "Closed as Won't Fix"). I'm looking for other ideas to resolve this. Using profiler, I found the code being run by the reporting server is:

DECLARE @currVer nvarchar(128), @currMajorVer nvarchar(32)
     , @idx int, @currMajorVerInt tinyint ;

SELECT @currVer = CONVERT(nvarchar(128), ServerProperty('ProductVersion')) ;
SET @idx = CHARINDEX('.', @currVer, 0) ;
SET @currMajorVer = SUBSTRING(@currVer, 1, @idx-1) ;
SET @currMajorVerInt = CONVERT(tinyint, @currMajorVer) ;

DECLARE @dbname sysname

IF @currMajorVerInt =  10
BEGIN       
    SELECT @dbname=DB_NAME()
    EXEC sp_dbcmptlevel @dbname, 100 ;      
END

Ideas?

The listed "workaround" suggested modifying the system stored procedure sp_dbcmptlevel, which although possible, is not recommended for a variety of good reasons.

Another thread on this issue suggested changing the system account used by the reporting services service. I'm not sure if that applies to our situation. Has anyone found that this resolves the issue for you?

Only other idea I had: is there any way to intercept and either block a query from being executed, or to alter the syntax of the query before it is executed? Like a kind of INSTEAD TRIGGER or something?

share|improve this question

1 Answer 1

Only a couple of things come to mind.

  1. Hack sp_dbcmptlevel and add a "IF suser_sname() = 'Your SSRS Account' RETURN" to the very top of it.

  2. Add a DDL trigger to the database that simply rolls back any alter database statements.

There's no way to intercept the database call and prevent it from being sent to the database engine.

share|improve this answer
    
The listed workaround modifies sp_dbcmptlevel by adding `if @input_cmptlevel = @new_cmptlevel' to make sure its not already at the level you're trying to switch to. Not sure about hacking system procs, though. I like the DLL trigger idea, will investigate it to see if that might work, will need to exclude other possibly valid ALTER DATABASE types... –  BradC Nov 29 '11 at 17:35
1  
I didn't say it was a good idea, just an idea. :) –  mrdenny Nov 29 '11 at 17:49
    
Hmm, finding links that say that ALTER_DATABASE triggers don't always roll back. Guess I will have to test. Also not sure if returning an error message to Reporting Services from the trigger will cause an "unhandled exception" error on the website. –  BradC Nov 29 '11 at 18:49
    
You don't necessarily have to return an error to SSRS, just silently return from sp_dbcmptlevel. –  Simon Righarts Nov 29 '11 at 20:11
    
Didn't mention anything about returning an error. Just bail out of the procedure so that SSRS thinks that it ran successfully. –  mrdenny Nov 30 '11 at 2:33

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.