RecordSet not returning Rows

[web.reports]

Hi All,

The following query returns one row when run in Query Analyzer:

DECLARE @IntCnt AS integer
SET @IntCnt = 0
SELECT @IntCnt = @IntCnt + COUNT(DISTINCT
B.ServiceID)
FROM dbo.TblServices A INNER JOIN
dbo.TblOccurences B
ON A.ServiceID = B.ServiceID
WHERE ({ fn LCASE(A.Prim_Sec) } =
'primary') AND (B.[Sort Date] > GETDATE()) AND (A.Assessment = 1)
GROUP BY B.ServiceID
SELECT
'Assessment' AS Whatever, @IntCnt AS Totals

How ever, if I run the same query in Enterprise Manager, or within an
ASP script, no rows are returned.

Any ideas?

Thanks,
Simon.

 

[Bob Barrows [MVP]]

Hi All,

The following query returns one row when run in Query Analyzer:

DECLARE @IntCnt AS integer
SET @IntCnt = 0
SELECT @IntCnt = @IntCnt + COUNT(DISTINCT
B.ServiceID)
FROM dbo.TblServices A INNER JOIN
dbo.TblOccurences B
ON A.ServiceID = B.ServiceID
WHERE ({ fn LCASE(A.Prim_Sec) } = 'primary')

Is your SQL Server set up with a case-sensitive collation? By default, SQL
Server is case-insensitive so you do not have to bother manipulating the
case like this. Also, T-SQL has built-in string functions to enable you to
modify the case without resorting to the ODBC functions as you have done
here.

I've never tried using ODBC functions like this so I don't know I don't know
if that is causing your problem. The best way to find out is to change this
to

LOWER(A.Prim_Sec) = 'primary'

and see if it makes a difference.

AND (B.[Sort Date] > GETDATE())
AND (A.Assessment = 1)
GROUP BY B.ServiceID
SELECT
'Assessment' AS Whatever, @IntCnt AS Totals

How ever, if I run the same query in Enterprise Manager, or within an

EM should never be used to run queries. Stick with QA.

ASP script, no rows are returned.

Any ideas?

Show us the code.
Really, the batch you've shown above should be encapsulated in a stored
procedure, at which point it is very easy to use the technique described
here:
http://tinyurl.com/jyy0