Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
kotonikak
Yak Posting Veteran
92 Posts |
 Posted - 2013-03-18 : 14:24:52
|
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 AnalyticsSET 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
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2013-03-18 : 14:33:30
|
| 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 OptimizerTG |
 |
|
|
kotonikak
Yak Posting Veteran
92 Posts |
Posted - 2013-03-18 : 14:52:30
|
| 1) yes2) 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... |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2013-03-18 : 15:08:07
|
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 Analyticsselect compatibility_levelfrom sys.databases where name = 'Analytics' Be One with the OptimizerTG |
|
|
|
kotonikak
Yak Posting Veteran
92 Posts |
Posted - 2013-03-18 : 15:15:42
|
| 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... |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2013-03-18 : 15:26:19
|
glad it worksquote:
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 OptimizerTG |
|
|
|
|
|
|
|
|
Compatibility mode error - SSMS 2012