sp_diff

for RuBoard

You may recall from Chapter 4 that we wired VSS's difference-checking tool into Query Analyzer's tools menu so that you could difference check T-SQL scripts. Sp_diff takes a similar approach. It calls VSS's command-line interface, SS.EXE, and accesses its differencing engine to check the differences between two files that you supply. It returns the differences VSS finds between the files as a result set. Here's the code (Listing 21-2):

Listing 21-2 sp_diff.

 USE master GO IF OBJECT_ID('sp_diff') IS NOT NULL   DROP PROC sp_diff GO CREATE PROC sp_diff @file1 sysname='/?', @file2 sysname=NULL /* Object: sp_diff Description: Returns the differences between two text files as a result set (uses VSS) Usage: sp_diff @file1=full path to first file, @file2=fullpath to second file Returns: (None) $Author: Ken Henderson $. Email: [e-mail] $Revision: 1.0 $ Example: sp_diff 'c:\customers.sql', 'c:\customers2.sql' Created: 2001-01-14. $Modtime: 2001-01-16 $. */ AS SET NOCOUNT ON IF (COALESCE(@file1+@file2,'/?')='/?') GOTO Help DECLARE @cmd varchar(1000) SET @cmd='SS diff '+@file1+' '+@file2+' -Yadmin' CREATE TABLE #diffs (line int identity, diff varchar(8000))  INSERT #diffs (diff)   EXEC master   ..   xp_cmdshell @cmd  SELECT ISNULL(diff,'') AS diff FROM #diffs ORDER BY line DROP TABLE #diffs RETURN 0 Help: EXEC sp_usage @objectname='sp_diff', @desc='Returns the differences between two text files as a result set (uses VSS)', @parameters='@file1=full path to first file, @file2=fullpath to second file', @author='Ken Henderson', @email='[e-mail]', @version='1',@revision='0', @datecreated='20010114', @datelastchanged='20010116', @example='sp_diff ''c:\customers.sql'', ''c:\customers2.sql'' ' RETURN -1 GO EXEC sp_diff 'c:\customers.sql', 'c:\customers2.sql' 

(Results)

 diff -------------------------------------------------------------------------------Diffing: c:\customers.sql Against: c:\customers2.sql   6 Change:   [CompanyName] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_A         To:   [Company] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NO  15    Del:   [Fax] [nvarchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , 

Here, I've supplied two scripts to sp_diff to difference check. It returns the two filenames at the top of the listing, then lists the differences between them. It shows that the CompanyName column was shortened to just Company in the second table, and that line 15 was deleted in the second table. How do we know which table the differences apply to? Look closely at the verbiage at the top of the listing "Diffing Against." This means that Customer2.SQL is being treated as the master copy of the code, and the listing is displaying the steps you would need to take to make Customer.SQL match it exactly.

As with sp_readtextfile, sp_diff calls on xp_cmdshell to do the real work of the procedure. It uses xp_cmdshell to call SS.EXE, the VSS command-line utility, and passes the two files to it so they can be difference checked. Notice the -Y parameter. This is the user name with which you want to log into VSS. Here I've just supplied the admin user with no password for simplicity's sakeyou'll likely use a different one.

We trap the output from xp_cmdshell in a temporary table, and, as with sp_readtextfile, cleanse the data of NULLs as we return it. The end result is a basic, yet very functional, difference-checking facility from Transact-SQL.

for RuBoard