Comparing Data From 2 different databases 1

[humbletechie]

Hi

I am trying create a page that shows all orders that hit the first database but never made it into the second database. The problem I am having is both databases are on different servers. The order hits the first database and then processes run that puts it into the second database. I want to determine which orders never made it into the second database. Can anyone provide some help with this?

Thanks!!

 

[ToddWW]

Sure, can you successfully set an ADODB connection to the database on the other server ??

ToddWW

 

[humbletechie]

Yes, I actually have a page set up that connects to both databases and will display order detail from an order number that posts to that page from a form. I just have no idea what SQL statement or VB code I need to use that gives me all the orders that hit the 1st database but not the second one.

Thanks in advance.

 

[ToddWW]

OK, that's great. No problem. Give me the field names of the data that you want displayed on the page. Also give me the unique ID field name (probably the order number) that we'll be using for comparison. I assume there is only one record for each order number right ??

Thanks..

ToddWW

 

[humbletechie]

Fields to display are: OrdNo, ItemNo, Qty, HoldCode, SalesRep, Status. The unique ID is the OrdNo field.

Thanks so much for you response.

 

[ToddWW]

No problem, I'm almost done with the sample code..

ToddWW

 

[ToddWW]

Here, try this. You supply the Connection strings and table names.

<%@ Language=VBScript %>
<% Option Explicit %>
<% Response.Buffer = True %>
<%
dim strProvider1
dim strProvider2
dim objRst1
dim objRst2
dim strQuery

strProvider1 =

'Conn string or DSN to db #1

strProvider2 =

'Conn string or DSN to db #2

Set objRst1 = Server.CreateObject("ADODB.recordset")
objRst1.CursorLocation = 3
objRst1.CursorType = 3
strQuery = "SELECT OrdNo FROM

tablename

"
objRst1.Open strQuery, strProvider1
set objRst1.ActiveConnection = Nothing

Set objRst2 = Server.CreateObject("ADODB.recordset")
objRst2.CursorLocation = 3
objRst2.CursorType = 3
strQuery = "SELECT OrdNo FROM

tablename

"
objRst2.Open strQuery, strProvider2
set objRst2.ActiveConnection = Nothing

WHILE NOT objRst1.EOF
  objRst2.Find "OrdNo = " & objRst1("OrdNo")
  IF objRst2.EOF THEN
    Response.Write objRst1(&quot;OrdNo&quot;) & &quot;<br>&quot;
  END IF
WEND
objRst1.Close
objRst2.Close
set objRst1 = Nothing
set objRst2 = Nothing
%>

Now this assumes that the OrdNo field is a number type. If it is a text type, change the Find method to this.

objRst2.Find "OrdNo = '" & objRst1("OrdNo") & "'"

Let me know how it works out.

ToddWW

 

[humbletechie]

Thank you so much for you response. It seems to be connecting to both database okay but I keep hitting the maximum time allowed to execute a script. It's probably because the second database table is huge. I tried adding criteria in my select statement so it would only pull from the past 3 days but it's still timing out. Do you have any suggestions on how to make it run faster?

Thanks!!

 

[ToddWW]

That's my fault.. :-D

Here, make this change..

WHILE NOT objRst1.EOF
objRst2.Find "OrdNo = " & objRst1("OrdNo&quot
IF objRst2.EOF THEN
Response.Write objRst1(&quot;OrdNo&quot & &quot;<br>&quot;
END IF
objRst1.MoveNext
WEND

My sincere apologies about that. That was a never ending loop without moving the recordset pointer one position. ojbRst1 never made it past the first record. I bet that gave your servers a workout !!

ToddWW

 

[humbletechie]

Thank You so much!!! This works like a charm. Thanks for spending the time on the detailed code.