Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old April 7th, 2004, 06:03 AM
Registered User
 
Join Date: Apr 2004
Location: Chennai, Tamil Nadu, India.
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Default Scheduled VBS file is not working properly

> I have one problem in scheduling the VBS file using
> Windows 2000 Task Scheduler.
>
> This vbs file contains the Wscript statements to send an
> email to my id. When i double click that it works
> well and i am receiving an email. But if i schedule
> the same VBS file using task scheduler then it shows
> it was executed successfully, but i did not receive
> any mail. What could be the reason ?
>


Reply With Quote
  #2 (permalink)  
Old April 7th, 2004, 06:34 AM
Imar's Avatar
Wrox Author
Points: 72,055, Level: 100
Points: 72,055, Level: 100 Points: 72,055, Level: 100 Points: 72,055, Level: 100
Activity: 100%
Activity: 100% Activity: 100% Activity: 100%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,086
Thanks: 80
Thanked 1,587 Times in 1,563 Posts
Default

Hi bsrini,

What exactly does your question have to do with databases and SQL Server? Maybe you should try a more appropriate forum, as this one is meant for topics specifically dealing with SQL Server.

Anyway, this is probably a permissions issue. When you double click the file, it runs under *your* account. When scheduled, it will run under a local system account by default.
In the scheduler, change the user logon name and password and then it will probably work.
If not, please post some code.

Imar


---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
Reply With Quote
  #3 (permalink)  
Old April 8th, 2004, 01:50 AM
Registered User
 
Join Date: Apr 2004
Location: Chennai, Tamil Nadu, India.
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Background info:

To get the failed jobs from the SQL Server, we have two methods 1) we can install Microsoft Outlook and create operators and set email notifications for the jobs, 2) we have some stored procedures to send email.

We have more servers, so we cannot install outlook in each and every box, so what i have decided is, using OSQL utility you can connect to that database and get the failed jobs result from the MSDB database. For this purpose, I have created a .bat file, which will execute in the specified time and get the jobs result from all the servers and store it in a text file and this text file will be sent as an attachment in an email to me. So that I can see what are the jobs failed and take necessary action.

I have scheduled the .bat file and it is working fine, it is fetching all the jobs result and storing in the text file. But for email sending, i have created a VBS file (I have attached the code below), when i double click this file manually then it sends me an email, but when i schedule this, the job status shows it is succesful, but i am not receiving email. Please let me know what could be the reason.

In fact, these jobs are running in my username / password, i have created 1 jobs which contains the code for getting failed jobs and sending email, the first step is working, but the second step does not.

Reply With Quote
  #4 (permalink)  
Old April 8th, 2004, 01:57 AM
Imar's Avatar
Wrox Author
Points: 72,055, Level: 100
Points: 72,055, Level: 100 Points: 72,055, Level: 100 Points: 72,055, Level: 100
Activity: 100%
Activity: 100% Activity: 100% Activity: 100%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,086
Thanks: 80
Thanked 1,587 Times in 1,563 Posts
Default

Under what account did you schedule the job in the Windows Scheduler? Also under your account?

And you may want to repost the code. It didn't show up here.....

Imar

---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
Reply With Quote
  #5 (permalink)  
Old April 8th, 2004, 02:10 AM
Registered User
 
Join Date: Apr 2004
Location: Chennai, Tamil Nadu, India.
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I have posted in my account (My userid / password), it is a Domain account not a local administrator. I have scheduled 2 jobs with the same account, but one is working another one is not working.

Option Explicit

Const olMailItem = 0 ' Constants for new items
Const olAppointmentItem = 1
Const olContactItem = 2
Const olTaskItem = 3
Const olJournalItem = 4
Const olNoteItem = 5
Const olPostItem = 6

Dim objOutl, objNameSpace, objMailItem
Dim recipient, message, subject

' define the items for the e-mail
recipient = "bbsrini@rediffmail.com"
subject = "Subject: Outlook example"
message = "Latest Message Last"

' Create Outlook object reference
Set objOutl = WScript.CreateObject("Outlook.Application")


' obtain an object reference to "Mapi" name space
Set objNameSpace = objOutl.GetNameSpace("MAPI")


' create a new mail item object
Set objMailItem = objOutl.CreateItem(olMailItem)

' set the mail object properties
objMailItem.Recipients.Add recipient ' E-mail address
'objMailItem.Attachments.Add("c:\test.txt")
objMailItem.Subject = subject ' subject
objMailItem.Body = message ' the body text



' now we are ready to "logon" to outlook (using Logon method)
objNameSpace.Logon
objMailItem.Send ' send method
objNameSpace.Logoff ' logoff

WScript.Echo "E-mail created for " & recipient ' Post ready message

WScript.Quit()

Reply With Quote
  #6 (permalink)  
Old April 8th, 2004, 02:24 AM
Imar's Avatar
Wrox Author
Points: 72,055, Level: 100
Points: 72,055, Level: 100 Points: 72,055, Level: 100 Points: 72,055, Level: 100
Activity: 100%
Activity: 100% Activity: 100% Activity: 100%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,086
Thanks: 80
Thanked 1,587 Times in 1,563 Posts
Default

Hi again,

I think this problem is related to the Outlook.Application object.
I am not 100% sure (this is not really my cup of tea) but I think it might be related to which "desktop" you're on. There can only be one real and active desktop, and it looks like Outlook only runs in the active desktop. So, when you run it as a service, even under your name, it doesn't work well. [All this might be nonsense, so don't take my word for it ;)].

Take a look here for someone with the same issues: http://p2p.wrox.com/topic.asp?TOPIC_ID=9430

Maybe you can ask the original poster if and how he fixed it, by adding a post to the thread.

As a work around, you could take a look at sending mail with CDO instead. Take a look here for some ideas: http://Imar.Spaanjaars.Com/QuickDocID.aspx?QUICKDOC=249
It's written for ASP. but it will also work in VB Script / WSH

Cheers,

Imar


---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
Reply With Quote
  #7 (permalink)  
Old April 8th, 2004, 04:15 AM
Registered User
 
Join Date: Apr 2004
Location: Chennai, Tamil Nadu, India.
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Default

But i need to specify SMTP server in CDONTS method. right ? I have a Exchange server name, can i use the same name in the smtp server ? If i send using this method, how long will it take to receive the mail.

Reply With Quote
  #8 (permalink)  
Old April 8th, 2004, 04:23 AM
Imar's Avatar
Wrox Author
Points: 72,055, Level: 100
Points: 72,055, Level: 100 Points: 72,055, Level: 100 Points: 72,055, Level: 100
Activity: 100%
Activity: 100% Activity: 100% Activity: 100%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,086
Thanks: 80
Thanked 1,587 Times in 1,563 Posts
Default

Yes, you can just specify the name of the Exchange server, instead of an SMTP server.

Mail should be sent instantly. So, the code drops the mail right into Exchange. Of course it'll depend on the Exchange server how long it will take before the mail arrives, but usually this should go really quick.

Imar


---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
Reply With Quote
  #9 (permalink)  
Old April 8th, 2004, 04:44 AM
Registered User
 
Join Date: Apr 2004
Location: Chennai, Tamil Nadu, India.
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Could you please let me know what is the parameter to specify the SMTP Server name ?

Reply With Quote
  #10 (permalink)  
Old April 8th, 2004, 05:13 AM
Imar's Avatar
Wrox Author
Points: 72,055, Level: 100
Points: 72,055, Level: 100 Points: 72,055, Level: 100 Points: 72,055, Level: 100
Activity: 100%
Activity: 100% Activity: 100% Activity: 100%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,086
Thanks: 80
Thanked 1,587 Times in 1,563 Posts
Default

It's in the article I pointed you to.....

You can only set the SMTP server when you use the CDO code, not with CDONTS. Sorry if I confused you with my previous message; I thought you were referring to Collaboration Data Objects for Exchange.

Imar


---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Web service not working properly in IIS Abhinavnaresh ASP.NET 2.0 Professional 1 April 10th, 2008 08:53 AM
ch 8 code is not working properly Dev_gh78 C# 1 August 4th, 2006 12:16 AM
Banner ad values not working properly. malhyp Dreamweaver (all versions) 3 January 17th, 2006 03:42 AM
PreparedStatement not working properly wslyhbb Java Databases 3 September 9th, 2003 01:08 PM



All times are GMT -4. The time now is 12:45 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.