I have a script that creates the following stored procedure :
CREATE PROCEDURE [dbo].[GetDurationFree]
@EquipmentName varchar(50)
AS
UPDATE dbo.EquipmentMessages
SET UnlockDuration = (SELECT DATEDIFF (SECOND,
(SELECT TOP 1 LAG(TimeUnlock) OVER (ORDER BY TimeUnlock) TimeUnlock
FROM dbo.EquipmentMessages
WHERE EquipmentName = @EquipmentName
ORDER BY TimeLock DESC),
(SELECT TOP 1 TimeLock FROM dbo.EquipmentMessages
WHERE EquipmentName = @EquipmentName
ORDER BY TimeLock DESC)))
WHERE TimeLock = (SELECT MAX(TimeLock) FROM dbo.EquipmentMessages
WHERE EquipmentName = @EquipmentName);
The only problem is that It uses a Lag, when I try to execute it I get the following errors :
Msg 195, Level 15, State 10, Procedure GetDurationFree, Line 6
'LAG' is not a recognized built-in function name.Msg 156, Level 15, State 1, Procedure GetDurationFree, Line 12
Incorrect syntax near the keyword 'ORDER'.
I was reading online and someone suggested the following :
ALTER DATABASE yourDBName
SET COMPATIBILITY_LEVEL = 110
However when I run this, I get the following error :
Msg 15048, Level 16, State 1, Line 1
Valid values of the database compatibility level are 80, 90, or 100.
I am running
SQL Server 2014 Management Studio, the express version
However when I enter the following SELECT @@version
I get :
Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64)
Mar 29 2009 10:11:52
Copyright (c) 1988-2008 Microsoft Corporation
Express Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
What I ended up doing is going into the control panel under uninstall a program and deleted everything that has to do with SQL. Then I re installed the program and It worked fine.
I noticed that there a early version of 2008 on the computer that wasn't installed. Maybe the problem comes from there.