SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Compatibility mode error - SSMS 2012
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

kotonikak
Yak Posting Veteran

81 Posts

Posted - 03/18/2013 :  14:24:52  Show Profile  Reply with Quote
Our company just upgraded to SQL Server Management Studio 2012 and this was very helpful because I needed to use the Percentile function for the code I was writing. At first, the function wasn't working because I had to set the compatibility level to 110 mode. After I did so, the function worked. However, now it's not working again and I'm getting the same error:

The PERCENTILE_DISC function is not allowed in the current compatibility mode. It is only allowed in 110 mode or higher.

I went to the database properties section, then options, and noticed that the compatibility mode hasn't changed, it's set at "SQL Server 2012 (110)." I even used the following code to see if it would help at all even though the compatibility mode is already set to that mode:

ALTER DATABASE Analytics
SET COMPATIBILITY_LEVEL = 110

Has anyone ever had this problem or know if I need to change something else in the database properties for this function to work? Is it just a bug in the new release?
quote:

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 03/18/2013 :  14:33:30  Show Profile  Reply with Quote
I have not seen that problem. Couple things I would check:

Are you certain that the window with the error is connected to the same server instance that you see in object explorer when looking at the database properties?

I've never tried this to see what happens but did you add a cross database reference in the statement with the 2012 function to a table in a database with lower compatibility level?

Be One with the Optimizer
TG
Go to Top of Page

kotonikak
Yak Posting Veteran

81 Posts

Posted - 03/18/2013 :  14:52:30  Show Profile  Reply with Quote
1) yes
2) all the tables i'm using are from that database. made sure to check.

:/

I have no idea why it's happening now when it worked fine a couple of weeks ago. I'm testing the same code that worked too...

Edited by - kotonikak on 03/18/2013 14:58:00
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 03/18/2013 :  15:08:07  Show Profile  Reply with Quote
And are you getting the error when executing the statement in a management studio query window or from something else?

Just to confirm (because this doesn't make sense)
Please run this in the same window that generates the compatibility error:

use Analytics
select compatibility_level
from   sys.databases 
where  name = 'Analytics'



Be One with the Optimizer
TG
Go to Top of Page

kotonikak
Yak Posting Veteran

81 Posts

Posted - 03/18/2013 :  15:15:42  Show Profile  Reply with Quote
ok never mind...now it works.
i had created a table with a variety of unions...i executed each select statement and then executed the whole thing and it worked....idk why that error was happening...

Edited by - kotonikak on 03/18/2013 15:19:20
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 03/18/2013 :  15:26:19  Show Profile  Reply with Quote
glad it works

quote:

select '80.0% (5 yr)' as [Path],
-1*(select top 1 PERCENTILE_DISC(0.8) within group (order by Reserve_Loss) over () from Analytics.dbo.TotalRisk)/1000 as Reserve,
-1*(select top 1 PERCENTILE_DISC(0.8) within group (order by UW_Loss) over () from Analytics.dbo.TotalRisk)/1000 as Underwriting,
-1*(select top 1 PERCENTILE_DISC(0.8) within group (order by Asset_Loss) over () from Analytics.dbo.TotalRisk)/1000 as Asset,
' ' as Credit,
-0.1*(select top 1 PERCENTILE_DISC(0.8) within group (order by Total_Loss) over () from Analytics.dbo.TotalRisk)/1000 as Operational,
-1*(select top 1 PERCENTILE_DISC(0.8) within group (order by Total_Loss) over () from Analytics.dbo.TotalRisk)/1000 as Total


have you considered selecting from the table once with rather than once per column? If the results are equivalent the performance may be better:

select -1 * PERCENTILE_DISC(0.8) within group (order by Reserve_Loss) over () / 1000 as Reserve,
       -1 * PERCENTILE_DISC(0.8) within group (order by UW_Loss) over () / 1000 as Underwriting,
       ....
from   Analytics.dbo.TotalRisk


EDIT:
I notice you have fully qualified references meaning you could have been in a different database when the code was executed and you wouldn't necessarily know. Perhaps when you ran the entire code block rather than just one of the unioned parts it included a USE statement that put you in the 110 database.

Be One with the Optimizer
TG

Edited by - TG on 03/18/2013 15:31:36
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.23 seconds. Powered By: Snitz Forums 2000