January 16, 2009 at 12:31 pm
In a sproc I have the following code:
DECLARE @rc integer
EXEC @rc = xp_startmail @user='abc'
SELECT @rc
The variable @rc has a value of 1 which tells me the command failed and in query analyzer on the message tab I have the following information:
Msg 17930, Level 16, State 1, Line 0
xp_startmail: Either there is no default mail client or the current mail client cannot fulfill the messaging request. Please run Microsoft Outlook and set it as the default mail client.
My question is, is there anyway to get access to this information in my sproc so I can give the calling program more information about why the command failed?
Thanks,
Kevin
January 17, 2009 at 9:07 am
You might try looking up the error code in sysmessages and if it's there, use the description column to capture the message.
Toni
January 18, 2009 at 8:27 am
Or... you could trace the error event to a table using SQL Profiler then inquire against the table to extract the message? (In 2005 there are actual system variables that provide the info you are seeking)
Toni
January 19, 2009 at 12:23 pm
Error 17930 is not in sysmessages. All the returns from xp_startmail when it fails is a 1, do you know how to easily get the error number in a stored procedure. It would be nice that 17930 was returned when it failed.
January 19, 2009 at 7:24 pm
Since it looks like you have the authority to issue xp_startmail, I am going to assume you can also use xp_cmdshell and osql. In that case, here is another alternative.
Create a text input file (in this example call it input.txt) with your command in it
DECLARE @rc integer
exec @rc=xp_startmail @user='user1'
Then you can use osql to execute the command, provide the return code and put the output from the command in another file (here it is report.txt). Finally you can use BCP to copy the output into an sql table (you could make this permanent) that you can read and extract what you want to from within your SQL statements.
create table xpstartmail (line varchar(2000))
declare @rc int
execute @rc=master.dbo.xp_cmdshell 'osql -i C:\input.txt -o C:\Report.txt -E -b'
if @rc <> 0
Print 'Return Code is ' + coalesce(cast(@rc as varchar(8)),'NULL')
else
Print 'Return Code was 0'
/* You could make this happen only if non-zero return code */
exec @rc=master.dbo.xp_cmdshell 'bcp sqlsrvc.dbo.xpstartmail in c:\report.txt -c -r -T'
select "the output is " + line from xpstartmail -- show the output from the command
drop table xpstartmail
The contents of the output file looks like:
1> 2> 3> Msg 18025, Level 16, State 1:
xp_startmail: failed with mail error 0x80040111
The MAPI error codes are mapped in the following link -
http://support.microsoft.com/kb/238119
If that doesn't do it for you, then you will have to wait for someone else.
Toni
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply