Problem With Login Null

Hi,
I have a problem. My web server is a Windows 2k Server and my database
server a Windows 2003. I use windows authentication to login at SQL Server,
but when I access from a client machine that uses Win 2k Professional I
always receive this message: "[Microsoft][ODBC SQL Server Driver][SQL
Server]Login failed for user '(null)'. Reason: Not associated with a trusted
SQL Server connection.".
When I access from a client machine that uses Windows XP everything its
ok.
This problem only occurs at the web site. When I try to access the Query
Analyzer or the Enterprise Manager I still have the connection.
Can anyone help me?!
Thanks,
Riane Santos
riane@.cpunet.com.br
I've seen this error when a local security policy was in place preventing
access from the network. But, I don't see how that could be your problem
since it works from some machines.
"Riane de Oliveira Torres Santos" <riane@.cpunet.com.br> wrote in message
news:eI4Ehr1jEHA.2788@.tk2msftngp13.phx.gbl...
> Hi,
> I have a problem. My web server is a Windows 2k Server and my database
> server a Windows 2003. I use windows authentication to login at SQL
Server,
> but when I access from a client machine that uses Win 2k Professional I
> always receive this message: "[Microsoft][ODBC SQL Server Driver][SQL
> Server]Login failed for user '(null)'. Reason: Not associated with a
trusted
> SQL Server connection.".
> When I access from a client machine that uses Windows XP everything its
> ok.
> This problem only occurs at the web site. When I try to access the Query
> Analyzer or the Enterprise Manager I still have the connection.
> Can anyone help me?!
> Thanks,
> Riane Santos
> riane@.cpunet.com.br
>
sql

Problem With Login null

Hi,
I have a problem. My web server is a Windows 2k Server and my database
server a Windows 2003. I use windows authentication to login at SQL Server,
but when I access from a client machine that uses Win 2k Professional I
always receive this message: "[Microsoft][ODBC SQL Server Driver]
1;SQL
Server]Login failed for user '(null)'. Reason: Not associated with a trusted
SQL Server connection.".
When I access from a client machine that uses Windows XP everything its
ok.
This problem only occurs at the web site. When I try to access the Query
Analyzer or the Enterprise Manager I still have the connection.
Can anyone help me?!
Thanks,
Riane Santos
riane@.cpunet.com.brHi Riane,
If your web application is attempting to use the credentials of the web
client, then you'll need to implement
Security Delegation to get this to work.
Here's a good whitepaper on setting up Kerberos Delegation.
http://www.microsoft.com/downloads/...f94f-e28a-4726-
bffe-2f64ae2f59a2&displaylang=en
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

Problem with left join

Hi,
I have a query that is supposed to return records and make a left join where
one field is not null, but for some reason is not working properly and
returns the records even though they are null.
SELECT *
FROM cases a
left join activities as w on a.id = w.caseid AND w.Dateinitiated = (Select
MAX(y.Dateinitiated)
From Activities y Where y.caseid = a.id AND y.ActType ='HISTORY' and
y.dateinitiated IS NOT NULL and y.processtep IS NOT NULL)
Any help is greately appreciated.
AleksBetter if you post some ddl, sample data and expected result.
Please provide DDL and sample data.
http://www.aspfaq.com/etiquette.asp?id=5006
AMB
"Aleks" wrote:

> Hi,
> I have a query that is supposed to return records and make a left join whe
re
> one field is not null, but for some reason is not working properly and
> returns the records even though they are null.
> --
> SELECT *
> FROM cases a
> left join activities as w on a.id = w.caseid AND w.Dateinitiated = (Select
> MAX(y.Dateinitiated)
> From Activities y Where y.caseid = a.id AND y.ActType ='HISTORY' and
> y.dateinitiated IS NOT NULL and y.processtep IS NOT NULL)
> --
> Any help is greately appreciated.
> Aleks
>
>|||Aleks,
The reason the query seems to be returnning records from Activities,
where the Dateinitiated column is null, is because you have specified an
Outer Join.
When you specify An Outer Join, Al records from the Outer table are
returned, even when there is no match on the other side. You actually are
NOT returning any data from Activities Table where Dateinitiated column is
null. If you look at those rows, you'll probably notice that all the field
s
from Activities table are null there...
"Aleks" wrote:

> Hi,
> I have a query that is supposed to return records and make a left join whe
re
> one field is not null, but for some reason is not working properly and
> returns the records even though they are null.
> --
> SELECT *
> FROM cases a
> left join activities as w on a.id = w.caseid AND w.Dateinitiated = (Select
> MAX(y.Dateinitiated)
> From Activities y Where y.caseid = a.id AND y.ActType ='HISTORY' and
> y.dateinitiated IS NOT NULL and y.processtep IS NOT NULL)
> --
> Any help is greately appreciated.
> Aleks
>
>

Problem with isnull. Need to substitute null if a var is null and compare it to null and return

Hey. I need to substitute a value from a table if the input var is null. This is fine if the value coming from table is not null. But, it the table value is also null, it doesn't work. The problem I'm getting is in the isnull line which is in Dark green color because @.inFileVersion is set to null explicitly and when the isnull function evaluates, value returned from DR.FileVersion is also null which is correct. I want the null=null to return true which is why i set ansi_nulls off. But it doesn't return anything. And the select statement should return something but in my case it returns null. If I comment the isnull statements in the where clause, everything works fine. Please tell me what am I doing wrong. Is it possible to do this without setting the ansi_nulls to off? Thank you

set ansi_nulls off

go

declare

@.inFileName VARCHAR (100),

@.inFileSize INT,

@.Id int,

@.inlanguageid INT,

@.inFileVersion VARCHAR (100),

@.ExeState int

set @.inFileName = 'A0006337.EXE'

set @.inFileSize = 28796

set @.Id= 1

set @.inlanguageid =null

set @.inFileVersion =NULL

set @.ExeState =0

select Dr.StateID from table1 dR

where

DR.[FileName] = @.inFileName

AND DR.FileSize =@.inFileSize

AND DR.FileVersion = isnull(@.inFileVersion,DR.FileVersion)

AND DR.languageid = isnull(@.inlanguageid,null)

AND DR.[ID]= @.ID

)

go

set ansi_nulls on

well actually you dont need to change the setting

if you're up to something like this

AND isnull (DR.FileVersion,-1) = isnull(@.inFileVersion,-1)

|||

There is a slight problem with this. If the right side is null, it will evaluate to -1. If the left side is not null, it will evaluate to value stored in the table. It's VERY likely that the value in the table won't be -1. So the condition will be false. But, in actuality, it should be true, correct? Shouldn't it be like this?

AND isnull (DR.FileVersion,-1) = isnull(@.inFileVersion,isnull(DR.FileVersion,-1))

Thank you

|||

with this

AND isnull (DR.FileVersion,-1) = isnull(@.inFileVersion,-1)

the ending equation would be

and (-1 = -1) which evaluates to true.

meaning null=null

remember that this equation resides in the "where clause" and not on the

select clause. if you want to have it returned you must

place a "case clause" in the select statement to evaluate this

nevertheless this clause must still exist in the where clause

to include the nulls

sql

problem with identity column value

Example:
create table test
(
id bigint identity(1000,1) NOT NULL,
x int
)
set IDENTITY_INSERT test on;
insert into test (id,x) values (10,1);
set IDENTITY_INSERT test off;
-- identity value is OK = 1000, but:
set IDENTITY_INSERT test on;
insert into test (id,x) values (2000,1);
set IDENTITY_INSERT test off;
-- identity value isn't OK = 2000, but I need identity value = 1000
-- I know solution, but it is to slow:
DECLARE @.id_seq bigint;
SET @.id_seq = (select IDENT_CURRENT ( 'test' ));
set IDENTITY_INSERT test on;
insert into test (id, x) values (2000, 1);
set IDENTITY_INSERT test off;
DBCC CHECKIDENT ('test', RESEED, @.id_seq);
-- do you know some faster and better solution? Thankssilber wrote:
> Example:
> create table test
> (
> id bigint identity(1000,1) NOT NULL,
> x int
> )
> set IDENTITY_INSERT test on;
> insert into test (id,x) values (10,1);
> set IDENTITY_INSERT test off;
> -- identity value is OK = 1000, but:
> set IDENTITY_INSERT test on;
> insert into test (id,x) values (2000,1);
> set IDENTITY_INSERT test off;
> -- identity value isn't OK = 2000, but I need identity value = 1000
> -- I know solution, but it is to slow:
> DECLARE @.id_seq bigint;
> SET @.id_seq = (select IDENT_CURRENT ( 'test' ));
> set IDENTITY_INSERT test on;
> insert into test (id, x) values (2000, 1);
> set IDENTITY_INSERT test off;
> DBCC CHECKIDENT ('test', RESEED, @.id_seq);
> -- do you know some faster and better solution? Thanks
I don't quite understand the problem. Apparently you want to reset the
current IDENTITY value after inserting some data with IDENTITY_INSERT
on. The only reason I can imagine performance would be in issue here is
if you had to issue DBCC CHECKIDENT on a frequent basis during
user-transactions. But if you had to do that on a frequent basis then
I'd say you would be better off not having the column as an IDENTITY at
all.
Alternatively, you could use only negatives for the IDENTITY_INSERT
values and use positives for the incrementing IDENTITY value. That way
the increment won't be affected.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Problem with dynamic SQL syntax

What exactly do you mean by an empty field? Generally if
your field allows null and you don't set a value into
that field for a row, the field is set to NULL and you
can test for this by using "type IS NULL" in your where
clause.
"<>" is the operator for not equals and it is not a
singleton operator, it is a comparison operator. You
need something on both sides of the "<>" to compare to
each other.
For instance, if you are looking for rows where the type
field is not equal to a space, you could try "type <> ' '"
I hope that this helps.
Matthew Bando
bandoM@.CSCTechnologies-dot-com

>--Original Message--
>I' m having a problem with the syntax when I'm trying to
run a dynamic SQL
>statement.
>The code -
>set @.sql = 'SELECT *
INTO '+@.db_name_dest+'.dbo.'+@.table_name+'
>
FROM '+@.servername_source+'.'+@.db_name_source+'.dbo.'+@.tab
le_name+' Where
>Date_ >='+'2001-01-01'+'
> And CompanyNo Is Not NULL And Type <>'
>exec sp_executesql @.sql
>- gives me the error "Incorrect syntax near '>'." The
purpose of the last <>
>is to find the records where this field is empty (that's
my understanding of
>it...). The basic structure of the query is from a DTS
Transform task, but
>I'm trying to "convert" this whole task to TSql.
>I've tried all sorts of different combinations of <>
and ' but it still
>won't do it. If I just prins the @.sql var. it looks fine.
>The CompanyNo field is int(4) and the Type field is
varchar(30).
>Is there any other ways to check for an empty varchar
field of can some of
>you guide me to what it is I'm missing in my "set
@.sql...." statement?
>Best Regards
>Steen
>
>.
>Hi
Sorry if I wasn't very clear. I assume that the purpose is to check for an
empty field. The "original" code that's being used in the DTS Transform task
is "...AND CompanyNo is not NULL and Type <>'' ". It's not me that have
written the transform task, but I assume that this last piece checks if
there're any empty fields. It might be my understanding of it that's wrong,
but then I'd be happy to hear about it.
This SQL statement runs fine in the DST task and also when I run it in Query
analyser using fixed values, but when I do it with variables/dynamic SQL it
seems to fail and not accept this last bit.
Regards
Steen
"Matthew Bando" <anonymous@.discussions.microsoft.com> skrev i en meddelelse
news:071c01c46e4c$74405700$a501280a@.phx.gbl...[vbcol=seagreen]
> What exactly do you mean by an empty field? Generally if
> your field allows null and you don't set a value into
> that field for a row, the field is set to NULL and you
> can test for this by using "type IS NULL" in your where
> clause.
> "<>" is the operator for not equals and it is not a
> singleton operator, it is a comparison operator. You
> need something on both sides of the "<>" to compare to
> each other.
> For instance, if you are looking for rows where the type
> field is not equal to a space, you could try "type <> ' '"
> I hope that this helps.
> Matthew Bando
> bandoM@.CSCTechnologies-dot-com
>
> run a dynamic SQL
> INTO '+@.db_name_dest+'.dbo.'+@.table_name+'
> FROM '+@.servername_source+'.'+@.db_name_source+'.dbo.'+@.tab
> le_name+' Where
> purpose of the last <>
> my understanding of
> Transform task, but
> and ' but it still
> varchar(30).
> field of can some of
> @.sql...." statement?|||Sorry. It was the missing second single quote I was
referring to as missing.
Aaron is correct. You need to repeat the single quotes
since they are inside of a quoted expression.

>--Original Message--
>Hi
>Sorry if I wasn't very clear. I assume that the purpose
is to check for an
>empty field. The "original" code that's being used in
the DTS Transform task
>is "...AND CompanyNo is not NULL and Type <>'' ". It's
not me that have
>written the transform task, but I assume that this last
piece checks if
>there're any empty fields. It might be my understanding
of it that's wrong,
>but then I'd be happy to hear about it.
>This SQL statement runs fine in the DST task and also
when I run it in Query
>analyser using fixed values, but when I do it with
variables/dynamic SQL it
>seems to fail and not accept this last bit.
>Regards
>Steen
>"Matthew Bando" <anonymous@.discussions.microsoft.com>
skrev i en meddelelse
>news:071c01c46e4c$74405700$a501280a@.phx.gbl...
if[vbcol=seagreen]
type[vbcol=seagreen]
<> ' '"[vbcol=seagreen]
to[vbcol=seagreen]
FROM '+@.servername_source+'.'+@.db_name_source+'.dbo.'+@.tab[vbcol=seagreen]
(that's[vbcol=seagreen]
fine.[vbcol=seagreen]
>
>.
>

Problem With Distinct clause

Hi

I have the following tables:

Suppliers:
SupplierID int not null primary key auto_increment
SubscriptionTypeID int not null
SubscriptionExpDate Date

Products:
ID int not null primary key auto_increment
SupplierID int not null as foreign key
ProductCode varchar(30) not null
productName varchar(255) not null
ImageID int not null as foreign key

Images:
ImageID int not null primary key auto_increment
Image blob not null

Whats is the correct SQL syntax to retrieve the disticnt Image and ProductName where ProductName is LIKE some user defined string and SubscriptionExpDate >= Date() order by SubscriptionTypeID DESC.

The above query will retrieve multiple records for the same Image if there are multiple suppliers for that product. How do I retrieve the distinct Image? The only other way i can think of solving this is to redesign the table and include the productName in the Images table. However all images are unique but product names supplied by the supplier for a particular image/product can be different. hence to increase the posibility of retrieving a match I have included it in the products Table!

Any help will be appreciated.what does "the distinct image" mean?

if a single product is supplied by multiple suppliers, and you have a query where you join product to suppliers, then why aren't you asking about returning "the distinct product" too?

that is where your difficulty lies ;)|||Basically I want to check if there is a product that matches the user defined search string on product name, for an item supplied by a supplier whos subcription to the database is still valid. If there is I only want to display one image and product name. Hence I want to retrieve distinct name as well. But that is not possible becuase the product name for a given product is different for each supplier yet it is for the same product. i dont think there is any other way round this than the way I metioned. By including product name in the images table aswell!

You may be wondering what is the purpose of such a query. well it is to be implemented on a web page. The above query should display all items which match the search string but onyl display unique images and any one name. At present I get duplicate images and names per row.

If you have any other suggestions I would be glad to hear them.|||here's the important part of what you just said --Basically I want to check if there is a product that matches the user defined search string on product name, for an item supplied by a supplier whos subcription to the database is still valid. If there is I only want to display one image and product name.so my question is, which one? the item with the shortest name? the supplier with the latest registration date?

to pick one from among many, you need a way to say which one

answers that are not allowed include "any one," "the first one," and "you pick one"

:)