March 5, 2015 at 8:43 pm
Hi,
Problem:
I want to set compatibility_level only when it is greater than 110.
Solution:
Select the compatibility level and if it is greater than 110, I alter database set compatibility level=110
ISSUE
Irrespective of IF Exist statement the alter database statement is executed all the time.
Here is the sql statement
IF EXISTS (
SELECT * FROM sys.databases where compatibility_level >110 AND name='mydatabase'
)
BEGIN
ALTER DATABASE mydatabase SET COMPATIBILITY_LEVEL = 110
END
What is that I am missing here ??
please support.
March 5, 2015 at 9:50 pm
Nothing Wrong with Your statement.
What do you mean by executing all the time ?
March 8, 2015 at 10:14 am
I mean, even if there are no record found for "query" under IF EXISTS (query), the alter database get executed.
Query returns zero record but alter database statement gets executed.
March 8, 2015 at 3:57 pm
Quick suggestion, do this instead
😎
USE master;
GO
IF (SELECT
SD.compatibility_level
FROM sys.databases SD
WHERE SD.name = N'mydatabase'
) > 110
BEGIN
ALTER DATABASE mydatabase SET COMPATIBILITY_LEVEL = 110
END
March 9, 2015 at 9:15 am
I would also check how you are confirming that the ALTER DATABASE command is actually being executed.
I tested your code to see if there were any strange quirks with how that was run, and it runs as expected. If mydatabase does not have a compatibility higher than 110, the ALTER DATABASE does not fire.
My best guess based on the information given is that the ALTER DATABASE is indeed not firing, and that you're being misled into thinking that it did. How are you checking that the ALTER DATABASE ran?
Cheers!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply