Thursday, November 4, 2010

[WAT] Why I hate ISNUMERIC

So, ISNUMERIC is simple, right? Put in numbers, and it tells you whether it is.
Except it has very specific exceptions you may not know about.

Any of these will come back with ISNUMERIC = 1:
  • 0D123
  • 123D50
  • 123E50
  • $,,1,,.1

Currency doesn't count (and that's ALL currency symbols), D and E don't count in certain circumstances, commas and periods don't count.



Instead, use something like this:
if (select PATINDEX('%[^0-9.]%','$00.01')) = 0  print 'numeric' 
 
(AND DON'T  FORGET THAT IT WILL EXCLUDE NULLS) 

[Replication] more replication trouble tracking

--get list of the possible databases by querying the publisher

SELECT * FROM distribution.dbo.MSpublisher_databases

--Now figure out what the article number is
--the database name there is the database that the publication is in.
Replication_Master..sp_helparticle @publication = 'User_Profiles'


--Now that you have the publisher database ID (step 1) and the article id (step 2)
--get the list of commands.
EXEC distribution..sp_browsereplcmds @publisher_database_id = 2, @article_id = 369


--And if you're really lucky, in sqlmonitor you'll get the following:
--(Transaction sequence number: 0x00018A0500009072002A00000000, Command ID: 1)
--in which case...

EXEC distribution..sp_browsereplcmds @publisher_database_id = 2, @article_id = 369, 
@xact_seqno_start = '0x00018A0500009072002A00000000', @xact_seqno_end = '0x00018A0500009072002A00000000'
--(set both start and end to the same value, the one in the error message)



-----------
simplified version 2013/07/12
was having more problems. 

first, look at commands:
use distribution 
 select top 1000 * from dbo.MSrepl_commands ORDER BY command_id  DESC

Now, from there we have two options.  The first gives you the name of the problem child.

 SELECT * FROM distribution.dbo.MSpublisher_databases
 SELECT * FROM dbo.MSarticles ORDER BY article_id

The second gives you the exact commands run


EXEC distribution..sp_browsereplcmds @publisher_database_id = 6, @article_id = 338, 
@xact_seqno_start = '0x0005334A0000049B0001', @xact_seqno_end = '0x0005334A0000049B0001'
--begin and end seqno will be the same.

and if you need to remove old records because you're running out of space and just did a bunch of changes....
(remembering that it saves all the commands for 72 hours by default)
   EXEC dbo.sp_MSdistribution_cleanup @min_distretention = 0, @max_distretention = 50