Start Free Trial

asked on

vb script run time error access denied

Hello Experts, I am getting vb script runtime permission denied error when a task scheduler run the vb script to sync intranet site form for inventory, our intranet site runs on iis web server 7 which is running on SR2008R2 which is a domain controller as well, below I have pasted the vb script for reference, can some please help with situation, thanks.

Dim oShell

Set oShell = CreateObject("WScript.Shell")
oShell.run "C:\Windows\Syswow64\wscript.exe ""D:\Company Files\DMSDataExtract\run_ucd_update.vbs""", 0, True
Set oShell = Nothing
vbs-runtime-error.PNG

Can I ask why are you trying to use a vbs to just run another vbs. Try just running the vbs D:\Company Files\DMSDataExtract\run_ucd_update.vbs directly from task manager and make sure that you are using an account that has high enough rights.

Also what happens when you manually run the Vbs ?

D:\Company Files\DMSDataExtract\run_ucd_update.vbs

ASKER

Same error when I run straight manually and the account does have admin rights

There is your answer. You need to run the task with an account that has admin rights.

Do you think your account should have the proper rights ?

ASKER

Honestly not sure about script what else it runs all I know it updates the form for sale team about inventory it was working fine and it's been a week we are having this problem

ASKER

The account admin is part of administrators group in AD and task scheduler runs under admin

But the need to use a VBS to just execute a vbs is not needed. You should be able to natively exe a vbs without any other intervention.

Now the rights issue is another story. You will need to speak to someone that manages the system you are trying to write to.

ASKER

Well it's same server which has the role of DC and IIS togather the script was running okay is there anyway if you can please look at the script I have pasted in my question I have no clue about vbs.

What I am saying there is no need to use a script to run the D:\Company Files\DMSDataExtract\run_ucd_update.vbs script. You should be able to just run the D:\Company Files\DMSDataExtract\run_ucd_update.vbs directly by double clicking it for a manual test and if that work just modify your task with the command line "D:\Company Files\DMSDataExtract\run_ucd_update.vbs"

Does that make sense?

ASKER

Yes I did ran it by double clicking on it but same error pops up we run through task scheduler to make sure it runs every morning.

Are you running

Dim oShell

Set oShell = CreateObject("WScript.Shell")
oShell.run "C:\Windows\Syswow64\wscript.exe ""D:\Company Files\DMSDataExtract\run_ucd_update.vbs""", 0, True
Set oShell = Nothing

Or

"D:\Company Files\DMSDataExtract\run_ucd_update.vbs

ASKER

I am running Dim O shell which is saved as sync.vbs, and i rum sync.vbs using task scheduler

Don't run the script with the DIM. Try just running "D:\Company Files\DMSDataExtract\run_ucd_update.vbs" by itself.

No need to do anything else.

Does that make sense.

You are adding complexity with your second vbs to run another vbs.

ASKER

I have just tried it but no luck I still can't the site still giving 500 error when this script run update it wouldn't give 500 error it will show updated inventory form

ASKER

what is this cmd inside the vbscript C:\Windows\Syswow64\wscript.exe

You are not understanding what I am saying. Your original sync.vbs is just a vbs to run another command, which you do not need because it is a vbs.

All you need to do is run your vbs that is the one syncing the information

I have said it about 5 or 6 times prior, just run. "D:\Company Files\DMSDataExtract\run_ucd_update.vbs" only. No need to do anything with your sync.vbs.

When I get home in 30 mins or so I will post some screenshot explaining what I am talking about.

ASKER

when i run D:\Company Files\DMSDataExtract\run_ucd_update.vbs" by itself it dosen't create error but does't update the site

You do not need to run wcript.exe at all. Windows Is aware of vbs and natively uses wscript.exe to run vbs files.

Then the sync.vbs will not do anything either because all you are doing with the sync.vbs is running your "D:\Company Files\DMSDataExtract\run_ucd_update.vbs" script.

Can you post the "run_ucd_update.vbs" script

ASKER

i did understand your suggestion i ran D:\Company Files\DMSDataExtract\run_ucd_update.vbs" by itself, it doesn't create error but doesn't update the site

ASKER

Here is the "run_ucd_update.vbs" script

Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = &H0001
Const sSertiPath = "\\192.168.10.24\aalt\"
Const sPath = "D:\Company Files\DMSDataExtract\"
Const sFile = "WATERLOOHONDA_vehicle_inventory.csv"

Call Main

'******* Main sub to download new xml and image files *************
Sub Main

Dim oFSO
Dim oShell
Dim oConnMDB
Dim oConnCSV
Dim oRSMDB
Dim oRSCSV
Dim bFound
Dim sSQL
Dim objDictionary
Dim lCost 
Dim lReconditioning 

Set oShell = CreateObject("WScript.Shell")
Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oConnMDB = CreateObject("ADODB.Connection")
Set oRSMDB = CreateObject("ADODB.Recordset")
Set oConnCSV = CreateObject("ADODB.Connection")
Set oRSCSV = CreateObject("ADODB.Recordset")
Set objDictionary = CreateObject("Scripting.Dictionary")
Set objRODictionary = CreateObject("Scripting.Dictionary")

'------- Section 1 Start -------'
'003 create backup
If oFSO.FileExists(sPath & "usedcars.002") Then
	oFSO.CopyFile sPath & "usedcars.002", sPath & "usedcars.003", True
End If
'002 create backup
If oFSO.FileExists(sPath & "usedcars.001") Then
	oFSO.CopyFile sPath & "usedcars.001", sPath & "usedcars.002", True
End If
'001 create backup
If oFSO.FileExists(sPath & "usedcars.mdb") Then
	oFSO.CopyFile sPath & "usedcars.mdb", sPath & "usedcars.001", True
End If

'------- Section 2 Start -------'
'Get current inventory file from serti (aalt dir)
Dim sSertiFileExists 
If oFSO.FileExists(sSertiPath & sFile) Then
	sSertiFileExists = True
Else
    sSertiFileExists = False
End If

Dim sPathFileExists 
If oFSO.FileExists(sPath & sFile) Then
	sPathFileExists = True
Else
    sPathFileExists = False
End If

If sSertiFileExists = True Then
	If sPathFileExists = True Then
		oFSO.DeleteFile sPath & sFile
		oFSO.CopyFile sSertiPath & sFile, sPath & sFile
	Else
		oFSO.CopyFile sSertiPath & sFile, sPath & sFile
	End If
Else
	Dim sertiIteration
	For sertiIteration = 0 To 10
		Do
			Dim fileCopied
			If oFSO.FileExists(sSertiPath & sFile) Then
				oFSO.DeleteFile sPath & sFile
				oFSO.CopyFile sSertiPath & sFile, sPath & sFile
				fileCopied = True
			Else
				fileCopied = False
			End If
			If fileCopied = True Then Exit Do
			WScript.Sleep 5000 'Sleeps for 5 seconds
		Loop While False
	Next
End If

'------- Section 3a Start -------'
'Update the inv file with and WIP RO Number
oConnCSV.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\192.168.10.24\wip\;Extended Properties=""text;HDR=No;FMT=Delimited"""
sSQL = "SELECT * FROM WATERLOO_HONDA_WIP_VEHICLE_SVC.csv;"
oRSCSV.Open sSQL, oConnCSV, adOpenStatic, adLockOptimistic, adCmdText
'load dictionary with stock and updated price
Do while not oRSCSV.EOF
	tmpRONumber = oRSCSV.Fields(2)&"|"&oRSCSV.Fields(4)
	tmpVIN = oRSCSV.Fields(17)
	If objRODictionary.Exists(tmpVIN) Then
  	'msgbox  "Specified key exists."
  Else
   	objRODictionary.Add tmpVIN,tmpRONumber
  End If
	oRSCSV.MoveNext
Loop
oRSCSV.Close
oConnCSV.Close

'------- Section 3b Start -------'
'Update the latest used price in inventory data file from vAuto
oConnCSV.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Company Files\eDealer\;Extended Properties=""text;HDR=YES;FMT=Delimited"""
sSQL = "SELECT * FROM UsedCarPriceList.txt WHERE ([StockNumber] Is Not Null);"
oRSCSV.Open sSQL, oConnCSV, adOpenStatic, adLockOptimistic, adCmdText
'load dictionary with stock and updated price
Do while not oRSCSV.EOF
	If oRSCSV("Pending") <> "" then
		tmpPrice = oRSCSV("Pending")
		tmpStock = oRSCSV("StockNumber")
		objDictionary.Add tmpStock,tmpPrice
	ElseIf oRSCSV("Price") <> "" then
		tmpPrice = oRSCSV("Price")
		tmpStock = oRSCSV("StockNumber")
		objDictionary.Add tmpStock,tmpPrice
	End If
	oRSCSV.MoveNext
Loop
oRSCSV.Close
oConnCSV.Close

'------- Section 3b Start -------'
'replace 3a and 3b dictionaries in csv file
oConnCSV.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sPath & ";Extended Properties=""text;HDR=NO;FMT=CSVDelimited"""
sSQL = "SELECT * FROM " & sFile & " ORDER BY [StockNumber];"
oRSCSV.Open sSQL, oConnCSV, adOpenStatic, adLockOptimistic, adCmdText
'create new TMP CSV file with updated prices and ro numbers
Set objCSVFile = oFSO.CreateTextFile("D:\Company Files\DMSDataExtract\TMPWATERLOOHONDA_vehicle_inventory.csv", True)
'loop csv file
Do while not oRSCSV.EOF
	tmpStock = oRSCSV("StockNumber")
	tmpVIN = oRSCSV("VIN")
	For i = 0 to 74 
		tmpValue = oRSCSV.Fields(i).Value			
		if i = 74 then ' last field no ","
			objCSVFile.Write tmpValue
 		elseif i = 23 then ' Price field replace with vAuto Price
			tmpPrice = objDictionary.Item(tmpStock)
			If tmpPrice <> "" Then
				objCSVFile.Write tmpPrice & ","
			else
				objCSVFile.Write tmpValue & ","
			end if				
 		elseif i = 28 then ' Location2 field replace with RO Number from wip file
 			tmpRONumber = objRODictionary.Item(tmpVIN)
			If tmpRONumber <> "" Then
				objCSVFile.Write left(tmpRONumber,instr(tmpRONumber,"|")-1) & ","
			else
				objCSVFile.Write tmpValue & ","
			end if
 		elseif len(tmpValue)<1 then
 			objCSVFile.Write tmpValue & ","
 		elseif IsNull(tmpValue) then
 			objCSVFile.Write tmpValue & ","
 		elseif IsNumeric(tmpValue) then
 			objCSVFile.Write tmpValue & ","
 		else
    	objCSVFile.Write chr(34) & tmpValue & chr(34) & ","
		end if   
	Next
	objCSVFile.Writeline
	oRSCSV.MoveNext
Loop
oRSCSV.Close
oConnCSV.Close
objCSVFile.Close
Set objCSVFile = nothing
'delete TMP csv file now that updated prices are in
oFSO.CopyFile sPath & "TMP" & sFile, sPath & sFile, True
If oFSO.FileExists(sPath & "TMP" & sFile) Then
	oFSO.DeleteFile sPath & "TMP" & sFile
End If

'------- Section 4 Start -------'
'Copy updated inventory file for Deskit 
If oFSO.FileExists("D:\Company Files\eDealer\WATERLOOHONDA_vehicle_inventory.csv") Then
	oFSO.DeleteFile "D:\Company Files\eDealer\WATERLOOHONDA_vehicle_inventory.csv"
End If
oFSO.CopyFile sPath & sFile, "D:\Company Files\eDealer\WATERLOOHONDA_vehicle_inventory.csv"

'------- Section 5 Start -------'
'Update UCD Database for intranet
oConnCSV.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sPath & ";Extended Properties=""text;HDR=NO;FMT=CSVDelimited"""
sSQL = "SELECT * FROM " & sFile & " WHERE [VehicleType]='U' ORDER BY [StockNumber];"
oRSCSV.Open sSQL, oConnCSV, adOpenStatic, adLockOptimistic, adCmdText

oConnMDB.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sPath  & "usedcars.mdb"
sSQL = "SELECT * FROM tblUsedcars"
oRSMDB.Open sSQL, oConnMDB, adOpenStatic, adLockOptimistic

'Deactivate all records
Do while not oRSMDB.EOF
	oRSMDB("Active") = False
	oRSMDB.Update
	oRSMDB.MoveNext
Loop

'Loop CSV file
Do while not oRSCSV.EOF
	bFound = False
	oRSMDB.movefirst
	Do while not oRSMDB.EOF 'Does the stock number exist in the MDB?
		If oRSMDB("StockNumber") = oRSCSV("StockNumber") Then
			bFound = True
			Exit Do
		Else
			oRSMDB.MoveNext
		End If
	Loop
	If bFound Then 'Update existing record
		tmpVIN = oRSCSV("VIN")
		sRONumber = objRODictionary.Item(tmpVIN) ' From txt file dictionary
		If sRONumber <> "" Then 
			iDaysInService = mid(sRONumber,instr(sRONumber,"|")+1)
			sRONumber = left(sRONumber,instr(sRONumber,"|")-1)
			iDaysInService = datediff("d",iDaysInService,date())+1
		End If
		
		oRSMDB("Year") = oRSCSV("Year")
		oRSMDB("MakeModel") = oRSCSV("Make") & " " & oRSCSV("ModelLong")
		oRSMDB("Kilometres") = oRSCSV("OdometerAtPurchase")
		oRSMDB("Colour") = oRSCSV("ColorLong")
		oRSMDB("Price") = oRSCSV("SalesPrice")
		lCost = cdbl(oRSCSV("Cost"))
		oRSMDB("Balance") = lCost
		oRSMDB("DoorType") = oRSCSV("BodyStyle")
		oRSMDB("Transmission") = oRSCSV("Transmission")
		oRSMDB("Cyl") = oRSCSV("Cylinders")
		oRSMDB("CarProofURL") = "https://webservice.carproof.com/memberreports/lookup.aspx?id=g2p0V1c4H1qytu9QVlgRaQ==&vin=" & oRSCSV("VIN")
		oRSMDB("Certified") = AddCert(oRSCSV("VIN")) ' From XML File
		oRSMDB("AdDescription") = AddDescription(oRSCSV("VIN")) ' From XML File
		oRSMDB("AdOptions") = AddOptions(oRSCSV("VIN")) ' From XML File
		oRSMDB("AdID") = AddID(oRSCSV("VIN")) ' From XML File
		If oRSMDB("RONumber") = "" then
			oRSMDB("RONumber") = sRONumber
			If DaysInService <> "" Then oRSMDB("DaysInService") = DaysInService & "+"
		Elseif sRONumber = "" Then
			ROClosedFlag = True
			If oRSMDB("DaysInService") <> "" Then oRSMDB("DaysInService") = Replace(oRSMDB("DaysInService"),"+","")
		Elseif oRSMDB("RONumber") <> sRONumber Then
			ROClosedFlag = False
			oRSMDB("RONumber") = oRSMDB("RONumber")
			'oRSMDB("RONumber") = sRONumber
			'oRSMDB("RONumber") = oRSMDB("RONumber") & "-" & sRONumber
			oRSMDB("DaysInService") = iDaysInService & "+"
		Else
			ROClosedFlag = False
			oRSMDB("RONumber") = sRONumber
			oRSMDB("DaysInService") = iDaysInService & "+"
		End if
		oRSMDB("Active") = True
		oRSMDB.Update		
	Else 'Add new record
		oRSMDB.AddNew
	  oRSMDB("StockNumber") = oRSCSV("StockNumber")
		oRSMDB("ENTRY") =mid(oRSCSV("InDate"),instr(oRSCSV("InDate"),"/")+1,2) & "/" & Replace(left(oRSCSV("InDate"),2), "/", "") & "/" & right(oRSCSV("InDate"),4)
		oRSMDB("Serial") = oRSCSV("VIN")
		oRSMDB("Year") = oRSCSV("Year")
		oRSMDB("MakeModel") = oRSCSV("Make") & " " & oRSCSV("ModelLong")
		oRSMDB("Kilometres") = oRSCSV("OdometerAtPurchase")
		oRSMDB("Colour") = oRSCSV("ColorLong")
		oRSMDB("Price") = oRSCSV("SalesPrice")
		lCost = cdbl(oRSCSV("Cost"))
		oRSMDB("Balance") = lCost
		oRSMDB("DoorType") = oRSCSV("BodyStyle")
		oRSMDB("Transmission") = oRSCSV("Transmission")
		oRSMDB("Cyl") = oRSCSV("Cylinders")
		oRSMDB("CarProofURL") = "https://webservice.carproof.com/memberreports/lookup.aspx?id=g2p0V1c4H1qytu9QVlgRaQ==&vin=" & oRSCSV("VIN")
		oRSMDB("Certified") = AddCert(oRSCSV("VIN")) ' From XML File
		oRSMDB("AdDescription") = AddDescription(oRSCSV("VIN")) ' From XML File
		oRSMDB("AdOptions") = AddOptions(oRSCSV("VIN")) ' From XML File
		oRSMDB("AdID") = AddID(oRSCSV("VIN")) ' From XML File
		oRSMDB("Active") = True
		oRSMDB("UserUpdated") = "DMS.Process"
		oRSMDB("DateAdded") = Date()
		oRSMDB.Update
	End if
	oRSCSV.MoveNext
Loop
oRSCSV.Close
oRSMDB.Close

sSQL = "SELECT * FROM tblProcessStatus"
oRSMDB.Open sSQL, oConnMDB, adOpenStatic, adLockOptimistic
oRSMDB("Status") = "Successful"
oRSMDB("DateCompleted") = Now()
oRSMDB.Update
oRSMDB.Close

oConnMDB.Close
oConnCSV.Close

Set oRSMDB = Nothing
Set oConnMDB = Nothing
Set oRSCSV = Nothing
Set oConnCSV = Nothing
Set oFSO = Nothing
Set oShell = Nothing
Set objDictionary = Nothing
End Sub

'******* Sub to update DB from XML file *************
Function AddCert(sVin)
Dim oXMLDoc
	Set oXMLDoc = CreateObject("Microsoft.XMLDOM")
	oXMLDoc.async = False
	oXMLDoc.load("D:\Company Files\eDealer\edealer.xml")
	Set NodeList = oXMLDoc.documentElement.selectNodes("//Vehicle")
	For Each Node In NodeList
		If Node.selectSingleNode("VIN").Text = sVin Then
			If Node.selectSingleNode("Certified").Text = "Y" Then
			 	AddCert = "Certified"
			 	Exit Function
			Else
			 	AddCert = ""
			 	Exit Function
			End If
    End If
  Next
End function

'******* Sub to update DB from XML file *************
Function AddDescription(sVin)
Dim oXMLDoc
	Set oXMLDoc = CreateObject("Microsoft.XMLDOM")
	oXMLDoc.async = False
	oXMLDoc.load("D:\Company Files\eDealer\edealer.xml")
	Set NodeList = oXMLDoc.documentElement.selectNodes("//Vehicle")
	For Each Node In NodeList
		If Node.selectSingleNode("VIN").Text = sVin Then
			AddDescription = Node.selectSingleNode("Description").Text
			Exit Function
    End If
  Next
End function

'******* Sub to update DB from XML file *************
Function AddOptions(sVin)
Dim oXMLDoc, i, SOptions
	Set oXMLDoc = CreateObject("Microsoft.XMLDOM")
	oXMLDoc.async = False
	oXMLDoc.load("D:\Company Files\eDealer\edealer.xml")
	Set NodeList = oXMLDoc.documentElement.selectNodes("//Vehicle")
	For Each Node In NodeList
		If Node.selectSingleNode("VIN").Text = sVin Then
			i = 0
	    Do while i < Node.selectSingleNode("Options").childNodes.length
	    	if i = 0 then
	    		SOptions = Node.selectSingleNode("Options").childNodes(i).childNodes(0).text
	    	else
	        SOptions = SOptions & ", " & Node.selectSingleNode("Options").childNodes(i).childNodes(0).text
	    	end if
				i = i + 1
	    Loop
			AddOptions = SOptions
			Exit Function
    End If
  Next
End function

'******* Sub to update DB from XML file *************
Function AddID(sVin)
Dim oXMLDoc
	Set oXMLDoc = CreateObject("Microsoft.XMLDOM")
	oXMLDoc.async = False
	oXMLDoc.load("D:\Company Files\eDealer\edealer.xml")
	Set NodeList = oXMLDoc.documentElement.selectNodes("//Vehicle")
	For Each Node In NodeList
		If Node.selectSingleNode("VIN").Text = sVin Then
			AddID = Node.selectSingleNode("Vehicle_ID").Text
			Exit Function
    End If
  Next
End function

Open in new window

You are missing what I am saying.

Your sync.vbs is not needed at all. Like I mentioned earlier you are adding complexity with your sync.vbs. All it is doing or trying to do is run another vbs. Windows natively runs vbs files without any need to run wscript.exe.

Since you said that "run_ucd_update.vbs" ran without any errors, but there was nothing that was synced. The same thing would happen if we figured out what is wrong with your sync.vbs.

You can accomplish this like this, but I think it is redundant.

Dim oShell 
Set oShell = Wscript.CreateObject("WScript.Shell")
oShell.Run "D:\Company Files\DMSDataExtract\run_ucd_update.vbs"   
Set oShell = Nothing

Open in new window

Or

"D:\Company Files\DMSDataExtract\run_ucd_update.vbs"

Open in new window

This will do the exact same thing. Does that make sense?

Wscript.exe are not needed to execute a VBS. This is the default application. Just as you do not need to use winword.exe to open a .docx.

oshell.run is good to exe other exe's like notepad.exe or cmd.exe /c ping ip. There is no need to run a VBS within a VBS.

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

Hmm it could be possible let me find out with staff on Monday who could it be with csv open, I know MS office files are not multiple user mode, thank you very much for your kind help I will update you on Monday.

ASKER

something else popped up in mind, It'd most likely be the IIS site that is locking it, as it reads out values and displays on the used inventory form, does this make sense?

That would depend on how it is reading the file I suppose. I would expect that to be a "read only" access, but if it wasn't then it could cause a problem.

»bp

ASKER

I am sorry I do not know how to check on that can you please help me with this as well.

I wouldn't have any idea what the website is doing to the CSV, you will have to talk to the folks that manage that website.

»bp

ASKER

Thanks Bill this helped me further investigate the issue.