Strange Query Behavior - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Strange Query Behavior

Author

Topic

aawan
Starting Member

24 Posts

Posted - 2005-12-01 : 11:39:16

I'm having a situation where one query works in one database, but not the other.

From what I can tell, the databases are setup identically (meaning their compatibility levels, etc...)

The query is something like this

SELECT userid
From USERS
WHERE user_password = 'password' AND user_name = 'username'
AND cancelled NOT IN ('C')

The problem is that in database A, this query works and gives me all the non-cancelled users.

However, I copy this query to database B, and I get no records back.

In both databases the cancelled field is NULL for the moment. Eventually, there will be 'A's and 'C's. But we need this to work as is for the time being. Since it will be a lot of work to determine which existing users are active or not, but that's outside of the realm of this discussion.

Anyone know why this query would work in one db and not the other?
I thought it might have something to do with a Stored proc being run in one of the db's that set the ANSI NULLS or Quoted Identifiers.

Any help is greatly appreciated.

Thanks,
A

X002548
Not Just a Number

15586 Posts

Posted - 2005-12-01 : 11:52:24

Got any data in the table?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

aawan
Starting Member

24 Posts

Posted - 2005-12-01 : 12:03:14

Yes, we have thousands of records.
This is for a nation-wide company, and their production database.

Actually you know what, that query doesn't work at all, on either database.

If someone has any insight as to why, and what is a good alternative, I would greatly appreciate it.

quote:
Originally posted by X002548

Got any data in the table?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

X002548
Not Just a Number

15586 Posts

Posted - 2005-12-01 : 12:29:46

First off user_name is a reserved word, so that might be causing some trouble. Second, where did userid come from? Please read the hint link in my sig for advice on how to post.

BUT! This works fine


USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myUSERS99(user_password varchar(20), [user_name]  varchar(20), cancelled char(1))
GO

INSERT INTO myUSERS99(user_password, [user_name], cancelled)
SELECT 'blank', 'sa', 'C' UNION ALL
SELECT 'password', 'username', 'A'
GO

SELECT *
  FROM myUSERS99
 WHERE user_password = 'password' AND [user_name] = 'username'
AND cancelled NOT IN ('C')
GO

SET NOCOUNT OFF
DROP TABLE myUSERS99
GO

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

aawan
Starting Member

24 Posts

Posted - 2005-12-01 : 13:33:19

Ok, let me try this again from the beginning.
I read your sig, and will post everything in as a correct format as I am able to follow. Hopefully this will help.

Here is a create script for the table:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Employee]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Employee]
GO

CREATE TABLE [dbo].[Employee] (
[Emp_ID] [int] IDENTITY (1, 1) NOT NULL ,
[FirstName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LastName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Dept_ID] [int] NULL ,
[StartDate] [smalldatetime] NULL ,
[Salary] [int] NULL ,
[Contract] [nvarchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[cancelled] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Employee] WITH NOCHECK ADD
CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED
(
[Emp_ID]
) ON [PRIMARY]
GO

Sample Data:
Insert into Employee(FirstName, LastName, Dept_ID, StartDate, Salary, Contract)
SELECT 'Ben','Franklin',1,'1/1/1980',45000,'Yes' UNION ALL
SELECT 'Fred','Flintstone',3,'2/6/1966',120000,'No' UNION ALL
SELECT 'Betty','Rubble',2,'12/7/1977',60000,'Yes' UNION ALL
SELECT 'Daffy','Duck',1,'3/3/1956',55000,'Yes' UNION ALL
SELECT 'Bugs','Bunny',3,'6/20/1935',130000,'No'

Attempted the following query(s):
1) Select *
FROM Employee
WHERE NOT (Cancelled = 'C')

expected result: all records (since cancelled is now NULL)

2)
Select *
FROM Employee
WHERE Cancelled <> 'C'

expected result: all records (since cancelled is now NULL)

3) Select *
FROM Employee
WHERE Cancelled NOT IN ('C')

expected result: all records (since cancelled is now NULL)

4) Select *
FROM Employee
WHERE Cancelled NOT LIKE 'C'

expected result: all records (since cancelled is now NULL)

I apologize for not posting in the correct format. Now, I hope the situation I was trying to illustrate is clear, and hopefully someone will be able to help.

Thanks,
A

quote:
Originally posted by X002548

First off user_name is a reserved word, so that might be causing some trouble. Second, where did userid come from? Please read the hint link in my sig for advice on how to post.

BUT! This works fine

USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myUSERS99(user_password varchar(20), [user_name] varchar(20), cancelled char(1))
GO

INSERT INTO myUSERS99(user_password, [user_name], cancelled)
SELECT 'blank', 'sa', 'C' UNION ALL
SELECT 'password', 'username', 'A'
GO

SELECT *
FROM myUSERS99
WHERE user_password = 'password' AND [user_name] = 'username'
AND cancelled NOT IN ('C')
GO

SET NOCOUNT OFF
DROP TABLE myUSERS99
GO

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-02 : 00:52:59

The column Cancelled doesnt have any value
You need to handle null

Try this

Select *
FROM Employee
WHERE Isnull(Cancelled,'') <> 'C'

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources