Add versioning to an Excel document

In many cases you would like to guarantee that your end-users are using the latest version of your SQL Spreads Excel file. This example shows how to implement a version check that prevents users from saving data to the database with an outdated version of a SQL Spreads Excel document.

The example is implemented using these steps:

1. Create a new table in SQL Server and call it eg DocumentVersions.

2. Add two columns to the table: a DocumentName and a LatestDocumentVersion.

3. Add a Post processing script to your SQL Spreads document that checks the latest version of the document in the database table and compares it with the version number stored in the document.

If the user tries to save changes to the database using an outdated document, an error message is shown warning the user that the document is outdated and the user will not be able to save any data changes.

The SQL Server table storing my document versions

CREATE TABLE [dbo].[DocumentVersions](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[DocumentName] [nvarchar](max) NOT NULL,
	[LatestValidVersion] [int] NOT NULL) 

The Post Processing SQL query

DECLARE @ThisDocumentVersion AS INT     -- The Version number on the document
DECLARE @LatestDocumentVersion AS INT   -- The latest document Version number fethed from the database
DECLARE @DocumentName AS NVARCHAR(MAX)  -- The name of the document in the version check table in the database. 

-- Update these parameters for your specific document
SET @ThisDocumentVersion = 17   -- This is the version number that you increase when you create a new version of your SQL Spreads document
SET @DocumentName = 'Forecast'  -- This is a name used to be able have several SQL Spreads documents in the DocumentVersions table

-- Get the latest document version from the SQL Server database and compare it with the verison of this document
SELECT @LatestDocumentVersion = LatestValidVersion  FROM SQLSpreads.dbo.DocumentVersions WHERE DocumentName = @DocumentName
IF @LatestDocumentVersion > @ThisDocumentVersion OR @LatestDocumentVersion IS NULL
RAISERROR('You are using an old version of this document. Please contact your manager to get the latest version',16,1);