While doing some T-SQL coding today against a SQL2K server I noticed a very frustrating issue: The xp_sprintf system stored procedure fails if the variable you send it to receive the formatted string is not long enough to hold the entire string. However, it doesn't tell you it fails. It just sets the string to a NULL and returns zero (Success). The @@ERROR server variable is also zero.
So if you're ever using xp_sprintf and you're not getting anything returned, make sure your output variable is large enough to hold the entire string! :)