Wise man or Wise guy? You Decide

Just another WordPress.com weblog

Archive for the ‘Code’ Category

What happened to that email?

Posted by sqlwiseguy on September 10, 2009

This question:

Created script to send mails using sp_send_dbmail- working like a charm.
Now searching for a way to get result code of sent mail (like Success = Recipient got it,
Failure = Did not get regardless of the reason).
I mean SP return codes 0 (success) or 1 (failure) refer to correct mail Profile, not missing Recipient, etc.
Frankly not sure this is possible as it looks like outside Sql Server authority/responsibility?!

asked in this thread on SQLServerCentral prompted me to do some research into Database Mail.  The result of the research is that there is no way to get this information from SQL Server.

Basically the way Database Mail/sp_send_dbmail works is that the message is placed in a Service Broker queue (sp_send_dbmail returns success), the external Database Mail executable reads the queue and sends the message to the designated SMTP mail server.  If the mail server accepts the message then Database Mail is done and the status is set to sent.  So, if you have an incorrect email address or the receiving server refuses it, SQL Server has no way to know.  In order to find this out you would need to use a valid Reply To or From email address and monitor that mailbox.

Here’s the query I use for checking Database Mail:

SELECT
SEL.event_type,
SEL.log_date,
SEL.description,
SF.mailitem_id,
SF.recipients,
SF.copy_recipients,
SF.blind_copy_recipients,
SF.subject,
SF.body,
SF.sent_status,
SF.sent_date
FROM
msdb.dbo.sysmail_faileditems AS SF JOIN
msdb.dbo.sysmail_event_log AS SEL
ON SF.mailitem_id = SEL.mailitem_id

Let me know if you have any better ways to find errors for Database Mail.

Posted in Code, Database Mail, SQL Server, SqlServerCentral | Leave a Comment »

The Deception of IsNumeric()

Posted by sqlwiseguy on April 14, 2009

Has anyone else ever used the IsNumeric() function to try to eliminate this error (or similar):

Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.

yet still get the error? Isn’t that what the function is for?  Here’s what is says in BOL:

ISNUMERIC returns 1 when the input expression evaluates to a valid numeric data type; otherwise it returns 0

But run this code (generates a list of ASCII Characters):

;WITH cteNumbers AS
   
(
   
SELECT TOP 255
        ROW_NUMBER
() OVER(ORDER BY NAME) AS n
   
FROM
       
sys.all_columns AS AC
   
)
   
SELECT
       
n AS ASCII_code,
       
CHAR(n) AS [character],
       
ISNUMERIC(CHAR(n)) AS is_numeric
   
FROM
       
cteNumbers
   
WHERE
       
n <=255 AND
       
ISNUMERIC(CHAR(n)) = 1

And you’ll see that the first 10 rows and last 8 rows returned are not what I would consider numeric.  Or how about this:

SELECT
   
ISNUMERIC('20,00')
GO
SELECT
   
CONVERT(DECIMAL(9,2), '20,00')

The first statement returns 1, while the second fails. 

Here is the last interesting behavior of IsNumeric() in relation to Cast/Convert provided by Frank Kalis on this SQLServerCentral thread:

SELECT ISNUMERIC(0X0e) AS E, ISNUMERIC(0X0fAS F

E           F          
----------- -----------
0           0

(1 row(s) affected)

WHILE

SELECT CAST(0X0e AS INT) AS E, CAST(0X0f AS INT) AS F

E           F          
----------- -----------
14          15

The moral of the story is that IsNumeric() <> CanBeConvertedToNumeric().

So what is the answer?  I don’t know.  You would need to customize the solution to meet your specific situation.  Jeff Moden suggests an IsAllDigits function in this thread on SQLServerCentral for instances where you want to eliminate rows with non-numeric characters.

Do you have anything that you recommend?

Posted in Bugs, Code, Code Example, Problems, SQL Server, SqlServerCentral | Leave a Comment »

Script featured on SQLServerCentral

Posted by sqlwiseguy on February 9, 2009

The script, Who has accessed my 2005 server?, I submitted was featured in the SQLServerCentral newsletter today.

The script uses the default trace to show who has logged into SQL Server. It is, of course, limited by the data contained in the Trace.

Posted in Code, Scripts, SQL Server, SqlServerCentral | Leave a Comment »