Solved

Run Vbscript as a Job in Enterprise Manager - Urgent:

Posted on 2002-06-23
5
549 Views
Last Modified: 2008-10-13
I have a vbscript msgsend.vbs that is running
as a scheduled task in Windows 2000.
It's running as DOMAIN\SA_Sql.
No problem.

Now I would like to move into SQL Server Enterprise Manager.

I have tried two things.

1. Created a jobstep of type 'Operative System Command'.
   Command: C:\msgsend.vbs
   
   Executed as user: DOMAIN\SA_Sql. The process could not be created for step 1 of job 0xF63D2282BF0A654191797150C6D96A4E (reason: Overlapped I/O operation is in progress).  The step failed.
   
2. Created a jobstep of type 'Active X script' and then pasted the script from the file.

   Executed as user: DOMAIN\SA_Sql. Error Code: 0  Error Source= Microsoft VBScript runtime error  Error Description: Permission denied: 'CreateObject'    Error on Line 4.  The step failed.    

As you see the results could have been better.

Here's the script.

*****************************************************

Dim myOlApp
Dim myNameSpace
Dim myItem

Set myOlApp = CreateObject("Outlook.Application")

Set myNameSpace = myOlApp.GetNameSpace("MAPI")
myNameSpace.Logon "SQLMail", "xxxxxx", False, True

Set myItem = myOlApp.CreateItem(MailItem)
myItem.To = "ola.hallengren@topnordic.se"
myItem.Send

myNameSpace.Logoff

Set myOlApp = Nothing
Set myNameSpace = Nothing
Set myItem = Nothing

*****************************************************
0
Comment
Question by:nr V
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 5

Expert Comment

by:spcmnspff
ID: 7103995
When you say you created a job of type ActiveX script did you create a DTS package?  When I want to schedule VBscrtipt fom SQL server, I create a DTS package with an ActiveX script task and paste the code in there.  This way you can control who owns the package and under which security context the package and the vb code will be excuted.  Let me know . . . .
0
 
LVL 10

Expert Comment

by:AndresM
ID: 7104309
For 1, "Created a jobstep of type 'Operative System Command'", the command should be "c:\winnt\system32\cscript.exe  C:\msgsend.vbs".
But If you do this, probably you will get the error 2, "Permission denied: 'CreateObject'".
The jobs run on the security context of the account that starts SQL Server Agent, so try checking the permissions of that account in the Outlook directory.
Hope this helps.
0
 

Author Comment

by:nr V
ID: 7104382
I found out.
The solution is to 'Use the DCOMcnfg utility to add the SQLAgent login account to allow access under DefaultAccessPermissions'.

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q298725

spcmnspff
I have not crated a dts package.
I just pasted the vbscript into the jobstep.
Are there other advantages than those you mention,
to put the code into a dts package?
0
 
LVL 5

Accepted Solution

by:
spcmnspff earned 200 total points
ID: 7104473
Once you've created the DTS package, it's a little easier to migrate i.e. saving the package to another server.  The real advantage, however, yopu can load global variables from various server parameters and manipulate thaem in your code.  Also, there's more flexibilty with what's going on with your server - you can trigger vbscript in the middle of package, after one DTS task but before another, etc.
0
 

Author Comment

by:nr V
ID: 7104857
My vbscript is now running fine from a dts package.

Thanks.
0

Featured Post

Enroll in July's Course of the Month

July's Course of the Month is now available! Enroll to learn HTML5 and prepare for certification. It's free for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

632 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question