First step is to read the SQL script file in BLOB format by using OPENROWSET().
:setvar script1 C:\Script1.sql DECLARE @val BIGINT SELECT @val = master.sys.fn_repl_hash_binary(BulkColumn) FROM OPENROWSET(BULK '$(script1 )', SINGLE_BLOB) AS A
OPENROWSET(BULK ...) uses BULK rowset provider that accepts full file path and read mode such as BLOB, CLOB. The sentance above returns binary data of the file in BulkColumn.
Next step is to hash the binary data. SQL Server has a built-in function called fn_repl_hash_binary() that accepts binary data and returns a hash value.
So by comparing this @val with the version column value of version table, I was able to decide whether the SQL script is modified or not.
One of the problem I ran into though is OPENROWSET(BULK) only accepts FULL file path. I only can specify relative path in my case. So it was a headache... Fortunately the SQL script used SQLCMD mode,so I can get around this problem by using this approach.
(1) First at the begining of main script, I added the following statements. It calls VersionCheck.cmd batch file (see (2)) that will create _ChkSum.sql. Running _ChkSum.sql actually calculates the checksum of SQL script file(s) and save the result to #ChkSum temp table. Once _ChkSum.sql is run, I read the #ChkSum temp table and fetch the chksum value. Then I simply compare the variable with stored SQL table value.
-- Run version check. If there is no script change, exit. :!! VersionCheck.cmd :r _ChkSum_.sql GO DECLARE @chk NUMERIC(38) SELECT TOP 1 @chk = chk FROM #ChkSum IF EXISTS (SELECT * FROM Master.Sys.Databases WHERE name = 'MyDB') IF EXISTS (SELECT * FROM $(dbName).Sys.Tables WHERE name = 'VersionControl') IF EXISTS (SELECT * FROM $(dbName).dbo.VersionControl WHERE Id=1 AND ChkSum=@chk) BEGIN RAISERROR('No change. Exit now.', 2, 1); END GO
(2) Here is VersionCheck.cmd. This file contains OPENROWSET() statement that calculates SQL Script file checksum. The example below calculate the checksum of two SQL files and add them up.
@echo off @echo :setvar script1 script1.sql > _ChkSum_.sql @echo :setvar script2 script2.sql >> _ChkSum_.sql @echo SET NOCOUNT ON >> _ChkSum_.sql @echo DECLARE @val BIGINT >> _ChkSum_.sql @echo DECLARE @chk NUMERIC(38) >> _ChkSum_.sql @echo SELECT @val = master.sys.fn_repl_hash_binary(BulkColumn) >> _ChkSum_.sql @echo FROM OPENROWSET(BULK '%~dp0$(script1)', SINGLE_BLOB) AS A >> _ChkSum_.sql @echo SET @chk = CAST(@val as NUMERIC(38)) >> _ChkSum_.sql @echo SELECT @val = master.sys.fn_repl_hash_binary(BulkColumn) >> _ChkSum_.sql @echo FROM OPENROWSET(BULK '%~dp0$(script2)', SINGLE_BLOB) AS A >> _ChkSum_.sql @echo SET @chk = @chk + CAST(@val as NUMERIC(38)) >> _ChkSum_.sql @echo CREATE TABLE #ChkSum(chk NUMERIC(38)) >> _ChkSum_.sql @echo INSERT #ChkSum VALUES (@chk) >> _ChkSum_.sql @echo GO >> _ChkSum_.sql
- To specify absolute full file path, I used %~dp0 in OPENROWSET(). This will be replaced by current working directory.
- Please note that I used @chk variable as NUMERIC(38) because arithmetic overflow can occur if @chk is defined as BIGINT.
- After sum of each checksum is added up, I saved the value to temp table (#ChkSum). Temp table can last in session so it can read in main script.
But with relatively big file, this approach worked fine to me.