we*********@poole.gov.uk wrote:
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
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"