Syntax error only on one specific server

Question

I have a query that runs as part of a tool I work on. It has been running fine for ages, but recently (not sure exactly when but it is probably in the past week) is now failing with a syntax error.

The query is:

SELECT sys.fn_PhysLocFormatter(%%physloc%%) FROM dbo.Categories

and the error is:

Any ideas why this is giving a syntax error, when my colleagues don't have this issue when running the same query, some of whom are using the same version of SQL Server?

The server it is running against is SQL Server 2019 Developer Edition. It was the RTM, but in trying to resolve this error I've now updated it to the latest CU32. The error still happens. I've tried it on the same version of SQL Server in a container and it doesn't error.

It's not the same instance or account, but the account is an admin user in both. What's more confusing is that it was working on my machine a few weeks back and now doesn't.

Running:

SELECT @@VERSION;
SELECT [compatibility_level] 
FROM sys.databases WHERE database_id = DB_ID();
SELECT [object_id], [type_desc] 
FROM sys.all_objects 
WHERE [object_id] = OBJECT_ID(N'sys.fn_PhysLocFormatter');

On the server where it's not working, the output is:

On a server where it is working, the output is:

I don't have another SQL 2019 server to hand other than in a container, so I'm aware that one is Windows and one is Linux.

Paul White · Accepted Answer · 2025-03-15T07:33:44.603

Assuming there is nothing unusual* about your setup and dbo.Categories is a regular table with no unusual features, for which %%physloc%% works as expected:

To troubleshoot this further, I would grab the function definition from:

SELECT OBJECT_DEFINITION(OBJECT_ID(N'sys.fn_PhysLocFormatter', N'FN'));

It should look like:

-------------------------------------------------------------------------------
-- Name: sys.fn_PhysLocFormatter
--
-- Description:
--  Formats the output of %%physloc%% virtual column
--
-- Notes:
-------------------------------------------------------------------------------
create function sys.fn_PhysLocFormatter (@physical_locator binary (8))
returns varchar (128)
as
begin
    declare @page_id binary(4);
    declare @file_id binary(2);
    declare @slot_id binary(2);
-- Page ID is the first four bytes, then 2 bytes of page ID, then 2 bytes of slot
--
set @page_id =
    substring(@physical_locator, 4, 1) +
    substring(@physical_locator, 3, 1) +
    substring(@physical_locator, 2, 1) +
    substring(@physical_locator, 1, 1);

set @file_id =
    substring(@physical_locator, 6, 1) +
    substring(@physical_locator, 5, 1);

set @slot_id =
    substring(@physical_locator, 8, 1) +
    substring(@physical_locator, 7, 1);

return
    '(' + cast(cast(@file_id as int) as varchar) +
    ':' + cast(cast(@page_id as int) as varchar) +
    ':' + cast(cast(@slot_id as int) as varchar) +
    ')';

end

If the definition doesn't look obviously corrupted, compare it byte for byte with a known good copy, in case there are any invisible characters.

If you do encounter corruption, well there's your problem. Might be confirmed by running DBCC CHECKDB on master.

Otherwise, create a dbo schema version of the same code and test it.

That will at least narrow down the list of suspects to explain why that one function is causing you problems on that one system.

* There was indeed something unusual, as Martin Smith reports in a comment. You apparently have Always Encrypted enabled, which does not like the undocumented %%physloc%% column.

With that enabled, even the following produces an error:

SELECT %%physloc%%
FROM master.dbo.spt_monitor;

Line 1, column 1
Incorrect syntax near SELECT.

The issue also requires you to enable parameterization for variables, which is strongly offered as an option when you choose to enable Always Encrypted when connecting with SSMS:

It can also be set in the general options as shown below:

With that unchecked, the error does not occur, but then you don't get the benefits of parameterisation (assuming you are actually using Always Encrypted and didn't set that option by mistake).

Syntax error only on one specific server

1 Answers1

Linked