RE: VBScript in SQL Server Agent/Jobs not working

From: AnthonyThomas (AnthonyThomas_at_discussions.microsoft.com)
Date: 11/05/04 

Date: Fri, 5 Nov 2004 10:14:02 -0800

Basically, Windows Scripting Host has two script calling executibles, WScript
and CScript. The first, a Windows (GUI) based host, the other, a Command
(Line) based host. You just store the exact same procedure in a file with
the .VBS or .JS extension and call it with WScript or CScript.

Yes, this is what the Agent does. It is also how it calls dts packages.
The problem is that is gets all wrapped up in its own process id as either a
thread or a spawned external process. All kinds of weird side effects can
happen.

Just use a T-SQL job to run the xp_cmdshell 'CScript "<script path>\<script
file>"' and forget about all those other little "features" that come bundled
up with SQL Server.

Here's a starter link, but if you look at the frame, there is a ton of
documentation.

http://msdn.microsoft.com/library/en-us/script56/html/wsoriWindowsScriptHost.asp?frame=true

Good luck.

Sincerely,

Anthony Thomas

"Kanan" wrote:

> I am not sure I know how to use cscript. Can you point me some place where I
> can
> read more about it. Did Cscript help run a VBScrpt program inside SQL Server
> Agent?
> thanks
> Kanan
>
> "AnthonyThomas" wrote:
>
> > I have had problems with the ActiveX Agent Jobs and the ActiveX task in dts.
> > Let SQL Server do what it does and leave all the bells and whistles for some
> > other process.
> >
> > I've had better luck in both Agent and DTS by using xp_cmdshell and calling
> > cscript to an actual script file. Also, this allows me a lot more
> > flexibility in debugging.
> >
> > Sincerely,
> >
> >
> > Anthony Thomas
> >
> >
> > "Kanan" wrote:
> >
> > > Hi,
> > > I have VBScript in one of my job (newly created job) in SQL Server Agent.
> > > THe problem seems to be in running a macro in an EXCEL workbook. Just plain
> > > open and
> > > close works smoothly. The code to run the macro in the EXCEL workbook makes
> > > the job forever. The same VBScript runs in command shell. Any ideas why
> > > there is any issue in SQL Server Agent? here is the code
> > >
> > > Dim objXL
> > > dim objFSO
> > > dim objFolder,objSubFolder, objthisFolder
> > > dim objFile,objstream,bookname
> > > const filename = "\\pashare\pamf
> > > apps\Analysis_SOlutions\SOLUTIONS_DEV\kanans.txt"
> > > set objFSO = CreateObject("Scripting.FileSystemObject")
> > >
> > > set objstream = objFSO.OpenTextFile(filename,1,False,0)
> > > Set objXL = CreateObject("Excel.Application")
> > > objXL.Visible = true
> > > do while not objstream.AtEndOfStream
> > > bookname = objstream.readline
> > > if right(bookname,4) = ".xls" then
> > > objXL.WorkBooks.Open(bookname)
> > > objXL.run("monthly_refresh")
> > > objXL.workbooks.close
> > > end if
> > > loop
> > >
> > > objstream.close
> > > objXL.quit
> > > set objXL = nothing
> > > set objFSO = nothing
> > >
> > >

Relevant Pages

  • Default Script Host
    ... My default Script Host magically changed from CScript to WScript. ...
    (microsoft.public.scripting.vbscript)
  • Re: ouput 100 lines from .wsf when using WScript
    ... > Hi Rob, ... > If they just need to see a list, I'd suggest using CScript, just create ... CScript as my default host. ... This script Torgeir hyperlinks to contains just what I needed. ...
    (microsoft.public.scripting.wsh)
  • Re: Can using Wscript.StdOut.Write ?
    ... 'WScript.StdOut.WriteLine' will raise an error 'invalid file handle' ... that refers to consoles by design, WScript doesn't support them. ... So if you launch your script by 'wscript bar.vbs' it won't work, ... Any example for using cscript can allow arguments input? ...
    (microsoft.public.scripting.wsh)
  • Re: Scripting hosts backwards on my system
    ... the //H switch sets the default host which will be used when a script is ... Copyright Microsoft Corporation 1996-2001. ... even though CScript was long ago ...
    (microsoft.public.scripting.vbscript)
  • Re: Scripting hosts backwards on my system
    ... the //H switch sets the default host which will be used when a script ... Copyright Microsoft Corporation 1996-2001. ... even though CScript was long ago ...
    (microsoft.public.scripting.vbscript)
Loading