SQL Server Internal error: An expression services limit has been reached. Please look for potentially complex expressions in your query, and try to simplify them.

sql server 2014

October 28, 2016 | Posted in SQL Server

For me, I received this error having got up to 1110 case when statements i.e.

case

when Something = ’1′ then ’2′

else null end as Test

So I assume this is the upper limit allowed by SQL Server.

Read More →

Incorrect Syntax Near ‘BEGIN’. Expecting EXTERNAL.

sql server 2014

October 3, 2016 | Posted in SQL Server

Example of where this might happen:

IF NOT EXISTS (SELECT NULL FROM sys.objects [objects] JOIN sys.schemas [schemas] ON [objects].schema_id = [schemas].schema_id WHERE [schemas].[Name] = ‘dbo’  AND [objects].[Name] like ‘%usp_MySproc%’)
BEGIN

CREATE PROCEDURE [dbo].[usp_MySproc]
(
@SiteID INT,
@JobEntryID INT,
@LastQueried datetime,
@ResourceId INT
)
AS
BEGIN

SELECT 1;
END
END
GO

 

Here of how to fix it:

IF NOT EXISTS (SELECT NULL FROM sys.objects [objects] JOIN sys.schemas [schemas] ON [objects].schema_id = [schemas].schema_id WHERE [schemas].[Name] = ‘dbo’  AND [objects].[Name] like ‘%usp_MySproc%’)
BEGIN
EXEC(‘CREATE PROCEDURE [dbo].[usp_MySproc]
(
@SiteID INT,
@JobEntryID INT,
@LastQueried datetime,
@ResourceId INT
)
AS
BEGIN

SELECT 1;
END’)
END
GO

Read More →