query assistance: users

Showing posts with label users. Show all posts

Wednesday, March 28, 2012

Query Help

Here's a brain-twister that I'm having a tough time wrapping my head around.
I have 3 tables. A Users table. A Committee table. And a
User_mm_Committee table (many-to-many join). Users can serve on multiple
committees, and of course committees have many users. Each User serves on a
Committee for a specified Term, at which point they are either re-elected or
replaced. I want to produce a Query that shows me this - a record for each
vacancy that will be created in the next 60 days (a term expires). An
example of some records returned by the query:
Name | Committee | Term Expiration Date
John Doe | City Committee | 6-30-06
Jane Doe | Green County Board | 7-15-06
Here's the pertinant table info.
Table: User
UserID (PK)
Name, etc.
Table: Committee
CommitteeID (PK)
CommitteeMembers (number of members committee needs to have at all times)
CommitteeTermLength (length of term in years)
Table: User_mm_Committee
CommitteeID (PK)
UserID (FK)
CommitteeID (FK)
TermBeginDate
TermEndDate
Thanks for any help!Now that I look at my own post it looks easy. Just query term end date for
terms ending in the next 60 days. The problem I'm having though is what if
those users have been re-elected to another term or another person has
already been selected to replace them? Perhaps I need another field. Maybe
a "replaced (boolean)" field in the User_mm_committee table that is set to
true when a user is re-elected or replaced? Is there any other way to do
it?
"Ryan" <Tyveil@.newsgroups.nospam> wrote in message
news:ekmGO0aiGHA.3848@.TK2MSFTNGP04.phx.gbl...
> Here's a brain-twister that I'm having a tough time wrapping my head
> around.
> I have 3 tables. A Users table. A Committee table. And a
> User_mm_Committee table (many-to-many join). Users can serve on multiple
> committees, and of course committees have many users. Each User serves on
> a Committee for a specified Term, at which point they are either
> re-elected or replaced. I want to produce a Query that shows me this - a
> record for each vacancy that will be created in the next 60 days (a term
> expires). An example of some records returned by the query:
> Name | Committee | Term Expiration Date
> John Doe | City Committee | 6-30-06
> Jane Doe | Green County Board | 7-15-06
> Here's the pertinant table info.
> Table: User
> UserID (PK)
> Name, etc.
> Table: Committee
> CommitteeID (PK)
> CommitteeMembers (number of members committee needs to have at all times)
> CommitteeTermLength (length of term in years)
> Table: User_mm_Committee
> CommitteeID (PK)
> UserID (FK)
> CommitteeID (FK)
> TermBeginDate
> TermEndDate
> Thanks for any help!
>|||Now that I look at my own post it looks easy. Just query term end date for
terms ending in the next 60 days. The problem I'm having though is what if
those users have been re-elected to another term or another person has
already been selected to replace them? Perhaps I need another field. Maybe
a "replaced (boolean)" field in the User_mm_committee table that is set to
true when a user is re-elected or replaced? Is there any other way to do
it?
"Ryan" <Tyveil@.newsgroups.nospam> wrote in message
news:ekmGO0aiGHA.3848@.TK2MSFTNGP04.phx.gbl...
> Here's a brain-twister that I'm having a tough time wrapping my head
> around.
> I have 3 tables. A Users table. A Committee table. And a
> User_mm_Committee table (many-to-many join). Users can serve on multiple
> committees, and of course committees have many users. Each User serves on
> a Committee for a specified Term, at which point they are either
> re-elected or replaced. I want to produce a Query that shows me this - a
> record for each vacancy that will be created in the next 60 days (a term
> expires). An example of some records returned by the query:
> Name | Committee | Term Expiration Date
> John Doe | City Committee | 6-30-06
> Jane Doe | Green County Board | 7-15-06
> Here's the pertinant table info.
> Table: User
> UserID (PK)
> Name, etc.
> Table: Committee
> CommitteeID (PK)
> CommitteeMembers (number of members committee needs to have at all times)
> CommitteeTermLength (length of term in years)
> Table: User_mm_Committee
> CommitteeID (PK)
> UserID (FK)
> CommitteeID (FK)
> TermBeginDate
> TermEndDate
> Thanks for any help!
>|||Try:
select
u.Name
, c.CommitteeName -- assumes this column exists
, uc.TermEndDate
from
Users u
join
User_mm_Committee uc on u uc.UserID = u.UserID
join
Committees c on c.CommitteeId = uc.CommitteeID
where
uc.TermEndDate < dateadd (dd, 60, getdate())
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Ryan" <Tyveil@.newsgroups.nospam> wrote in message
news:ekmGO0aiGHA.3848@.TK2MSFTNGP04.phx.gbl...
Here's a brain-twister that I'm having a tough time wrapping my head around.
I have 3 tables. A Users table. A Committee table. And a
User_mm_Committee table (many-to-many join). Users can serve on multiple
committees, and of course committees have many users. Each User serves on a
Committee for a specified Term, at which point they are either re-elected or
replaced. I want to produce a Query that shows me this - a record for each
vacancy that will be created in the next 60 days (a term expires). An
example of some records returned by the query:
Name | Committee | Term Expiration Date
John Doe | City Committee | 6-30-06
Jane Doe | Green County Board | 7-15-06
Here's the pertinant table info.
Table: User
UserID (PK)
Name, etc.
Table: Committee
CommitteeID (PK)
CommitteeMembers (number of members committee needs to have at all times)
CommitteeTermLength (length of term in years)
Table: User_mm_Committee
CommitteeID (PK)
UserID (FK)
CommitteeID (FK)
TermBeginDate
TermEndDate
Thanks for any help!|||Thanks for the help, Tom. I think I can get this to work now.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:upaed5aiGHA.2220@.TK2MSFTNGP05.phx.gbl...
> Try:
> select
> u.Name
> , c.CommitteeName -- assumes this column exists
> , uc.TermEndDate
> from
> Users u
> join
> User_mm_Committee uc on u uc.UserID = u.UserID
> join
> Committees c on c.CommitteeId = uc.CommitteeID
> where
> uc.TermEndDate < dateadd (dd, 60, getdate())
>
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Ryan" <Tyveil@.newsgroups.nospam> wrote in message
> news:ekmGO0aiGHA.3848@.TK2MSFTNGP04.phx.gbl...
> Here's a brain-twister that I'm having a tough time wrapping my head
> around.
> I have 3 tables. A Users table. A Committee table. And a
> User_mm_Committee table (many-to-many join). Users can serve on multiple
> committees, and of course committees have many users. Each User serves on
> a
> Committee for a specified Term, at which point they are either re-elected
> or
> replaced. I want to produce a Query that shows me this - a record for
> each
> vacancy that will be created in the next 60 days (a term expires). An
> example of some records returned by the query:
> Name | Committee | Term Expiration Date
> John Doe | City Committee | 6-30-06
> Jane Doe | Green County Board | 7-15-06
> Here's the pertinant table info.
> Table: User
> UserID (PK)
> Name, etc.
> Table: Committee
> CommitteeID (PK)
> CommitteeMembers (number of members committee needs to have at all times)
> CommitteeTermLength (length of term in years)
> Table: User_mm_Committee
> CommitteeID (PK)
> UserID (FK)
> CommitteeID (FK)
> TermBeginDate
> TermEndDate
> Thanks for any help!
>sql

Query Help

To start off i have a database table that consists of "profiles", another
that consists of "users" and finally one that consists of "exclusions",
these are defined in the DDL below. I am trying to select all the profiles
minus any exclusions that are set up for that user but i keep getting
duplicate entries for profiles that aren't excluded and single entries for
profiles that are excluded.

IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME
= 'Users') DROP TABLE Users
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME
= 'User_Profile_Exclusions') DROP TABLE User_Profile_Exclusions
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME
= 'Profiles') DROP TABLE Profiles

CREATE TABLE Users
(
ID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
NAME VARCHAR(50) NOT NULL
)

CREATE TABLE User_Profile_Exclusions
(
ID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
User_ID INT NOT NULL,
Profile_ID INT NOT NULL
)

CREATE TABLE Profiles
(
ID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
Name VARCHAR(50) NOT NULL
)

INSERT INTO Users (Name) VALUES ('Bob')

INSERT INTO Profiles (Name) VALUES ('Microsoft')
INSERT INTO Profiles (Name) VALUES ('Intel')
INSERT INTO Profiles (Name) VALUES ('IBM')
INSERT INTO Profiles (Name) VALUES ('Sony')

INSERT INTO User_Profile_Exclusions (User_ID,Profile_ID) VALUES (1,3)
INSERT INTO User_Profile_Exclusions (User_ID,Profile_ID) VALUES (1,4)

SELECT P.*
FROM Profiles as P
JOIN User_Profile_Exclusions AS UPE ON UPE.Profile_ID <> P.ID
WHERE UPE.User_ID = 1

The results i'm expecting to get back should look something like this:

ID Name
------
1 Microsoft
2 Intel

Instead i'm getting back:

ID Name
------
1 Microsoft
2 Intel
4 Sony
1 Microsoft
2 Intel
3 IBMSELECT P.id, P.name
FROM Profiles AS P
WHERE NOT EXISTS
(SELECT *
FROM User_Profile_Exclusions
WHERE profile_id = P.id AND user_id = 1)

If you redesigned your schema replacing the Exclusions table with an
Inclusions table then this query would become a straightforward INNER JOIN
between the Profiles and UserProfiles. Recording inclusions seems like a
more natural way to do this than recording the exclusions but I suppose it
depends on your requirements.

--
David Portas
----
Please reply only to the newsgroup
--|||I agree, missing from my example though is also an inclusion table and an
owners table. Basically i select all the profiles for a user that belong to
a specific owner minus exclusions plus any inclusions of profiles that
belong to a different owner. Boy thats a mouth full, basically a user can
be given access to all of a set of profiles minus exclusion OR they can be
given access to none of a set of profiles plus inclusions.

Thanks for your help, i just couldn't wrap my head around this yesterday but
your example is perfect.

Best,
Muhd.

"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:jpSdnSSfsI5wQ43dRVn-jA@.giganews.com...
> SELECT P.id, P.name
> FROM Profiles AS P
> WHERE NOT EXISTS
> (SELECT *
> FROM User_Profile_Exclusions
> WHERE profile_id = P.id AND user_id = 1)
> If you redesigned your schema replacing the Exclusions table with an
> Inclusions table then this query would become a straightforward INNER JOIN
> between the Profiles and UserProfiles. Recording inclusions seems like a
> more natural way to do this than recording the exclusions but I suppose it
> depends on your requirements.
> --
> David Portas
> ----
> Please reply only to the newsgroup
> --|||Argh, I don't know why i just can't wrap my head around this. After i get
this sorted out I'm gonna have to find time to sit and practice building
tables and queries until i fully understand things. In the meantime I got
the exclusions to work, then added another layer of complexity which worked
fine then tried to add an inclusion list but all i ever get for results is
the one profile on the inclusion list. Below is the modified DDL and DML
query, note the query doesn't select profiles that are in the inclusion
list. Basically i want to select all the profiles that belong to a specific
owner if a user has access to that owners profiles minus any profiles on the
exclusion list. So that works, but the extra layer is that a user may also
have a few profiles that he has been given specific access to view in the
inclusion list.

The end result will be four scenarios:
1) User can see all profiles for a given owner. (Working)
2) User can see all profiles for a given owner minus profiles on the
exclusion list. (Working)
3) User can see no profiles for a given owner. (Working)
4) User can see no profiles for a given owner plus profiles on the inclusion
list. (Not Working)

IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME
= 'TUsers') DROP TABLE TUsers
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME
= 'TUser_Profile_Lists') DROP TABLE TUser_Profile_Lists
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME
= 'TUser_Profile_Exclusions') DROP TABLE TUser_Profile_Exclusions
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME
= 'TUser_Profile_Inclusions') DROP TABLE TUser_Profile_Inclusions
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME
= 'TProfiles') DROP TABLE TProfiles

CREATE TABLE TUsers
(
ID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
NAME VARCHAR(50) NOT NULL
)

CREATE TABLE TUser_Profile_Lists
(
ID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
User_ID INT NOT NULL,
Owner_ID INT NOT NULL
)

CREATE TABLE TUser_Profile_Exclusions
(
ID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
User_ID INT NOT NULL,
Profile_ID INT NOT NULL
)

CREATE TABLE TUser_Profile_Inclusions
(
ID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
User_ID INT NOT NULL,
Profile_ID INT NOT NULL
)

CREATE TABLE TProfiles
(
ID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
Owner_ID INT NOT NULL,
Name VARCHAR(50) NOT NULL
)

INSERT INTO TUsers (Name) VALUES ('Bob')

INSERT INTO TUser_Profile_Lists (User_ID,Owner_ID) VALUES (1,1)

INSERT INTO TProfiles (Name,Owner_ID) VALUES ('Microsoft',1)
INSERT INTO TProfiles (Name,Owner_ID) VALUES ('Intel',1)
INSERT INTO TProfiles (Name,Owner_ID) VALUES ('IBM',1)
INSERT INTO TProfiles (Name,Owner_ID) VALUES ('Sony',1)
INSERT INTO TProfiles (Name,Owner_ID) VALUES ('Sun Microsystems',2)

INSERT INTO TUser_Profile_Exclusions (User_ID,Profile_ID) VALUES (1,3)
INSERT INTO TUser_Profile_Exclusions (User_ID,Profile_ID) VALUES (1,4)

INSERT INTO TUser_Profile_Inclusions (User_ID,Profile_ID) VALUES (1,2)

DECLARE @.userID INT
SET @.userID = 1

SELECT P.*, UPL.*
FROM TProfiles AS P
JOIN TUser_Profile_Lists AS UPL ON UPL.Owner_ID = P.Owner_ID AND UPL.User_ID
= @.userID
WHERE NOT EXISTS
(
SELECT * FROM TUser_Profile_Exclusions WHERE Profile_ID = P.ID AND User_ID
= @.userID
)|||I think i solved by simply doing a UNION on anthoer query specific to
inclusions but i would still love to hear from someone else if they other
ideas as how to solve this.

DECLARE @.userID INT
SET @.userID = 1

SELECT P.*
FROM TProfiles AS P
JOIN TUser_Profile_Lists AS UPL ON UPL.Owner_ID = P.Owner_ID AND UPL.User_ID
= @.userID
WHERE NOT EXISTS
(
SELECT * FROM TUser_Profile_Exclusions WHERE Profile_ID = P.ID AND User_ID
= @.userID
)

UNION

SELECT P.*
FROM TProfiles AS P
JOIN TUser_Profile_Inclusions AS UPI ON UPI.Profile_ID = P.ID
WHERE UPI.User_ID = @.userID

ORDER BY P.Name ASC

Best,
Muhd.

"Muhd" <eat@.joes.com> wrote in message
news:jMfQb.248589$ts4.150985@.pd7tw3no...
> Argh, I don't know why i just can't wrap my head around this. After i get
> this sorted out I'm gonna have to find time to sit and practice building
> tables and queries until i fully understand things. In the meantime I got
> the exclusions to work, then added another layer of complexity which
worked
> fine then tried to add an inclusion list but all i ever get for results is
> the one profile on the inclusion list. Below is the modified DDL and DML
> query, note the query doesn't select profiles that are in the inclusion
> list. Basically i want to select all the profiles that belong to a
specific
> owner if a user has access to that owners profiles minus any profiles on
the
> exclusion list. So that works, but the extra layer is that a user may
also
> have a few profiles that he has been given specific access to view in the
> inclusion list.
> The end result will be four scenarios:
> 1) User can see all profiles for a given owner. (Working)
> 2) User can see all profiles for a given owner minus profiles on the
> exclusion list. (Working)
> 3) User can see no profiles for a given owner. (Working)
> 4) User can see no profiles for a given owner plus profiles on the
inclusion
> list. (Not Working)
> IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE
TABLE_NAME
> = 'TUsers') DROP TABLE TUsers
> IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE
TABLE_NAME
> = 'TUser_Profile_Lists') DROP TABLE TUser_Profile_Lists
> IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE
TABLE_NAME
> = 'TUser_Profile_Exclusions') DROP TABLE TUser_Profile_Exclusions
> IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE
TABLE_NAME
> = 'TUser_Profile_Inclusions') DROP TABLE TUser_Profile_Inclusions
> IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE
TABLE_NAME
> = 'TProfiles') DROP TABLE TProfiles
>
> CREATE TABLE TUsers
> (
> ID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
> NAME VARCHAR(50) NOT NULL
> )
> CREATE TABLE TUser_Profile_Lists
> (
> ID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
> User_ID INT NOT NULL,
> Owner_ID INT NOT NULL
> )
> CREATE TABLE TUser_Profile_Exclusions
> (
> ID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
> User_ID INT NOT NULL,
> Profile_ID INT NOT NULL
> )
> CREATE TABLE TUser_Profile_Inclusions
> (
> ID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
> User_ID INT NOT NULL,
> Profile_ID INT NOT NULL
> )
> CREATE TABLE TProfiles
> (
> ID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
> Owner_ID INT NOT NULL,
> Name VARCHAR(50) NOT NULL
> )
> INSERT INTO TUsers (Name) VALUES ('Bob')
> INSERT INTO TUser_Profile_Lists (User_ID,Owner_ID) VALUES (1,1)
> INSERT INTO TProfiles (Name,Owner_ID) VALUES ('Microsoft',1)
> INSERT INTO TProfiles (Name,Owner_ID) VALUES ('Intel',1)
> INSERT INTO TProfiles (Name,Owner_ID) VALUES ('IBM',1)
> INSERT INTO TProfiles (Name,Owner_ID) VALUES ('Sony',1)
> INSERT INTO TProfiles (Name,Owner_ID) VALUES ('Sun Microsystems',2)
> INSERT INTO TUser_Profile_Exclusions (User_ID,Profile_ID) VALUES (1,3)
> INSERT INTO TUser_Profile_Exclusions (User_ID,Profile_ID) VALUES (1,4)
> INSERT INTO TUser_Profile_Inclusions (User_ID,Profile_ID) VALUES (1,2)
> DECLARE @.userID INT
> SET @.userID = 1
> SELECT P.*, UPL.*
> FROM TProfiles AS P
> JOIN TUser_Profile_Lists AS UPL ON UPL.Owner_ID = P.Owner_ID AND
UPL.User_ID
> = @.userID
> WHERE NOT EXISTS
> (
> SELECT * FROM TUser_Profile_Exclusions WHERE Profile_ID = P.ID AND
User_ID
> = @.userID
> )|||Muhd (eat@.joes.com) writes:
> CREATE TABLE TUsers
> ( ID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
> NAME VARCHAR(50) NOT NULL)
> CREATE TABLE TUser_Profile_Lists
> (ID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
> User_ID INT NOT NULL,
> Owner_ID INT NOT NULL)
> CREATE TABLE TUser_Profile_Exclusions
> ( ID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
> User_ID INT NOT NULL,
> Profile_ID INT NOT NULL)
> CREATE TABLE TUser_Profile_Inclusions
> (ID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
> User_ID INT NOT NULL,
> Profile_ID INT NOT NULL)
> CREATE TABLE TProfiles
> (ID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
> Owner_ID INT NOT NULL,
> Name VARCHAR(50) NOT NULL)

I think you should review this schema. There are far too many identity
columns here, and I cannot really see any connection with the other
columns. Is TUsers.ID the same as User_ID in other tables? In such case
you should up a foreign-key constraint to establish this fact.

The ID column be warranted for in TUsers, but in TUser_Profile_Lists,
TUser_Profile_Exclusions and TUser_Profile_Inclusions, I suspect that
(User_ID, Owner_ID) and (User_ID, Profile_ID) respectively are meant to
be unique, and these pairs of columns should be the primary key for these
tables.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Query Help

What I want to generate is a list of all users and if they belong to a
site or not. Below is what I have for tables, sample data and the
results. I am a newb with writing my own SQL and could use a few
pointers and help.

For example if I have the following data:

Table: [Users]
UserIDUserName
1Bob
2Joe
3Sam

Table: [Users_Sites]
UserIDSiteID
12
32

Table: [Sites]
SiteIDSiteName
1Flower
2Spring
3Bush

If I pick Site 1 I get the list:

UserIDSiteID
<null>1
<null>1
<null>1

If I pick Site 2 I get the list:

UserIDSiteID
12
<null>2
32

Thanks in advance...Will (wrhighfield@.hotmail.com) writes:
> What I want to generate is a list of all users and if they belong to a
> site or not. Below is what I have for tables, sample data and the
> results. I am a newb with writing my own SQL and could use a few
> pointers and help.

SELECT us.SiteID, u.UserID
FROM Users_Sites us
LEFT JOIN Users u ON us.UserID = u.UserID
WHERE us.SiteID = @.siteid

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Monday, March 26, 2012

query governor for users?

How to make only some users not to run long running blocking queries ?
TIAHi
You could SET DEADLOCK_PRIORITY to low and a QUERY_GOVERNOR_COST_LIMIT,
setting ROWCOUNT and a LOCK_TIMEOUT may also be options.
John
"DallasBlue" wrote:

> How to make only some users not to run long running blocking queries ?
> TIA
>sql

query governor for users?

How to make only some users not to run long running blocking queries ?
TIAHi
You could SET DEADLOCK_PRIORITY to low and a QUERY_GOVERNOR_COST_LIMIT,
setting ROWCOUNT and a LOCK_TIMEOUT may also be options.
John
"DallasBlue" wrote:
> How to make only some users not to run long running blocking queries ?
> TIA
>

Wednesday, March 21, 2012

query failure

I've got an email from one of the users complaining about query failure. He
is doing queries using MS ACCESS. He is getting the following error message:
ODBC - call failed.
[Microsoft][ODBC SQL Server Driver][SQL Server] Could not allocate new page
for database 'TEMPDB'. There are no more pages available in filegroup
DEFAULT. Space can be created by dropping objects, adding additional files,
or allowing file growth. (#1101)
However, the TEMPDB is 60MB in size with 59MB free. It has unrestricted
growth as well. Any suggestions?
Thanks in advance,
Antonin
The answer is in the message you've got. TempDB has grown and there is no disk space to continue growth.
|||Thanks Ray. I cleaned up the disk and the problem is gone.
Antonin
"Ray D" <ray_d@.mail.ru> wrote in message
news:05B82F27-73AB-4EAA-A10E-4A46A67C71D2@.microsoft.com...
> The answer is in the message you've got. TempDB has grown and there is no
disk space to continue growth.

query failure

I've got an email from one of the users complaining about query failure. He
is doing queries using MS ACCESS. He is getting the following error message:
ODBC - call failed.
[Microsoft][ODBC SQL Server Driver][SQL Server] Could not allocate new page
for database 'TEMPDB'. There are no more pages available in filegroup
DEFAULT. Space can be created by dropping objects, adding additional files,
or allowing file growth. (#1101)
However, the TEMPDB is 60MB in size with 59MB free. It has unrestricted
growth as well. Any suggestions?
Thanks in advance,
AntoninThe answer is in the message you've got. TempDB has grown and there is no disk space to continue growth.|||Thanks Ray. I cleaned up the disk and the problem is gone.
Antonin
"Ray D" <ray_d@.mail.ru> wrote in message
news:05B82F27-73AB-4EAA-A10E-4A46A67C71D2@.microsoft.com...
> The answer is in the message you've got. TempDB has grown and there is no
disk space to continue growth.

query failure

I've got an email from one of the users complaining about query failure. He
is doing queries using MS ACCESS. He is getting the following error message:
ODBC - call failed.
[Microsoft][ODBC SQL Server Driver][SQL Server] Could not alloca
te new page
for database 'TEMPDB'. There are no more pages available in filegroup
DEFAULT. Space can be created by dropping objects, adding additional files,
or allowing file growth. (#1101)
However, the TEMPDB is 60MB in size with 59MB free. It has unrestricted
growth as well. Any suggestions?
Thanks in advance,
AntoninThe answer is in the message you've got. TempDB has grown and there is no di
sk space to continue growth.|||Thanks Ray. I cleaned up the disk and the problem is gone.
Antonin
"Ray D" <ray_d@.mail.ru> wrote in message
news:05B82F27-73AB-4EAA-A10E-4A46A67C71D2@.microsoft.com...
> The answer is in the message you've got. TempDB has grown and there is no
disk space to continue growth.

Tuesday, March 20, 2012

query execution failed for dataset

have all my reports on a server, and so far have had no problems giving
users access. They are all located in the Cg Role, i created. Here's the
delima, I added a new user and to this role. The user can view report
manager, view the folders, and view the reports..but when they try to run a
report, they get this error.
query execution failed for dataset "CrashStatisticalYear" (rsError
executingcommand)
She has access to everything, why is she the only one getting this error'
Please Help!It seems like she doesnt have access to a specific table inside that
dataset.
"Tenchy" <Tenchy@.discussions.microsoft.com> wrote in message
news:4088044D-2DD6-4FB1-A9BC-59BED72B1C24@.microsoft.com...
> have all my reports on a server, and so far have had no problems giving
> users access. They are all located in the Cg Role, i created. Here's the
> delima, I added a new user and to this role. The user can view report
> manager, view the folders, and view the reports..but when they try to run
> a
> report, they get this error.
>
> query execution failed for dataset "CrashStatisticalYear" (rsError
> executingcommand)
>
> She has access to everything, why is she the only one getting this error'
>
> Please Help!
>

Monday, March 12, 2012

Query Domain Users

How do I retrieve a list of all domain users using a queryAug,
Do you want a list of all users in the domain?
use: exec xp_cmdshell 'net user /DOMAIN'
NOTE: You need to be sysadmin or have exec rights on xp_cmdshell.
For a list of all users in a database use sp_helpuser.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk|||HI
Create Linked Server for Active Directory:
EXEC sp_addlinkedserver
@.server = 'ADSI',
@.srvproduct = 'Active Directory Services 2.5',
@.provider = 'ADSDSOObject',
@.datasrc = 'adsdatasource'
You can use simple selec
eg:
select * from OPENQUERY(ADSI, "select distinguishedName, physicalDeliveryOff
iceName, displayName from 'LDAP://OU=Organizational,OU= Users,DC=vodafone,DC
=hu' where objectCategory = 'Person' and objectClass= 'user' order by sn")
or create view based on openquery.
Warning: the default resultsel limited: on w2k 1000 record and on w2003 1500
record.
If You need increase use the ntdsutil.exe. For more information: <http://support.m
icrosoft.co...kb;EN-US;315071>
JBandi

Friday, March 9, 2012

Query dbowner

We have a script that allows customers to change the users of the
database including the database owner. This script can be run at any
time. However, sometimes (and it really is only sometimes!) when the
following statement executes:

"exec sp_changedbowner @.USER_OWNER"

the following error is reported:

"The proposed new database owner is already a user in the database."

I have checked the other postings and the Books Online but cannot find
a way to query the current name of the dbowner i.e. the value of
@.USER_OWNER above. We have to be able to do this within the script so
that if the @.USER_OWNER variable is already the dbowner we do not
bother to execute the statement as otherwise this could display the
error. Can anybody help?

Thanks in Advance

PaulEM gets it from exec sp_MSdbuseraccess N'db', N'%'

which calls

select o.name, o.version, o.crdate, suser_sname(o.sid), o.dbid, o.status,
o.category, o.status2, DatabaseProperty(o.name, N'isfulltextenabled')
from master.dbo.sysdatabases o where o.name = @.qual

select suser_sname(o.sid) from master.dbo.sysdatabases where name = 'DBNAME'
should do it.

Danny

<paulwragg2323@.hotmail.com> wrote in message
news:1102498820.367939.212520@.f14g2000cwb.googlegr oups.com...
> Hi
> We have a script that allows customers to change the users of the
> database including the database owner. This script can be run at any
> time. However, sometimes (and it really is only sometimes!) when the
> following statement executes:
> "exec sp_changedbowner @.USER_OWNER"
> the following error is reported:
> "The proposed new database owner is already a user in the database."
> I have checked the other postings and the Books Online but cannot find
> a way to query the current name of the dbowner i.e. the value of
> @.USER_OWNER above. We have to be able to do this within the script so
> that if the @.USER_OWNER variable is already the dbowner we do not
> bother to execute the statement as otherwise this could display the
> error. Can anybody help?
> Thanks in Advance
> Paul|||This works fine (except missing o for alias for
master.dbo.sysdatabases) and is just what I needed.

Thanks Danny.|||This works fine (except missing o for alias for
master.dbo.sysdatabases) and is just what I needed.

Thanks Danny.|||One method to retrieve the current database owner:

DECLARE @.CurrentOwner nvarchar(256)
SELECT @.CurrentOwner = SUSER_SNAME(sid)
FROM master..sysdatabases
WHERE name = DB_NAME()

You can then conditionally change the owner:

IF @.CurrentOwner <> @.USER_OWNER
EXEC sp_changedbowner @.USER_OWNER

However, your error message is most likely because the new database owner is
a current database user rather than the database owner. The query below
will identify this condition as well as the database owner.

SELECT
l.name as LoginName,
u.name AS UserName
FROM master..syslogins l
JOIN sysusers u ON u.sid = l.sid
WHERE l.sid = SUSER_SID(@.USER_OWNER)

--
Hope this helps.

Dan Guzman
SQL Server MVP

<paulwragg2323@.hotmail.com> wrote in message
news:1102498820.367939.212520@.f14g2000cwb.googlegr oups.com...
> Hi
> We have a script that allows customers to change the users of the
> database including the database owner. This script can be run at any
> time. However, sometimes (and it really is only sometimes!) when the
> following statement executes:
> "exec sp_changedbowner @.USER_OWNER"
> the following error is reported:
> "The proposed new database owner is already a user in the database."
> I have checked the other postings and the Books Online but cannot find
> a way to query the current name of the dbowner i.e. the value of
> @.USER_OWNER above. We have to be able to do this within the script so
> that if the @.USER_OWNER variable is already the dbowner we do not
> bother to execute the statement as otherwise this could display the
> error. Can anybody help?
> Thanks in Advance
> Paul|||Thanks to both of you for replying to my post.
I have now solved this and proven that both methods will work.

Thanks.

Wednesday, March 7, 2012

query cannot return any results

I have a very uncommon problem ... I have a application that runs like this:
1) it receives users inserts, including a Status = 'U' field;
2) based on a Status field (index) the application query and select the last
inserted registries all day long, each 30 seconds;
3) every time it read the registries it changes the Status field to 'R'.
The problem is that after about 24 hours, the query identifies no longer
registries with that index. It returns nothing. I use a execurereader
command, and it happens that myreader.hasrows = false, even if there are row
s
with Status field = 'U'
Can somebody help me to know what is happening?
--
Sergio R Piresquery optimiser should use best available index or column stats to decide
strategy, and this may be cached for long period.
Maybe stats get recomputed automatically if you make lotsa changes and have
updatestats dboption set, or explicitly by your DBA [recommendation used to
be to do explicitly due to excessive overhead but nowadays with autonomics
MSSQL does the right thing].
If you truncate/delete staging table [daily] just before query is compiled
into cache it may decide to use tablescan even if index available [since so
_few_ rows] and this may persist some time even if cardinality builds up a
lot.
Unfortunately the sysindexes.rowcnt cannot be relied on for accuracy [due to
transaction activity], so you may have to force count(*) to get real count
but this has locking issues [nolock would only give approx count like
sysindexes].
Dependencies can be omitted from sysdepends [to support forward compilation]
so optimiser may be similarly ignorant.
I suspect the optimiser is getting

, so suggest that
1. check latest Service Pack applied
2. exec sp_dboption 'pubs','auto create statistics','on' -- substitute
dbname for pubs
3. exec sp_dboption 'pubs','auto update statistics','on' -- substitute
dbname for pubs
4. use QA to show query plan [Control-L]
5. try explicit sp_recompile
6. check dependencies
if all else fails you can mark your sproc "WITH RECOMPILE" to ignore cached
copy, thus keep abreast of actual cardinality
best wishes!
Dick
"Sergio R Pires" wrote:

> I have a very uncommon problem ... I have a application that runs like thi
s:
> 1) it receives users inserts, including a Status = 'U' field;
> 2) based on a Status field (index) the application query and select the la
st
> inserted registries all day long, each 30 seconds;
> 3) every time it read the registries it changes the Status field to 'R'.
> The problem is that after about 24 hours, the query identifies no longer
> registries with that index. It returns nothing. I use a execurereader
> command, and it happens that myreader.hasrows = false, even if there are r
ows
> with Status field = 'U'
> Can somebody help me to know what is happening?
> --
> Sergio R Pires

Saturday, February 25, 2012

QUERY BY USER

I have a datagrid with rows that pertain to multiple users. I want to
write a query statement so that whomever the CURRENT logged-in
user is, he can go to the datagrid page and it will show ONLY rows
that pertain to him (programmatic security). To accomplish this,
I have a column in the datatable that references each user's
username. So I need a query statement that will compare the table's
'username' column to the current logged-in user. Something like...

SELECT... FROM [VendorEquipment]
WHERE [UserName] = Context.Current.User.Name

...or something like this. Question is, can someone help on how to
syntax this. Also, do I need anything special in the login form or in
web.config to 'pave the way' for this query to work.

Thx much.
Reid C.after a successfull login,you can store the username in sessions...|||Thanks for the reply...but I am a novice and will probably need more to clear up
the picture...can you suggest how I can syntax my SELECT command to accomplish
the above-stated objective and is there something I need to do via sessions to
enable this--and where would I do that?

Thx much.
Reid C.|||i believe you are checking for user authorization when the user logs in right ?

just put the username into session after the validation is successfull


session("username")=username

and frm any page
you can get it back as


dim uname as string
uname=session("username")

so when you query the db for the records you can do something like


strsql=select * From table where username='" & uname & "'"

and bind the datagrid.

HTH|||Thanks again...this sounds like what I'm looking for...just two
more questions due to my relative inexperience:

1) the statement: session("username")=username
you suggested...does that need to be in the login form
like in the "sub loginBtn_click" subroutine? and if so where in
the subroutine does it need to go...is the placement of the
statement critical?

2) is "username" interchangeable in this operation with "userid" ?
because I have written my registration form and login with a
"userid" instead of a "username"...but I will change this if it is
important to have "username" for forms authentication.

Thx.
Reid C.|||yep you can have any variable in session you want..username,userid...doesnt matter.
right after you check the userid with the password and if the query returns as a valid user, store the userid in sessions...and you should be able to access it frm any page...

HTH|||Thank you ...do I place the session stmt in global.asax?

like this, for instance:

Sub Session_OnStart()
 Session("userId")=userId
End Sub

Thx.
Reid C.|||no...you put it in ur login.aspx where ever you validate the authenticity of the user...|||o.k., thanks again, ...but I am a complete novice and have been building pages
using WebMatrix, so sorry to be dense...but do I place the "session..." statement
in the "Function GetUser..." or in my "Sub LoginBtn_click..." subroutine, or after
that and right before the </script> tag ? I know placement of this statement is
important, I just don't know where?

Thx again.
Reid C.|||if you post your code for the login page..i can tell you where to place it...|||Thanks again for all your help...here is the code for the login (minus the html) and
I've got the statement placed right before the end </script> tag...tell me if you see
a glaring case of needing to move it to a more appropriate space.


<script runat="server">
Function GetUser(ByVal userId As String, ByVal userPassword As String) As System.Data.DataSet
 Dim connectionString As String = "server='(local)'; trusted_connection=true; database='VSdatastore'"
 Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString) Dim queryString As String = "SELECT [Users].* FROM [Users] WHERE (([Users].[UserId] = @.UserId) AND ([Users].[U"& _
 "serPassword] = @.UserPassword))"
 Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand
 dbCommand.CommandText = queryString
 dbCommand.Connection = dbConnection
 Dim dbParam_userId As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
 dbParam_userId.ParameterName = "@.UserId"
 dbParam_userId.Value = userId
 dbParam_userId.DbType = System.Data.DbType.String
 dbCommand.Parameters.Add(dbParam_userId)
 Dim dbParam_userPassword As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
 dbParam_userPassword.ParameterName = "@.UserPassword"
 dbParam_userPassword.Value = userPassword
 dbParam_userPassword.DbType = System.Data.DbType.String
 dbCommand.Parameters.Add(dbParam_userPassword)
 Dim dataAdapter As System.Data.IDbDataAdapter = New System.Data.SqlClient.SqlDataAdapter
 dataAdapter.SelectCommand = dbCommand
 Dim dataSet As System.Data.DataSet = New System.Data.DataSet
 dataAdapter.Fill(dataSet)
 Return dataSet
End Function
Sub LoginBtn_Click(Sender As Object, E As EventArgs)
 If Page.IsValid Then
 Dim userDS As New System.Data.DataSet
 userDS = GetUser(UserId.Text, UserPassword.Text)
 If userDS.Tables(0).Rows.Count = 1 Then
 FormsAuthentication.RedirectFromLoginPage(UserId.Text, false)
 Else
 Msg.Text = "Invalid UserId or Password: Please try again"
 End If
 End If
End Sub
session("UserId")=UserId
</script

Thx again.
Reid C.|||i think this should work...


If Page.IsValid Then Dim userDS As New System.Data.DataSet
 userDS = GetUser(UserId.Text, UserPassword.Text)
 If userDS.Tables(0).Rows.Count = 1 Then
 'valid user...
 session("UserId")=UserId
 FormsAuthentication.RedirectFromLoginPage(UserId.Text, false)
 Else
 Msg.Text = "Invalid UserId or Password: Please try again"
 End If
 End If
End Sub
session("UserId")=UserId

|||In your revision you have the "session..." statement right after
the "If userDS.Tables(0).Rows.Count = 1 Then" statement which
made sense as soon as I saw it. But you also have the same
"session..." statement again after the "End Sub" statement. Is
this just a typo on your part from copying and pasting what I
sent you, or does it need to be there?

I am assuming it is a copy&paste oversight...unless you answer
back that it needs to be repeated...

Thx much again for your help.
Reid C.|||See if you can review the code for my datagrid page and see where I'm going wrong.
One thing, the "UserId" I need to compare is in the datatable as "PropUserId" because
I also have a column in the table "VendorUserId" and need to reference both. So I
need to compare the session "UserId" with the table's "PropUserId" column and I've
added a statement in the "Function GetVendorEquipment..." that says the "PropUserId"
column is equivalent to "UserId"...but I am getting a blank table on the page when I
run it.


<script runat="server"
 Function GetVendorEquipment() As System.Data.DataSet
 Dim connectionString As String = "server='(local)'; trusted_connection=true; database='VSdatastore'"
 Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)
 dim UserId as String
 dim PropUserId as String
 PropUserId=session("UserId")
 Dim queryString As String = "SELECT [VendorEquipment].* FROM [VendorEquipment] WHERE [PropUserId]='" & UserId & "'"
 Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand
 dbCommand.CommandText = queryString
 dbCommand.Connection = dbConnection Dim dataAdapter As System.Data.IDbDataAdapter = New System.Data.SqlClient.SqlDataAdapter
 dataAdapter.SelectCommand = dbCommand
 Dim dataSet As System.Data.DataSet = New System.Data.DataSet
 dataAdapter.Fill(dataSet)
 Return dataSet
 End Function
 Sub Page_Load(sender As Object, e As EventArgs)
 DataGrid1.DataBind()
 End Sub
</script>

Thx again for your help.
Reid C.|||ur userid seems to be a number...
so you have to use


Dim queryString As String = "SELECT [VendorEquipment].* FROM [VendorEquipment] WHERE [PropUserId]=" & PropUserId

also one other suggestion...bind your datagrid within if not ispostback...loop


Sub Page_Load(sender As Object, e As EventArgs)
 if not ispostback then
 DataGrid1.DataBind()
 end if
End Sub

HTH

query builder

hi friends
i've one question is it possible to use SMO to replicate "Query Designer" programmatically?
i want to write a query builder for end users and am wondering which objects in SMO i need to use to achieve what i want.
any ideas on this one much appreciated.
Thank you very much for your help.You can use SMO for part of the problem, but not all. You can use SMO to obtain a listing of tables, columns, and views and various properties (e.g. keys, datatypes, and etc). But this is no way in SMO to provide it some tables and conditional statements to generate a query.

Peter|||Yes Peter ,i was able to gather table,columns info from SMO but did not see any class inside this namespace to generate query .
do you have any other ideas/advise in writing a sql engine?
Thanks for your help.|||Sorry, I don't have any other suggestions.

Peter|||SMO will be helpful for you in obtaining metadata of the objects (tables, colums, foreign keys etc.) which will help you building the queries. However the query building portion is something you need to build yourself. SMO will not help you in any way.|||

Thanks for the reply Michiel.

finally we ended up using Report builder that ships with sql reporting services.

query builder

Thanks for the reply Michiel.

finally we ended up using Report builder that ships with sql reporting services.

Wednesday, March 28, 2012

Query Help

Query Help

Query Help

Query Help

Monday, March 26, 2012

query governor for users?

query governor for users?

Wednesday, March 21, 2012

query failure

query failure

query failure

Tuesday, March 20, 2012

query execution failed for dataset

Monday, March 12, 2012

Query Domain Users

Friday, March 9, 2012

Query dbowner

Wednesday, March 7, 2012

query cannot return any results

Saturday, February 25, 2012

QUERY BY USER

query builder

query builder

query assistance

Blog Archive

About Me