!= or <> not working??? - 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 2008 Forums

Transact-SQL (2008)

!= or <> not working???

Author

Topic

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2010-12-09 : 05:01:46

This line does not work with != or <>. IF I use = it works?

Where Type <> ''Connect'' OR Type <> ''Disconnect'' OR Type <> ''Finsihed'' OR Type <> ''Site Acknowledge'' OR Type <> ''Site Select'' OR Type <> ''Site Release''

USE [VC]
GO
/****** Object: StoredProcedure [dbo].[EventType_RP] Script Date: 12/09/2010 20:18:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Steve Harlington
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[EventType_RP]

AS

Declare @Path nvarchar (200)
Declare @Table nvarchar (200)
Declare @sql1 nvarchar(max)
Declare @objFSys int
Declare @i int
Declare @File varchar(1000)
Declare @sql nvarchar(max)

SET @Path = (SELECT 'C:\Reports\Data\Event_Activity.MDB')
--Print @Path

SET @Table = (SELECT 'Results')
--Print @Table

SET NOCOUNT ON

Select @sql=''
Select @sql=@sql+ 'SELECT EventType, ''VCP Database''
FROM '+name+'.dbo.EventDetails
GROUP BY EventType
' from sys.databases where name='VC' or name like 'VCA%'

Create table #t([EventType] nvarchar(max), [Database] nvarchar(50))

Insert into #t
Exec(@sql)
--Print @sql

Exec sp_OACreate 'Scripting.FileSystemObject', @objFSys out
Exec sp_OAMethod @objFSys, 'FileExists', @i out, @Path

If @i = 1

Begin

Select @sql1=''
Select @sql1=@sql1+ 'SELECT Type, ''VCG Database''
FROM OPENROWSET(
''Microsoft.Jet.OLEDB.4.0'',
''' + @Path + ''';
''admin'';
'''',
' + @Table + ')
Where Type <> ''Connect'' OR Type <> ''Disconnect'' OR Type <> ''Finsihed'' OR Type <> ''Site Acknowledge'' OR Type <> ''Site Select'' OR Type <> ''Site Release''
GROUP BY Type
'

Insert into #t
Exec(@sql1)
--Print @sql1

End

Else
Print 'Database does not Exist'

Exec sp_OADestroy @objFSys

Select DISTINCT [EventType], [Database]
From #t

UNION ALL

Select '(Exclude Event Type)', 'VCP Database'

Order By [EventType]

Drop Table #t

SET NOCOUNT OFF

RETURN

Kristen
Test

22859 Posts

Posted - 2010-12-09 : 05:18:50

If the [Type] column IS NULL then it will "fail" both an = and a <> test

So you should maybe use:


WHERE Type <> 'Connect' OR Type IS NULL OR ...

although if the [Type] column is defined as NOT NULL then it won't allow NULL to be stored, and then you won't need to also check for NULL

Kristen
Test

22859 Posts

Posted - 2010-12-09 : 05:21:47

Ah! That may not be the problem! But something for you to consider anyway.

Where Type <> ''Connect'' OR Type <> ''Disconnect'' OR Type <> ''Finsihed'' OR Type <> ''Site Acknowledge'' OR Type <> ''Site Select'' OR Type <> ''Site Release''

If Type = 'Connect' then the test "Type <> 'Disconnect'" will be TRUE.

You probably want a NOT IN:

Where Type IS NULL OR Type NOT IN('Connect', 'Disconnect', 'Finsihed', 'Site Acknowledge', 'Site Select', 'Site Release')

Also note spelling mistake in your code for "Finsihed"

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-09 : 05:28:53

in fact
Where Type <> ''Connect'' OR Type <> ''Disconnect'' OR Type <> ''Finsihed'' OR Type <> ''Site Acknowledge'' OR Type <> ''Site Select'' OR Type <> ''Site Release''

is the same as
Where Type <> ''Connect'' OR Type <> ''Disconnect''
is the same as
where Type is not null

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

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2010-12-09 : 05:38:02

Thankyou. I will look at your answers tomorrow as it is very late and my eyes and fingers have stopped working :)

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2010-12-09 : 07:26:40

ps. "Finsihed" looks to be spelt wrong.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources