Update SQL Table with a VB Script from a Remote Computer

[intelwizrd]

I am trying to write a vb script to update a sql table with information from a mail server. I have the information i want to update and i think i built the query correctly but it doesnt seem to work. i have tried a number of things but i am stuck. any help with the code would be great.
It has been years since i have touched vb so i am extremely rusty.

'---------------------------------------------------------------------
' Mail Server Directory Count Script
'---------------------------------------------------------------------

' Standard Variables
Const vbMinimizedNoFocus = 6
Const ForReading         = 1
Const ForWriting         = 2
const ForAppending       = 8
const adOpenForwardOnly  = 0
const adOpenKeyset       = 1
const adOpenDynamic      = 2
const adOpenStatic       = 3

on error resume next
'---------------------------------------------------------------------
' Get PATH Information From Registry & Ini File
'---------------------------------------------------------------------
Set WSHShell = CreateObject("WScript.Shell")
RegKey = "HKLM\SOFTWARE\Alt-N Technologies\MDaemon\"
iniFile  = WSHShell.RegRead(RegKey & "IniPath")
AppPath  = WSHShell.RegRead(RegKey & "AppPath")
MDPath   = Left(AppPath, instrrev(AppPath,"\",(Len(AppPath)-1)))

set fs = CreateObject("Scripting.FileSystemObject")

'---------------------------------------------------------------------
' Parse MDaemon.ini for Local and Remote Que Paths
'---------------------------------------------------------------------
set MDConfig = fs.OpenTextFile(iniFile,ForReading, true)
    while not MDConfig.AtEndOfStream
    CurrentLine=MDConfig.ReadLine

    if lcase(left(CurrentLine,11)) = "domainname=" Then _
	   MailServer = Mid(CurrentLine,12)

    if lcase(left(CurrentLine,8)) = "inbound=" Then _
	   InboundQPathRoot = Mid(CurrentLine,9)

    if lcase(left(CurrentLine,8)) = "remoteq=" Then _
	   RetryQPath = Mid(CurrentLine,9) & "retry\"

    if lcase(left(CurrentLine,7)) = "localq=" Then _
	   LocalQPath = Mid(CurrentLine,8)

    if lcase(left(CurrentLine,8)) = "remoteq=" Then _
	   RemoteQPath = Mid(CurrentLine,9)

    wend
MDConfig.Close


'---------------------------------------------------------------------
' Count Email Message in Both Local and Remote Ques
'---------------------------------------------------------------------
InboundQCount   = 0
RetryQCount     = 0
LocalQCount  = 0
RemoteQCount = 0

' Count Inbound Queue Size
For subdir = 1 to 65
	if subdir < 10 then subdirt = "0" & subdir
	if subdir > 9 then subdirt = subdir
	InboundQPath = InboundQPathRoot & "\" & subdirt & "\"
	
	set InboundQFolder = fs.GetFolder(InboundQPath)
	    For Each Email in InboundQFolder.Files
	     If Right(LCase(Email.Name),4)=".msg" Then
		InboundQCount = InboundQCount + 1
	     End If
	    Next
Next

'Count Retry Queue Size
set RetryQFolder = fs.GetFolder(RetryQPath)
    For Each Email in RetryQFolder.Files

     If Right(LCase(Email.Name),4)=".msg" Then
      RetryQCount = RetryQCount + 1
     End If

    Next

'Count Local Queue Size
set LocalQFolder = fs.GetFolder(LocalQPath)
    For Each Email in LocalQFolder.Files

     If Right(LCase(Email.Name),4)=".msg" Then
      LocalQCount = LocalQCount + 1
     End If

    Next

'Count Remote Queue Size
set RemoteQFolder = fs.GetFolder(RemoteQPath)
    For Each Email in RemoteQFolder.Files

     If Right(LCase(Email.Name),4)=".msg" Then
      RemoteQCount = RemoteQCount + 1
     End If

    Next


'---------------------------------------------------------------------
' Echo results for testing
'---------------------------------------------------------------------
wscript.echo MailServer
wscript.echo InboundQCount
wscript.echo RetryQCount
wscript.echo LocalQCount
wscript.echo RemoteQCount

'---------------------------------------------------------------------
' Update SQL with results
'---------------------------------------------------------------------
Dim conn
Dim RstCase
Dim StrSQLupdateIQ
Dim StrSQLupdateRTQ
Dim StrSQLupdateLQ
Dim StrSQLupdateRQ

' Create ADO conneciton and Recordset Objects
set conn = CreateObject("ADODB.Connection")
set RstCase = CreateObkect("ADODB.Recordset")

conn.Open "Provider=SQLOLEDB;Data Source=mailserversql;Database=dbSuppression;Uid=mailserver;pwd=testing;"

StrSQLupdateIQ = "Update varMailServerQueue set CurrentSize = " _
	& InboundQCount _
	& ", CurrentSizeTS = getdate()" _
	& " Where MSQType = 4 AND MSQMailServer IN" _
	& " (Select MSId From tblMailServer where (msname like '" & MailServer & "'))"

StrSQLupdateRTQ = "Update varMailServerQueue set CurrentSize = " _
	& RetryQCount _
	& ", CurrentSizeTS = getdate()" _
	& " Where MSQType = 3 AND MSQMailServer IN" _
	& " (Select MSId From tblMailServer where (msname like '" & MailServer & "'))"

StrSQLupdateLQ = "Update varMailServerQueue set CurrentSize = " _
	& LocalQCount _
	& ", CurrentSizeTS = getdate()" _
	& " Where MSQType = 1 AND MSQMailServer IN" _
	& " (Select MSId From tblMailServer where (msname like '" & MailServer & "'))"

StrSQLupdateRQ = "Update varMailServerQueue set CurrentSize = " _
	& RemoteQCount _
	& ", CurrentSizeTS = getdate()" _
	& " Where MSQType = 2 AND MSQMailServer IN" _
	& " (Select MSId From tblMailServer where (msname like '" & MailServer & "'))"


conn.Execute StrSQLupdateIQ, NumAffected
wscript.echo "Inbound" NumAffected
conn.Execute StrSQLupdateRTQ, NumAffected
wscript.echo "Retry" NumAffected
conn.Execute StrSQLupdateLQ, NumAffected
wscript.echo "Local" NumAffected
conn.Execute StrSQLupdateRQ, NumAffected
wscript.echo "Remote" NumAffected

this is the output when i run "cscript filecount.vbs"

Mail.mydomain.com
0
471
0
0
Inbound
Retry
Local
Remote

Any Ideas?
The SQL Server is SQL 2000

----------------------------
Josh
CCNA, MCSE 2003(in progress)

 

[tsuji]

Just wonder if you really mean it.
>[tt]if lcase(left(CurrentLine,8)) = [red]"remoteq="[/red] Then _
RetryQPath = Mid(CurrentLine,9) & "retry\"[/tt]
>[tt]if lcase(left(CurrentLine,8)) = [red]"remoteq="[/red] Then _
RemoteQPath = Mid(CurrentLine,9)[/tt]

 

[intelwizrd]

Yes, the retry queue is a subfolder of the remote queue.

----------------------------
Josh
CCNA, MCSE 2003(in progress)

 

[tsuji]

>Yes, the retry queue is a subfolder of the remote queue
Okay, how about taking out the "on error resume next" to see where it error out?

 

[tsuji]

>[tt]set RstCase = CreateObkect("ADODB.Recordset")[/tt]
[tt]set RstCase = CreateOb[red]j[/red]ect("ADODB.Recordset")[/tt]
and you don't even need it.

 

[intelwizrd]

I removed the "on error resume next" and the "set RstCase..." lines, ran it, got this error.

Microsoft OLE DB Provider for SQL Server: [DBNMPNTW]Specified SQL server not found.

Any ideas? the dsn is there, i set it up myself and tested it. Is my connection string wrong?

----------------------------
Josh
CCNA, MCSE 2003(in progress)

 

[intelwizrd]

I changed the connection string to use the ip instead of the dsn and it worked like a charm. thanks for your help though

conn.Open "Provider=SQLOLEDB;Data Source=xxx.xxx.xxx.xxx;Initial Catalog=dbSuppression;Network=DBMSSOCN;Uid=mailserver;pwd=testing;"

----------------------------
Josh
CCNA, MCSE 2003(in progress)