How to insert data into database

[pandu101]

Hi all, started collecting data from an XML file that is created by our backup software. Was able to get a lot of that done thanks to the good folks here.

Now I am trying to dump that data into a SQL 2005 db. I am doing this by calling a function named bkupJobTableInsert, But I get errors like "multiple-step OLE DB operation generated errors" on line 101, which is -->
objRS("start_date") = start_time

Would appreciate any tips on dumping data into a SQL db. Thanks.

Below is the code

Dim server, filepath, myErr, x, y, bytesBackedup
Dim fso,fold,fil,XMLDoc 

Set fso = CreateObject("Scripting.FileSystemObject") 
Set fold = fso.GetFolder("C:\Program Files\Symantec\Backup Exec\Data") 

For each fil in fold.files 
	if (DateDiff("d",Now,fil.DateCreated) < 0) And (right((fil.Name),3) = "xml") And (UCase(Left((fil.Name),3)) = "BEX") And (fil.Size > 5000) Then
	'checks if the file is older than today, that it is an XML file, that it starts with BEX and has a size greater than 5000 bytes	
    Set xmlDoc = CreateObject("Msxml2.DOMDocument") 
	    fil = fold & "\" & fil.name 
	    xmlDoc.load(fil) 
		'loads the first xml file
		
		if (xmlDoc.parseError.errorCode <> 0) Then
			myErr = xmlDoc.parseError.reason
			Wscript.Echo "You have error:--> " & myErr
			' echoes errors if there was a problem reading the xml file
		else       
	         Set ElemList = xmlDoc.getElementsByTagName("name") 
	         job_name = ElemList.item(0).text
			 'loads the backup job_name tag from the XML file
			 
			 
        	 if (job_name = "Job name: LTO Archive Rotation A") Or (job_name = "Job name: LTO Archive Rotation B") Then
					'if the xml file contains a tag whose value is "Job name: LTO Archive Rotation A" or "Job name: LTO Archive Rotation B" then proceed to next step
					 Wscript.Echo job_name
					 		
					 'Set ElemList = xmlDoc.getElementsByTagName("set_resource_name")
					 'x = ElemList.length
					 'For i = 0 to x-1
						'backup_selection = ElemList.item(i).text
						'Wscript.Echo "Backup Target: " & backup_selection
					 'Next
					 'Above code is used to print out the various backup sources
					
					 Set ElemList = xmlDoc.getElementsByTagName("start_time") 
					 start_time = ElemList.item(0).text
					 Wscript.Echo start_time 
					 'print the start_time of job on screen
				  
					 Set ElemList = xmlDoc.getElementsByTagName("end_time")
					 x = ElemList.Length-1
					 'find the size of the array so that we can reference the last item of the array which is stored as X. This is done
					 'because each XML file contains multiple end_time tags. We r only interested in the last one, hence we reference
					 'the last end_time in the array
					 end_time = ElemList.item(x).Text
					 Wscript.Echo end_time 
					 'print the end_time of job on screen

					 Set ElemList = xmlDoc.getElementsByTagName("new_processed_bytes")				
					 'generates an array of all instances of "new_processed_bytes" 
					 y = ElemList.length-1
					 'figures out the length of the array
					 bytesBackedup = 0
					 For i=0 to y
					 	 bytesBackedup = Split(ElemList.item(i).text," ")(1) + bytesBackedup
						 'splits each instance of "new_proceesed_bytes" by spaces and then saves it into an array. Then grabs the 2nd value of the array
					 Next
					 databackedup = FormatNumber(((bytesBackedup/1024)/1024)/1024)
					 Wscript.Echo "Data Amount: " & databackedup & " GB"
					 'print the amount of data backed up
					 
					 Set ElemList = xmlDoc.getElementsByTagName("engine_completion_status") 
					 engine_completion_status = Replace(ElemList.item(0).Text,"Job completion status:","") 
					 Wscript.Echo "Job Status ---> " & engine_completion_status 
					 'print the job completion status
					 
					 Call bkupJobTableInsert(job_name, start_time, end_time, databackedup, engine_completion_status)
					 
					 Wscript.Echo 	
					 Wscript.Echo 	
					 Wscript.Echo 	
					 'print empty lines for formating purposes
			 End If
		End If
		
	End if
Next 


Function bkupJobTableInsert(job_name, start_time, end_time, databackedup, engine_completion_status)
	Set objConn = CreateObject("ADODB.Connection")
	'create a database connection object called objConn that will use ADODB drivers
	objConn.Open "Driver={SQL Server};" & _ 
		"Server=XXXXX;" & _ 
		"Database=SysAdminDB;" & _
		"user id=xxxxx;" & _
		"password=xxxxx;"
	'Open the database connection by providing db name, srv name, user, pass, etc. 
	
	Set objRS = CreateObject("ADODB.Recordset")
	'creates a recordset object. A recordset is a collection of objects retrieved from a database. 

	objRS.open "tbl_Backup_Jobs",objConn,2,2
	'opens the "tbl_Backup_Jobs" table
	
	objRS.AddNew
		objRS("job_name") = job_name
		objRS("server_name") = NULL
		objRS("start_date") = start_time
		objRS("end_date") = end_time
		objRS("data_amount") = databackedup
		objRS("job_status") = engine_completion_status
	objRS.Update
	
	objRS.Close
	objConn.Close
End Function

 

[PHV]

I'd try this:
objRS("start_date") = CDate(start_time)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886