SQL tool for MSDE: configured

Showing posts with label configured. Show all posts

Thursday, March 22, 2012

Alert not triggering

I've configured an alert for the TLog that when the server generates error
9002 (out of log space) to kick off a job that will backup the TLog to clear
it. I am testing the alert and I receive the error message, but the it will
not activate the alert. I've ensured that it is enabled and that the job
works, but for some reason the alert is not working. Can anyone help?
AndrewDoes the error show up in the `Windows event log?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Big Ern" <BigErn@.discussions.microsoft.com> wrote in message
news:0BD5E05C-F708-43D7-ADC1-6CCC75F61D26@.microsoft.com...
> I've configured an alert for the TLog that when the server generates error
> 9002 (out of log space) to kick off a job that will backup the TLog to clear
> it. I am testing the alert and I receive the error message, but the it will
> not activate the alert. I've ensured that it is enabled and that the job
> works, but for some reason the alert is not working. Can anyone help?
> Andrew|||Yes the error appears in both the SQL and Windows event logs.
"Tibor Karaszi" wrote:
> Does the error show up in the `Windows event log?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Big Ern" <BigErn@.discussions.microsoft.com> wrote in message
> news:0BD5E05C-F708-43D7-ADC1-6CCC75F61D26@.microsoft.com...
> > I've configured an alert for the TLog that when the server generates error
> > 9002 (out of log space) to kick off a job that will backup the TLog to clear
> > it. I am testing the alert and I receive the error message, but the it will
> > not activate the alert. I've ensured that it is enabled and that the job
> > works, but for some reason the alert is not working. Can anyone help?
> >
> > Andrew
>|||The last think I can think of is that agent isn't started. If that isn't the case and you can't find
anything obvious in Eventlog, I'm out of ideas, I'm afraid...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Big Ern" <BigErn@.discussions.microsoft.com> wrote in message
news:7AA07BA5-5744-4DC1-BF8F-310C1C700A49@.microsoft.com...
> Yes the error appears in both the SQL and Windows event logs.
> "Tibor Karaszi" wrote:
>> Does the error show up in the `Windows event log?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Big Ern" <BigErn@.discussions.microsoft.com> wrote in message
>> news:0BD5E05C-F708-43D7-ADC1-6CCC75F61D26@.microsoft.com...
>> > I've configured an alert for the TLog that when the server generates error
>> > 9002 (out of log space) to kick off a job that will backup the TLog to clear
>> > it. I am testing the alert and I receive the error message, but the it will
>> > not activate the alert. I've ensured that it is enabled and that the job
>> > works, but for some reason the alert is not working. Can anyone help?
>> >
>> > Andrew
>>

Alert not triggering

Alert Not Firing

Hi, I added a new message using sp_addmessage(50001,16,'My Message',1)
I then configured an alert to check this message id.
In a stored procedure that does a check for a certain condition, if
condition is true raiserror(50001,16,1).
My hope was that tha alert would fire ans the the response would be to send
me an email.
Since the alert is not firing I get no email.
What am I missing here. Do I have this configured correctly?
Any help is appreciated.
Thanks
Gary
Try changing the evert alert definition to reference database name "master",
or "(All Databases)". I had a similar problem and it seem that messages
are send via the master database so the event alert definition needs to be
triggered from master.
----
-
Need SQL Server Examples check out my website
http://www.geocities.com/sqlserverexamples
"Gary" <Gary@.discussions.microsoft.com> wrote in message
news:FAEAAD58-1CA0-467D-8C5B-1A988D2DB962@.microsoft.com...
> Hi, I added a new message using sp_addmessage(50001,16,'My Message',1)
> I then configured an alert to check this message id.
> In a stored procedure that does a check for a certain condition, if
> condition is true raiserror(50001,16,1).
> My hope was that tha alert would fire ans the the response would be to
send
> me an email.
> Since the alert is not firing I get no email.
> What am I missing here. Do I have this configured correctly?
> Any help is appreciated.
> Thanks
> Gary
|||I've got the same problem and have tried setting the reference to All and
master to no avail. Any ideas? My system:
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Evaluation Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
Jim
"Gregory A. Larsen" wrote:

> Try changing the evert alert definition to reference database name "master",
> or "(All Databases)". I had a similar problem and it seem that messages
> are send via the master database so the event alert definition needs to be
> triggered from master.
> --
> ----
> ----
> -
> Need SQL Server Examples check out my website
> http://www.geocities.com/sqlserverexamples
>
> "Gary" <Gary@.discussions.microsoft.com> wrote in message
> news:FAEAAD58-1CA0-467D-8C5B-1A988D2DB962@.microsoft.com...
> send
>
>
|||Jim, I got my alert to work. In the properties dialog there is a text box
labeled "Error message contains this text" I cleared it and did not enter
anything. I then clicked apply and then clicked ok. I ran my stored procedure
and it fired the alert. I hope this helps you.
Gary
"Jim" wrote:
[vbcol=seagreen]
> I've got the same problem and have tried setting the reference to All and
> master to no avail. Any ideas? My system:
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
> Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation
> Enterprise Evaluation Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
> Jim
> "Gregory A. Larsen" wrote:
|||1. The messages need to show up in the eventlog. This is what agent polls. No messages in Eventlog =
Agent is not aware of them.
2. The "Databases" textbox is misleading. This is only a filter. At least AFAIK (and I wrote a
similar functionality to Agent's event handling myself). If what you put in the "Databases" text box
isn't in the message text then the alert will not fire.
3. Same thing for "Error message contains this text".
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Gary" <Gary@.discussions.microsoft.com> wrote in message
news:BD2713F2-666C-4842-B479-1F38DAA7FE90@.microsoft.com...[vbcol=seagreen]
> Jim, I got my alert to work. In the properties dialog there is a text box
> labeled "Error message contains this text" I cleared it and did not enter
> anything. I then clicked apply and then clicked ok. I ran my stored procedure
> and it fired the alert. I hope this helps you.
> Gary
> "Jim" wrote:
|||Perfect! Thanks everyone.
Jim

Alert Not Firing

Hi, I added a new message using sp_addmessage(50001,16,'My Message',1)
I then configured an alert to check this message id.
In a stored procedure that does a check for a certain condition, if
condition is true raiserror(50001,16,1).
My hope was that tha alert would fire ans the the response would be to send
me an email.
Since the alert is not firing I get no email.
What am I missing here. Do I have this configured correctly?
Any help is appreciated.
Thanks
GaryTry changing the evert alert definition to reference database name "master",
or "(All Databases)". I had a similar problem and it seem that messages
are send via the master database so the event alert definition needs to be
triggered from master.
----
----
-
Need SQL Server Examples check out my website
http://www.geocities.com/sqlserverexamples
"Gary" <Gary@.discussions.microsoft.com> wrote in message
news:FAEAAD58-1CA0-467D-8C5B-1A988D2DB962@.microsoft.com...
> Hi, I added a new message using sp_addmessage(50001,16,'My Message',1)
> I then configured an alert to check this message id.
> In a stored procedure that does a check for a certain condition, if
> condition is true raiserror(50001,16,1).
> My hope was that tha alert would fire ans the the response would be to
send
> me an email.
> Since the alert is not firing I get no email.
> What am I missing here. Do I have this configured correctly?
> Any help is appreciated.
> Thanks
> Gary|||I've got the same problem and have tried setting the reference to All and
master to no avail. Any ideas? My system:
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Evaluation Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
Jim
"Gregory A. Larsen" wrote:

> Try changing the evert alert definition to reference database name "master
",
> or "(All Databases)". I had a similar problem and it seem that messages
> are send via the master database so the event alert definition needs to be
> triggered from master.
> --
> ----
--
> ----
--
> -
> Need SQL Server Examples check out my website
> http://www.geocities.com/sqlserverexamples
>
> "Gary" <Gary@.discussions.microsoft.com> wrote in message
> news:FAEAAD58-1CA0-467D-8C5B-1A988D2DB962@.microsoft.com...
> send
>
>|||Jim, I got my alert to work. In the properties dialog there is a text box
labeled "Error message contains this text" I cleared it and did not enter
anything. I then clicked apply and then clicked ok. I ran my stored procedur
e
and it fired the alert. I hope this helps you.
Gary
"Jim" wrote:
[vbcol=seagreen]
> I've got the same problem and have tried setting the reference to All and
> master to no avail. Any ideas? My system:
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
> Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation
> Enterprise Evaluation Edition on Windows NT 5.0 (Build 2195: Service Pack
4)
> Jim
> "Gregory A. Larsen" wrote:
>|||1. The messages need to show up in the eventlog. This is what agent polls. N
o messages in Eventlog =
Agent is not aware of them.
2. The "Databases" textbox is misleading. This is only a filter. At least AF
AIK (and I wrote a
similar functionality to Agent's event handling myself). If what you put in
the "Databases" text box
isn't in the message text then the alert will not fire.
3. Same thing for "Error message contains this text".
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Gary" <Gary@.discussions.microsoft.com> wrote in message
news:BD2713F2-666C-4842-B479-1F38DAA7FE90@.microsoft.com...[vbcol=seagreen]
> Jim, I got my alert to work. In the properties dialog there is a text box
> labeled "Error message contains this text" I cleared it and did not enter
> anything. I then clicked apply and then clicked ok. I ran my stored proced
ure
> and it fired the alert. I hope this helps you.
> Gary
> "Jim" wrote:
>|||Perfect! Thanks everyone.
Jimsql

Alert is not Firing

I have configured the alert of
Type SQL Server event alert
for the severity 016-Miscellaneous user Error
DB is all Database
In response tab, I have confiured the job.
I have created one procedure in pubs dB like
create proc test
as
begin
raiserror('going to configure ',16,1)
end
After that I have executed this Stored Procedure.
exec test.
My understanding is that now the alert should be fired and then it
should execute a job. But it is not at all happening. I.e The alert is
not at all firing.
Kindly give some solution.
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005
23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Developer
Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
Thanks and regards
Praveen.AAlert firing is based on the eventlog. Make sure you write to eventlog. Use either WITH LOG for
RAISERROR or (preferably) design your own messages (sp_addmessage) and specify there to always write
to eventlog.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Praveen" <apveen@.gmail.com> wrote in message
news:1127218958.512008.318710@.g14g2000cwa.googlegroups.com...
>I have configured the alert of
> Type SQL Server event alert
> for the severity 016-Miscellaneous user Error
> DB is all Database
> In response tab, I have confiured the job.
> I have created one procedure in pubs dB like
> create proc test
> as
> begin
> raiserror('going to configure ',16,1)
> end
> After that I have executed this Stored Procedure.
> exec test.
> My understanding is that now the alert should be fired and then it
> should execute a job. But it is not at all happening. I.e The alert is
> not at all firing.
> Kindly give some solution.
> Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005
> 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Developer
> Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
>
> Thanks and regards
> Praveen.A
>|||Thanks a lot Tibor.
I got a solution. Alert is firing now.

Alert is not Firing

I have configured the alert of
Type SQL Server event alert
for the severity 016-Miscellaneous user Error
DB is all Database
In response tab, I have confiured the job.
I have created one procedure in pubs dB like
create proc test
as
begin
raiserror('going to configure ',16,1)
end
After that I have executed this Stored Procedure.
exec test.
My understanding is that now the alert should be fired and then it
should execute a job. But it is not at all happening. I.e The alert is
not at all firing.
Kindly give some solution.
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005
23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Developer
Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
Thanks and regards
Praveen.A
Alert firing is based on the eventlog. Make sure you write to eventlog. Use either WITH LOG for
RAISERROR or (preferably) design your own messages (sp_addmessage) and specify there to always write
to eventlog.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Praveen" <apveen@.gmail.com> wrote in message
news:1127218958.512008.318710@.g14g2000cwa.googlegr oups.com...
>I have configured the alert of
> Type SQL Server event alert
> for the severity 016-Miscellaneous user Error
> DB is all Database
> In response tab, I have confiured the job.
> I have created one procedure in pubs dB like
> create proc test
> as
> begin
> raiserror('going to configure ',16,1)
> end
> After that I have executed this Stored Procedure.
> exec test.
> My understanding is that now the alert should be fired and then it
> should execute a job. But it is not at all happening. I.e The alert is
> not at all firing.
> Kindly give some solution.
> Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005
> 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Developer
> Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
>
> Thanks and regards
> Praveen.A
>
|||Thanks a lot Tibor.
I got a solution. Alert is firing now.
sql

Alert is not Firing

I have configured the alert of
Type SQL Server event alert
for the severity 016-Miscellaneous user Error
DB is all Database
In response tab, I have confiured the job.
I have created one procedure in pubs dB like
create proc test
as
begin
raiserror('going to configure ',16,1)
end
After that I have executed this Stored Procedure.
exec test.
My understanding is that now the alert should be fired and then it
should execute a job. But it is not at all happening. I.e The alert is
not at all firing.
Kindly give some solution.
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005
23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Developer
Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
Thanks and regards
Praveen.AAlert firing is based on the eventlog. Make sure you write to eventlog. Use
either WITH LOG for
RAISERROR or (preferably) design your own messages (sp_addmessage) and speci
fy there to always write
to eventlog.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Praveen" <apveen@.gmail.com> wrote in message
news:1127218958.512008.318710@.g14g2000cwa.googlegroups.com...
>I have configured the alert of
> Type SQL Server event alert
> for the severity 016-Miscellaneous user Error
> DB is all Database
> In response tab, I have confiured the job.
> I have created one procedure in pubs dB like
> create proc test
> as
> begin
> raiserror('going to configure ',16,1)
> end
> After that I have executed this Stored Procedure.
> exec test.
> My understanding is that now the alert should be fired and then it
> should execute a job. But it is not at all happening. I.e The alert is
> not at all firing.
> Kindly give some solution.
> Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005
> 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Developer
> Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
>
> Thanks and regards
> Praveen.A
>|||Thanks a lot Tibor.
I got a solution. Alert is firing now.

Tuesday, March 20, 2012

Alert fails to notify Operator (SQL Server 2005)

I am trying to set up a new alert which will notify me when a particular
error is raised. I have configured the SQL Server Agent Alert System to use
Database Mail, and confirmed that this works with Job notifications (I have
successfully notified myself by email on completion of a job).
I have added a new user-defined error with sp_addmessage and have created an
alert to notify me when this error is raised. I have then generated the error
with the raiserror statement. The error message is returned in the query
result window, but I do not get the email.Hi Jon
"Jon Ley" wrote:
> I am trying to set up a new alert which will notify me when a particular
> error is raised. I have configured the SQL Server Agent Alert System to use
> Database Mail, and confirmed that this works with Job notifications (I have
> successfully notified myself by email on completion of a job).
> I have added a new user-defined error with sp_addmessage and have created an
> alert to notify me when this error is raised. I have then generated the error
> with the raiserror statement. The error message is returned in the query
> result window, but I do not get the email.
Is there anything in the SQL Server error log?
If you have email blocking as part of the antivirus suite you may have
exceptions logged there.
John|||How did you define the alert notification? By error number?
--
TheSQLGuru
President
Indicium Resources, Inc.
"Jon Ley" <JonLey@.discussions.microsoft.com> wrote in message
news:29B92338-9259-489C-BFBA-C3D75B72B901@.microsoft.com...
>I am trying to set up a new alert which will notify me when a particular
> error is raised. I have configured the SQL Server Agent Alert System to
> use
> Database Mail, and confirmed that this works with Job notifications (I
> have
> successfully notified myself by email on completion of a job).
> I have added a new user-defined error with sp_addmessage and have created
> an
> alert to notify me when this error is raised. I have then generated the
> error
> with the raiserror statement. The error message is returned in the query
> result window, but I do not get the email.|||On May 19, 1:18 pm, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
> How did you define the alert notification? By error number?
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "Jon Ley" <Jon...@.discussions.microsoft.com> wrote in message
> news:29B92338-9259-489C-BFBA-C3D75B72B901@.microsoft.com...
>
> >I am trying to set up a new alert which will notify me when a particular
> > error is raised. I have configured the SQL Server Agent Alert System to
> > use
> > Database Mail, and confirmed that this works with Job notifications (I
> > have
> > successfully notified myself by email on completion of a job).
> > I have added a new user-defined error with sp_addmessage and have created
> > an
> > alert to notify me when this error is raised. I have then generated the
> > error
> > with the raiserror statement. The error message is returned in the query
> > result window, but I do not get the email.- Hide quoted text -
> - Show quoted text -
Are you on service pak 2? There are problems with database mail that
are corrected with service SP2.
Worth a shot anyway.
Kristina|||John,
Thanks for your response. There does not appear to be anything in the error
log.
Jon.
"John Bell" wrote:
> Hi Jon
> "Jon Ley" wrote:
> > I am trying to set up a new alert which will notify me when a particular
> > error is raised. I have configured the SQL Server Agent Alert System to use
> > Database Mail, and confirmed that this works with Job notifications (I have
> > successfully notified myself by email on completion of a job).
> >
> > I have added a new user-defined error with sp_addmessage and have created an
> > alert to notify me when this error is raised. I have then generated the error
> > with the raiserror statement. The error message is returned in the query
> > result window, but I do not get the email.
> Is there anything in the SQL Server error log?
> If you have email blocking as part of the antivirus suite you may have
> exceptions logged there.
> John|||TheSQLGuru: Yes, I defined the alert notification by error number (50001)
Kristina: I have just applied sp2 and retried. Still not working.
Regards,
Jon.
"Kristina" wrote:
> On May 19, 1:18 pm, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
> > How did you define the alert notification? By error number?
> >
> > --
> > TheSQLGuru
> > President
> > Indicium Resources, Inc.
> >
> > "Jon Ley" <Jon...@.discussions.microsoft.com> wrote in message
> >
> > news:29B92338-9259-489C-BFBA-C3D75B72B901@.microsoft.com...
> >
> >
> >
> > >I am trying to set up a new alert which will notify me when a particular
> > > error is raised. I have configured the SQL Server Agent Alert System to
> > > use
> > > Database Mail, and confirmed that this works with Job notifications (I
> > > have
> > > successfully notified myself by email on completion of a job).
> >
> > > I have added a new user-defined error with sp_addmessage and have created
> > > an
> > > alert to notify me when this error is raised. I have then generated the
> > > error
> > > with the raiserror statement. The error message is returned in the query
> > > result window, but I do not get the email.- Hide quoted text -
> >
> > - Show quoted text -
> Are you on service pak 2? There are problems with database mail that
> are corrected with service SP2.
> Worth a shot anyway.
> Kristina
>|||Hi Jon
"Jon Ley" wrote:
> John,
> Thanks for your response. There does not appear to be anything in the error
> log.
> Jon.
>
Under the Alert Properties, there is a history dialog which should show you
if the alert has been triggered. If this is not updated when you cause the
error then you are not satisfying the criteria described in the remarks
section of http://msdn2.microsoft.com/en-us/library/ms189531.aspx. Check that
the errors appears in the windows event log.
John|||Thanks, John, I have got it working now using the WITH LOG option on the
RAISERROR statement. This appears to be a change from SQL Server 2000 - I had
exactly the same alert configured with a severity level of 10 and without it
being logged, and it would quite happily notify me by email when this error
was raised.
Regards,
Jon Ley.
"John Bell" wrote:
> Hi Jon
> "Jon Ley" wrote:
> > John,
> >
> > Thanks for your response. There does not appear to be anything in the error
> > log.
> >
> > Jon.
> >
> Under the Alert Properties, there is a history dialog which should show you
> if the alert has been triggered. If this is not updated when you cause the
> error then you are not satisfying the criteria described in the remarks
> section of http://msdn2.microsoft.com/en-us/library/ms189531.aspx. Check that
> the errors appears in the windows event log.
> John|||Did you configure the alert to always log to windows eventlog? You can check this in sys.messages.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jon Ley" <JonLey@.discussions.microsoft.com> wrote in message
news:54D9247D-1915-41DB-AA98-09D1E1E7B4DF@.microsoft.com...
> Thanks, John, I have got it working now using the WITH LOG option on the
> RAISERROR statement. This appears to be a change from SQL Server 2000 - I had
> exactly the same alert configured with a severity level of 10 and without it
> being logged, and it would quite happily notify me by email when this error
> was raised.
> Regards,
> Jon Ley.
> "John Bell" wrote:
>> Hi Jon
>> "Jon Ley" wrote:
>> > John,
>> >
>> > Thanks for your response. There does not appear to be anything in the error
>> > log.
>> >
>> > Jon.
>> >
>> Under the Alert Properties, there is a history dialog which should show you
>> if the alert has been triggered. If this is not updated when you cause the
>> error then you are not satisfying the criteria described in the remarks
>> section of http://msdn2.microsoft.com/en-us/library/ms189531.aspx. Check that
>> the errors appears in the windows event log.
>> Johnsql

alert

Hi
I have SQL Server Mail configured and working correctly, but I was just
wondering if it were possible to send an e-mail alert to an operator
indicating that a SQL Server service had stopped? If so, how do I go about it?
ThanksHi
Write a stored procedure that trying to ping the server. If it got 'time out
..' then send the email.
"jonjo" <jonjo@.discussions.microsoft.com> wrote in message
news:6312060A-0A9A-4D3D-BC38-BE221D10B87C@.microsoft.com...
> Hi
> I have SQL Server Mail configured and working correctly, but I was just
> wondering if it were possible to send an e-mail alert to an operator
> indicating that a SQL Server service had stopped? If so, how do I go about
it?
> Thanks|||Hi
Thanks for the reply. I've not done much development or sp work so could you
point me in the right direction as how to start writing the sp in question?
Thanks
"Uri Dimant" wrote:
> Hi
> Write a stored procedure that trying to ping the server. If it got 'time out
> ...' then send the email.
>
>
> "jonjo" <jonjo@.discussions.microsoft.com> wrote in message
> news:6312060A-0A9A-4D3D-BC38-BE221D10B87C@.microsoft.com...
> > Hi
> >
> > I have SQL Server Mail configured and working correctly, but I was just
> > wondering if it were possible to send an e-mail alert to an operator
> > indicating that a SQL Server service had stopped? If so, how do I go about
> it?
> >
> > Thanks
>
>|||Hi
I think you'd be better off finding a moitoring tool that can monitor the
SQL server service, and then let this tool email you when the service is
down. Additonally, if you use SQL Server Agent to do the monitoring, I doubt
that you can trust the result to check the server it self. SQL Server Agent
requires MSSQLSERVER to be running, so if the SQLserver service is down, SQL
server agent is most likely down as well.
Regards
Steen
jonjo wrote:
> Hi
> Thanks for the reply. I've not done much development or sp work so
> could you point me in the right direction as how to start writing the
> sp in question?
> Thanks
> "Uri Dimant" wrote:
>> Hi
>> Write a stored procedure that trying to ping the server. If it got
>> 'time out ...' then send the email.
>>
>>
>> "jonjo" <jonjo@.discussions.microsoft.com> wrote in message
>> news:6312060A-0A9A-4D3D-BC38-BE221D10B87C@.microsoft.com...
>> Hi
>> I have SQL Server Mail configured and working correctly, but I was
>> just wondering if it were possible to send an e-mail alert to an
>> operator indicating that a SQL Server service had stopped? If so,
>> how do I go about it?
>> Thankssql

alert

Hi
I have SQL Server Mail configured and working correctly, but I was just
wondering if it were possible to send an e-mail alert to an operator
indicating that a SQL Server service had stopped? If so, how do I go about it?
Thanks
Hi
Write a stored procedure that trying to ping the server. If it got 'time out
...' then send the email.
"jonjo" <jonjo@.discussions.microsoft.com> wrote in message
news:6312060A-0A9A-4D3D-BC38-BE221D10B87C@.microsoft.com...
> Hi
> I have SQL Server Mail configured and working correctly, but I was just
> wondering if it were possible to send an e-mail alert to an operator
> indicating that a SQL Server service had stopped? If so, how do I go about
it?
> Thanks
|||Hi
Thanks for the reply. I've not done much development or sp work so could you
point me in the right direction as how to start writing the sp in question?
Thanks
"Uri Dimant" wrote:

> Hi
> Write a stored procedure that trying to ping the server. If it got 'time out
> ...' then send the email.
>
>
> "jonjo" <jonjo@.discussions.microsoft.com> wrote in message
> news:6312060A-0A9A-4D3D-BC38-BE221D10B87C@.microsoft.com...
> it?
>
>
|||Hi
I think you'd be better off finding a moitoring tool that can monitor the
SQL server service, and then let this tool email you when the service is
down. Additonally, if you use SQL Server Agent to do the monitoring, I doubt
that you can trust the result to check the server it self. SQL Server Agent
requires MSSQLSERVER to be running, so if the SQLserver service is down, SQL
server agent is most likely down as well.
Regards
Steen
jonjo wrote:[vbcol=seagreen]
> Hi
> Thanks for the reply. I've not done much development or sp work so
> could you point me in the right direction as how to start writing the
> sp in question?
> Thanks
> "Uri Dimant" wrote:

alert

Hi
I have SQL Server Mail configured and working correctly, but I was just
wondering if it were possible to send an e-mail alert to an operator
indicating that a SQL Server service had stopped? If so, how do I go about i
t?
ThanksHi
Write a stored procedure that trying to ping the server. If it got 'time out
..' then send the email.
"jonjo" <jonjo@.discussions.microsoft.com> wrote in message
news:6312060A-0A9A-4D3D-BC38-BE221D10B87C@.microsoft.com...
> Hi
> I have SQL Server Mail configured and working correctly, but I was just
> wondering if it were possible to send an e-mail alert to an operator
> indicating that a SQL Server service had stopped? If so, how do I go about
it?
> Thanks|||Hi
Thanks for the reply. I've not done much development or sp work so could you
point me in the right direction as how to start writing the sp in question?
Thanks
"Uri Dimant" wrote:

> Hi
> Write a stored procedure that trying to ping the server. If it got 'time o
ut
> ...' then send the email.
>
>
> "jonjo" <jonjo@.discussions.microsoft.com> wrote in message
> news:6312060A-0A9A-4D3D-BC38-BE221D10B87C@.microsoft.com...
> it?
>
>|||Hi
I think you'd be better off finding a moitoring tool that can monitor the
SQL server service, and then let this tool email you when the service is
down. Additonally, if you use SQL Server Agent to do the monitoring, I doubt
that you can trust the result to check the server it self. SQL Server Agent
requires MSSQLSERVER to be running, so if the SQLserver service is down, SQL
server agent is most likely down as well.
Regards
Steen
jonjo wrote:[vbcol=seagreen]
> Hi
> Thanks for the reply. I've not done much development or sp work so
> could you point me in the right direction as how to start writing the
> sp in question?
> Thanks
> "Uri Dimant" wrote:
>

Monday, March 19, 2012

ahh fer the luvva...

With a ForEach container, configured to loop through files in a directory, if I have a problem with a file.. can I direct the loop to skip on to the next file?
I'm processing structured files, first record of each is some header info, body records are in the middle, and then the last record is a trailer containg a checksum
So, for each file in the directory, I split the records into three raw files, one for header rec(s) , one for body recs and one for trailer recs. (based on line numbers and using a conditional split to direct the records)
Then I start by processing the header recs in a dataflow.. if all goes well there I move on to the next dataflow to process the body recs from the DataRecs raw file.. etc...
I would like to do some validation at each processing step.. if a header rec fails validation say... then I'd like to just stop processing that file and move onto the next file...
Now, I don't see my validation throwing an exception... so its more that I'd decide (maybe using an Audit ) that the header doesn't pass validation.. then I'd like to put a record in an error table (with info about filename, source etc, not just content of the current data row)
But not sure what approach to take on this...
If there is an appropriate section in BOL please point me at it...
Thanks
PJ

OK, so a record could fail validation but won't necassarily cause an error, is that right?

If you can find a way of populating a boolean variable stating whether the validation was successful or not then you could put a conditional precedence constraint beween data-flow1 and data-flow2 to determine whether data-flow2 should execute or not.

-Jamie

|||

... and if data-flow2 shouldn't execute then the ForEach will just loop around?

|||

Ok... so have to figure out how to access global variables in a script task....

OR...

...could do a conditional split.. which carries out the validation checking...and has one output connected to an OLE DB Destination to store a valid header record.. the other output directed to a RowCount...

If the row count >0 then we have a validation failure.. so could set the global variable there... is there a way to send a row count to a package variable?

|||

PJFINTRAX wrote:

... and if data-flow2 shouldn't execute then the ForEach will just loop around?

PJ

Absolutely! (Unless you have any other OnSuccess precedence constraints from data-flow1). Once all work is done for that iteration then it'll go onto the next iteration.

|||

PJFINTRAX wrote:

a way to send a row count to a package variable?

PJ

Err yeah. That's exactly what it does (and the only thing it does). i.e. Store the rowcount in a variable :)

|||

Jamie Thomson wrote:

PJFINTRAX wrote:

a way to send a row count to a package variable?

PJ

Err yeah. That's exactly what it does (and the only thing it does). i.e. Store the rowcount in a variable :)

DOH! Well, we all know I'm only making this up as I go along :^)

Thanks

|||

sigh.. this is like pulling teeth... now another problem has reared its ugly head...

when i use the SQL Task to retrieve the ID of the FileHeader record (that I just inserted) into a package variable... I get a datatype error.. and can't figure out which bloomin data type to use...(for the variable)

In my DB table the ID is a bigint....

what on earth is the equivalent in SSIS datatypes.... I have literally tried em all.. only one that works is Object !!!

So, SQL Task is executing SQL query as follows "select MAX(ID) as ID from fileheader"

Table definition is :

CREATE TABLE [dbo].[FileHeader](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[Source] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FileName] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LoadDate] [datetime] NULL CONSTRAINT [DF_FileHeader_LoadDate] DEFAULT (getdate())
) ON [PRIMARY]

So what should my variable type be if its to hold the result of the query?

PJ....

|||

Ahh what they hell.. I'll just use String.. that works....

|||Guys, I have the bigint problem again, can't just use a string this time...
has anyone any idea what the SSIS datatype equivalent of a bigint is at all?
Thanks
PJ
|||Is this just so obvious that nobody is bothering to answer?
|||

Probably cos they don't know. And I'm afraid I include myself in that. I don't have a SSIS instance to hand but as and when I do I'll try and take a look OK.

-Jamie

|||

Thanks Jamie,

but I have been through every single datatype .. and if I try to wedge a bigint into anything other than a string I get an error...

I would have thought an INT64 would work.. but no...

the bigint is an identity column in a table in my db... I'm a result set (which includes it) into an object and using that as a recordset using a foreach.. and I need to compare the ID column numerically.. to decide which flow to go down (ie If FiletypeID >12 and FileTypeID > 4 )

i suppose since the presedence contstraint is an expression I could try casting to a int64 maybe...

bit awkward tho.... would have thought this would all be easier... (like most SSIS things, heh)

Saturday, February 25, 2012

agent mail / outlook 2003 catch 22

I've just installed and configured outlook 2003 with sql agent mail and I've come across a strange conundrum: messages from agent mail are not actually sent unless the outlook client is open, but the default mail folder is locked by agent when agent is started, thus preventing outlook 2003 from opening. If I try things the other way - opening the client and then configuring/restarting agent mail - then agent mail can't access the default mail folder because the client has it locked.

There doesn't seem to be any way to win here.

Anyone come across this issue and found a workaround?I don't have a solution for your specific problem. I use this:

http://sqldev.net/xp/xpsmtp.htm

which doesn't require an email client on the server at all. Perhaps it would work in your situation.|||Deleted duplicate post.|||If I recall correctly, you have to configure SQL Agent Mail using the Outlook 2000 Client.

Here are some links:

MS FAQ (http://support.microsoft.com/default.aspx?scid=kb;en-us;311231)
Common Problems (http://support.microsoft.com/default.aspx?scid=kb;en-us;315886)
Configuring SQL Mail (http://support.microsoft.com/default.aspx?scid=kb;en-us;263556)

Regards,

hmscott|||Thanks, guys. It appears that Outlook 2003 is not compatible with Agent Mail (though it's fine with SQL Mail).

For anyone else who runs into this problem, I've thrown together a stored procedure which, when scheduled into an hourly job, will email you when a job fails along with the cause of the failure. I'm sure I could have made this neater, but it works and that's all I need. An example of the error email message is as follows:

--Original Message--
From: sender@.mydomain.com
Sent: Tuesday, January 31, 2006 5:04 PM
To: me@.mydomain.com
Subject: Jobs failed in the last hour - Feb 01 2006 10:00AM

The jobs listed below failed in the last hour:

Job: testjob
Step: 1
Step Name: test
Date Failed: Feb 1 2006 9:31AM
Error: Executed as user: SERVERS\Administrator. Invalid object name 'zigglyzorp'. [SQLSTATE 42S02] (Error 208). The step failed.
--sp attached as txt file--

Thursday, March 22, 2012

Alert not triggering

Alert not triggering

Alert not triggering

Alert Not Firing

Alert Not Firing

Alert is not Firing

Alert is not Firing

Alert is not Firing

Tuesday, March 20, 2012

Alert fails to notify Operator (SQL Server 2005)

Alert fails to notify Operator (SQL Server 2005)

Alert fails to notify Operator (SQL Server 2005)

alert

alert

alert

Monday, March 19, 2012

ahh fer the luvva...

Saturday, February 25, 2012

agent mail / outlook 2003 catch 22

SQL tool for MSDE

Blog Archive

About Me