פבר25
Written by:
ronen ariely
25/02/2023 14:18 
Introduction
Following the inquiry in the forums, it seems that there is a bug when we are using sp_refreshsqlmodule on a stored procedure which call HIDDEN system-versioned temporal tables with hidden columns. In this post I will reproduce the issue and present a simple explanation. For solution we will need Microsoft team to fix the bug.
The short explanation is that this issue related to the HIDDEN columns in the system-versioned temporal tables.
Credit to find the but and ask about it in the forum: Aswin
The issue was reported at Microsoft Feedback system here.
Reproduce the issue and a short discussion
The short explanation is that this issue related to the HIDDEN columns in the system-versioned temporal tables.
For the discussion and for monitoring, we can **reproduce the issue**:
CREATE
TABLE
dbo.RonenA
(
C1
int
NOT
NULL
PRIMARY
KEY
CLUSTERED
, C2 nvarchar(100)
NOT
NULL
, [ValidFrom] datetime2 GENERATED ALWAYS
AS
ROW START HIDDEN
NOT
NULL
, [ValidTo] datetime2 GENERATED ALWAYS
AS
ROW
END
HIDDEN
NOT
NULL
, PERIOD
FOR
SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH
(SYSTEM_VERSIONING =
ON
(HISTORY_TABLE = dbo.RonenAHistory));
GO
INSERT
RonenA (C1,C2)
values
(1,
'Ronen'
)
GO
INSERT
RonenA (C1,C2)
values
(2,
'Ariely'
)
GO
UPDATE
RonenA
SET
C1= 3
where
c2 =
'Ronen'
GO
Since the columns `[ValidFrom]` and `[ValidTo]` are HIDDEN, using `SELECT *` will not returns these columns in the original table `RonenA`, but the same query from the history table returns all columns.
Therefore the following query will return error:
SELECT
*
FROM
RonenA
UNION
SELECT
*
FROM
RonenAHistory
GO
The solution is to explicitly add the hidden columns in the select
SELECT
* , [ValidFrom],[ValidTo]
FROM
RonenA
UNION
SELECT
*
FROM
RonenAHistory
GO
-- OK
Now, we can create SP from this query:
CREATE
PROCEDURE
RonenA_SP
as
SELECT
* , [ValidFrom],[ValidTo]
FROM
RonenA
UNION
SELECT
*
FROM
RonenAHistory
GO
EXEC
RonenA_SP
GO
All the above bahaves normal, but the issue is when we want to set the `sp_refreshsqlmodule` on the SP
exec
sp_refreshsqlmodule N
'RonenA_SP'
GO
-- ERROR: All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.
It seems like `sp_refreshsqlmodule` checks all the columns when we use "`SELECT *`" including the HIDDEN columns.
Therefore by using the query "`SELECT * , [ValidFrom],[ValidTo] FROM VerHIDDEN`" it actually "think" that we have 6 columns in the result set (four from the star and two from the explicit column names.
This lead to the error since the second query from the history table includes only four columns.
Bypass the issue
Do not use HIDDEN columns or simply use explicit columns names.
Conclusions
**Is this a bug?** YES!
in my opinion it is a bug, since the behaviour is not consistency with the idea of using the `sp_refreshsqlmodule` for cases we use implicit list of columns (using start *) and as such it does not support HIDDEN columns in the system-versioned temporal tables
Cleaning
ALTER
TABLE
[dbo].RonenA
SET
( SYSTEM_VERSIONING =
OFF
)
GO
DROP
TABLE
[dbo].RonenA
GO
DROP
TABLE
[dbo].RonenAHistory
GO