How to find Queries which makes CPU 100% ?

Hi,
There is an application which makes connection of more than 300 users
to a SQL Server 2000 DB.
Most of the times the server CPU hits the 100% for 2-3 seconds.
Since lots of queries are fired hence i'm unable to track down which
query makes the CPU 100%.
I'm using the SQL profiler (with settings on the Duration filter),
still i'm unabl;e to track down the BAD Queries.
Please suggest me a way/method/application to TRACK DOWN THE QUERIES
RESPONSIBLE TO OVERLOAD THE CPU BY 100 %.
Thanks,
~Nimit Sharma.Hi
Turn on SQL Server Profiler and filter by CPU
<nimitsharma16@.gmail.com> wrote in message
news:1189330367.686296.120070@.19g2000hsx.googlegroups.com...
> Hi,
> There is an application which makes connection of more than 300 users
> to a SQL Server 2000 DB.
> Most of the times the server CPU hits the 100% for 2-3 seconds.
> Since lots of queries are fired hence i'm unable to track down which
> query makes the CPU 100%.
> I'm using the SQL profiler (with settings on the Duration filter),
> still i'm unabl;e to track down the BAD Queries.
> Please suggest me a way/method/application to TRACK DOWN THE QUERIES
> RESPONSIBLE TO OVERLOAD THE CPU BY 100 %.
> Thanks,
> ~Nimit Sharma.
>|||Generally, it a normal behavior when some query utilizie 100% of CPU for 2-3
seconds, specially if it is using the data that are already in cache. Do you
really need to track such queries?
"nimitsharma16@.gmail.com" wrote:
> Hi,
> There is an application which makes connection of more than 300 users
> to a SQL Server 2000 DB.
> Most of the times the server CPU hits the 100% for 2-3 seconds.
> Since lots of queries are fired hence i'm unable to track down which
> query makes the CPU 100%.
> I'm using the SQL profiler (with settings on the Duration filter),
> still i'm unabl;e to track down the BAD Queries.
> Please suggest me a way/method/application to TRACK DOWN THE QUERIES
> RESPONSIBLE TO OVERLOAD THE CPU BY 100 %.
> Thanks,
> ~Nimit Sharma.
>

How to find out Usersinfo using JDBC Connection to SQL Server

Hi..
I want to find out the List of users created in SQL Server.
Im using type 4 Driver to Connect SQL Server.
Regards
maruthi.List of active users, users for a database or logins for the sql server ?|||select * from master.dbo.syslogins
select * from master.dbo.sysxlogins
select * from your_database.dbo.sysusers|||Thanks

the problem is solved.

How to find out the job/report that is currently running?

How do I find out the report that is currently being run? I have a scenario where some users run a report without knowing the amount of data that will be pulled by it... In such a case, it bogs down the server and doesn't let me access the Report Manager application.

How can I find out what is the report that is currently being run? I know I can get that information from the Show Jobs link fromm Site Settings in Report Manager. But in this case, I am not even able to login to the Report Manager. The memory (8 gigs) in the app server is fully maxed out and it doesn't log me in to the Report Manger.

I ran a query against the ExecutionLog table in the ReportServer database that logs all the executions. But it looks like the data to this table gets logged only after the report execution is completed. There is no way to tell what is being executed right now. I also checked the table RunningJobs in the ReportServer database, but there are no records in that table.

Can someone help?

Thanks.

can someone help?|||

I am unable to find information on the web related to this. Can someone help?

Thanks for your response.

|||In the ReportServer database there is a RunningJobs table which hold this data.|||I have mentioned in my post that this table did not contain any records when I checked. Any possible scenarios/cases as to when such a thing could occur?

How to find out the job/report that is currently running?

How do I find out the report that is currently being run? I have a scenario where some users run a report without knowing the amount of data that will be pulled by it... In such a case, it bogs down the server and doesn't let me access the Report Manager application.

How can I find out what is the report that is currently being run? I know I can get that information from the Show Jobs link fromm Site Settings in Report Manager. But in this case, I am not even able to login to the Report Manager. The memory (8 gigs) in the app server is fully maxed out and it doesn't log me in to the Report Manger.

I ran a query against the ExecutionLog table in the ReportServer database that logs all the executions. But it looks like the data to this table gets logged only after the report execution is completed. There is no way to tell what is being executed right now. I also checked the table RunningJobs in the ReportServer database, but there are no records in that table.

Can someone help?

Thanks.

can someone help?|||

I am unable to find information on the web related to this. Can someone help?

Thanks for your response.

|||In the ReportServer database there is a RunningJobs table which hold this data.|||I have mentioned in my post that this table did not contain any records when I checked. Any possible scenarios/cases as to when such a thing could occur?

How to find matching profiles?

Users have to answer 17 simple yes/no questions and the answers are stored in an column for each question as tinyint 0/1 values.

At least that's what seems reasonable to me at the moment.

The table is under my control so I could change it if needed.

Now from several tenthousend or maybe hundreds of thousends of entries I need to find those with the closest match. Of course, I need all of the entries that have the exact same answers and this is no problem. But - at least if there are not enough full matches - then I need all records that have maybe 16,15,14... matches out of the 17 answers.

I have not yet the idea on how to handle this without quering 17*16 different answer schemes.

Hi,

I wouldn′t store the data denormalized. The better way to store it IMHO is to normalize the data, if you want closer machtes you can also setup a score for each answered question: Here is an extract of a possible solution:

CREATE TABLE Question
(
QuestionId INT
QuestionText VARCHAR(100)
)

CREATE TABLE Anwers
(
QuestionId INT
AnswerId INT
AnswerValue VARCHAR(10) --indicates the right answer
Score INT
)

CREATE TABLE AnweredQuestions
(
QuestionId INT
AnswerId INT
ParticipantId INT
AnswerValue
)

Thats just a quick one, could be sure more normalized, but these tables could be easy joined and scored as well in one query rather than using the different columns and its even more extensible than your current one.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||I actually have set up the tables more close to what you recommended, like this and then of course the user table. I first have to do some frontenmd stuff and then will come back to see how to handle this.

Actually there are no right or wrong answers since questions have the form like: Iwrite down what I need to buy before I go shopping. true/false.

CREATE TABLE [dbo].[tCmsElementCustomPartnermatchQuestion] (
[question_id] [int] IDENTITY (1, 1) NOT NULL ,
[question_categoryID] [int] NOT NULL ,
[question_weight] [int] NULL ,
[question_text_DE] [nvarchar] (255) ,
[question_text_FR] [nvarchar] (255) ,
[question_text_IT] [nvarchar] (255) ,
[question_active] [tinyint] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tCmsElementCustomPartnermatchQuestionUser] (
[question_user_id] [int] IDENTITY (1, 1) NOT NULL ,
[question_user_userID] [int] NOT NULL ,
[question_user_questionID] [int] NOT NULL ,
[question_user_questionanswer] [int] NOT NULL
) ON [PRIMARY]
GO|||

Hi,

glad to hear that you picked up some ideas. There sure could be more normalized (but I do not want to exaggerate :-) ). Come back if you have any more questions, you are welcome :-)

I keep an eye on the post I answered, anyway if I overlook your answer or rerequest, feel free to contact me through my website which is mentioned below.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||thanks, very friendly.

I actually I became totally lost.

I received a hint from my boss but this one needs a totally denormalized table and only allows answers as yes or no.

Something like this:

From this kind of table

CREATE TABLE #test (

[person_id] [smallint] IDENTITY(1,1),
[person_name] [varchar] (20),
[answer001] [tinyint],
[answer002] [tinyint],
[answer003] [tinyint],
[answer004] [tinyint],
[answer005] [tinyint],
[answer006] [tinyint],
[answer007] [tinyint],
[answer008] [tinyint],
[answer009] [tinyint]
)

I should do a query like this:

SELECT t1.person_name AS p1_name, t2.person_name AS p2_name
, '''' AS person_rank
, t1.answer009*1+t1.answer008*2+t1.answer007*4+t1.answer006*8+t1.answer005*16+t1.answer004*32+t1.answer003*64+t1.answer002*128+t1.answer001*256 AS p1_value
, t2.answer009*1+t2.answer008*2+t2.answer007*4+t2.answer006*8+t2.answer005*16+t2.answer004*32+t2.answer003*64+t2.answer002*128+t2.answer001*256 AS p2_value

FROM #test t1, #test t2
WHERE t1.person_id <> t2.person_id
AND t1.person_name = 'Fritz' AND t2.person_name <> t1.person_name

and then find matches with something like this (in ColdFusion since we couldn't find the respectively SQL functions)

<cfoutput query="qgetmatchesprodandtype">
<cfset tmp = QuerySetCell(qTest, 'person_rank', 9- Len(Replace(FormatBaseN(BitXor(qTest.p1_value,qTest.p2_value),2), "0", "", "ALL")), qTest.CurrentRow)>
</cfoutput>

So, I could really need some enlightening examples

I'd prefer to match answers by counting the number of matches for each question and user undependently if the answer can only be 0/1 or any value between 0-9 (ore anything else)|||I might look at doing it this way:

Let's assume that you have a table of traits of people and you have a candidate and want to find the closest matches.... Kind of like a dating service.

If I take the absolute value of (person1.trait1 - person2.trait1) then if that is 0 they are a match on that trait, if it is 1 then they are not a match....

It follows that if I sum up the abs values of the subtracted trait pairs then the lower the overall sum the more "compatible" the two individual are:

That would lead me to look at:

Select
p1.name,
p2.name,
Sum(
abs(p1.trait1 - p2.trait1) +
abs(p1.trait2 - p2.trait2) +
abs(p1.trait3 - p2.trait3) +
.....
abs(p1.trait17 - p2.trait17)
) As matchfactor
From members as p1, members.p2
Where p1.id <> p2.id And p1.name = 'smith'
Order by matchfactor

|||

Jens Sü?meyers answer below exactly answered my problem if I added a
ORDER BY Numberofmatches DESC

the following query will solve your described problem:

SELECT

t2.[question_user_UserId],COUNT(*) AS Numberofmatches

FROM [dbo].[tCmsElementCustomPartnermatchQuestionUser] t1

INNER JOIN

tCmsElementCustomPartnermatchQuestionUser t2

ON t1.[question_user_questionID] = t2.[question_user_questionID] AND

t1.[question_user_questionanswer] = t2.[question_user_questionanswer] AND NOT

t1.[question_user_userid] = t2.[question_user_userid] --to eliminate the actual user which has the best match with himself :-)

Where T1.[question_user_userid] = 15

Group by t2.[question_user_UserId]

Let me know if that worked for you.

-Jens.

How to find matching profiles?

Users have to answer 17 simple yes/no questions and the answers are stored in an column for each question as tinyint 0/1 values.

At least that's what seems reasonable to me at the moment.

The table is under my control so I could change it if needed.

Now from several tenthousend or maybe hundreds of thousends of entries I need to find those with the closest match. Of course, I need all of the entries that have the exact same answers and this is no problem. But - at least if there are not enough full matches - then I need all records that have maybe 16,15,14... matches out of the 17 answers.

I have not yet the idea on how to handle this without quering 17*16 different answer schemes.

Hi,

I wouldn′t store the data denormalized. The better way to store it IMHO is to normalize the data, if you want closer machtes you can also setup a score for each answered question: Here is an extract of a possible solution:

CREATE TABLE Question
(
QuestionId INT
QuestionText VARCHAR(100)
)

CREATE TABLE Anwers
(
QuestionId INT
AnswerId INT
AnswerValue VARCHAR(10) --indicates the right answer
Score INT
)

CREATE TABLE AnweredQuestions
(
QuestionId INT
AnswerId INT
ParticipantId INT
AnswerValue
)

Thats just a quick one, could be sure more normalized, but these tables could be easy joined and scored as well in one query rather than using the different columns and its even more extensible than your current one.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||I actually have set up the tables more close to what you recommended, like this and then of course the user table. I first have to do some frontenmd stuff and then will come back to see how to handle this.

Actually there are no right or wrong answers since questions have the form like: Iwrite down what I need to buy before I go shopping. true/false.

CREATE TABLE [dbo].[tCmsElementCustomPartnermatchQuestion] (
[question_id] [int] IDENTITY (1, 1) NOT NULL ,
[question_categoryID] [int] NOT NULL ,
[question_weight] [int] NULL ,
[question_text_DE] [nvarchar] (255) ,
[question_text_FR] [nvarchar] (255) ,
[question_text_IT] [nvarchar] (255) ,
[question_active] [tinyint] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tCmsElementCustomPartnermatchQuestionUser] (
[question_user_id] [int] IDENTITY (1, 1) NOT NULL ,
[question_user_userID] [int] NOT NULL ,
[question_user_questionID] [int] NOT NULL ,
[question_user_questionanswer] [int] NOT NULL
) ON [PRIMARY]
GO|||

Hi,

glad to hear that you picked up some ideas. There sure could be more normalized (but I do not want to exaggerate :-) ). Come back if you have any more questions, you are welcome :-)

I keep an eye on the post I answered, anyway if I overlook your answer or rerequest, feel free to contact me through my website which is mentioned below.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||thanks, very friendly.

I actually I became totally lost.

I received a hint from my boss but this one needs a totally denormalized table and only allows answers as yes or no.

Something like this:

From this kind of table

CREATE TABLE #test (

[person_id] [smallint] IDENTITY(1,1),
[person_name] [varchar] (20),
[answer001] [tinyint],
[answer002] [tinyint],
[answer003] [tinyint],
[answer004] [tinyint],
[answer005] [tinyint],
[answer006] [tinyint],
[answer007] [tinyint],
[answer008] [tinyint],
[answer009] [tinyint]
)

I should do a query like this:

SELECT t1.person_name AS p1_name, t2.person_name AS p2_name
, '''' AS person_rank
, t1.answer009*1+t1.answer008*2+t1.answer007*4+t1.answer006*8+t1.answer005*16+t1.answer004*32+t1.answer003*64+t1.answer002*128+t1.answer001*256 AS p1_value
, t2.answer009*1+t2.answer008*2+t2.answer007*4+t2.answer006*8+t2.answer005*16+t2.answer004*32+t2.answer003*64+t2.answer002*128+t2.answer001*256 AS p2_value

FROM #test t1, #test t2
WHERE t1.person_id <> t2.person_id
AND t1.person_name = 'Fritz' AND t2.person_name <> t1.person_name

and then find matches with something like this (in ColdFusion since we couldn't find the respectively SQL functions)

<cfoutput query="qgetmatchesprodandtype">
<cfset tmp = QuerySetCell(qTest, 'person_rank', 9- Len(Replace(FormatBaseN(BitXor(qTest.p1_value,qTest.p2_value),2), "0", "", "ALL")), qTest.CurrentRow)>
</cfoutput>

So, I could really need some enlightening examples

I'd prefer to match answers by counting the number of matches for each question and user undependently if the answer can only be 0/1 or any value between 0-9 (ore anything else)|||I might look at doing it this way:

Let's assume that you have a table of traits of people and you have a candidate and want to find the closest matches.... Kind of like a dating service.

If I take the absolute value of (person1.trait1 - person2.trait1) then if that is 0 they are a match on that trait, if it is 1 then they are not a match....

It follows that if I sum up the abs values of the subtracted trait pairs then the lower the overall sum the more "compatible" the two individual are:

That would lead me to look at:

Select
p1.name,
p2.name,
Sum(
abs(p1.trait1 - p2.trait1) +
abs(p1.trait2 - p2.trait2) +
abs(p1.trait3 - p2.trait3) +
.....
abs(p1.trait17 - p2.trait17)
) As matchfactor
From members as p1, members.p2
Where p1.id <> p2.id And p1.name = 'smith'
Order by matchfactor

|||

Jens Sü?meyers answer below exactly answered my problem if I added a
ORDER BY Numberofmatches DESC

the following query will solve your described problem:

SELECT

t2.[question_user_UserId],COUNT(*) AS Numberofmatches

FROM [dbo].[tCmsElementCustomPartnermatchQuestionUser] t1

INNER JOIN

tCmsElementCustomPartnermatchQuestionUser t2

ON t1.[question_user_questionID] = t2.[question_user_questionID] AND

t1.[question_user_questionanswer] = t2.[question_user_questionanswer] AND NOT

t1.[question_user_userid] = t2.[question_user_userid] --to eliminate the actual user which has the best match with himself :-)

Where T1.[question_user_userid] = 15

Group by t2.[question_user_UserId]

Let me know if that worked for you.

-Jens.

How to find group user login Name

Our system administrator set up an NT server group in order to allow
our users to login to our application via https to our sql server.
The group appears as a User in SQL Server when you look at it in
Enterprise Manager. That said, I can not see the users associated
with the group from Enterprise Manager, but know they can login to the
database.
The problem is this. When we login via the web we get access to the
database without problem, but when you look at the current_user what
you see is the login Name the user entered and NOT the name of the
group/User. That is to say, I can see a UserID which is not listed as
a User in SQL Server and can't see the name of the group, which is
listed as a user in SQL Server.
I need to know who's logging in order to direct them to the
appropriate web page via their role. Before the admin set up the
group, I was using sp_helpuser to get the role, but then again I had
the userID to do this.
The question I have now, is there any way to see what the
group/user is who logged in i.e. the goup listed as the User in
Enterprise Manager? Otherwise I have to build a table of userIDs and
their group/User name, which seems to defeat the purpose of having he
server authenticate users.

Thanks,
Tom"Tom Loach" <tcl4p@.virginia.edu> wrote in message
news:o7vttvktkbdbib39pqklga3f8s9lgdsksc@.4ax.com...
> Our system administrator set up an NT server group in order to allow
> our users to login to our application via https to our sql server.
> The group appears as a User in SQL Server when you look at it in
> Enterprise Manager. That said, I can not see the users associated
> with the group from Enterprise Manager, but know they can login to the
> database.
> The problem is this. When we login via the web we get access to the
> database without problem, but when you look at the current_user what
> you see is the login Name the user entered and NOT the name of the
> group/User. That is to say, I can see a UserID which is not listed as
> a User in SQL Server and can't see the name of the group, which is
> listed as a user in SQL Server.
> I need to know who's logging in order to direct them to the
> appropriate web page via their role. Before the admin set up the
> group, I was using sp_helpuser to get the role, but then again I had
> the userID to do this.
> The question I have now, is there any way to see what the
> group/user is who logged in i.e. the goup listed as the User in
> Enterprise Manager? Otherwise I have to build a table of userIDs and
> their group/User name, which seems to defeat the purpose of having he
> server authenticate users.
> Thanks,
> Tom

I'm not sure I understand your setup completely, but if you use CURRENT_USER
you will see the user name in the current database, not the login name. If
you use SYSTEM_USER instead, you should see the user's login - does this
give the result you expect?

If not, then perhaps you could clarify the sequence of events used to set up
the group, espeically the difference between a server login and a database
user - this is what's not entirely clear from your post above. If you can
provide the actual SQL commands used to set up server access and permissions
for the Windows group, that would be the least ambiguous description,
although of course you may well have done it through Enterprise Manager
instead.

Simon|||Simon,
thanks for your suggestion, but alas it did not work. Iam sorrry
I was not able to make the situation more clear, but perhaps another
try.
We have a group account set up, which if you look at in Enterprise
manager appears as a User i.e. a_Group. Obviously, since this is a
group account it is comprised of multiple users who need access to SQL
Server. The problem we're having is, if you login to the system and
if I run a stored procedure that returns the Current_User, what I get
is not the name of the User i.e. a_Group that appears in Enterprise
Manager, but the UserID i.e. h3tcl that was used to login to the
Windows server.
When I tried to exec sp_helpuser using Current_User I get an error
since SQL Server does not know who the user is (it sees h3tcl). If I
exec sp_helpuser and enter the user name (a_Group) found in
Enterprise manager sp_helpuser works fine and returns the appropriate
data from SQL Server.
So I'm back to the problem is how and where do I look for the group
name? If you have any other ideas I'm more than open. Thanks again
for your input.

Regards,
Tom

On Tue, 16 Dec 2003 16:44:21 +0100, "Simon Hayes" <sql@.hayes.ch>
wrote:

>"Tom Loach" <tcl4p@.virginia.edu> wrote in message
>news:o7vttvktkbdbib39pqklga3f8s9lgdsksc@.4ax.com...
>> Our system administrator set up an NT server group in order to allow
>> our users to login to our application via https to our sql server.
>> The group appears as a User in SQL Server when you look at it in
>> Enterprise Manager. That said, I can not see the users associated
>> with the group from Enterprise Manager, but know they can login to the
>> database.
>> The problem is this. When we login via the web we get access to the
>> database without problem, but when you look at the current_user what
>> you see is the login Name the user entered and NOT the name of the
>> group/User. That is to say, I can see a UserID which is not listed as
>> a User in SQL Server and can't see the name of the group, which is
>> listed as a user in SQL Server.
>> I need to know who's logging in order to direct them to the
>> appropriate web page via their role. Before the admin set up the
>> group, I was using sp_helpuser to get the role, but then again I had
>> the userID to do this.
>> The question I have now, is there any way to see what the
>> group/user is who logged in i.e. the goup listed as the User in
>> Enterprise Manager? Otherwise I have to build a table of userIDs and
>> their group/User name, which seems to defeat the purpose of having he
>> server authenticate users.
>>
>> Thanks,
>> Tom
>I'm not sure I understand your setup completely, but if you use CURRENT_USER
>you will see the user name in the current database, not the login name. If
>you use SYSTEM_USER instead, you should see the user's login - does this
>give the result you expect?
>If not, then perhaps you could clarify the sequence of events used to set up
>the group, espeically the difference between a server login and a database
>user - this is what's not entirely clear from your post above. If you can
>provide the actual SQL commands used to set up server access and permissions
>for the Windows group, that would be the least ambiguous description,
>although of course you may well have done it through Enterprise Manager
>instead.
>Simon

How to find and delete orphan users in all db on server?

Hi
I need help ;)
How to find orphan users in all databases on sql server 2000 and delete
them in one script or in any automated or semi automated way ?
I try do it using sp_MSforeachdb sp_change_users_login 'Report' but i
did't succeded. Probably i'm not experienced enough to complete this
task by myself.
Maybe you know sites with such a usfull scripts ?
Thanks
M.
See, if the queries from my article help:
http://vyaskn.tripod.com/troubleshoo...phan_users.htm
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"marta" <grupy_d@.go2.pl> wrote in message
news:1123684712.782296.155920@.g47g2000cwa.googlegr oups.com...
> Hi
> I need help ;)
> How to find orphan users in all databases on sql server 2000 and delete
> them in one script or in any automated or semi automated way ?
> I try do it using sp_MSforeachdb sp_change_users_login 'Report' but i
> did't succeded. Probably i'm not experienced enough to complete this
> task by myself.
> Maybe you know sites with such a usfull scripts ?
> Thanks
> M.
>

How to find and delete orphan users in all db on server?

Hi
I need help ;)
How to find orphan users in all databases on sql server 2000 and delete
them in one script or in any automated or semi automated way ?
I try do it using sp_MSforeachdb sp_change_users_login 'Report' but i
did't succeded. Probably i'm not experienced enough to complete this
task by myself.
Maybe you know sites with such a usfull scripts ?
Thanks
M.See, if the queries from my article help:
http://vyaskn.tripod.com/troubleshooting_orphan_users.htm
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"marta" <grupy_d@.go2.pl> wrote in message
news:1123684712.782296.155920@.g47g2000cwa.googlegroups.com...
> Hi
> I need help ;)
> How to find orphan users in all databases on sql server 2000 and delete
> them in one script or in any automated or semi automated way ?
> I try do it using sp_MSforeachdb sp_change_users_login 'Report' but i
> did't succeded. Probably i'm not experienced enough to complete this
> task by myself.
> Maybe you know sites with such a usfull scripts ?
> Thanks
> M.
>

How to find and delete orphan users in all db on server?

Hi
I need help ;)
How to find orphan users in all databases on sql server 2000 and delete
them in one script or in any automated or semi automated way ?
I try do it using sp_MSforeachdb sp_change_users_login 'Report' but i
did't succeded. Probably i'm not experienced enough to complete this
task by myself.
Maybe you know sites with such a usfull scripts ?
Thanks
M.See, if the queries from my article help:
http://vyaskn.tripod.com/troublesho...rphan_users.htm
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"marta" <grupy_d@.go2.pl> wrote in message
news:1123684712.782296.155920@.g47g2000cwa.googlegroups.com...
> Hi
> I need help ;)
> How to find orphan users in all databases on sql server 2000 and delete
> them in one script or in any automated or semi automated way ?
> I try do it using sp_MSforeachdb sp_change_users_login 'Report' but i
> did't succeded. Probably i'm not experienced enough to complete this
> task by myself.
> Maybe you know sites with such a usfull scripts ?
> Thanks
> M.
>

how to filter output based on user's AD group membership?

I want to filter reports based on a users active directory security i.e. what
parameters they can select, what columns they can see
But I can't figure out the best way of doing this and the documentation on
AD seems to assume that the user already understands AD.
From what I can tell I have two options
1) use sql with the openquery syntax - don't think this is going to be an
option due to the hassle here of setting up linked servers (bureaucracy in
the extreme)
2)use system.directoryservices with some code built into to the report - i
don't really understand how to do this, none of the code samples i have seen
seem to do what I want e.g. pass in username from global report parameters
along with the groupname i want to check against, and return whether they are
in that particular group as true/false
help greatly appreciated!
do you know a better way of doing this? code samples? etc
thanks!AD queries in SQL Server using the ADSI can be a problem... I am told it will
only search the first 1000 rows returned by the AD..
if you are using SQL 2005... Take a look at the sys.login_token
It shows all of the AD groups the user is a member of.
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"adolf garlic" wrote:
> I want to filter reports based on a users active directory security i.e. what
> parameters they can select, what columns they can see
> But I can't figure out the best way of doing this and the documentation on
> AD seems to assume that the user already understands AD.
> From what I can tell I have two options
> 1) use sql with the openquery syntax - don't think this is going to be an
> option due to the hassle here of setting up linked servers (bureaucracy in
> the extreme)
> 2)use system.directoryservices with some code built into to the report - i
> don't really understand how to do this, none of the code samples i have seen
> seem to do what I want e.g. pass in username from global report parameters
> along with the groupname i want to check against, and return whether they are
> in that particular group as true/false
> help greatly appreciated!
> do you know a better way of doing this? code samples? etc
> thanks!

How to Filter Expression in Data Region

how do i filter any expression in the data region
e.g. i want all users for just New York?
how to apply that filter condition in the expression in data region?See BOL:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rshowto/htm/hrs_designer_v1_3diq.asp
Example:
Filter expression: =Fields!City.Value
Operator: =Value expression: ="New York" or just New York (because it is a string)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Raj" <Raj@.discussions.microsoft.com> wrote in message
news:D5F8D505-BC13-4F20-887A-62BC1BF2A669@.microsoft.com...
> how do i filter any expression in the data region
> e.g. i want all users for just New York?
> how to apply that filter condition in the expression in data region?|||Sorry, but based on your questions I thought you want to filter data.
However, you actually want to calculate some aggregates. Try this
expression:
=Count( iif(Fields!City.Value = "New York", 1, Nothing) )
The count aggregate also takes a scope parameter, so depending on where you
use this expression in the report, you might need to add a scope for the
aggregate (e.g. the dataset name or containing reportitem name). E.g.:
=Count( iif(Fields!City.Value = "New York", 1, Nothing), "DataSet1" )
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Raj" <Raj@.discussions.microsoft.com> wrote in message
news:7031923E-DD39-4A21-9721-F9CEF898D707@.microsoft.com...
> hi robert
> i am still confused
> i have a field but i dont want to use filter on the fields.city.value
> i want to count users where city is new york
> 1 field in my table is userid
> 1 field is their residing city
> what shall i do?
> "Robert Bruckner [MSFT]" wrote:
> > See BOL:
> >
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rshowto/htm/hrs_designer_v1_3diq.asp
> >
> > Example:
> > Filter expression: =Fields!City.Value
> > Operator: => > Value expression: ="New York" or just New York (because it is a string)
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> > "Raj" <Raj@.discussions.microsoft.com> wrote in message
> > news:D5F8D505-BC13-4F20-887A-62BC1BF2A669@.microsoft.com...
> > > how do i filter any expression in the data region
> > > e.g. i want all users for just New York?
> > > how to apply that filter condition in the expression in data region?
> >
> >
> >|||i want to count the same thing
but count(nuserid) which is userid
in a text box in table
how will the box understand using this query
=Count( iif(Fields!City.Value = "New York", 1, Nothing), "DataSet1" )
that i want to count userid?
i dont have any parameters in my report
"Robert Bruckner [MSFT]" wrote:
> Sorry, but based on your questions I thought you want to filter data.
> However, you actually want to calculate some aggregates. Try this
> expression:
> =Count( iif(Fields!City.Value = "New York", 1, Nothing) )
> The count aggregate also takes a scope parameter, so depending on where you
> use this expression in the report, you might need to add a scope for the
> aggregate (e.g. the dataset name or containing reportitem name). E.g.:
> =Count( iif(Fields!City.Value = "New York", 1, Nothing), "DataSet1" )
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Raj" <Raj@.discussions.microsoft.com> wrote in message
> news:7031923E-DD39-4A21-9721-F9CEF898D707@.microsoft.com...
> > hi robert
> > i am still confused
> > i have a field but i dont want to use filter on the fields.city.value
> > i want to count users where city is new york
> > 1 field in my table is userid
> > 1 field is their residing city
> > what shall i do?
> >
> > "Robert Bruckner [MSFT]" wrote:
> >
> > > See BOL:
> > >
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rshowto/htm/hrs_designer_v1_3diq.asp
> > >
> > > Example:
> > > Filter expression: =Fields!City.Value
> > > Operator: => > > Value expression: ="New York" or just New York (because it is a string)
> > >
> > > --
> > > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> > >
> > > "Raj" <Raj@.discussions.microsoft.com> wrote in message
> > > news:D5F8D505-BC13-4F20-887A-62BC1BF2A669@.microsoft.com...
> > > > how do i filter any expression in the data region
> > > > e.g. i want all users for just New York?
> > > > how to apply that filter condition in the expression in data region?
> > >
> > >
> > >
>
>