QA and DTS results are different - aargh!

Question

Post reply

QA and DTS results are different - aargh!

gonefishin

Grasshopper

Points: 19
More actions
August 21, 2007 at 7:58 pm

#118218

Hi,
I have a MS Access ADP over a SQL Server 2000 install that is giving me grief. I have a DTS that has been converted to VBA and it is giving me different results to the same query run in QA.
SELECT dbo.REPORTING.CustNum, dbo.REPORTING.InvDate, dbo.CUST_CURRENT.CoLastName,
dbo.CUST_CURRENT.FirstName, dbo.CUST_CURRENT.Addr1Line1,
dbo.CUST_CURRENT.Addr1Line2, dbo.CUST_CURRENT.Addr1Line3,
dbo.CUST_CURRENT.Addr1Line4, dbo.CUST_CURRENT.Addr1City,
dbo.CUST_CURRENT.Addr1State, dbo.CUST_CURRENT.Addr1Postcode,
dbo.CUST_CURRENT.Addr1Country, dbo.CUST_CURRENT.Addr1Email,
dbo.REPORTING.FullItemNumber AS MemberType, dbo.REPORTING.CustomList1,
dbo.REPORTING.CustomList2, dbo.CUST_CURRENT.CustomList3,
dbo.CUST_CURRENT.CustomField1, dbo.CUST_CURRENT.CustomField2, dbo.CUST_CURRENT.CustomField3
FROM dbo.REPORTING LEFT OUTER JOIN
dbo.CUST_CURRENT ON dbo.REPORTING.CustNum = dbo.CUST_CURRENT.CardID
WHERE (dbo.REPORTING.CustomList2 = N'NSW Branch') AND (dbo.REPORTING.InvDate BETWEEN '07/01/2006' AND '07/31/2007')
ORDER BY dbo.REPORTING.InvDate, dbo.REPORTING.CustomList1
Run through MS ACCESS VBA I get 1078 results - when run through QA I get 1125 results.
Can anyone please give me some advice on isolating/fixing this problem?
Thanks
Kindest Regards,

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Michael Meierruth SSChampion Points: 10051 More actions · Answer 1

Get a third opinion by running the plain-vanilla VBA script below:

1. modify the connect string line

2. save it as test.vbs

3. start a Command shell, navigate to the folder and type: cscript test.vbs

By the way, those dates should be coded yyyymmdd instead of mm/dd/yyyy - just to be sure.

Option Explicit

Dim strConn

Dim objConn

Dim s

dim objRs

Dim k

strConn = "PROVIDER=SQLOLEDB;DATA SOURCE=server-name-or-ip;DATABASE=database-name;USER ID=user;PASSWORD=password"

Set objConn = CreateObject("ADODB.Connection")

objConn.Open strConn

s = s & " SELECT dbo.REPORTING.CustNum, dbo.REPORTING.InvDate, dbo.CUST_CURRENT.CoLastName,"

s = s & " dbo.CUST_CURRENT.FirstName, dbo.CUST_CURRENT.Addr1Line1,"

s = s & " dbo.CUST_CURRENT.Addr1Line2, dbo.CUST_CURRENT.Addr1Line3,"

s = s & " dbo.CUST_CURRENT.Addr1Line4, dbo.CUST_CURRENT.Addr1City,"

s = s & " dbo.CUST_CURRENT.Addr1State, dbo.CUST_CURRENT.Addr1Postcode,"

s = s & " dbo.CUST_CURRENT.Addr1Country, dbo.CUST_CURRENT.Addr1Email,"

s = s & " dbo.REPORTING.FullItemNumber AS MemberType, dbo.REPORTING.CustomList1,"

s = s & " dbo.REPORTING.CustomList2, dbo.CUST_CURRENT.CustomList3,"

s = s & " dbo.CUST_CURRENT.CustomField1, dbo.CUST_CURRENT.CustomField2, dbo.CUST_CURRENT.CustomField3"

s = s & " FROM dbo.REPORTING LEFT OUTER JOIN"

s = s & " dbo.CUST_CURRENT ON dbo.REPORTING.CustNum = dbo.CUST_CURRENT.CardID"

s = s & " WHERE (dbo.REPORTING.CustomList2 = N'NSW Branch') AND (dbo.REPORTING.InvDate BETWEEN '07/01/2006' AND '07/31/2007')"

s = s & " ORDER BY dbo.REPORTING.InvDate, dbo.REPORTING.CustomList1"

Set objRs = CreateObject("ADODB.Recordset")

objRs.Open s, objConn, 0, 1

Do While Not objRs.Eof

k = k + 1

objRs.MoveNext

Loop

objRs.Close

WScript.Echo CStr(k) & " rows"

gonefishin Grasshopper Points: 19 More actions · Answer 2

Thanks Michael,

I'll give this a try and report back

Cheers

Kindest Regards,

Antares686 SSC Guru Points: 125444 More actions · Answer 3

Unfortunately I don't have my QA in front of me but when you connect via QA there are certain options such as SET ANSI_NULLS already set on. VBA connects in plain vanilla with server defaults set on only. Will usually account for your differences when you figure out which option it is that you need.

gonefishin Grasshopper Points: 19 More actions · Answer 4

Hi Antares686,

thanks for your reply. I ran Michael's plain vanilla VBA script and got 1125 rows - same as the QA result.

Does this suggest that the QA settings and the vanilla VBA settings are the same?

If that is the case then it is my DTS -> VBA that has introduced the discrepancies?

Thanks to both of you for your prompt replies.

Still quite lost

Cheers

Kindest Regards,

Antares686 SSC Guru Points: 125444 More actions · Answer 5

VBScript is not exactly the same as VBA but for the most part instantiating the SQL Drive in plain form should give the same results. Try Micheal's code by creating a blank form in Access with a single button with the code in it to execute it. Just remember you will have to type your DIM objects and use MsgBox to display the results instead of echo.

That said if you get the correct result count I would use Profiler to capture the acutally query delivered by the Access item failing to provide the sam count to confirm exactly what query string is being passed as well as options possibly set during the execution.