Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1883
  • Last Modified:

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

0
rryff
Asked:
rryff
1 Solution
 
rryffAuthor Commented:
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)
0
 
EE_AutoDeleterCommented:
rryff,
Because you have presented a solution to your own problem which may be helpful to future searches, this question is now PAQed and your points have been refunded.

EE_AutoDeleter
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now