Solved

Run Vbscript as a Job in Enterprise Manager - Urgent:

Posted on 2002-06-23
5
548 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

The Ultimate Checklist to Optimize Your Website

Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.

Question has a verified solution.

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

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
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…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

734 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