en-UShe-IL
You are here:   Blog
Register   |  Login

Blog Archive:

Maximize
* Can be used in order to search for older blogs Entries

Search in blogs


Blog Categories:

Maximize
* Can be used in order to search for blogs Entries by Categories

Blog Tags:

Maximize
* Can be used in order to search for blogs by keywords

TNWikiSummit


Awared MVP

 


Microsoft® Community Contributor 


Microsoft® Community Contributor


 Read this before you use the blog! Maximize

Recent Entries

Minimize
פבר25

Written by: ronen ariely
25/02/2023 14:18 RssIcon

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