asked on

Join to MSrepl_Commands table xact_seqno field which is varbinary to a varchar value retreived elsewhere produces 0 results.

The following code yeilds zero results.

DECLARE @SEQNO Varbinary(100)

SET @SEQNO = (select CONVERT(VARBINARY,LAST_POSITION) FROM DI_CDCTest.dbo.AL_CDC_CHECKPOINT where CDC_TABLE = 'JDEGAACCOUNTBALANCES')

SELECT com.xact_seqno, tr.entry_time, com.partial_command, com.command
FROM MSrepl_transactions tr, MSrepl_commands com
WHERE com.publisher_database_id = 2
AND com.article_id = 10
AND tr.xact_seqno=com.xact_seqno
AND com.xact_seqno = @SEQNO
ORDER BY com.xact_seqno, com.command_id

If I hard code @SEQNO with 0x0000272C0000111E0001 It works.

Why does a conversion from the varchar field of value 0000272C0000111E0001 return this 0x3030303032373243303030303131314530303031. ????

I have a varchar field that contains 0000272C0000111E0001 I need to compare this to xact_seqno in msrepl_commands. How can I do this? Any help is Much appreciated. Thanks

rryff

ASKER

Found the solution: The following works

DECLARE @SEQNO AS VARCHAR(100)

USE DI_CDCTest

SELECT
@SEQNO = '0x' + MIN(CP.LAST_POSITION)
FROM AL_CDC_CHECKPOINT CP

USE distribution
declare @string varchar(2000)

set @string = 'Select * From MSRepl_Commands Where xact_seqno >= '+ @SEQNO

exec (@string)

ASKER CERTIFIED SOLUTION

EE_AutoDeleter

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial