Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
| 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 software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #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 ?
>


  #2 (permalink)  
Old April 7th, 2004, 06:34 AM
Imar's Avatar
Wrox Author
Points: 70,322, Level: 100
Points: 70,322, Level: 100 Points: 70,322, Level: 100 Points: 70,322, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 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.
  #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.

  #4 (permalink)  
Old April 8th, 2004, 01:57 AM
Imar's Avatar
Wrox Author
Points: 70,322, Level: 100
Points: 70,322, Level: 100 Points: 70,322, Level: 100 Points: 70,322, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 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.
  #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()

  #6 (permalink)  
Old April 8th, 2004, 02:24 AM
Imar's Avatar
Wrox Author
Points: 70,322, Level: 100
Points: 70,322, Level: 100 Points: 70,322, Level: 100 Points: 70,322, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 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.
  #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.

  #8 (permalink)  
Old April 8th, 2004, 04:23 AM
Imar's Avatar
Wrox Author
Points: 70,322, Level: 100
Points: 70,322, Level: 100 Points: 70,322, Level: 100 Points: 70,322, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 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.
  #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 ?

  #10 (permalink)  
Old April 8th, 2004, 05:13 AM
Imar's Avatar
Wrox Author
Points: 70,322, Level: 100
Points: 70,322, Level: 100 Points: 70,322, Level: 100 Points: 70,322, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 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.




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





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.