I am not aware of such example, but the task is fairly easy if you use VBScript job in 24x7. Here is the schenario:
1. Get List of tables from one database (for example from SQL server)
2. Open the log file for writing
3. Loop though the list of tables and for each table run "select count(*)"
4. Write results to the log Here is the script that can be used for the task: Sub Main()
Dim conn1, conn2, count1, count2, rs_table, rs_count, sql, fso, log_file ' Open connections
Set conn1 = CreateObject("ADODB.Connection")
conn1.open "SQL Server ODBCDSNName","username","password"
Set conn2 = CreateObject("ADODB.Connection")
conn2.open "Informix ODBCDSNName","username","password" ' Open log file for writing
Set fso = CreateObject("Scripting.FileSystemObject")
Set log_file = fso.CreateTextFile("c:\log.txt", True) ' Get table list
sql = "SELECT table_name = (U.name + '.' + O.name) FROM dbo.sysobjects O, dbo.sysusers U WHERE U.uid = O.uid and O.type = 'U'"
Set rs_table = CreateObject("ADODB.Recordset")
rs_table.Open sql, conn1, 3, 3 ' Loop though table list and run count(*) on each table
Set rs_count = CreateObject("ADODB.Recordset") do until rs_table.eof table_name = rs_table("table_name") sql = "SELECT count(*) total_rows FROM " + table_name rs_count.Open sql, conn1, 3, 3 count1 = rs_count("total_rows") rs_count.Close rs_count.Open sql, conn2, 3, 3 count2 = rs_count("total_rows") rs_count.Close ' fix nulls (if any) and write to the log If isNull(count1) Then count1 = 0 If isNull(count2) Then count2 = 0 log_file.WriteLine(count1 & " -- " & count2) ' advance to the next table rs_table.movenext
loop rs_table.Close ' close log file
log_file.Close ' close connections, free resources
conn1.close
set conn1 = Nothing
conn2.close
set conn2 = Nothing End Sub Feel free to customize this script as needed : I'd like to compare the number of rows of all tables from two different
: databases via ODBC. The databases are hosted on different database systems
: (Informix = source / SQl-Server 2000 = target). The result should be a log
: file containing all differences between the tables. Is there already an
: example which shows the solution of my problem.
|
