"not in" clause doesn't work

Is there anything that would cause a "not in" clause to not work in SQL2000?
DETAIL: I have 2 simple tables called "People" and "PeopleImport". Each of
these tables has a "flxID" field of type varchar(10). There is a record in
the "PeopleImport" table with a "flxID" value of '12345' and no records in
the People table with that value (I deleted it). Running the following
query does not return any records but (I think) should:
select flxid from peopleimport where flxid not in (select flxid from people)
I have also tried the following diviations:
select flxid from peopleimport where convert(char(5), flxid) not in (select
convert(char(5), flxid) from people)
select flxid from peopleimport where rtrim(convert(char(5), flxid)) not in
(select rtim(convert(char(5), flxid)) from people)
I hope someone has some thoughts on this because I am out of ideas.
Schoo
On Thu, 23 Dec 2004 15:54:46 -0600, Schoo wrote:

>Is there anything that would cause a "not in" clause to not work in SQL2000?
Hi Schoo,
None that I'm aware of, but many people get tripped by the way NULL values
are treated in IN and NOT IN clauses. Remember that NULL is (to the
database) an indicator for "unknown value".
When you deal with NULLs, you start using three-valued logic: 'true' and
'false' get company of their little brother 'maybe'. A row is only
included in the query if the where clause evaluates to 'true'; it is
discarded on 'false' and on 'maybe'.
For IN, the "strange" behaviour doesn't cause apparently wrong results:
* Is the value 15 IN the list {14, 17} --> NO (false)
* Is the value 15 IN the list {15, unknown value, 17} --> YES (true)
* Is the value 15 IN the list {14, unknown value, 17} --> MAYBE
The row will only be included in the query in the second example.
For NOT IN, the same logic yields results that "look" unlogical:
* Is the value 15 NOT IN the list {14, 17} --> YES (true)
* Is the value 15 NOT IN the list {15, unknown value, 17} --> NO (false)
* Is the value 15 NOT IN the list {14, unknown value, 17} --> MAYBE
This time, only the first case will return a row. The NOT IN with a NULL
value in the subquery can only result in NO or MAYBE and will therefor
automatically exclude all rows from the result.
Two ways to fix it:
Recommended: use NOT EXISTS instead of NOT IN. I never use NOT IN, except
when testing against a fixed list of constants!
SELECT flxid
FROM peopleimport AS pi
WHERE NOT EXISTS (SELECT *
FROM people AS p
WHERE p.flxid = pi.flxid)
Quick fix (not recommended, as you'll keep running into this as long as
you have subqueries with NOT IN): include WHERE flxid IS NOT NULL in the
subquery.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Hugo,
Thank you for your input. You are correct, dealing with the nulls in this
way works. I will incorporate these changes in the future.
Scott
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:mkhms0hfp9hjdrpbtsp9v85vkkb1ksrgt7@.4ax.com... [vbcol=seagreen]
> On Thu, 23 Dec 2004 15:54:46 -0600, Schoo wrote:
SQL2000?
> Hi Schoo,
> None that I'm aware of, but many people get tripped by the way NULL values
> are treated in IN and NOT IN clauses. Remember that NULL is (to the
> database) an indicator for "unknown value".
> When you deal with NULLs, you start using three-valued logic: 'true' and
> 'false' get company of their little brother 'maybe'. A row is only
> included in the query if the where clause evaluates to 'true'; it is
> discarded on 'false' and on 'maybe'.
> For IN, the "strange" behaviour doesn't cause apparently wrong results:
> * Is the value 15 IN the list {14, 17} --> NO (false)
> * Is the value 15 IN the list {15, unknown value, 17} --> YES (true)
> * Is the value 15 IN the list {14, unknown value, 17} --> MAYBE
> The row will only be included in the query in the second example.
> For NOT IN, the same logic yields results that "look" unlogical:
> * Is the value 15 NOT IN the list {14, 17} --> YES (true)
> * Is the value 15 NOT IN the list {15, unknown value, 17} --> NO (false)
> * Is the value 15 NOT IN the list {14, unknown value, 17} --> MAYBE
> This time, only the first case will return a row. The NOT IN with a NULL
> value in the subquery can only result in NO or MAYBE and will therefor
> automatically exclude all rows from the result.
> Two ways to fix it:
> Recommended: use NOT EXISTS instead of NOT IN. I never use NOT IN, except
> when testing against a fixed list of constants!
> SELECT flxid
> FROM peopleimport AS pi
> WHERE NOT EXISTS (SELECT *
> FROM people AS p
> WHERE p.flxid = pi.flxid)
> Quick fix (not recommended, as you'll keep running into this as long as
> you have subqueries with NOT IN): include WHERE flxid IS NOT NULL in the
> subquery.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)

"not a trusted connection", means what EXACTLY?

There's no explaination of this error on MS or any of the documentation that
I can find. Yes, I know some of the cases that cause it, but that's a
different issue entirely.
I'm getting this error on a new SQL Server install. All of the permissions
are copied from the older server. About 2/3rds of the machines in the office
can log in fine, but the other 1/3rd return this error. There is no obvious
difference between the machines. We use Windows Authentication for everything.
So, does this error mean "I cannot verify you with the domain controller"?
Or perhaps "your domain credentials do no allow you to log in"? Or something
else entirely? Depending on the exact nature of the error, the steps to
correct it will be very different.
This part of the error message mean that there is no trust (or the trust
cannot be confirmed) between the account thatt is logging in and the SQL
Server machine. The first part of the message is the most important. What
is the complete message that the users are getting?
Rand
This posting is provided "as is" with no warranties and confers no rights.
|||"Rand Boyd [MSFT]" wrote:

> Server machine. The first part of the message is the most important. What
> is the complete message that the users are getting?
SQLState '28000'
SQL Server Error 18452
[...] Login failed for user '(null)'. Reason: not associated with a trusted
SQL Server connection.
Thanks!
|||Are you using a .NET application?
Sasan Saidi,
MSc in CS, MCSE (NT4), IBM Certified MQ Administrator
Senior DBA
Brascan Business Services
"Maury Markowitz" wrote:

> "Rand Boyd [MSFT]" wrote:
>
> SQLState '28000'
> SQL Server Error 18452
> [...] Login failed for user '(null)'. Reason: not associated with a trusted
> SQL Server connection.
> Thanks!
>
|||"Sasan Saidi" wrote:

> Are you using a .NET application?
No. I'm simply trying to set up the DSN in the Data Sources (ODBC) control
panel.
|||Has the user logged onto the domain? Does the user account have an
associated SQL login?
Jeff
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:F095B40F-78FB-4096-89CA-7E9E1F9E0580@.microsoft.com...
> "Sasan Saidi" wrote:
>
> No. I'm simply trying to set up the DSN in the Data Sources (ODBC) control
> panel.
|||My guess is that the SQL Server is setup to allow only Windows logins and you try to login using a
SQL server login.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in message
news:BAD981FB-7BB8-46BB-A297-80DE6CF7B543@.microsoft.com...
> "Rand Boyd [MSFT]" wrote:
>
> SQLState '28000'
> SQL Server Error 18452
> [...] Login failed for user '(null)'. Reason: not associated with a trusted
> SQL Server connection.
> Thanks!
>

"not a trusted connection", means what EXACTLY?

There's no explaination of this error on MS or any of the documentation that
I can find. Yes, I know some of the cases that cause it, but that's a
different issue entirely.
I'm getting this error on a new SQL Server install. All of the permissions
are copied from the older server. About 2/3rds of the machines in the office
can log in fine, but the other 1/3rd return this error. There is no obvious
difference between the machines. We use Windows Authentication for everythin
g.
So, does this error mean "I cannot verify you with the domain controller"?
Or perhaps "your domain credentials do no allow you to log in"? Or something
else entirely? Depending on the exact nature of the error, the steps to
correct it will be very different.This part of the error message mean that there is no trust (or the trust
cannot be confirmed) between the account thatt is logging in and the SQL
Server machine. The first part of the message is the most important. What
is the complete message that the users are getting?
Rand
This posting is provided "as is" with no warranties and confers no rights.|||"Rand Boyd [MSFT]" wrote:

> Server machine. The first part of the message is the most important. What
> is the complete message that the users are getting?
SQLState '28000'
SQL Server Error 18452
[...] Login failed for user '(null)'. Reason: not associated with a trus
ted
SQL Server connection.
Thanks!|||Are you using a .NET application?
Sasan Saidi,
MSc in CS, MCSE (NT4), IBM Certified MQ Administrator
Senior DBA
Brascan Business Services
"Maury Markowitz" wrote:

> "Rand Boyd [MSFT]" wrote:
>
> SQLState '28000'
> SQL Server Error 18452
> [...] Login failed for user '(null)'. Reason: not associated with a tr
usted
> SQL Server connection.
> Thanks!
>|||"Sasan Saidi" wrote:

> Are you using a .NET application?
No. I'm simply trying to set up the DSN in the Data Sources (ODBC) control
panel.|||Has the user logged onto the domain? Does the user account have an
associated SQL login?
Jeff
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:F095B40F-78FB-4096-89CA-7E9E1F9E0580@.microsoft.com...
> "Sasan Saidi" wrote:
>
> No. I'm simply trying to set up the DSN in the Data Sources (ODBC) control
> panel.|||My guess is that the SQL Server is setup to allow only Windows logins and yo
u try to login using a
SQL server login.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in messag
e
news:BAD981FB-7BB8-46BB-A297-80DE6CF7B543@.microsoft.com...
> "Rand Boyd [MSFT]" wrote:
>
> SQLState '28000'
> SQL Server Error 18452
> [...] Login failed for user '(null)'. Reason: not associated with a tr
usted
> SQL Server connection.
> Thanks!
>

"not a trusted connection", means what EXACTLY?

There's no explaination of this error on MS or any of the documentation that
I can find. Yes, I know some of the cases that cause it, but that's a
different issue entirely.
I'm getting this error on a new SQL Server install. All of the permissions
are copied from the older server. About 2/3rds of the machines in the office
can log in fine, but the other 1/3rd return this error. There is no obvious
difference between the machines. We use Windows Authentication for everything.
So, does this error mean "I cannot verify you with the domain controller"?
Or perhaps "your domain credentials do no allow you to log in"? Or something
else entirely? Depending on the exact nature of the error, the steps to
correct it will be very different.This part of the error message mean that there is no trust (or the trust
cannot be confirmed) between the account thatt is logging in and the SQL
Server machine. The first part of the message is the most important. What
is the complete message that the users are getting?
Rand
This posting is provided "as is" with no warranties and confers no rights.|||"Rand Boyd [MSFT]" wrote:
> Server machine. The first part of the message is the most important. What
> is the complete message that the users are getting?
SQLState '28000'
SQL Server Error 18452
[...] Login failed for user '(null)'. Reason: not associated with a trusted
SQL Server connection.
Thanks!|||Are you using a .NET application?
--
Sasan Saidi,
MSc in CS, MCSE (NT4), IBM Certified MQ Administrator
Senior DBA
Brascan Business Services
"Maury Markowitz" wrote:
> "Rand Boyd [MSFT]" wrote:
> > Server machine. The first part of the message is the most important. What
> > is the complete message that the users are getting?
> SQLState '28000'
> SQL Server Error 18452
> [...] Login failed for user '(null)'. Reason: not associated with a trusted
> SQL Server connection.
> Thanks!
>|||"Sasan Saidi" wrote:
> Are you using a .NET application?
No. I'm simply trying to set up the DSN in the Data Sources (ODBC) control
panel.|||Has the user logged onto the domain? Does the user account have an
associated SQL login?
Jeff
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:F095B40F-78FB-4096-89CA-7E9E1F9E0580@.microsoft.com...
> "Sasan Saidi" wrote:
> > Are you using a .NET application?
> No. I'm simply trying to set up the DSN in the Data Sources (ODBC) control
> panel.|||My guess is that the SQL Server is setup to allow only Windows logins and you try to login using a
SQL server login.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in message
news:BAD981FB-7BB8-46BB-A297-80DE6CF7B543@.microsoft.com...
> "Rand Boyd [MSFT]" wrote:
> > Server machine. The first part of the message is the most important. What
> > is the complete message that the users are getting?
> SQLState '28000'
> SQL Server Error 18452
> [...] Login failed for user '(null)'. Reason: not associated with a trusted
> SQL Server connection.
> Thanks!
>