[Bug?] Where clause in SQLSERVER7 doesn't return results where it really should

Hi group,
I found the following behaviour really surprising:
[SQLServer 7]
consider following query:
SELECT L.logid, L.logdate, L.vragensetid, L.dooruserid, L.incidentid,
L.action, U.realname
FROM tbllog AS L LEFT OUTER JOIN tbluser AS U ON (L.dooruserid=U.userid)
WHERE((logdate >= '2006-02-08') AND (logdate <= '2006-02-08'))
ORDER BY logdate;
This query returns 0 results.
If I remove one part of the where-clause, it works just fine:
Like this
SELECT L.logid, L.logdate, L.vragensetid, L.dooruserid, L.incidentid,
L.action, U.realname
FROM tbllog AS L LEFT OUTER JOIN tbluser AS U ON (L.dooruserid=U.userid)
WHERE((logdate <= '2006-02-08'))
ORDER BY logdate;
or
SELECT L.logid, L.logdate, L.vragensetid, L.dooruserid, L.incidentid,
L.action, U.realname
FROM tbllog AS L LEFT OUTER JOIN tbluser AS U ON (L.dooruserid=U.userid)
WHERE((logdate >= '2006-02-08'))
ORDER BY logdate;
(These queries only differ in the >= or <= in the whereclause.)
Both queries return some rows!
(As expected by me because I know the table does have rows with logdate
'2006-02-08')
It seems that SQLServer is unable to find ANY records if I use both
whereclauses at the same time.
The reason this weird situation is needed is because I need to assemble the
query dynamically based on some form-info posted by a user.
Can anybody explain this?
Is this a known bug?
Or is maybe something going on that has to do with my (limmited)
understanding of SQLServer/SQL ?
I can of course code around this issue by checking in my script if the dates
are the same and in that case make only whereclause (logdate='2006-02-08'),
but I am curious what is going on.
Thanks for your time!
Regards,
Erwin Moller
Erwin Moller wrote:
Hi, I made the situation even simpler, and found the reason for my problem.
I removed the join, and left only 1 where-clause.
So the following query:
SELECT logid, logdate, vragensetid, dooruserid, incidentid, action
FROM tbllog
WHERE(logdate <= '2006-02-08') ORDER BY logdate;
Resulted in NO results.
Where the database contains records on this date.
Reason is: I am a simpleminded idiot.
I do not tell the database how many hours/minutes/seconds....
So while there are records on 2006-02-08, they are ALL after midnight.
:-)
Sorry for the noise!
Regards,
Erwin Moller

[Bug?] Where clause in SQLSERVER7 doesn't return results where it really should

Hi group,
I found the following behaviour really surprising:
[SQLServer 7]
consider following query:
SELECT L.logid, L.logdate, L.vragensetid, L.dooruserid, L.incidentid,
L.action, U.realname
FROM tbllog AS L LEFT OUTER JOIN tbluser AS U ON (L.dooruserid=U.userid)
WHERE((logdate >= '2006-02-08') AND (logdate <= '2006-02-08'))
ORDER BY logdate;
--
This query returns 0 results.
If I remove one part of the where-clause, it works just fine:
Like this
--
SELECT L.logid, L.logdate, L.vragensetid, L.dooruserid, L.incidentid,
L.action, U.realname
FROM tbllog AS L LEFT OUTER JOIN tbluser AS U ON (L.dooruserid=U.userid)
WHERE((logdate <= '2006-02-08'))
ORDER BY logdate;
--
or
SELECT L.logid, L.logdate, L.vragensetid, L.dooruserid, L.incidentid,
L.action, U.realname
FROM tbllog AS L LEFT OUTER JOIN tbluser AS U ON (L.dooruserid=U.userid)
WHERE((logdate >= '2006-02-08'))
ORDER BY logdate;
--
(These queries only differ in the >= or <= in the whereclause.)
Both queries return some rows!
(As expected by me because I know the table does have rows with logdate
'2006-02-08')
It seems that SQLServer is unable to find ANY records if I use both
whereclauses at the same time.
The reason this weird situation is needed is because I need to assemble the
query dynamically based on some form-info posted by a user.
Can anybody explain this?
Is this a known bug?
Or is maybe something going on that has to do with my (limmited)
understanding of SQLServer/SQL '
I can of course code around this issue by checking in my script if the dates
are the same and in that case make only whereclause (logdate='2006-02-08'),
but I am curious what is going on.
Thanks for your time!
Regards,
Erwin MollerErwin Moller wrote:
Hi, I made the situation even simpler, and found the reason for my problem.
I removed the join, and left only 1 where-clause.
So the following query:
SELECT logid, logdate, vragensetid, dooruserid, incidentid, action
FROM tbllog
WHERE(logdate <= '2006-02-08') ORDER BY logdate;
--
Resulted in NO results.
Where the database contains records on this date.
Reason is: I am a simpleminded idiot.
I do not tell the database how many hours/minutes/seconds....
So while there are records on 2006-02-08, they are ALL after midnight.
:-)
Sorry for the noise!
Regards,
Erwin Moller

[BEA][SQLServer JDBC Driver][SQLServer]Warning:Fatal error 605 occurred

Dear group
The following error occured
[BEA][SQLServer JDBC Driver][SQLServer]Warning:Fatal error 605 occurred
and after using DBCC was rectified, but we are not sure the cause of
this problem.
Is it due to SAN installed last month or is there any other reason.
KaranError 605
Severity Level 21
Attempt to fetch logical page %S_PGID in database '%.*ls' belongs to object
'%.*ls', not to object '%.*ls'.
You have corruption in your DB.
Look at http://www.karaszi.com/SQLServer/info_corrupt_suspect_db.asp
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Karan Lobana" wrote:
>
> Dear group
> The following error occured
> [BEA][SQLServer JDBC Driver][SQLServer]Warning:Fatal error 605 occurred
> and after using DBCC was rectified, but we are not sure the cause of
> this problem.
> Is it due to SAN installed last month or is there any other reason.
>
> Karan
>

@query error

ODBC error 4604 (42000) There is no such user or group 'sa8'

this is the error message I get when I run my stored procedure that has the @.query parameter and I have been told this is a bug within sql that microsoft doesn't seem to be aware off.

Are you coming across this and what do you do to go around it?

RegardsHi:

I have found this "solution". Please check if you can use it ;)

We had a similar problem, the way we got around it was to add access rights to the TEMP directory in the Document and Settings folder on the account logged into the Server. As we had the NT Administrator account permanatley logged onto the server. If I tried to run a temp query from xp_sendmail it would fail, but if I used the administraor account it worked okay. The query writes the output file it needs to to the temp folder of which we had no access rights. Once I changed the access rights it worked okay. Did find this answer in one of the many SQL forums on the net. Hope this help you.|||I hope I can be more helper ;)