Run Vbscript as a Job in Enterprise Manager - Urgent:

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 = ""


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

nr VAsked:
Who is Participating?
spcmnspffConnect With a Mentor Commented:
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.
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 . . . .
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.
nr VAuthor Commented:
I found out.
The solution is to 'Use the DCOMcnfg utility to add the SQLAgent login account to allow access under DefaultAccessPermissions'.;en-us;Q298725

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?
nr VAuthor Commented:
My vbscript is now running fine from a dts package.

All Courses

From novice to tech pro — start learning today.