We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

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

rryff
rryff asked
on
Medium Priority
2,048 Views
Last Modified: 2012-08-13
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

Comment
Watch Question

Author

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)
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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.