• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2607
  • Last Modified:

VBScript compare differences in two record sets?

Experts,

I have a VBScript that opens two separate SQL databases and creates record sets from the data it reads.  *Code below.

Would anybody happen to know where I could find information on how to make the script compare the two record sets?

Right now, I'm closing the record sets after execution, just to keep the script tidy - but I know that they'll both probably have to be open at the same time in order to accomplish what I'm trying to do - which isn't a problem at all.

I'm needing to compare both tables, and add records / delete records based on information received from the MasterSQLServer, if that makes sense.
Const adOpenStatic = 3
            Const adLockOptimistic = 3

        objLogFile.WriteLine "Connecting to Master SQL Server Instance:  " & MasterSQLServer

                Set objMasterConnection = CreateObject("ADODB.Connection")
                Set objMasterRecordSet = CreateObject("ADODB.Recordset")

                objMasterConnection.Open _
                    "Provider=SQLOLEDB;Data Source=" & MasterSQLServer & ";" & _
                    "Trusted_Connection=Yes;Initial Catalog=" & SQLDatabase & ";"

        objLogFile.WriteLine "Connecting to Child SQL Server Instance:  " & ChildSQLServer

                Set objChildConnection = CreateObject("ADODB.Connection")
                Set objChildRecordSet = CreateObject("ADODB.Recordset")

                objChildConnection.Open _
                    "Provider=SQLOLEDB;Data Source=" & ChildSQLServer & ";" & _
                    "Trusted_Connection=Yes;Initial Catalog=" & SQLDatabase & ";"







            Set MasterRSAvailableRoles = objMasterConnection.Execute(SQLCommandSelectAvailableRoles)

                objLogFile.WriteLine "            Raw data received:"

                Do Until MasterRSAvailableRoles.EOF

                        objLogFile.WriteLine "                " & MasterRSAvailableRoles.Fields("Id") & " " & MasterRSAvailableRoles.Fields("PrinterRole") & " " & MasterRSAvailableRoles.Fields("PrinterRoleNomen")

                    MasterRSAvailableRoles.MoveNext

                Loop

             MasterRSAvailableRoles.Close







            Set ChildRSAvailableRoles = objChildConnection.Execute(SQLCommandSelectAvailableRoles)

                objLogFile.WriteLine "            Raw data received:"

                Do Until ChildRSAvailableRoles.EOF

                        objLogFile.WriteLine "                " & ChildRSAvailableRoles.Fields("Id") & " " & ChildRSAvailableRoles.Fields("PrinterRole") & " " & ChildRSAvailableRoles.Fields("PrinterRoleNomen")

                    ChildRSAvailableRoles.MoveNext

                Loop

             ChildRSAvailableRoles.Close

Open in new window

0
usslindstrom
Asked:
usslindstrom
  • 3
  • 2
1 Solution
 
DSmithVzCommented:
Are you looking to compare the individual values in each row/field between the databases (if a row in database A is missing from B then you can execute a command to add it to B) match or are you looking to compare that overall the two databases match?
0
 
usslindstromAuthor Commented:
I'm needing to do the first scenario.

This is in an environment that I'm unable to install the SQL replication components, but need to have a way to make all the databases have the same content.

I have a "master" SQL server that will be the place where all cahnges are made.  Then, once each night, I'm planning on running this particular script to update the "child" SQL servers.

So, aside from the long story, the answer to that question is:  I need to see which records are in the first table that don't match the 2nd.  I'll do the same, with reverse logic, to handle the records that need to be deleted as well.  These tables will ~at most~ get updated one or two times every month.

-----

As I've been playing with the script throughout the day today (as time's permitted) - I've updated it to convert the RecordSets to arrays, as I'd imagine it's much easier to work with array objects.  But I'm still a bit confused on how I would compare the two.
0
 
DSmithVzCommented:
Here is what I have off the top of my head. I switched the recordset to a two dim array like you mentioned for the comparison. I do not have a database to test this, but it did compile (for what it's worth). This will check if each ID in the Master table is in the Child table. If not it will output to your log file (I assume you just didn't include the code to link/create the log file). It then does the reverse, checking for Child IDs that are not found in the master. Let me know how it goes (especially any issues encountered).

If you have many thousands of records this could take a bit of time. If this is the case, and you can have the SQL query return the values ordered by the ID the search comparison can be done much quicker in a bit different way:
objLogFile.WriteLine "Connecting to Master SQL Server Instance:  " & MasterSQLServer

''Get the Master table data
Set objMasterConnection = CreateObject("ADODB.Connection")
objMasterConnection.Open _
	"Provider=SQLOLEDB;Data Source=" & MasterSQLServer & ";" & _
	"Trusted_Connection=Yes;Initial Catalog=" & SQLDatabase & ";"
Set MasterRSAvailableRoles = objMasterConnection.Execute(SQLCommandSelectAvailableRoles)
aryMaster = MasterRSAvailableRoles.GetRows()
MasterRSAvailableRoles.Close

objLogFile.WriteLine "Connecting to Child SQL Server Instance:  " & ChildSQLServer

''GEt the Child table data
Set objChildConnection = CreateObject("ADODB.Connection")
objChildConnection.Open _
	"Provider=SQLOLEDB;Data Source=" & ChildSQLServer & ";" & _
	"Trusted_Connection=Yes;Initial Catalog=" & SQLDatabase & ";"
Set ChildRSAvailableRoles = objChildConnection.Execute(SQLCommandSelectAvailableRoles)
aryChild = ChildRSAvailableRoles.GetRows()
ChildRSAvailableRoles.Close

objLogFile.WriteLine "            Raw data received:"

''Destroy objects
Set ChildRSAvailableRoles = Nothing
Set MasterRSAvailableRoles = Nothing
Set objChildConnection = Nothing
Set objMasterConnection = Nothing

''Loop through both recordsets and output any rows found in the Master table that are not found in the child table
objLogFile.WriteBlankLines 1
objLogFile.WriteLine "Rows Found in Master, but missing in child:"
objLogFile.WriteLine "ID		PrinterRole		PrinterRoleNomen"
For i = 0 to UBound(aryMaster, 2)

	blnFound = False
	For j = 0 to UBound(aryChild, 2)
		''Check if the next Child Row ID = The current Master Row ID
		If aryMaster(0, i) = aryChild(0, j) Then
			blnFound = True
			Exit For
		End If
	Next
	
	''If the id from Master was not found in the Child
	If Not blnFound Then
		objLogFile.WriteLine aryMaster(0, i) & "	" & aryMaster(1, i) & "		" & aryMaster(2, i)
		''Add any additional code to execute if the match was not found (like building or executing SQL to update the child DB)
	End If
Next

''Reverse the check. Look through the Child table to find rows missing in the Master table
objLogFile.WriteBlankLines 1
objLogFile.WriteLine "Rows Found in Child, but missing in master:"
objLogFile.WriteLine "ID		PrinterRole		PrinterRoleNomen"
For i = 0 to UBound(aryChild, 2)
	
	blnFound = False
	For j = 0 to UBound(aryMaster, 2)
		''Check if the next Child Row ID = The current Master Row ID
		If aryChild(0, i) = aryMaster(0, j) Then
			blnFound = True
			Exit For
		End If
	Next
	
	''If the id from Master was not found in the Child
	If Not blnFound Then
		objLogFile.WriteLine aryChild(0, i) & "		" & aryChild(1, i) & "		" & aryChild(2, i)
		''Add any additional code to execute if the match was not found (like building or executing SQL to update the child DB)
	End If
Next

Open in new window

0
 
usslindstromAuthor Commented:
O.o    WOW.  :)  You just coded my entire project for me.  :)

Instead of just copying pasting, please give me a minute to read what you're trying to show me, so I can fully understand it.

Much appreciated on everything!
0
 
usslindstromAuthor Commented:
Absolutely perfect!

This solution works great!  Thank you very much for the information / explinations!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now