|
The description you gave seems fine.
Is the login enabled? You could also use sp_validatelogins[^] to check that it's valid.
Also it would be good to know what is the error you get when trying to connect to the db. You could try it for example using Management Studio (or some other program that gives the original error message SQL Server throws).
|
|
|
|
|
Yeap, it is enabled (Login properties -> Status).
The procedure sp_validatelogins did return no rows. Should it return rows?
The error message that appears if I try to login:
"Cannot connect to mypc\SQLSERVER2005.
Additional Information:
Login failed for user 'domain\user'. (Microsoft SQL Server, Error: 18456)"
The error message from the server log:
"Error: 18456, Severity: 14, State: 11."
Of course I searched the www for that message. The result was that state 11 means "Valid login but server access failure". But no solution that fits...
Again the scenario:
domain\user is the user that is a member of the group (AD) domain\SqlServerUserGroup. On the database there is a login domain\SqlServerUserGroup. So I think it should work, shouldn't it? If I add a login domain\user it is working. But my intention is to maintain the access using the assignment of user to the AD group.
modified on Thursday, February 19, 2009 3:28 AM
|
|
|
|
|
Diving Flo wrote: The procedure sp_validatelogins did return no rows. Should it return rows?
No it shouldn't. If it doesn't return rows, all windows authenticated logins in SQL Server are valid in Windows domain.
Your description seems fine to me and it should work. Could you try to add the login for that group again (or perhaps a new test group), just in case that it's interpreted as local group or something else.
You could also check what you see with:
select * from syslogins
Especially check columns: name, loginname, hasaccess, isntname, isntgroup and isntuser. Those should show if there's some misinterpretation somewhere.
|
|
|
|
|
Sorry for the late reply.
Adding a new user to the original group does not make any difference. The newly added user cannot log on to the database as well.
Adding a new group to the domain is not possible. But I have about 6 different groups that I need to configure on the database. With none of them it is possible to log on.
But I did try the following: I added a local group with users of our domain. And now the users of that local group can log on to the server. Is it possible that the database is not able to request or resolve the nt group of a user that wants to log on? May be the account (local system) that is used to run the sql server has not the right priviliges?
I also checked the syslogins. Name and loginname are equal for the groups of interest. All of them have hasaccess, isntname and isntgroup set as true. Isntuser is false for those entries. Seems to be as it should be.
|
|
|
|
|
Diving Flo wrote: Adding a new user to the original group does not make any difference. The newly added user cannot log on to the database as well
That would be expected since the login is executed at group level so the problem aplies to all users in that group.
Diving Flo wrote: With none of them it is possible to log on
Ok, so it's not group specific.
Diving Flo wrote: Is it possible that the database is not able to request or resolve the nt group of a user that wants to log on? May be the account (local system) that is used to run the sql server has not the right priviliges
Oh, if you're using local system. That account may not have any access to domain services, depending on the privileges you have given to it. See: Service Account[^] and Setting Up Windows Service Accounts[^].
Also if you use Kerberos (which is recommended) instead of NTLM, you should take a look at this: http://support.microsoft.com/kb/909801[^].
|
|
|
|
|
hi,
how to trace(break point) the stored procedures
can we get values that was declared in the stored procedure..
thanks..
|
|
|
|
|
|
Hi all ,
Iam Using a query which returns a bulk of data.
first my query contains sub query to find the count so i panned to change as a function ..
So i created a function which returns a table..
create function fnname
returns table(name as varchar,....,....,...,...,...,...)
I inserted the values into the temp table from the select query and
(i want to find the count of receipt numbers so ...
i updated the query with another query update temp table set rno =(select count(rno) from table where
....)
then i executed the function but it is quite slow
please suggest me any way to increase the speed of the function or query..
how to increase the speed of the function
|
|
|
|
|
cbenan wrote: sub query
Have you tried a join?
|
|
|
|
|
It is unikely that the use of the function will affect the speed, it is more likely to be the number of times you call it. Quite often you can change the performance by totally restructuring your query. Break it down to part and do the part that reduces your data volume the most first.
Try using the execution plan to identify table scans that do not use indexes.
I have found the using table vars to hold large volumes is a disaster - I have gone back to #temp tables for anything over 2k rows. I also have no compunction about throwing an index onto a temp table.
If you have 2005 then you may want to look at rownumber and partitioning in a query to get the count by invoices.
Your question does not have 1 answer, this is where science turns into art, good luck!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
<ok thank="" u="" very="" much="" for="" ur="" reply="">
|
|
|
|
|
Typically if you break down a single query to multiple different operations using a function or a procedure, the result can be slower. Because if you have separate SQL statements there's no way for the optimizer to search for the best plan to the whole query, only for the subcomponents.
Also if you use temp tables and update and select them, you'll most likely cause physical I/O which may/may not be prevented when using a single query.
Sometimes it's just impossible to write a single query for a certain task, but if you do have the opportunity, you should test the results using this approach (create a single query, combine few operations thus eliminating intermediate steps etc).
Another thing is indexing. Make sure that you have adequate indexes and on the righ columns (or column combinations).
For further details, I think you should post the code you currently have.
|
|
|
|
|
i am bit confused about my question only please help me to get the result
i hav 3 tables
table1,table2,table3
table1
uid shiftcode startdate enddate
100 s1 xxxxxx xxxxx
101 s1 xxxxxx xxxxx
102 s1 xxxxxx xxxxx
table2
uid patterncode
103 p1
table3
uid shiftcode patterncode
103 s2 p1
i want shiftcode's of 100 to 103 users
how to get
plzz help me....
icanmakeiteasy
|
|
|
|
|
I think your question is lacking some information. Could you explain the logic of combining those rows together. Is it for example that when table1 has shiftcode s1 you want shiftcode s2 from table3 or is the idea to go through table2 and join patterncode, but then again how does pattern code relate to shiftcode in table1 etc.
|
|
|
|
|
thx for reply...
user 103 is on bench so his shift code is s2 or any other
if user got work for some days simply he moves to table1 with his working days and his work code(s1,s2,s3..etc)
just i want work code(shiftcode) of 100 to 103 users between given dates
icanmakeiteasy
modified on Tuesday, February 17, 2009 8:18 AM
|
|
|
|
|
So did I understand correctly, table1 contains info about users andtheir work periods and you want to find users having work in certain period. If that's the case, could it be something like
select ...
from table1
where startdate between periodstart and periodend
or enddate between periodstart and periodend
where the periodstart and periodend are the dates you're interested in
|
|
|
|
|
thx for reply...
srry... i am mistaken
table3 does not have uid column
table3
shiftcode patterncode
s2 p1
i want the shift code of users (100 to 103) between given periodic times
by using given query i can not get the shift code of user 103 where does not exist in the table1, so i have to get the shift code from table3, here table3 having patterncode only, now i have to get the paaterncode from table2... using that i want to show the shiftcode of user 103....
am i clear now??
icanmakeiteasy
|
|
|
|
|
it is very simple use joins and co related sub queries
|
|
|
|
|
i am little poor in joins and sub queries
can u plzz give me some idea
icanmakeiteasy
|
|
|
|
|
i got solved this problem
[^] from this forum
icanmakeiteasy
|
|
|
|
|
Merci Ashfield.
I used your solution :
SELECT ID_STUDENT, CASE WHEN (CODE) = 1 THEN 'abs' ELSE CONVERT(varchar, [121]) END AS note1,<br />
CASE WHEN (CODE) = 1 THEN 'ASD' ELSE CONVERT(varchar,<br />
[122]) END AS note3,<br />
CASE WHEN (CODE) = 1 THEN 'ASD' ELSE CONVERT(varchar, [123]) END AS note3<br />
<br />
FROM (SELECT ID_FIELD, ID_STUDENT, NOTE, CODE<br />
FROM EXAMEN) p PIVOT (sum(NOTE) FOR ID_FIELD IN ([121], [122], [123])) AS pvt<br />
<br />
<br />
I have not the problem of data type but I have the data displayed by double
Here are my data TABLE examen
Id_student---- id_feild ---- note----code
9306-------------121 ----------- 0-------1
9306-------------122---- -------4-------0
9306------------123----------- 12----- 0
9307------------121 ------------3------ 0
9307------------122------------ 0------ 1
9307------------123 ------------9------ 0
9308------------121------------ 12------0
9308------------122------------ 4------ 0
9308------------123------------ 9------0
following the requet résultatt
9306----NULL----NULL----NULL
9307----NULL----NULL----NULL
9308----NULL----NULL-----NULL
9306----NULL----4-------12
9307-----3------NULL----9
9308-----12-----4------9
9306-----ASD----ASD-----ASD
9307-----ASD----ASD-----ASD
and this is what I want
id_student---note1----note2---note3
9306---------ASD-------4---------12
9307---------3-------ASD -------9
9308--------12------4---- ------9
I dont know why please help me.
Thanks
modified on Tuesday, February 17, 2009 6:32 AM
|
|
|
|
|
Hi!
I think I have this result because we must specify
that the ID_FIELD= 121 here:CASE WHEN (CODE) = 1 THEN 'abs' ELSE CONVERT(varchar, [121]) END AS note1,
and ID_FIELD=122 here:CASE WHEN (CODE) = 1 THEN 'abs' ELSE CONVERT(varchar, [122]) END AS note2
how ? the case when accepts two conditions?
thanks!
|
|
|
|
|
I don't if I follow the logic but if you drop away the code in pivot like this:
SELECT *
FROM (SELECT ID_FIELD,
ID_STUDENT,
NOTE
FROM EXAMEN) p
PIVOT (sum(NOTE) FOR ID_FIELD IN ([121], [122], [123])) AS pvt
you will get a result as wanted where note is 0 for the rows where code is 1 (is this always true?):
ID_STUDENT 121 122 123
---------- --- --- ---
9306 0 4 12
9307 3 0 9
9308 12 4 9
Now if you replace the zeros like:
SELECT ID_STUDENT,
CASE WHEN ([121]) = 0 THEN 'abs' ELSE CONVERT(varchar, [121]) END AS note1,
CASE WHEN ([122]) = 0 THEN 'ASD' ELSE CONVERT(varchar, [122]) END AS note2,
CASE WHEN ([123]) = 0 THEN 'ASD' ELSE CONVERT(varchar, [123]) END AS note3
FROM (SELECT ID_FIELD,
ID_STUDENT,
NOTE
FROM EXAMEN) p
PIVOT (sum(NOTE) FOR ID_FIELD IN ([121], [122], [123])) AS pvt
you will get:
ID_STUDENT note1 note2 note3
---------- ----- ----- -----
9306 abs 4 12
9307 3 ASD 9
9308 12 4 9
Is this getting you any closer to the result?
|
|
|
|
|
Thank you very much Mika Wendelius you helped me a lot in this site  .
for me code = 1 means that the student is abcent so his note= 0 and I have to replace 0 by 'abc' to differentiate between a student absent and another who is present and received 0.why I used code code_absence.
I do not know if I come to explain my problem.
Thnaks.
|
|
|
|
|
It didn't come out very pretty but could you use this for somekind of starting point. The idea is that the first query gets the records where the student is absent and the second gets those where the student isn't absent. then these are joined and columns are combined:
SELECT coalesce(a.ID_STUDENT, b.ID_STUDENT) as id_student,
coalesce(a.note1, b.note1) as note1,
coalesce(a.note2, b.note2) as note2,
coalesce(a.note3, b.note3) as note3
FROM
(SELECT ID_STUDENT,
CASE WHEN ([121]) = 0 THEN 'Abs' ELSE CONVERT(varchar, [121]) END AS note1,
CASE WHEN ([122]) = 0 THEN 'Abs' ELSE CONVERT(varchar, [122]) END AS note2,
CASE WHEN ([123]) = 0 THEN 'Abs' ELSE CONVERT(varchar, [123]) END AS note3
FROM (SELECT ID_FIELD, ID_STUDENT, NOTE
FROM EXAMEN WHERE code = 1) p
PIVOT (sum(NOTE) FOR ID_FIELD IN ([121], [122], [123])) AS pvt) a
full outer join
(SELECT ID_STUDENT,
CONVERT(varchar, [121]) AS note1,
CONVERT(varchar, [122]) AS note2,
CONVERT(varchar, [123]) AS note3
FROM (SELECT ID_FIELD, ID_STUDENT, NOTE
FROM EXAMEN WHERE code = 0) p
PIVOT (sum(NOTE) FOR ID_FIELD IN ([121], [122], [123])) AS pvt) b
on a.ID_STUDENT = b.ID_STUDENT
The result is:
id_student note1 note2 note3
---------- ----- ----- -----
9306 Abs 4 12
9307 3 Abs 9
9308 12 4 9
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
