How to get VBS-Script running inside of MacroScheduler

[migro]

Hi there,

does anybody can help me with this Problem?
I like to run the following script inside of MacroScheduler, but it won't be executed. If I run it directly as *.vbs it works well.

Code: Select all

Let>Action=MyInstance
Vbstart

Option Explicit
On Error Resume Next

Dim strInstanceName : strInstanceName = "%Action%"
Dim objShell : Set objShell = Nothing
Set objShell = Wscript.CreateObject("WScript.Shell") : CheckError

Dim strSqlCmd, strShellCmd
strSqlCmd = "SELECT @@servername" & vbcrlf & "GO" & vbcrlf

strShellCmd = """" & GetSqlCmdFilePath() & """ -S .\" & strInstanceName & " -E -Q """ & strSqlCmd & """ -o C:\test.log"
objShell.Run strShellCmd, 0, True

Function GetSqlCmdFilePath()
   Dim strPath
   strPath = objShell.RegRead("HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\90\Tools\ClientSetup\Path")
   GetSqlCmdFilePath = strPath & "sqlcmd.exe"
End Function

Sub CheckError
	Dim message, errRec
	If Err = 0 Then Exit Sub
	message = Err.Source & " " & Hex(Err) & ": " & Err.Description
	Wscript.Echo message
	Wscript.Quit 2
End Sub

Vbend

Label>ReadTmp1
ReadLn>C:\test.log,k,line
If>line=##EOF##,Exit
MessageModal>%line%
Let>k=k+1
Goto>ReadTmp1

Label>Exit

[Marcus Tettmar]

1) Remove references to WScript - the WScript object is the host. Has no meaning when the host is Macro Scheduler.

2) You can't just reference a MacroScript variable inside VBscript. But you can PASS a variable INTO VBScript.

3) Put your code inside a Sub then you can VBRun it from MacroScript and pass any variables into it you need.

So:

Code: Select all

VBSTART
Option Explicit
On Error Resume Next

Dim objShell : Set objShell = Nothing

Sub DoIt(strInstanceName)
  Set objShell = CreateObject("WScript.Shell") : CheckError

  Dim strSqlCmd, strShellCmd
  strSqlCmd = "SELECT @@servername" & vbcrlf & "GO" & vbcrlf
  strShellCmd = """" & GetSqlCmdFilePath() & """ -S .\" & strInstanceName & " -E -Q """ & strSqlCmd & """ -o C:\test.log"
  objShell.Run strShellCmd, 0, True
End Sub

Function GetSqlCmdFilePath()
   Dim strPath
   strPath = objShell.RegRead("HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\90\Tools\ClientSetup\Path")
   GetSqlCmdFilePath = strPath & "sqlcmd.exe"
End Function

Sub CheckError
	Dim message, errRec
	If Err = 0 Then Exit Sub
	message = Err.Source & " " & Hex(Err) & ": " & Err.Description
	'Wscript.Echo message
	MsgBox message
	'Wscript.Quit 2
End Sub
VBEND

VBRun>DoIt,MyInstance

[migro]

Dear Marcus,

thanks for the great help.

I try it out.