Comparing Two Image fields in a Table Objects

 

In this article my main objective is to compare 2 image fields (old image and new image) of a table objects. For this I use extended stored procedure ex_md5 from master database.

I provide the complete set of scenario for this test environment, it can be depends upon you to change the scenario according to you.

Step-1

Extract or build the DLL file xp_md5.dll and place it in 

C:\Program Files\Microsoft SQL Server\MSSQL\Binn(or wherever appropriate).

A precompiled DLL is in the Release directory of the source distribution.

Step-2

Create an Extended Stored Procedure called xp_md5 in the "master" database.

Right-click "Extended Stored Procedures" under the master database in the Server Manager and click "New Extended Stored Procedure...". Enter xp_md5 for the "Name" and for the "Path", enter the full path to xp_md5.dll.

 

Adding extended stored procedure.

 

USE master;

EXEC sp_addextendedproc 'xp_md5', 'xp_md5.dll'

Step-3

Create an User define Function

CREATE FUNCTION [dbo].[fn_md5x] (@data IMAGE, @len INT = -1)

RETURNS CHAR(32) AS

BEGIN

DECLARE @hash CHAR(32)

EXEC master.dbo.xp_md5 @data, @len, @hash OUTPUT

RETURN @hash

END

Step-4

Compare old and new Image

SELECT *

FROM   tablename

WHERE  dbo.fn_md5x(oldimage, datalength(oldimage))

               <> dbo.fn_md5x(newimage, datalength(newimage))

 

 

I think the article is quite informative and thanking for giving time on it.

 

 

Posted by: MR. JOYDEEP DAS