SQL2000 Alert question - Tricky!

From: Clement Nappoly (cnappoly@hotmail.com)
Date: 05/28/02 

From:     Clement Nappoly <cnappoly@hotmail.com>
Date: Tue, 28 May 2002 05:58:30 -0700

Hi SQL Gurus-
I got a trick question (at least I hope) for you all.

    We want to be able to track a "Login failed" Alert to the NODE and
the PROGRAM used for SQL server connectivity. We have a lot of people
in our firm trying various tools to connect to SQL server.
    The login failed alert has the message_id (18456) mapped to the
error number in sysmessages. The message is "Login failed for user
'%ls'. What we'd like to get is the hostname and the program name
along with this login name when this alert fires. I know we could get
this hostname from either using host_name() built-in function or
querying sysprocesses, provided the login succeeds. Our issue is that
the user SID doesn't even make it to sysprocesses table since the
login fails. I have phrased my question in 2 ways to make sure I am
asking the right thing. Any help would be greatly appreciated.

Option 1:
The pre-defined message for a "Login Failed" alert (Error 18456) is
"Login failed for user '%ls'. I'd like to get the hostname &
program_name "along" with this message. Updating the sysmessages table
for this error number did not help. SysAlerts and Sysmessages JOIN on
"message_id" and "error:. Any ideas?
OR
Option 2: ( this is really what we want. once we know this, we can
customize alerts the way we want)
Can you pass values between an alert and the job it invokes? If yes,how.
Typically we need to be able to pass the value trapped by alerts in
those wierd parameters "%ls, %d, %!hs" etc. Any ideas?

Thanks
CFN

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Relevant Pages

  • Re: Tivloli with SQL Server 2000
    ... When it is loading data it never has a cache hit ratio above about ... before blindly moving an alert threshold. ... I support the Professional Association for SQL Server ... > Buffer Extended Memory Cache Hit Ratio to low < 90 ...
    (microsoft.public.sqlserver.server)
  • Re: how to do automatic backup for transaction log and truncate it?
    ... create a job that runs master.dbo._resizemydb dont shedule ... Create an Alert .. ... select SQL server databases as Object ... Select execute Job and Select our Job that ll ...
    (microsoft.public.sqlserver.server)
  • Re: email notificaiton when log files are getting large
    ... Enterprise Manager, Management, SQL Server Agent, right-click Alerts, New Alert. ... Tibor Karaszi, SQL Server MVP ...
    (microsoft.public.sqlserver.tools)
  • Re: This one could be tough
    ... running on sql server currently? ... Since our alert system sends out emails ... > whenever there are data problems, we usually get thousands of alert ...
    (microsoft.public.sqlserver.programming)
  • Re: SQL2000 Alert question - Tricky!
    ... I chose to store the details of the trace onto a table. ... name.....whatever columns you need for the alert to report to you. ... The message in the sysmessages table looked something like this ... >> in our firm trying various tools to connect to SQL server. ...
    (microsoft.public.sqlserver.security)