Embed VBS or Call VBS from Job Script

How do you call an external VBS script from within a T-SQL Job?
At the end of a job I want a VBS to fire off in the last step.
Not sure how to do this. Looked everywhere ... :)Typically, T-SQL (Execute SQL Task) and VBS (ActiveX Script Task) are
included in a DTS Package, which is then scheduled for execution using a
job.
http://msdn.microsoft.com/library/d... />
x_06er.asp
"Whispering Leaf" <WhisperingLeaf@.discussions.microsoft.com> wrote in
message news:63FFAF27-72B1-45B6-A326-5DCDFED2735A@.microsoft.com...
> How do you call an external VBS script from within a T-SQL Job?
> At the end of a job I want a VBS to fire off in the last step.
> Not sure how to do this. Looked everywhere ... :)

Emails repeating and growing...

My dept has a SQL Server that uses an SMTP server to distribute emails for
Job Completion notifications. We recently had the server rebooted and I
received a small flood of emails. I've since asked for a daily reboot and
have been receiving a growing number of emails at the time of reboot (3am).
I'm recieving emails from 7/1/05 forward on a daily basis.
I opened up Outlook and there are no emails stuck in the Outbox. Would any
of you have an idea where to look?
FYI :
SQL Server SP4
SQL has it's own ID on this machine with it's own mail profile
This mail profile is shared by both Agent and SQL Server
POP3 goes through a Lotus Domino POP3 interface
I don't think the POP3 has any bearing on it... but since the whole
Send/Recieve process is touchy maybe there's something there.
Any suggestions would be appreciatedOk... maybe an easier question would be... where do I find the SQL Server
outbound mail queue?
"Peter Capazzi" wrote:
> My dept has a SQL Server that uses an SMTP server to distribute emails for
> Job Completion notifications. We recently had the server rebooted and I
> received a small flood of emails. I've since asked for a daily reboot and
> have been receiving a growing number of emails at the time of reboot (3am).
> I'm recieving emails from 7/1/05 forward on a daily basis.
> I opened up Outlook and there are no emails stuck in the Outbox. Would any
> of you have an idea where to look?
> FYI :
> SQL Server SP4
> SQL has it's own ID on this machine with it's own mail profile
> This mail profile is shared by both Agent and SQL Server
> POP3 goes through a Lotus Domino POP3 interface
> I don't think the POP3 has any bearing on it... but since the whole
> Send/Recieve process is touchy maybe there's something there.
> Any suggestions would be appreciated|||SQL Server doesn't have any mail queue. Xp_sendmail sends the email to MAPI synchronously. This is
why it is a bad idea to send email from a trigger, for example. Also, MAPI isn't the best choice for
a service. Have you considered using xp_smtp_sendmail? www.sqldev.net.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Peter Capazzi" <PeterCapazzi@.discussions.microsoft.com> wrote in message
news:E10AA6E4-D1F0-4C8A-B8E4-571FF79DF107@.microsoft.com...
> Ok... maybe an easier question would be... where do I find the SQL Server
> outbound mail queue?
> "Peter Capazzi" wrote:
>> My dept has a SQL Server that uses an SMTP server to distribute emails for
>> Job Completion notifications. We recently had the server rebooted and I
>> received a small flood of emails. I've since asked for a daily reboot and
>> have been receiving a growing number of emails at the time of reboot (3am).
>> I'm recieving emails from 7/1/05 forward on a daily basis.
>> I opened up Outlook and there are no emails stuck in the Outbox. Would any
>> of you have an idea where to look?
>> FYI :
>> SQL Server SP4
>> SQL has it's own ID on this machine with it's own mail profile
>> This mail profile is shared by both Agent and SQL Server
>> POP3 goes through a Lotus Domino POP3 interface
>> I don't think the POP3 has any bearing on it... but since the whole
>> Send/Recieve process is touchy maybe there's something there.
>> Any suggestions would be appreciated|||Well, these notifications are coming from the pager system in SQL Server...
in that repsect I don't think I have much of a choice.
So what can I do to analzye MAPI to see if these messages are simply sitting
in a queue somewhere? If I delete all mail profiles and recreate will it be
resolved?
"Tibor Karaszi" wrote:
> SQL Server doesn't have any mail queue. Xp_sendmail sends the email to MAPI synchronously. This is
> why it is a bad idea to send email from a trigger, for example. Also, MAPI isn't the best choice for
> a service. Have you considered using xp_smtp_sendmail? www.sqldev.net.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Peter Capazzi" <PeterCapazzi@.discussions.microsoft.com> wrote in message
> news:E10AA6E4-D1F0-4C8A-B8E4-571FF79DF107@.microsoft.com...
> > Ok... maybe an easier question would be... where do I find the SQL Server
> > outbound mail queue?
> >
> > "Peter Capazzi" wrote:
> >
> >> My dept has a SQL Server that uses an SMTP server to distribute emails for
> >> Job Completion notifications. We recently had the server rebooted and I
> >> received a small flood of emails. I've since asked for a daily reboot and
> >> have been receiving a growing number of emails at the time of reboot (3am).
> >> I'm recieving emails from 7/1/05 forward on a daily basis.
> >>
> >> I opened up Outlook and there are no emails stuck in the Outbox. Would any
> >> of you have an idea where to look?
> >>
> >> FYI :
> >> SQL Server SP4
> >> SQL has it's own ID on this machine with it's own mail profile
> >> This mail profile is shared by both Agent and SQL Server
> >> POP3 goes through a Lotus Domino POP3 interface
> >> I don't think the POP3 has any bearing on it... but since the whole
> >> Send/Recieve process is touchy maybe there's something there.
> >>
> >> Any suggestions would be appreciated
>|||You can get job notification and alerts from Agent through SMTP directly with a little work:
http://www.karaszi.com/SQLServer/info_no_mapi.asp
> So what can I do to analzye MAPI to see if these messages are simply sitting
> in a queue somewhere?
I suggest you ask in a mail/exchange group.
> If I delete all mail profiles and recreate will it be
> resolved?
I don't know if it will be resolved, but I tend to start troubleshooting by deleting and creating
the MAPI profile.
Btw, I believe that some configurations, you need to actually have Outlook running in order for the
emails to leave the queue. That is why I prefer to use Outlook 2000. However, I can imagine that
even with Outlook 2000, this can happen based on the backend storage. KB, Google and time is your
friend.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Peter Capazzi" <PeterCapazzi@.discussions.microsoft.com> wrote in message
news:21681631-A554-45A1-8234-0996FE8A6929@.microsoft.com...
> Well, these notifications are coming from the pager system in SQL Server...
> in that repsect I don't think I have much of a choice.
> So what can I do to analzye MAPI to see if these messages are simply sitting
> in a queue somewhere? If I delete all mail profiles and recreate will it be
> resolved?
> "Tibor Karaszi" wrote:
>> SQL Server doesn't have any mail queue. Xp_sendmail sends the email to MAPI synchronously. This
>> is
>> why it is a bad idea to send email from a trigger, for example. Also, MAPI isn't the best choice
>> for
>> a service. Have you considered using xp_smtp_sendmail? www.sqldev.net.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Peter Capazzi" <PeterCapazzi@.discussions.microsoft.com> wrote in message
>> news:E10AA6E4-D1F0-4C8A-B8E4-571FF79DF107@.microsoft.com...
>> > Ok... maybe an easier question would be... where do I find the SQL Server
>> > outbound mail queue?
>> >
>> > "Peter Capazzi" wrote:
>> >
>> >> My dept has a SQL Server that uses an SMTP server to distribute emails for
>> >> Job Completion notifications. We recently had the server rebooted and I
>> >> received a small flood of emails. I've since asked for a daily reboot and
>> >> have been receiving a growing number of emails at the time of reboot (3am).
>> >> I'm recieving emails from 7/1/05 forward on a daily basis.
>> >>
>> >> I opened up Outlook and there are no emails stuck in the Outbox. Would any
>> >> of you have an idea where to look?
>> >>
>> >> FYI :
>> >> SQL Server SP4
>> >> SQL has it's own ID on this machine with it's own mail profile
>> >> This mail profile is shared by both Agent and SQL Server
>> >> POP3 goes through a Lotus Domino POP3 interface
>> >> I don't think the POP3 has any bearing on it... but since the whole
>> >> Send/Recieve process is touchy maybe there's something there.
>> >>
>> >> Any suggestions would be appreciated
>>

Emailing results from query

Hi,
I just installed sql05 ent version and set up the DB Mail and ran a test
mail without a problem. I also managed to create a job which would execute a
query. I'd like now the sql server send to several email addresses the
results of the query and here where I'm stuck.
HOWDO you do it?
TIA
Ana
Read about sp_send_dbmail.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Ana" <ananospam@.yahoo.es> wrote in message
news:E878A2A8-B75A-4040-A252-DE709E44A372@.microsoft.com...
> Hi,
> I just installed sql05 ent version and set up the DB Mail and ran a test mail without a problem. I
> also managed to create a job which would execute a query. I'd like now the sql server send to
> several email addresses the results of the query and here where I'm stuck.
> HOWDO you do it?
> TIA
> Ana
|||Hi,
This atricle may be of use:
http://builder.com.com/5100-6388-6164310-2.html#Listing%20B
Dan
"Ana" <ananospam@.yahoo.es> wrote in message
news:E878A2A8-B75A-4040-A252-DE709E44A372@.microsoft.com...
> Hi,
> I just installed sql05 ent version and set up the DB Mail and ran a test
> mail without a problem. I also managed to create a job which would execute
> a query. I'd like now the sql server send to several email addresses the
> results of the query and here where I'm stuck.
> HOWDO you do it?
> TIA
> Ana

Emailing results from query

Hi,
I just installed sql05 ent version and set up the DB Mail and ran a test
mail without a problem. I also managed to create a job which would execute a
query. I'd like now the sql server send to several email addresses the
results of the query and here where I'm stuck.
HOWDO you do it?
TIA
AnaRead about sp_send_dbmail.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Ana" <ananospam@.yahoo.es> wrote in message
news:E878A2A8-B75A-4040-A252-DE709E44A372@.microsoft.com...
> Hi,
> I just installed sql05 ent version and set up the DB Mail and ran a test m
ail without a problem. I
> also managed to create a job which would execute a query. I'd like now the
sql server send to
> several email addresses the results of the query and here where I'm stuck.
> HOWDO you do it?
> TIA
> Ana|||Hi,
This atricle may be of use:
http://builder.com.com/5100-6388-61...tml#Listing%20B
Dan
"Ana" <ananospam@.yahoo.es> wrote in message
news:E878A2A8-B75A-4040-A252-DE709E44A372@.microsoft.com...
> Hi,
> I just installed sql05 ent version and set up the DB Mail and ran a test
> mail without a problem. I also managed to create a job which would execute
> a query. I'd like now the sql server send to several email addresses the
> results of the query and here where I'm stuck.
> HOWDO you do it?
> TIA
> Ana

Emailing results from query

Hi,
I just installed sql05 ent version and set up the DB Mail and ran a test
mail without a problem. I also managed to create a job which would execute a
query. I'd like now the sql server send to several email addresses the
results of the query and here where I'm stuck.
HOWDO you do it?
TIA
AnaRead about sp_send_dbmail.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Ana" <ananospam@.yahoo.es> wrote in message
news:E878A2A8-B75A-4040-A252-DE709E44A372@.microsoft.com...
> Hi,
> I just installed sql05 ent version and set up the DB Mail and ran a test mail without a problem. I
> also managed to create a job which would execute a query. I'd like now the sql server send to
> several email addresses the results of the query and here where I'm stuck.
> HOWDO you do it?
> TIA
> Ana|||Hi,
This atricle may be of use:
http://builder.com.com/5100-6388-6164310-2.html#Listing%20B
--
Dan
"Ana" <ananospam@.yahoo.es> wrote in message
news:E878A2A8-B75A-4040-A252-DE709E44A372@.microsoft.com...
> Hi,
> I just installed sql05 ent version and set up the DB Mail and ran a test
> mail without a problem. I also managed to create a job which would execute
> a query. I'd like now the sql server send to several email addresses the
> results of the query and here where I'm stuck.
> HOWDO you do it?
> TIA
> Ana

Emailing from MS SQL

Hey,

I would like to set up a job to send mail out to clients on a semi-regular basis. Is there a way to mail directly from MS SQL.

or some application which I can set to interface with MS SQL and send these mails out.

I'm currently using asp.net, however, doing it through the web is very time consuming, I would much rather use some application to generate the mails on the server as it currently takes close to 20 min to send through the web interface (and that's only to 900 members, we are looking at having at least 2000 members in a few weeks). What am I looking for is something I can call perhaps with transact sql so I can run it through an ms sql server job, however any solution you have would be fantastic.

If you don't have specific details, that's fine, I'm not even sure what apps etc to look into, even a name of some technology to use (or does MS SQL have a way of doing all of this itself?)

Thanks a lot for your help, life saver ;-)have you looked at xp_sendmail in the bol??|||lol, arg, it's always something simple.

Awesome, that looks pretty much like example what I want.

Thanks a lot mate ;-)|||So worries, hope it works out for you. I have often wanted to use it but due to server setup and network admins being what they are it's never happened... :(|||it's pretty simple to set up SQLMail
the trick is to get the SQL Server agent a domain user account to use as a startup account. then just set up a user mail profile by logging in as that user account. and opening your client (Isuggest outlook 98 or newer)
it will work with any MAPI1 compatible mail server even so even lotus notes...|||KBA (http://support.microsoft.com/default.aspx?kbid=263556) to configure the SQLMail.|||even so even so even so even so

sorry i was stuck in the inner groove of sgt pepper|||Hey,

I've had a few weeks on another project, and I'm back onto this job again (um, yay?, lol).

Anyway, I have had a look over the KBA link Satya posted. I am unsure if this will solve my problem, so, here are some more details about my situation.

I have a dedicated webserver at Cradle (an internet hosting company in australia).

My machine is isolated from the other machines (I'm not 100% sure about this, but it 'seems' to be the case).

They have a mail server which is something like: host1.xxxxxx.com.au. I need to send my mail through this.

The mail I send, I also need to be able to control the <From> field.

As the user who sends the mail, needs to be the user who the message is <From>, the stuff I've read in the KBA seems to send the mail through a single mail account (correct me if I'm wrong).

Also, I don't have access to Outlook on that machine. Is there some free tool etc I can install, or will I need to ship an old copy of outlook to the server for them to install for me?

Thanks, I'll head back to the KBA and keep reading now, see if I can't try and answer my own questions (fingers crossed)|||If you don't wish to set up SQL Server with a domain account or if you don't wish to use Outlook/Exchange, there are alternatives. I use a stored procedure that just needs a SMTP server to relay mail through that I found here:

http://www.sqlservercentral.com/scripts/contributions/510.asp

You'll have to register with the site to get the script, but it's well worth it. Their script library is VERY impressive.

Email sent half of the time...

Hi! I have a big problem here...
I use SQL 7.0 SP4 and when the job for backup logs fails,
it send an email, as specified in the job, but only 1/2
times!!! The Net send message is sent correctly, but not
emails... Do someone know why or is experiencing this
problem?
Thanks
not sure what you mean half of the time...
first, make sure you have correctly setup email profile. Can you send a
test message via email?
"David" <david.parenteau@.compuware.com> wrote in message
news:16f1501c41819$fa018870$a301280a@.phx.gbl...
> Hi! I have a big problem here...
> I use SQL 7.0 SP4 and when the job for backup logs fails,
> it send an email, as specified in the job, but only 1/2
> times!!! The Net send message is sent correctly, but not
> emails... Do someone know why or is experiencing this
> problem?
>
> Thanks
>
|||yes, email are sent successfully when sent. By half of the
time I mean this:
I go to the Enterprise Managers, display the list of all
jobs in my SQL server. Right-click on a job and
choose "View Job History..."
The 5 most recent execution are failed. All executions has
sent NET SEND command. The first, third and fifth have
also sent an email notification. But not the second and
fourth!
Is it more clear?
Thanks

>--Original Message--
>not sure what you mean half of the time...
>first, make sure you have correctly setup email profile.
Can you send a
>test message via email?
>"David" <david.parenteau@.compuware.com> wrote in message
>news:16f1501c41819$fa018870$a301280a@.phx.gbl...
fails,
>
>.
>
|||the 5 executions are for the same job? they were executed
by the same user?

>--Original Message--
>yes, email are sent successfully when sent. By half of
the
>time I mean this:
>I go to the Enterprise Managers, display the list of all
>jobs in my SQL server. Right-click on a job and
>choose "View Job History..."
>The 5 most recent execution are failed. All executions
has
>sent NET SEND command. The first, third and fifth have
>also sent an email notification. But not the second and
>fourth!
>Is it more clear?
>Thanks
profile.
>Can you send a
>fails,
1/2
not
>.
>
|||Yes. These jobs were created by the Maintenance Plan
wizard and updated manually to set notification custom.
The first failure (and the others too) was due to a non
logged transactions (SELECT INTO). RIght now the problem
of failing is resolved, but the random problem of sending
email is not: "NOTE: Failed to notify 'teamSQLAdmin' via
email." was the reason of failed where no email was sent.
This job occurs every hours.
All emails marked as sent have been received normall in
the inbox.
David
>--Original Message--
>the 5 executions are for the same job? they were executed
>by the same user?
>the
>has
>profile.
>1/2
>not
>.
>
|||David,
Are you using MS Exchange Server? Perhaps there are exchange server outages periodically? Could you check with your exchange server admin team?
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
|||Mark, yes, we use MS Exchange Server to send emails. The
mail server is dedicated on another server on the domain.
No outages are known for the mail server...
What this could involved?

>--Original Message--
>David,
>Are you using MS Exchange Server? Perhaps there are
exchange server outages periodically? Could you check with
your exchange server admin team?
>--
>Mark Allison, SQL Server MVP
>http://www.markallison.co.uk
>.
>

Email sent half of the time...

Hi! I have a big problem here...
I use SQL 7.0 SP4 and when the job for backup logs fails,
it send an email, as specified in the job, but only 1/2
times!!! The Net send message is sent correctly, but not
emails... Do someone know why or is experiencing this
problem?
Thanksnot sure what you mean half of the time...
first, make sure you have correctly setup email profile. Can you send a
test message via email?
"David" <david.parenteau@.compuware.com> wrote in message
news:16f1501c41819$fa018870$a301280a@.phx
.gbl...
> Hi! I have a big problem here...
> I use SQL 7.0 SP4 and when the job for backup logs fails,
> it send an email, as specified in the job, but only 1/2
> times!!! The Net send message is sent correctly, but not
> emails... Do someone know why or is experiencing this
> problem?
>
> Thanks
>|||yes, email are sent successfully when sent. By half of the
time I mean this:
I go to the Enterprise Managers, display the list of all
jobs in my SQL server. Right-click on a job and
choose "View Job History..."
The 5 most recent execution are failed. All executions has
sent NET SEND command. The first, third and fifth have
also sent an email notification. But not the second and
fourth!
Is it more clear?
Thanks

>--Original Message--
>not sure what you mean half of the time...
>first, make sure you have correctly setup email profile.
Can you send a
>test message via email?
>"David" <david.parenteau@.compuware.com> wrote in message
> news:16f1501c41819$fa018870$a301280a@.phx
.gbl...
fails,
>
>.
>|||the 5 executions are for the same job? they were executed
by the same user?

>--Original Message--
>yes, email are sent successfully when sent. By half of
the
>time I mean this:
>I go to the Enterprise Managers, display the list of all
>jobs in my SQL server. Right-click on a job and
>choose "View Job History..."
>The 5 most recent execution are failed. All executions
has
>sent NET SEND command. The first, third and fifth have
>also sent an email notification. But not the second and
>fourth!
>Is it more clear?
>Thanks
>
profile.
>Can you send a
>fails,
1/2
not
>.
>|||Yes. These jobs were created by the Maintenance Plan
wizard and updated manually to set notification custom.
The first failure (and the others too) was due to a non
logged transactions (SELECT INTO). RIght now the problem
of failing is resolved, but the random problem of sending
email is not: "NOTE: Failed to notify 'teamSQLAdmin' via
email." was the reason of failed where no email was sent.
This job occurs every hours.
All emails marked as sent have been received normall in
the inbox.
David
>--Original Message--
>the 5 executions are for the same job? they were executed
>by the same user?
>
>the
>has
>profile.
>1/2
>not
>.
>|||David,
Are you using MS Exchange Server? Perhaps there are exchange server outages
periodically? Could you check with your exchange server admin team?
Mark Allison, SQL Server MVP
http://www.markallison.co.uk|||Mark, yes, we use MS Exchange Server to send emails. The
mail server is dedicated on another server on the domain.
No outages are known for the mail server...
What this could involved?

>--Original Message--
>David,
>Are you using MS Exchange Server? Perhaps there are
exchange server outages periodically? Could you check with
your exchange server admin team?
>--
>Mark Allison, SQL Server MVP
>http://www.markallison.co.uk
>.
>

email notifications scheduled jobs

In the notifications tab of the job I have e-mail operator selected. Click on the box with the elipses to enter the email addresses. It will let me enter 2 email addresses in the e-mail name field. It looks like the field has a limited length...is there a way to manually enter several addresses?http://www.sqlmag.com/Article/ArticleID/21450/sql_server_21450.html
http://www.informit.com/guides/content.asp?g=sqlserver&seqNum=31

email notification on job status

In MSDE, since there is no sql mail, I am thinking of doing the following:
1. write a shell script which will query the sysjobhistory table to
determine if any job failed. If so, then an email about the failure is sent
out.
Question: What should my sql command and my mail command look like. I dont
have much experience in shell scripting or am not very familiar with MSDB
database either.
hi,
inquisite wrote:
> In MSDE, since there is no sql mail, I am thinking of doing the
> following:
> 1. write a shell script which will query the sysjobhistory table to
> determine if any job failed. If so, then an email about the failure
> is sent out.
> Question: What should my sql command and my mail command look like. I
> dont have much experience in shell scripting or am not very familiar
> with MSDB database either.
if an SMPT solution is viable, I'd resort on xp_smpt extended stored
procedure provided at http://sqldev.net/xp/xpsmtp.htm
you can then code your task's step to send a mail on success or failure..
SQL Server MVP Tibor Karaszi presents and describes such a feature at
http://www.karaszi.com/sqlserver/info_no_mapi.asp
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||SMTP solution is not available. That is why I wanted to send an email through
some script running on the server.
"Andrea Montanari" wrote:

> hi,
> inquisite wrote:
> if an SMPT solution is viable, I'd resort on xp_smpt extended stored
> procedure provided at http://sqldev.net/xp/xpsmtp.htm
> you can then code your task's step to send a mail on success or failure..
> SQL Server MVP Tibor Karaszi presents and describes such a feature at
> http://www.karaszi.com/sqlserver/info_no_mapi.asp
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
>
|||If an SMTP server is not available then how were you planing to send email?
Jim
"inquisite" <inquisite@.discussions.microsoft.com> wrote in message
news:D7FE12AC-C359-4B74-A8C6-E497686705CB@.microsoft.com...[vbcol=seagreen]
> SMTP solution is not available. That is why I wanted to send an email
> through
> some script running on the server.
> "Andrea Montanari" wrote:

Email delivery failure

Has anyone had issues with emails being delivered? The job runs successfully,
but the email does not get delivered. It seems to be related to the Reporting
Services server and not the email server. The reason I believe this is that,
after I reboot the Reporting Services server, the emails are successful. I
haven't been able to determine what causes the blockage on that server.Correction: Has anyone had issues with emails "not" being delievered?
"Rand" wrote:
> Has anyone had issues with emails being delivered? The job runs successfully,
> but the email does not get delivered. It seems to be related to the Reporting
> Services server and not the email server. The reason I believe this is that,
> after I reboot the Reporting Services server, the emails are successful. I
> haven't been able to determine what causes the blockage on that server.|||I am having the same exact issue. Any luck? Can you provide input on how you
resolved your issue, if you did?
"Rand" wrote:
> Correction: Has anyone had issues with emails "not" being delievered?
> "Rand" wrote:
> > Has anyone had issues with emails being delivered? The job runs successfully,
> > but the email does not get delivered. It seems to be related to the Reporting
> > Services server and not the email server. The reason I believe this is that,
> > after I reboot the Reporting Services server, the emails are successful. I
> > haven't been able to determine what causes the blockage on that server.

email alert on job failure

Hi all,
Is there a way to send an email alert when a sql server job fails?
Thanks in advance.Hi
If you are using SQL 2000 check the notifications tab on the job properties.
John
"helpful sql" wrote:

> Hi all,
> Is there a way to send an email alert when a sql server job fails?
> Thanks in advance.
>
>|||Hi
Add two additional steps. One is for success (OK) and another is for failure
(NO)
Job's step is going to the (OK) on success action and to (NO) on failure
action
"helpful sql" <nospam@.stopspam.com> wrote in message
news:%237ApnAMXGHA.1220@.TK2MSFTNGP02.phx.gbl...
> Hi all,
> Is there a way to send an email alert when a sql server job fails?
> Thanks in advance.
>|||This example job may help!
BEGIN TRANSACTION
DECLARE @.JobID BINARY(16)
DECLARE @.ReturnCode INT
SELECT @.ReturnCode = 0
IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'Error
Handling') < 1
EXECUTE msdb.dbo.sp_add_category @.name = N'Error Handling'
-- Delete the job with the same name (if it exists)
SELECT @.JobID = job_id
FROM msdb.dbo.sysjobs
WHERE (name = N'Error Handling Example')
IF (@.JobID IS NOT NULL)
BEGIN
-- Check if the job is a multi-server job
IF (EXISTS (SELECT *
FROM msdb.dbo.sysjobservers
WHERE (job_id = @.JobID) AND (server_id <> 0)))
BEGIN
-- There is, so abort the script
RAISERROR (N'Unable to import job ''Error Handling Example'' since there
is already a multi-server job with this name.', 16, 1)
GOTO QuitWithRollback
END
ELSE
-- Delete the [local] job
EXECUTE msdb.dbo.sp_delete_job @.job_name = N'Error Handling Example'
SELECT @.JobID = NULL
END
BEGIN
-- Add the job
EXECUTE @.ReturnCode = msdb.dbo.sp_add_job @.job_id = @.JobID OUTPUT ,
@.job_name = N'Error Handling Example', @.owner_login_name = N'sa',
@.description = N'Error Handling Example.', @.category_name = N'Error
Handling', @.enabled = 0, @.notify_level_email = 0, @.notify_level_page = 0,
@.notify_level_netsend = 0, @.notify_level_eventlog = 2, @.delete_level= 0
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
-- Add the job steps
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobstep @.job_id = @.JobID, @.step_id =
1, @.step_name = N'SQL DUMP', @.command = N'BACKUP DATABASE [MyDatabase] TO
DISK = N''C:\Backup\MyDatabase.bak'' WITH INIT , NOUNLOAD , NAME =
N''Database Backup'', NOSKIP , STATS = 10, NOFORMAT', @.database_name =
N'MyDatabase', @.server = N'', @.database_user_name = N'', @.subsystem =
N'TSQL', @.cmdexec_success_code = 0, @.flags = 6, @.retry_attempts = 0,
@.retry_interval = 1, @.output_file_name = N'', @.on_success_step_id = 2,
@.on_success_action = 4, @.on_fail_step_id = 3, @.on_fail_action = 4
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobstep @.job_id = @.JobID, @.step_id =
2, @.step_name = N'Inform Success', @.command = N'EXEC
master.dbo.xp_smtp_sendmail
@.FROM = N''MyAccount@.SQLServer'',
@.TO = N''MyAccount@.Home'',
@.subject = N''Success - Error Handler Job'',
@.message = N''Error Handler Test Succeeded'',
@.type = N''text/plain'',
@.server = N''MyRelay''
RAISERROR( ''Process Completed Successfully'' , 16,1)', @.database_name =
N'MyDatabase', @.server = N'', @.database_user_name = N'', @.subsystem =
N'TSQL', @.cmdexec_success_code = 0, @.flags = 6, @.retry_attempts = 0,
@.retry_interval = 1, @.output_file_name = N'', @.on_success_step_id = 0,
@.on_success_action = 1, @.on_fail_step_id = 0, @.on_fail_action = 1
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobstep @.job_id = @.JobID, @.step_id =
3, @.step_name = N'Inform Failure', @.command = N'EXEC
master.dbo.xp_smtp_sendmail
@.FROM = N''MyAccount@.SQLServer'',
@.TO = N''MyAccount@.Home'',
@.subject = N''Failure - Error Handler Job'',
@.message = N''Error Handler Test Failed'',
@.type = N''text/plain'',
@.server = N''MyRelay''
RAISERROR( ''Process Failed see log ''' for details'' , 16,1)',
@.database_name = N'MyDatabase', @.server = N'', @.database_user_name = N'',
@.subsystem = N'TSQL', @.cmdexec_success_code = 0, @.flags = 6, @.retry_attempts
= 0, @.retry_interval = 1, @.output_file_name = N'', @.on_success_step_id = 0,
@.on_success_action = 2, @.on_fail_step_id = 0, @.on_fail_action = 2
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @.ReturnCode = msdb.dbo.sp_update_job @.job_id = @.JobID,
@.start_step_id = 1
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
-- Add the job schedules
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobschedule @.job_id = @.JobID, @.name
= N'Error Handling Example', @.enabled = 1, @.freq_type = 4, @.active_start_dat
e
= 19900101, @.active_start_time = 214500, @.freq_interval = 1,
@.freq_subday_type = 1, @.freq_subday_interval = 0, @.freq_relative_interval =
0, @.freq_recurrence_factor = 0, @.active_end_date = 99991231, @.active_end_tim
e
= 235959
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
-- Add the Target Servers
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobserver @.job_id = @.JobID,
@.server_name = N'(local)'
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
END
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@.@.TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
"Uri Dimant" wrote:

> Hi
> Add two additional steps. One is for success (OK) and another is for failu
re
> (NO)
> Job's step is going to the (OK) on success action and to (NO) on failure
> action
>
>
> "helpful sql" <nospam@.stopspam.com> wrote in message
> news:%237ApnAMXGHA.1220@.TK2MSFTNGP02.phx.gbl...
>
>

email alert on job failure

Hi all,
Is there a way to send an email alert when a sql server job fails?
Thanks in advance.Hi
If you are using SQL 2000 check the notifications tab on the job properties.
John
"helpful sql" wrote:
> Hi all,
> Is there a way to send an email alert when a sql server job fails?
> Thanks in advance.
>
>|||Hi
Add two additional steps. One is for success (OK) and another is for failure
(NO)
Job's step is going to the (OK) on success action and to (NO) on failure
action
"helpful sql" <nospam@.stopspam.com> wrote in message
news:%237ApnAMXGHA.1220@.TK2MSFTNGP02.phx.gbl...
> Hi all,
> Is there a way to send an email alert when a sql server job fails?
> Thanks in advance.
>|||This example job may help!
BEGIN TRANSACTION
DECLARE @.JobID BINARY(16)
DECLARE @.ReturnCode INT
SELECT @.ReturnCode = 0
IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'Error
Handling') < 1
EXECUTE msdb.dbo.sp_add_category @.name = N'Error Handling'
-- Delete the job with the same name (if it exists)
SELECT @.JobID = job_id
FROM msdb.dbo.sysjobs
WHERE (name = N'Error Handling Example')
IF (@.JobID IS NOT NULL)
BEGIN
-- Check if the job is a multi-server job
IF (EXISTS (SELECT *
FROM msdb.dbo.sysjobservers
WHERE (job_id = @.JobID) AND (server_id <> 0)))
BEGIN
-- There is, so abort the script
RAISERROR (N'Unable to import job ''Error Handling Example'' since there
is already a multi-server job with this name.', 16, 1)
GOTO QuitWithRollback
END
ELSE
-- Delete the [local] job
EXECUTE msdb.dbo.sp_delete_job @.job_name = N'Error Handling Example'
SELECT @.JobID = NULL
END
BEGIN
-- Add the job
EXECUTE @.ReturnCode = msdb.dbo.sp_add_job @.job_id = @.JobID OUTPUT ,
@.job_name = N'Error Handling Example', @.owner_login_name = N'sa',
@.description = N'Error Handling Example.', @.category_name = N'Error
Handling', @.enabled = 0, @.notify_level_email = 0, @.notify_level_page = 0,
@.notify_level_netsend = 0, @.notify_level_eventlog = 2, @.delete_level= 0
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
-- Add the job steps
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobstep @.job_id = @.JobID, @.step_id =1, @.step_name = N'SQL DUMP', @.command = N'BACKUP DATABASE [MyDatabase] TO
DISK = N''C:\Backup\MyDatabase.bak'' WITH INIT , NOUNLOAD , NAME =N''Database Backup'', NOSKIP , STATS = 10, NOFORMAT', @.database_name =N'MyDatabase', @.server = N'', @.database_user_name = N'', @.subsystem =N'TSQL', @.cmdexec_success_code = 0, @.flags = 6, @.retry_attempts = 0,
@.retry_interval = 1, @.output_file_name = N'', @.on_success_step_id = 2,
@.on_success_action = 4, @.on_fail_step_id = 3, @.on_fail_action = 4
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobstep @.job_id = @.JobID, @.step_id =2, @.step_name = N'Inform Success', @.command = N'EXEC
master.dbo.xp_smtp_sendmail
@.FROM = N''MyAccount@.SQLServer'',
@.TO = N''MyAccount@.Home'',
@.subject = N''Success - Error Handler Job'',
@.message = N''Error Handler Test Succeeded'',
@.type = N''text/plain'',
@.server = N''MyRelay''
RAISERROR( ''Process Completed Successfully'' , 16,1)', @.database_name =N'MyDatabase', @.server = N'', @.database_user_name = N'', @.subsystem =N'TSQL', @.cmdexec_success_code = 0, @.flags = 6, @.retry_attempts = 0,
@.retry_interval = 1, @.output_file_name = N'', @.on_success_step_id = 0,
@.on_success_action = 1, @.on_fail_step_id = 0, @.on_fail_action = 1
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobstep @.job_id = @.JobID, @.step_id =3, @.step_name = N'Inform Failure', @.command = N'EXEC
master.dbo.xp_smtp_sendmail
@.FROM = N''MyAccount@.SQLServer'',
@.TO = N''MyAccount@.Home'',
@.subject = N''Failure - Error Handler Job'',
@.message = N''Error Handler Test Failed'',
@.type = N''text/plain'',
@.server = N''MyRelay''
RAISERROR( ''Process Failed see log ''' for details'' , 16,1)',
@.database_name = N'MyDatabase', @.server = N'', @.database_user_name = N'',
@.subsystem = N'TSQL', @.cmdexec_success_code = 0, @.flags = 6, @.retry_attempts
= 0, @.retry_interval = 1, @.output_file_name = N'', @.on_success_step_id = 0,
@.on_success_action = 2, @.on_fail_step_id = 0, @.on_fail_action = 2
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @.ReturnCode = msdb.dbo.sp_update_job @.job_id = @.JobID,
@.start_step_id = 1
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
-- Add the job schedules
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobschedule @.job_id = @.JobID, @.name
= N'Error Handling Example', @.enabled = 1, @.freq_type = 4, @.active_start_date
= 19900101, @.active_start_time = 214500, @.freq_interval = 1,
@.freq_subday_type = 1, @.freq_subday_interval = 0, @.freq_relative_interval =0, @.freq_recurrence_factor = 0, @.active_end_date = 99991231, @.active_end_time
= 235959
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
-- Add the Target Servers
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobserver @.job_id = @.JobID,
@.server_name = N'(local)'
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
END
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@.@.TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
"Uri Dimant" wrote:
> Hi
> Add two additional steps. One is for success (OK) and another is for failure
> (NO)
> Job's step is going to the (OK) on success action and to (NO) on failure
> action
>
>
> "helpful sql" <nospam@.stopspam.com> wrote in message
> news:%237ApnAMXGHA.1220@.TK2MSFTNGP02.phx.gbl...
> > Hi all,
> > Is there a way to send an email alert when a sql server job fails?
> >
> > Thanks in advance.
> >
>
>

email alert on job failure

Hi all,
Is there a way to send an email alert when a sql server job fails?
Thanks in advance.Hi
If you are using SQL 2000 check the notifications tab on the job properties.
John
"helpful sql" wrote:

> Hi all,
> Is there a way to send an email alert when a sql server job fails?
> Thanks in advance.
>
>|||Hi
Add two additional steps. One is for success (OK) and another is for failure
(NO)
Job's step is going to the (OK) on success action and to (NO) on failure
action
"helpful sql" <nospam@.stopspam.com> wrote in message
news:%237ApnAMXGHA.1220@.TK2MSFTNGP02.phx.gbl...
> Hi all,
> Is there a way to send an email alert when a sql server job fails?
> Thanks in advance.
>|||This example job may help!
BEGIN TRANSACTION
DECLARE @.JobID BINARY(16)
DECLARE @.ReturnCode INT
SELECT @.ReturnCode = 0
IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'Error
Handling') < 1
EXECUTE msdb.dbo.sp_add_category @.name = N'Error Handling'
-- Delete the job with the same name (if it exists)
SELECT @.JobID = job_id
FROM msdb.dbo.sysjobs
WHERE (name = N'Error Handling Example')
IF (@.JobID IS NOT NULL)
BEGIN
-- Check if the job is a multi-server job
IF (EXISTS (SELECT *
FROM msdb.dbo.sysjobservers
WHERE (job_id = @.JobID) AND (server_id <> 0)))
BEGIN
-- There is, so abort the script
RAISERROR (N'Unable to import job ''Error Handling Example'' since there
is already a multi-server job with this name.', 16, 1)
GOTO QuitWithRollback
END
ELSE
-- Delete the [local] job
EXECUTE msdb.dbo.sp_delete_job @.job_name = N'Error Handling Example'
SELECT @.JobID = NULL
END
BEGIN
-- Add the job
EXECUTE @.ReturnCode = msdb.dbo.sp_add_job @.job_id = @.JobID OUTPUT ,
@.job_name = N'Error Handling Example', @.owner_login_name = N'sa',
@.description = N'Error Handling Example.', @.category_name = N'Error
Handling', @.enabled = 0, @.notify_level_email = 0, @.notify_level_page = 0,
@.notify_level_netsend = 0, @.notify_level_eventlog = 2, @.delete_level= 0
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
-- Add the job steps
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobstep @.job_id = @.JobID, @.step_id =
1, @.step_name = N'SQL DUMP', @.command = N'BACKUP DATABASE [MyDatabase] T
O
DISK = N''C:\Backup\MyDatabase.bak'' WITH INIT , NOUNLOAD , NAME =
N''Database Backup'', NOSKIP , STATS = 10, NOFORMAT', @.database_name =
N'MyDatabase', @.server = N'', @.database_user_name = N'', @.subsystem =
N'TSQL', @.cmdexec_success_code = 0, @.flags = 6, @.retry_attempts = 0,
@.retry_interval = 1, @.output_file_name = N'', @.on_success_step_id = 2,
@.on_success_action = 4, @.on_fail_step_id = 3, @.on_fail_action = 4
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobstep @.job_id = @.JobID, @.step_id =
2, @.step_name = N'Inform Success', @.command = N'EXEC
master.dbo.xp_smtp_sendmail
@.FROM = N''MyAccount@.SQLServer'',
@.TO = N''MyAccount@.Home'',
@.subject = N''Success - Error Handler Job'',
@.message = N''Error Handler Test Succeeded'',
@.type = N''text/plain'',
@.server = N''MyRelay''
RAISERROR( ''Process Completed Successfully'' , 16,1)', @.database_name =
N'MyDatabase', @.server = N'', @.database_user_name = N'', @.subsystem =
N'TSQL', @.cmdexec_success_code = 0, @.flags = 6, @.retry_attempts = 0,
@.retry_interval = 1, @.output_file_name = N'', @.on_success_step_id = 0,
@.on_success_action = 1, @.on_fail_step_id = 0, @.on_fail_action = 1
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobstep @.job_id = @.JobID, @.step_id =
3, @.step_name = N'Inform Failure', @.command = N'EXEC
master.dbo.xp_smtp_sendmail
@.FROM = N''MyAccount@.SQLServer'',
@.TO = N''MyAccount@.Home'',
@.subject = N''Failure - Error Handler Job'',
@.message = N''Error Handler Test Failed'',
@.type = N''text/plain'',
@.server = N''MyRelay''
RAISERROR( ''Process Failed see log ''' for details'' , 16,1)',
@.database_name = N'MyDatabase', @.server = N'', @.database_user_name = N'',
@.subsystem = N'TSQL', @.cmdexec_success_code = 0, @.flags = 6, @.retry_attempts
= 0, @.retry_interval = 1, @.output_file_name = N'', @.on_success_step_id = 0,
@.on_success_action = 2, @.on_fail_step_id = 0, @.on_fail_action = 2
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @.ReturnCode = msdb.dbo.sp_update_job @.job_id = @.JobID,
@.start_step_id = 1
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
-- Add the job schedules
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobschedule @.job_id = @.JobID, @.name
= N'Error Handling Example', @.enabled = 1, @.freq_type = 4, @.active_start_dat
e
= 19900101, @.active_start_time = 214500, @.freq_interval = 1,
@.freq_subday_type = 1, @.freq_subday_interval = 0, @.freq_relative_interval =
0, @.freq_recurrence_factor = 0, @.active_end_date = 99991231, @.active_end_tim
e
= 235959
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
-- Add the Target Servers
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobserver @.job_id = @.JobID,
@.server_name = N'(local)'
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
END
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@.@.TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
"Uri Dimant" wrote:

> Hi
> Add two additional steps. One is for success (OK) and another is for failu
re
> (NO)
> Job's step is going to the (OK) on success action and to (NO) on failure
> action
>
>
> "helpful sql" <nospam@.stopspam.com> wrote in message
> news:%237ApnAMXGHA.1220@.TK2MSFTNGP02.phx.gbl...
>
>

EM: Jobs: Runnable column

Hi,
I havent been able to figure out when a job is marked as not runnable.
I have several jobs, some enabled, some disabled, but they are all marked
as runnable.
Someone who can tell?A job without any jobsteps isn't runnable.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Gurba" <gurbao@.hotmail.com> wrote in message
news:Xns96FE6969F3064gurbaohotmailcom@.129.250.171.66...
> Hi,
> I havent been able to figure out when a job is marked as not runnable.
> I have several jobs, some enabled, some disabled, but they are all marked
> as runnable.
> Someone who can tell?

Editing SQL Jobs

I have scheduled a T-SQL job that runs every morning using Enterprise Manager. Now I want to change the SELECT and UPDATE statements that this job runs, but I can't find anywhere to edit a job that has already been scheduled. Any help would be appreciated.

ThanksIn Enterprise Manager, under your SQL Server
-- expand the Management node
-- expand the SQL Server Agent node
-- click on Jobs
-- right-click on your job and choose Properties
-- click on the Steps tab
-- click on the desired Step and choose Edit...

Terri