SQL Server Software Tools, Online Courses. eBooks, Articles and Webcasts - SQLNetHub

An existing History Table cannot be specified with LEDGER=ON – How to Resolve

by

Hi friends, in this post, we will be discussing about the SQL Server 2022 error message: An existing History Table cannot be specified with LEDGER=ON.

 

What are Ledger Tables in SQL Server 2022?

Ledger tables are special tables originally introduced in SQL Server 2022.

They constitute a major part of the security enhancements in SQL Server 2022.

Ledger tables, create a track of data modifications over time, and these historical records cannot be modified, since they are encrypted and powered using blockchain technology.

To this end, using Ledger Tables, among other, is an excellent option, when you have tables with sensitive data that need to be frequently audited.

We will extensively talk about SQL Server Ledger Tables in a future article.

For now, let’s proceed and further discuss about the specific error message.

 

Example that gives the error message:

Now, regarding the error message that we are examining in this post, consider the below example, where we try to create an updateable ledger table in SQL Server 2022.

CREATE TABLE dbo.[EmployeeDetails]
(
    [EmployeeID] INT NOT NULL PRIMARY KEY CLUSTERED,
    [FirstName] VARCHAR (50) NOT NULL,
    [LastName] VARCHAR (50) NOT NULL,
    [Address] VARCHAR (50) NOT NULL,
    [EmploymentDate] DATE NOT NULL
)
WITH 
(
 SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.[EmployeeDetails]),
 LEDGER = ON
);

If we execute the above T-SQL script in SQL Server 2o22 or later, we will get the below error message:

Msg 37355, Level 16, State 1, Line 3
An existing History Table cannot be specified with LEDGER=ON.

 

Why we get the error message?

The reason we get the error message, is that we tried to specify as history table, the same table as the original table that we have created. In this example, we have tried to specify the name EmployeeDetails as both the actual and historical (ledger) table.

 

Example that works

Now, let’s fix the issue with the above script, in order to make it work:

CREATE TABLE dbo.[EmployeeDetails]
(
    [EmployeeID] INT NOT NULL PRIMARY KEY CLUSTERED,
    [FirstName] VARCHAR (50) NOT NULL,
    [LastName] VARCHAR (50) NOT NULL,
    [Address] VARCHAR (50) NOT NULL,
    [EmploymentDate] DATE NOT NULL
)
WITH 
(
 SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.[EmployeeDetails_History]),
 LEDGER = ON
);

What we fixed in the above code, is that we defined a different name for the history table, since the name for this table, must be different than the actual table.

When we execute the above code, we can see that our updateable ledger table is successfully created.

 

Learn what’s new in SQL Server 2022. Enroll to the course!

Check our online course titled “SQL Server 2022: What’s New – New and Enhanced Features”
and learn all about the new features and enhancements in SQL server 2022!
(special limited-time discount included in link).

SQL Server 2022: What's New - New and Enhanced Features (Online Course)
(Lifetime access, live demos, downloadable resources, quizzes)

Learn More

 

Featured Online Courses:

 

Read Also:

 

Recommended Software Tools

Snippets Generator: Create and modify T-SQL snippets for use in SQL Management Studio, fast, easy and efficiently.

Snippets Generator - SQL Snippets Creation Tool

Learn more

 

Dynamic SQL Generator: Convert static T-SQL code to dynamic and vice versa, easily and fast.

Dynamic SQL Generator: Easily convert static SQL Server T-SQL scripts to dynamic and vice versa.

Learn more

 

Get Started with Programming Fast and Easy – Enroll to the Online Course!

Check our online course “Introduction to Computer Programming for Beginners”
(special limited-time discount included in link).

The Philosophy and Fundamentals of Computer Programming - Online Course - Get Started with C, C++, C#, Java, SQL and Python
(Lifetime Access, Q&A, Certificate of Completion, downloadable resources and more!)

Learn the main principles of Computer Programming and get introduced to C, C++, C#, Python, Java and SQL.

Learn More

 

Subscribe to our newsletter and stay up to date!

Subscribe to our YouTube channel (SQLNetHub TV)

Easily generate snippets with Snippets Generator!

Secure your databases using DBA Security Advisor!

Generate dynamic T-SQL scripts with Dynamic SQL Generator!

Check our latest software releases!

Check our eBooks!

 

Rate this article: 1 Star2 Stars3 Stars4 Stars5 Stars (1 votes, average: 5.00 out of 5)

Loading...

Reference: SQLNetHub.com (https://www.sqlnethub.com)

© SQLNetHub

Views: 2,121