ADO Recordset returns different result from query analyzer?

[bluecjh]

a simple query:

SELECT COUNT(Field1)

FROM #Temporarytable1

WHERE Field1 = 1

Not: Field1 is a BIT datatype

I am using sql server 6.5 and Excel 97

the above returns a valid (correct) number in the QA and
zero(0) in my ADO recordset. If I remove the WHERE
clause the results agree? (i.e. the QA and ADO/Excel
get the same correct figure) but with the WHERE clause Excel/ADO fails, why might this be?

Thanks.

 

[SqueakinSweep]

Try

SELECT COUNT(Field1) AS MyCount
FROM #Temporarytable1
WHERE Field1 = 1

Sweep
...if it works dont mess with it

 

[bluecjh]

Sqeakinsweep, this did not improve matters?

 

[nigelrivett]

I would have said that it was a null problem except that you will still get the warning if you remove the where clause (and it's 6.5 which doesn't allow nulls in a bit).

Guess it doesn't like the 1. How are you coding the statement and populating the temp table?

Try where Field1 <> 0
Field1 <> 1 too.
it might give a clue.


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[bluecjh]

NigelRivett,

I looked at these issues. If I put Field1 = 0
I get 1821 as my count in QA and 1830 in my recordset.

If I put Field1 = 1 I get QA returns 9, recordset returns 0.

In other words through ADO every value for Field1 is
perceived as 0?

In answer to your question the temporary table is populated
via a cursor which is scrutinised for various column values
and some BIT type fields are flagged accordingly.

I then report on the count of one of the fields

 

[bluecjh]

Seemingly the Error did not lie with SQL Server,
the correct results are returned with an OLEDB
connection but not with ADO, any ideas why this might be?

 

[gavinhuet]

Just a thought, I note that you are doing a select from a temporary table and want this result returned. Because of the temporary table I assume that there are other SQL statements which are executed beforehand.
If this is the case ADO is probably assuming multiple recordsets are being returned. To work around this try the following:

SET NOCOUNT ON
... SQL Statements ...
SET NOCOUNT OFF

SELECT COUNT(Field1)
FROM #Temporarytable1
WHERE Field1 = 1

Regards
-- Gavin

 

[VogonPoet]

Try testing for -1 in ADO. Some programs use 1 for true and some use -1 for true.

 

[bluecjh]

Gavin,
tried your sugestion but VBA returns
'operation is not allowed when the object is closed'
i.e if the NO COUNT is OFF before I make the SELECT then
I can't open the recordset properly?

Interestingly, I have now re written the Stored Procedure to remove the use of a cursor and a temporary table. My results are in the last lines selected from a table, but the problem is unchanged?

I am still confused as to where the problem lies, SQL server 6.5 or ADO? As I said using SQLOLE the correct results have been derived in the spreadsheet but this is rather unsatisfatory, I am not familiar with the SQLOLE objects and I would either like to know why the ADO route fails or a solution? thanks so far,

 

[nigelrivett]

Well it returns the expected result from query analyser so the problem is obviously not with sql server.

Can you post the complete SP.
You can also log values (including the resultset) to a trace table from within the SP to see what is happenning.
It could be the environment it is working in from excel - ansii settings for instance.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[bluecjh]

IF EXISTS(select name from sysobjects where name='cro_enforcement')
DROP PROCEDURE cro_enforcement
GO




CREATE PROCEDURE cro_enforcement
@datefrom DATETIME, @dateto DATETIME, @bolCrystal BIT
AS

SET NOCOUNT ON
--chris hardy circa Sep 2003
--use reports

DELETE t_enforcement


DECLARE @bol1stfailure BIT,
@nulldate DATETIME,
@returnvalue INT,
@bolstopcount BIT,
@bolproceed BIT,
@bolreqrecord BIT,
@appliedfortargetdate DATETIME,
@crn VARCHAR(40),
@bolnoaction BIT,
@bol1stbreach BIT,
@eventid INT,
@iCurrentRowId INT,

@ucount INT,
@bolappoccurred BIT,
@bolnewsuper BIT,
@2nducontactdate DATETIME,

@v_bolappoccurred BIT,
@v_bolreqrecord BIT,
@v_bolnewsuper BIT,
@v_surname VARCHAR(20),
@v_forename VARCHAR(40),
@v_crn VARCHAR(7),
@v_teamname VARCHAR(30),
@v_supervisiontype VARCHAR(2),
@v_commencement DATETIME,
@v_eventid INT,
@v_termdate DATETIME,
@v_ordertype VARCHAR(45),
@v_apptkept VARCHAR(2),
@v_action VARCHAR(50),
@v_contactdate DATETIME,
@v_contacttype CHAR(2),
@v_officerid CHAR(7),
@v_offsurname VARCHAR(20),
@v_offforename VARCHAR(20),
@v_supervisioncount INT,
@v_breachcount INT,
@v_targetdate DATETIME,
@v_noactionoccurred INT



SELECT @bol1stfailure = 1,
@bolstopcount = 0,
@bolproceed = 1,
@bolappoccurred = 0,
@bolreqrecord = 0,
@ucount = 0,
@bolnewsuper = 0,
@nulldate = NULL,
@bolnoaction = 0,
@bol1stbreach = 1


INSERT INTO t_enforcement

SELECT @bolappoccurred,
@bolreqrecord,
@bolnewsuper,
c.surname,
c.forename,
c.crn,
t.teamname,
s.supervisiontype,
s.commencement,
s.eventid,
s.termdate,
o.type,
l.apptkept,
l.action,
l.contactdate,
l.contacttype,

p.personalno AS officerid,
p.surname AS offsurname,
p.forename AS offforename,
0,
0,
@nulldate,
0

FROM

client c,
event e,
supervision s,
orders o,
contactlog l,
team t,
po p

WHERE

c.dbnumber = e.dbnumber
AND c.urn = e.urn
AND e.dbnumber = s.dbnumber
AND e.urn = s.urn
AND e.supid = s.eventid
AND s.supervisiontype = o.code

AND s.dbnumber = l.dbnumber
AND s.eventid = l.eventid
AND s.urn = l.urn

AND s.teamcode = t.teamcode

AND p.personalno = s.suppopersonalno

AND c.dlf IS NULL --not deleted flag in client table
AND e.dlf IS NULL --not deleted flag in event table
AND s.supervisiontype ='01'

AND (s.additionalreq1 IS NULL OR s.additionalreq1 <> 'h')
AND s.transout IS NULL --not transferred out (of area)
AND s.commencement BETWEEN @datefrom AND @dateto
--and s.commencement between '01-sep-2002' and '01-sep-2003'
--and s.termdate is null -- not terminated

AND (l.apptkept = 'u' --unattended

OR (l.action LIKE '%summ%%appl%' AND l.action NOT LIKE '%VOID%')
OR l.action LIKE '%warrant applied%'
OR l.action LIKE '%breach%%action%%reque%'
OR l.action LIKE '%warrant req%'
-- OR l.action LIKE '%induction%'
OR l.action LIKE '%to contin%'
OR l.action LIKE '%otc%'
OR l.action LIKE '%br%%ex%' --breach exempt
OR (l.action LIKE '%withdrawn%'AND l.action not LIKE '%ins%'AND l.action not LIKE '%nvq%')) --'%breach withdrawn%'

AND
(SELECT COUNT(*)

FROM

client c1,
event e1,
supervision s1,
orders o1,
contactlog l1,
team t1,
po p1

WHERE

c1.dbnumber = e1.dbnumber
AND c1.urn = e1.urn
AND e1.dbnumber = s1.dbnumber
AND e1.urn = s1.urn
AND e1.supid = s1.eventid
AND s1.supervisiontype = o1.code

AND s1.teamcode = t1.teamcode

AND s1.dbnumber = l1.dbnumber
AND s1.eventid = l1.eventid
AND s1.urn = l1.urn

AND p1.personalno = s1.suppopersonalno

--AND c1.crn = c.crn
and s1.eventid = s.eventid
AND c1.dlf IS NULL --not deleted flag in client table
AND e1.dlf IS NULL --not deleted flag in event table
AND l1.apptkept = 'u' --unattended
AND s1.supervisiontype ='01'

AND (s.additionalreq1 IS NULL OR s.additionalreq1 <> 'h')
AND s1.commencement BETWEEN @datefrom AND @dateto
--and s1.commencement between '01-sep-2002' and '01-sep-2003'
AND s1.transout IS NULL)>1 --not transferred out (of area)

ORDER BY s.eventid, l.contactdate

------------------------------------------------------------------------------



DECLARE @iLoopControl INT,
@iNextRowId INT


SELECT @iLoopControl = 1,
@iNextRowId = Min(id) FROM t_Enforcement



SELECT @iCurrentRowId = id,
@v_bolappoccurred = cbolappoccurred,
@v_bolreqrecord = cbolreqrecord,
@v_bolnewsuper = cbolnewsuper,
@v_surname = csurname,
@v_forename = cforename,
@v_crn = ccrn,
@v_supervisiontype = csupervisiontype,
@v_commencement = ccommencement,
@v_eventid = ceventid,
@v_termdate = ctermdate,
@v_ordertype = cordertype,
@v_apptkept = capptkept,
@v_action = caction,
@v_contactdate = ccontactdate,
@v_contacttype = ccontacttype,
@v_officerid = cofficerid,
@v_offsurname = coffsurname,
@v_offforename = coffforename,
@v_supervisioncount = csupervisioncount,
@v_breachcount = cbreachcount,
@v_targetdate = ctargetdate,
@v_noactionoccurred = cnoactionoccurred

FROM t_enforcement

WHERE id = @iNextRowId

--print @v_surname



WHILE @iLoopControl = 1

--now iterate through the cursor to establish the target date (within working 10 days)for the referral to
--court after the 2nd 'u'. establish if they hit it or not and flag the record as required.


BEGIN

SELECT @bolreqrecord = 0
SELECT @bolappoccurred = 0
IF @v_eventid <> @eventid --have we moved on to a new crn?
BEGIN
IF @bolnoaction = 1 AND @eventid IS NOT NULL AND @bol1stfailure = 1 --no breach action has been taken
BEGIN
--SELECT @iCurrentRowId = @@IDENTITY --the previous eventid had no action taken,@@identity
--should give the previous id inserted. however
--have problems using @@indentity variable leaave this
--in could be used with sql 2000 as a function.
SELECT @iCurrentRowId = (SELECT MAX(id) FROM t_enforcement WHERE ceventid = @eventid)
UPDATE t_enforcement SET cbolreqrecord = 1, cnoactionoccurred = 1 WHERE IDENTITYCOL = @iCurrentRowId
END
SELECT @bol1stfailure = 1,
@bol1stbreach = 1,
@bolnoaction = 0,
@appliedfortargetdate = GETDATE(), -- set to a neutral date
@eventid = @v_eventid,
@bolproceed = 1,
@bolstopcount = 0,
@ucount = 0,
@2nducontactdate = NULL
END

IF (@v_action LIKE '%to contin%' OR @v_action LIKE '%otc%' OR @v_action LIKE '%to contin%' OR @v_action LIKE '%br%%ex%')
OR (@v_action LIKE '%withdrawn%' AND @v_action not LIKE '%ins%'AND @v_action not LIKE '%nvq%' ) --court appearance with an otc etc..
--so start counting u's from 0
BEGIN
SELECT @bol1stbreach = 1,
@bolnoaction = 0,
@appliedfortargetdate = GETDATE(), -- set to a neutral date
@bolproceed = 1,
@2nducontactdate = NULL,
@bolstopcount = 0,
@ucount = 0
END
IF @bolproceed = 1
BEGIN
IF @v_apptkept = 'u' AND @bolstopcount = 0
BEGIN
SELECT @ucount = @ucount + 1
IF @ucount = 2 --2nd 'u'
BEGIN
IF @bol1stbreach = 1
BEGIN
SELECT @bolnoaction = 1 --no action has been taken as yet so set to 1
END
SELECT @bol1stbreach = 0
EXEC @returnvalue = calc_working_days @datefrom = @v_contactdate, @number = 10
SELECT @appliedfortargetdate = DATEADD(dd,@returnvalue,@v_contactdate)
IF @appliedfortargetdate > GETDATE() OR @appliedfortargetdate > @v_termdate --terminated before target date
BEGIN
SELECT @v_bolnewsuper = 1,
@bolnoaction = 0 --no action is required anyway
END
SELECT @v_targetdate = @appliedfortargetdate,
@ucount = 0,
@bolstopcount = 1,
@2nducontactdate = @v_contactdate
END
END
IF @v_apptkept = '-' AND ( @v_action LIKE '%summ%%appl%'
OR @v_action LIKE '%warrant applied%'
--OR @v_action LIKE '%induction%' --missed appt
OR @v_action LIKE '%breach%%action%%reque%'
OR @v_action LIKE '%warrant req%')
BEGIN
SELECT @ucount = 0, --reset counter
@bolproceed = 0
END
IF @2nducontactdate IS NOT null
BEGIN
IF @v_apptkept = '-' AND ( @v_action LIKE '%summ%%appl%'
OR @v_action LIKE '%warrant applied%'
--OR @v_action LIKE '%induction%' --missed appt
OR @v_action LIKE '%breach%%action%%reque%'
OR @v_action LIKE '%warrant req%')
-- AND @v_contacttype <> 'c' -- equates to a court referral
AND @v_contactdate >= @2nducontactdate
BEGIN

SELECT @bolappoccurred = 1,
@bolnoaction = 0 --action has been taken
IF @v_contactdate > @appliedfortargetdate
BEGIN
IF @bol1stfailure = 1
BEGIN
SELECT @bolreqrecord = 1,
@bol1stfailure = 0
END
END
--SELECT @bolproceed = 0
END
END
END


UPDATE t_enforcement

SET

cbolappoccurred = @v_bolappoccurred,
cbolreqrecord = @v_bolreqrecord,
cbolnewsuper = @v_bolnewsuper,
csurname = @v_surname,
cforename = @v_forename,
ccrn = @v_crn,
csupervisiontype = @v_supervisiontype,
ccommencement = @v_commencement,
ceventid = @v_eventid,
ctermdate = @v_termdate,
cordertype = @v_ordertype,
capptkept = @v_apptkept,
caction = @v_action,
ccontactdate = @v_contactdate,
ccontacttype = @v_contacttype,
cofficerid = @v_officerid,
coffsurname = @v_offsurname,
coffforename = @v_offforename,
csupervisioncount = @v_supervisioncount,
cbreachcount = @v_breachcount,
ctargetdate = @v_targetdate,
cnoactionoccurred = @v_noactionoccurred

WHERE

id = @iNextRowId


SELECT @iNextRowId = NULL,
@iNextRowId = MIN(id)

FROM t_enforcement

WHERE id > @iCurrentRowId

IF ISNULL(@iNextRowId,0) = 0
BEGIN
BREAK
END
SELECT
@iCurrentRowId = id,
@v_bolappoccurred = cbolappoccurred,
@v_bolreqrecord = cbolreqrecord,
@v_bolnewsuper = cbolnewsuper,
@v_surname = csurname,
@v_forename = cforename,
@v_crn = ccrn,
@v_supervisiontype = csupervisiontype,
@v_commencement = ccommencement,
@v_eventid = ceventid,
@v_termdate = ctermdate,
@v_ordertype = cordertype,
@v_apptkept = capptkept,
@v_action = caction,
@v_contactdate = ccontactdate,
@v_contacttype = ccontacttype,
@v_officerid = cofficerid,
@v_offsurname = coffsurname,
@v_offforename = coffforename,
@v_supervisioncount = csupervisioncount,
@v_breachcount = cbreachcount,
@v_targetdate = ctargetdate,
@v_noactionoccurred = cnoactionoccurred

FROM t_enforcement

WHERE id = @iNextRowId

END

--------------------------------------------------------------------------------------------
--insert supervision count data for each relevant po for the crystal report

INSERT INTO t_enforcement

(cbolappoccurred,
cbolreqrecord,
cbolnewsuper,
cteamname,
cofficerid,
coffsurname,
coffforename,
csupervisioncount)

SELECT

0,
0,
0,
--0,
t.teamname,
p.personalno AS personalno,
p.surname AS offsurname,
p.forename AS offforename,
COUNT (s.eventid) AS supervisioncount

FROM


client c,
event e,
supervision s,
po p,
team t

WHERE

c.dbnumber = e.dbnumber
AND c.urn = e.urn
AND e.dbnumber = s.dbnumber
AND e.urn = s.urn
AND e.supid = s.eventid
AND p.personalno = s.suppopersonalno
AND s.teamcode = t.teamcode

AND c.dlf IS NULL --not deleted flag in client table
AND e.dlf IS NULL --not deleted flag in event table
AND s.transout IS NULL --not transferred out (of area)
AND s.supervisiontype ='01'
AND (s.additionalreq1 IS NULL OR s.additionalreq1 <> 'h')
--and s.additionalreq1 <> 'h'
AND s.commencement BETWEEN @datefrom AND @dateto
--and s.commencement between '01-sep-2002' and '01-sep-2003'

GROUP BY

t.teamname,
p.personalno,
p.surname,
p.forename


--------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------
--insert breach count data for each relevant po for the crystal report


INSERT INTO t_enforcement

(cbolappoccurred,
cbolreqrecord,
cbolnewsuper,
cteamname,
cofficerid,
coffsurname,
coffforename,
cbreachcount)

SELECT

0,
0,
0,
--0,
cteamname,
cofficerid,
coffsurname,
coffforename,
COUNT (distinct(t_enforcement.ceventid))

FROM

t_enforcement

WHERE

cbolnewsuper = 0

GROUP BY

cteamname,
cofficerid,
coffsurname,
coffforename



--------------------------------------------------------------------------------
IF @bolCrystal = 1

SELECT

cbolappoccurred,
cbolreqrecord,
cbolnewsuper,
csurname,
cforename,
ccrn,
cteamname,
csupervisiontype,
ccommencement,
ceventid,
ctermdate,
cordertype,
capptkept,
caction,
ccontactdate,
ccontacttype,
cofficerid,
coffsurname,
coffforename,
csupervisioncount,
cbreachcount,
ctargetdate,
cnoactionoccurred

FROM

t_enforcement e2

WHERE

(select
COUNT(*)

FROM t_enforcement

WHERE
ceventid = e2.ceventid AND cbolreqrecord = 1) >0
OR

ceventid IS NULL -- add the po and breach totals

ELSE

-------------------------------------------------------------------------------------------------
--returns for excel workbook---------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
--declare @test int
--select @test = (SELECT COUNT(cnoactionoccurred) FROM t_enforcement WHERE cnoactionoccurred = 1)


SELECT SUM (csupervisioncount) AS SupervisionCount,SUM (cbreachcount) AS Breaches,
SUM(cnoactionoccurred) AS NoActiontaken,
--(SELECT COUNT(ctargetdate) FROM t_enforcement WHERE ctargetdate IS NOT NULL) as anything,
(SELECT COUNT(cbolreqrecord) FROM t_enforcement WHERE cbolreqrecord = 1) AS NotMeetingTargetCount

FROM
t_enforcement


RETURN

 

[nigelrivett]

Some things you shouldn't ask

You can change the select to this but it shouldn't make any difference.

SELECT SUM (csupervisioncount) AS SupervisionCount,SUM (cbreachcount) AS Breaches,
SUM(cnoactionoccurred) AS NoActiontaken,
--(SELECT COUNT(ctargetdate) FROM t_enforcement WHERE ctargetdate IS NOT NULL) as anything,
sum(case when cbolreqrecord = 1 then 1 else 0 end) AS NotMeetingTargetCount

FROM
t_enforcement

What happens if you run the query on t_enforcement after executing the SP?
How about leaving t_enforcement and creating an SP to just do the select - what happens then.


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[bluecjh]

Nigel,
tried your SELECT change, no change. I have already tested
changing bit type to integer and summing them but to no
avail.

When I run the final SELECT from QA it generally works
however I have managed to get it to fail on a couple
of occassions immediatley after running my code from
excel, are some records getting locked in some way?

I will go and try your last suggestion.

 

[nigelrivett]

I notice that you are relying on the order of insertiing records into t_enforcement.
As it is inserting into a table sql server doesn't always honour the order by - it might be worthwhile checking that you haven't an instance where that is causing problems. Would be surprised if it always fails from excel but only sometimes from query analyser though - might be worthwhile trying with top 100 percent in the insert query.

Other than that I guess it's just a matter of putting in trace code to find out where eit is going wrong.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[nigelrivett]

Just noticed you have v6.5 so can't use top.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[bluecjh]

Interesting,

When VB code (in excel) runs the stored Proc, no
1's for the 'offending' bit type column are set in the table. When QA runs the SP there are some 1's set.

Therefore the part of the query that is failing (when the SP is run from VB) is within the iterative 'WHILE'
process .

Any ideas?

 

[bluecjh]

Nigel,
have established that the final select query always fails
in QA after running the stored proc from VB(as per my
previous comment, since VB does not process the stored proc properly)

 

[bluecjh]

Solution

IF @v_eventid <> @eventid

change to:

IF @v_eventid <> @eventid OR @eventid IS NULL

I don't understand why this makes VB run the Stored
Proc correctly but it does. I thought the concept
was that the server did the processing and returned
the results, so why should a line of TSQL run
differently from another application?

Thanks for all the help particularly NigelRivett.