Friday, March 30, 2012
Query works then later timeouts
n 300 rows each.
The query is fine when there is less than 50 users but when it gets over 50
it runs until it timeouts. During this time while it is running and not ret
urning I can do selects on each individual table but if I run the query with
the joins it never returnsWhat is the waittype for that spid during the slow times?
Andrew J. Kelly
SQL Server MVP
"Darin Browne" <anonymous@.discussions.microsoft.com> wrote in message
news:00EDE2AB-4CE0-4014-A0F6-18552B409DB1@.microsoft.com...
> We have a query which joins 3 tables where all 3 of the tables have less
than 300 rows each.
> The query is fine when there is less than 50 users but when it gets over
50 it runs until it timeouts. During this time while it is running and not
returning I can do selects on each individual table but if I run the query
with the joins it never returns.
> We've checked locks, if anything is blocking it, indexes used and
everything is fine during the never ending select.
> Any suggestions on what we could do? We're stumped.
> Thanks.
>
Wednesday, March 28, 2012
Query with multiple arguments?
Hi, I'm a bit stumped as to how to do this.
I have a string[] with a list of users, and I want to query my database to select only the users in this array and bind the datasource to a GridView, but I don't know how to write an SQL query to search for multiple results from the same field.
E.g. Say I have two results in my string[], fred and bob.
How can I select data from the database for just those two users - "SELECT * FROM tblUsers WHERE UserName='bob' AND ??";
IF this is possible, I also need to bind it to a gridview. I tried the following, but it didn't work as I needed it to:
for(int a = 0; a < userArray.Length; a++)
{
conn.Open();
SqlCommand command = new SqlCommand("SELECT * FROM tblUsers WHERE UserName='" + userArray[a] + "'", conn);
SqlDataReader reader = command.ExecuteReader();
grid.DataSource = reader;
grid.DataBind();
conn.Close()
}
That 'worked', but as I'm sure you can see, the data that was bound to the gridview was only the last result found, not the whole result set.
Any help is greatly appreciated.
schuminator:
for(int a = 0; a < userArray.Length; a++)
{
conn.Open();
SqlCommand command = new SqlCommand("SELECT * FROM tblUsers WHERE UserName='" + userArray[a] + "'", conn);
SqlDataReader reader = command.ExecuteReader();
grid.DataSource = reader;
grid.DataBind();
conn.Close()
}
try out as below
String strUsers = String.Empty;for (int a = 0; a < userArray.Length; a++) strUsers = strUsers + @."'" + userArray[a] + @."',"; strUsers = strUsers.Substring(0, strUsers.Length - 1); conn.Open(); SqlCommand command =new SqlCommand("SELECT * FROM tblUsers WHERE UserName in (" + strUsers +")", conn); SqlDataReader reader = command.ExecuteReader(); grid.DataSource = reader; grid.DataBind(); conn.Close();
Good Luck./.
|||Sorry, I tried to delete the thread but it was too late.
I got it sorted...it was so simple, I feel like an idiot!
"SELECT * FROM tblUsers WHERE UserName='fred' OR UserName='bob'" etc
I also wrote a little for loop to add another OR... to the end of the string when necessary.
|||you can use above method also...
so instead of UserName='fred' OR UserName='bob'"......
if will formulate query using IN keywork as..
UserName in ('fred','bob')
|||ahh ok awesome, thanks :)
Friday, March 23, 2012
Query users in a Security Group with LDAP
SELECT [name], [samaccountname] from OpenQuery( ADSI,
'SELECT name, samaccountname FROM ''LDAP://DC=domain,DC=com'' WHERE objectClass = ''user'' and objectCategory=''Person''')
Now I am trying to select all the users in a specifed security group, but I am not having much luck. What is the best way to get this?
Thanks much.If that can't be done, is there anyway to check if a user is a member of a group or not through a linked server?sql
Tuesday, March 20, 2012
Query to report database user role membership
I using sql server 2005 sp2. I'm trying to construct a query that'll show me all roles and the users that are in the roles. I'm having trouble figuring how sql server stores this information in the system tables. I think it's based around the database_principals system tables but haven't been able to display the info i need.
If anyone has a query like this can they please help me?
thanks,
Dave
I figured it out. I found the sys.database_role_members which had the info i was after:
select distinct c.name, b.name from sys.database_role_members a
inner join sys.database_principals b on b.principal_id = a.member_principal_id
inner join sys.database_principals c on c.principal_id = a.role_principal_id
and b.type <> 'R
Query to report database user role membership
I using sql server 2005 sp2. I'm trying to construct a query that'll show me all roles and the users that are in the roles. I'm having trouble figuring how sql server stores this information in the system tables. I think it's based around the database_principals system tables but haven't been able to display the info i need.
If anyone has a query like this can they please help me?
thanks,
Dave
I figured it out. I found the sys.database_role_members which had the info i was after:
select distinct c.name, b.name from sys.database_role_members a
inner join sys.database_principals b on b.principal_id = a.member_principal_id
inner join sys.database_principals c on c.principal_id = a.role_principal_id
and b.type <> 'R
Query to obtain users and privileges from databases
Hi to all, is my first post, i need a query or script to obtain all users and privileges from all my databases, someone to help me. I'm learning Administration SQL server 2005.
I know that sys.database_principals and sys.server_principals have information about that, but i need users - privileges of every database.
thank you
There are a couple of views that can help you here.
sys.server_permissions and sys.database_permissions will show what permissions are granted to logins and users respectively.
Do you need to only check for individual users or do you need to check for roles as well?
thx,
-steven
|||The following solution provides a way to query all the permissions of all users in the current database. So it is not a complete answer to your question. In order to navigate in every database, it is easier to write managed code program to do that.
Select sys.database_principals.name, permission_name,state, state_desc
from sys.database_permissions, sys.database_principals
where grantee_principal_id = principal_id
|||Thank you very much for your answers
Query to obtain users and privileges from databases
Hi to all, is my first post, i need a query or script to obtain all users and privileges from all my databases, someone to help me. I'm learning Administration SQL server 2005.
I know that sys.database_principals and sys.server_principals have information about that, but i need users - privileges of every database.
thank you
There are a couple of views that can help you here.
sys.server_permissions and sys.database_permissions will show what permissions are granted to logins and users respectively.
Do you need to only check for individual users or do you need to check for roles as well?
thx,
-steven
|||The following solution provides a way to query all the permissions of all users in the current database. So it is not a complete answer to your question. In order to navigate in every database, it is easier to write managed code program to do that.
Select sys.database_principals.name, permission_name,state, state_desc
from sys.database_permissions, sys.database_principals
where grantee_principal_id = principal_id
|||Thank you very much for your answers
Monday, March 12, 2012
Query to get the all the users from ADS datasource
Please help me to get the all the user from ADS, i searched a lot and found we can get that using linked server,
i ran the following query to add the linked server,
sp_addlinkedserver 'ADSI', 'Active Directory Service Interfaces',
'ADSDSOObject', 'adsdatasource'
The query ran succesfully and the 'ADSI' has been added as linked server
I got a query from net to get the users from ADS
SELECT [Name],SN[Last Name]
FROM OPENQUERY( ADSI,
'SELECT Name,SN FROM ''LDAP://servername.domainname.com/CN=Users,
DC=domainname,DC=com''
WHERE objectCategory = ''Person'' AND objectClass = ''user'' order by
name')
I am not able to understand the query above and what i need to give to get my ADS users
please help me.
i tried from my side after refering these links ... go through this...
http://codebetter.com/blogs/brendan.tompkins/archive/2003/12/19/4746.aspx
http://support.microsoft.com/kb/299410
http://blogs.msdn.com/euanga/archive/2007/03/22/faq-how-do-i-query-active-directory-from-sql-server.aspx
Madhu
|||Madhu,
I am having the same problem as posted above, but despite trying your suggestions, it still doesnt work.
The code used:
Code Block
sp_addlinkedserver 'ADSI', 'Active Directory Service Interfaces', 'ADsDSOObject', 'adsdatasource'sp_addlinkedserver 'ADSI', 'Active Directory Service Interfaces', 'ADsDSOObject', 'adsdatasource'
SELECT * FROM OPENQUERY( ADSI,
'SELECT name, adsPath
FROM ''LDAP://DC=myCompany,DC=lan''
WHERE objectCategory = ''Person'' AND objectClass= ''user''')
PS: the domain is myCompany.lan
We use windows authentication mode. I dont belong to the administrators' group (which is systems administrator's stuff here).
May it be something related to the linked server's security? I've tried "be made using the login's current security context" and "be made without a security context" (we never know ). Both without sucess.
Or would it be related to AD reading permissions?
|||Well, a clue:
Digging around, I've found this script at http://www.microsoft.com/technet/scriptcenter/resources/qanda/aug04/hey0824.mspx:
Code Block
On Error Resume Next
Const ADS_SCOPE_SUBTREE = 2
Set objConnection = CreateObject("ADODB.Connection")
Set objCommand = CreateObject("ADODB.Command")
objConnection.Provider = "ADsDSOObject"
objConnection.Open "Active Directory Provider"
Set objCommand.ActiveConnection = objConnection
objCommand.Properties("Page Size") = 1000
objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE
objCommand.CommandText = _
"SELECT Name FROM 'LDAP://dc=myCompany,dc=lan' " & _
"WHERE objectCategory='user'"
Set objRecordSet = objCommand.Execute
objRecordSet.MoveFirst
Do Until objRecordSet.EOF
Wscript.Echo objRecordSet.Fields("Name").Value
objRecordSet.MoveNext
Loop
It works like charm. Whatever it may be, is not related to permissions. An interesting thing: in Brendan Tompkins' blog http://adsdsoobject.codebetter.com/blogs/brendan.tompkins/archive/2003/12/19/4746.aspx, he quotes this link: http://www.dbforums.com/archive/index.php/t-958399.html, which mentions certain windows registry keys I havent found in both my server and desktops: there is no entry called "provider", under the mentioned path. Is there anything missing here?
Thanks in advance!
|||More news:
Indeed, the query runs smoothly... using SSMS on the server machine. But not in any workstation.
As I said, I can run that very vb script quoted above from my workstation, but not the query in SSMS. Why is it so?
No clues?!
|||Do you get any error when you are not expecting correct results?
If it doesn't work with server name you might try with IP Address instead.
|||Thanks for your reply, Satya.
I didnt understand your first question...
Actually, the query below doesnt work when I try to run from my workstation (that same "An error occurred while preparing the query..." error). I've tried using IP as you suggested:
select * from openquery
(ADSI,'SELECT name
FROM ''LDAP://172.23.0.21''
WHERE objectCategory = ''Person'' AND objectClass = ''user''')
Something weird: it doesnt work while running from SSMS right on the server, either! I get this error:
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "ADsDSOObject" for linked server "ADSI".
Query to get the all the users from ADS datasource
Please help me to get the all the user from ADS, i searched a lot and found we can get that using linked server,
i ran the following query to add the linked server,
sp_addlinkedserver 'ADSI', 'Active Directory Service Interfaces',
'ADSDSOObject', 'adsdatasource'
The query ran succesfully and the 'ADSI' has been added as linked server
I got a query from net to get the users from ADS
SELECT [Name],SN[Last Name]
FROM OPENQUERY( ADSI,
'SELECT Name,SN FROM ''LDAP://servername.domainname.com/CN=Users,
DC=domainname,DC=com''
WHERE objectCategory = ''Person'' AND objectClass = ''user'' order by
name')
I am not able to understand the query above and what i need to give to get my ADS users
please help me.
i tried from my side after refering these links ... go through this...
http://codebetter.com/blogs/brendan.tompkins/archive/2003/12/19/4746.aspx
http://support.microsoft.com/kb/299410
http://blogs.msdn.com/euanga/archive/2007/03/22/faq-how-do-i-query-active-directory-from-sql-server.aspx
Madhu
|||Madhu,
I am having the same problem as posted above, but despite trying your suggestions, it still doesnt work.
The code used:
Code Block
sp_addlinkedserver 'ADSI', 'Active Directory Service Interfaces', 'ADsDSOObject', 'adsdatasource'sp_addlinkedserver 'ADSI', 'Active Directory Service Interfaces', 'ADsDSOObject', 'adsdatasource'
SELECT * FROM OPENQUERY( ADSI,
'SELECT name, adsPath
FROM ''LDAP://DC=myCompany,DC=lan''
WHERE objectCategory = ''Person'' AND objectClass= ''user''')
PS: the domain is myCompany.lan
We use windows authentication mode. I dont belong to the administrators' group (which is systems administrator's stuff here).
May it be something related to the linked server's security? I've tried "be made using the login's current security context" and "be made without a security context" (we never know ). Both without sucess.
Or would it be related to AD reading permissions?
|||Well, a clue:
Digging around, I've found this script at http://www.microsoft.com/technet/scriptcenter/resources/qanda/aug04/hey0824.mspx:
Code Block
On Error Resume Next
Const ADS_SCOPE_SUBTREE = 2
Set objConnection = CreateObject("ADODB.Connection")
Set objCommand = CreateObject("ADODB.Command")
objConnection.Provider = "ADsDSOObject"
objConnection.Open "Active Directory Provider"
Set objCommand.ActiveConnection = objConnection
objCommand.Properties("Page Size") = 1000
objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE
objCommand.CommandText = _
"SELECT Name FROM 'LDAP://dc=myCompany,dc=lan' " & _
"WHERE objectCategory='user'"
Set objRecordSet = objCommand.Execute
objRecordSet.MoveFirst
Do Until objRecordSet.EOF
Wscript.Echo objRecordSet.Fields("Name").Value
objRecordSet.MoveNext
Loop
It works like charm. Whatever it may be, is not related to permissions. An interesting thing: in Brendan Tompkins' blog http://adsdsoobject.codebetter.com/blogs/brendan.tompkins/archive/2003/12/19/4746.aspx, he quotes this link: http://www.dbforums.com/archive/index.php/t-958399.html, which mentions certain windows registry keys I havent found in both my server and desktops: there is no entry called "provider", under the mentioned path. Is there anything missing here?
Thanks in advance!
|||More news:
Indeed, the query runs smoothly... using SSMS on the server machine. But not in any workstation.
As I said, I can run that very vb script quoted above from my workstation, but not the query in SSMS. Why is it so?
No clues?!
|||Do you get any error when you are not expecting correct results?
If it doesn't work with server name you might try with IP Address instead.
|||Thanks for your reply, Satya.
I didnt understand your first question...
Actually, the query below doesnt work when I try to run from my workstation (that same "An error occurred while preparing the query..." error). I've tried using IP as you suggested:
select * from openquery
(ADSI,'SELECT name
FROM ''LDAP://172.23.0.21''
WHERE objectCategory = ''Person'' AND objectClass = ''user''')
Something weird: it doesnt work while running from SSMS right on the server, either! I get this error:
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "ADsDSOObject" for linked server "ADSI".
Friday, March 9, 2012
Query to determine public access
-Kyle
All users are members of the public role by default and out of the box public won't have any explicit permissions assigned to it. So unless you've tampered with these settings, you should be ok.
Remember if you assign explict deny permissions on public this could have unexpected consequences as ALL logins won't have access to those tables.
Id suggest having a user defined database role which you add all users that has the desired permissions.
HTH!
|||Being a DBA in a somewhat large company means that you inherit a lot of the main aspects of the job. Along with that, you can't satisfy auditers with the explanation that out of the box the public user role won't have rights to the tables. Thus, if anybody has a query that would tell the tables that the public user role has that would be fantastic.Saturday, February 25, 2012
Query that adds data for every user
Dear Masters;
I have a Messages table; I use this table to post System Messages to my users. But what I don't know is how can I add data for multiple users. I mean I want to add same message for multiple users (ex: Please update your infos). In the belove table I have some messages for users 100 and 200; How can I add same (please update your infos) messages to both users?
Ex: Messages Table
ID UserID Msg
1 100 "Hello"
2 100 "Hi"
3 200 "Hello"
Thanks..
try this to add your message to current message
UPDATE Messages
SET msg=msg + ' Please update your infos '
where userID in (100,200)
or this to create new content for message
UPDATE Messages
SET msg = 'Please update your infos '
where userID in (100,200)
Thanks
|||
Thanks for repliying;
But may I write a select statement for example in the in() section?
Cause I may need to add for all users so I gues it should be something like "for" statement.. But if I can write select statement inside in() That may solve...
I'll try thanks
|||Yes, you can try for example
where userID in (select UserID from messages where userID>100)
but your select statement should return only one column.
Thanks
|||I don't think you want an update statement for this purpose. If you update where UserID = 100, you will update both records 1 and 2. You will need to do multipl einsert statements. You can do this inside a stored procedure that you pass paramaters to (ie. parameter 1 is the message and parameter 2 is a comma seperated list of user ids). If you are not comfortable with stored procedures, you will need to construct and execute multiple insert statements in your code. Don't forget to use a transaction in case of failure.|||
Hi;
I guess I'm a little bit confused:) Could you explain me more deeply? I think I'm ok with stored procedures but what should this stored procedure include?
don't you think if J's solution works? I mean why shloud it update both records?
Thanks
|||
In your example table structure you have the same userID for 2 first records so f you do update for userID=100 both records will be updated. If you would like to do it for specific one you have to use ID column to select only one record. It was mistake or you can have two entries for the same userID in your table?
Thanks
|||Oh i c;
Thanks, I will actually insert a new row I'm not gonna update so problem solved thanks...
Thanks to both masters;)
|||Here is an article on how to write a stored proc to take a comma seperated value (CSV) list into a stored proc. You should be able to adapt this for your own needs.Monday, February 20, 2012
Query strings that dont start with a letter
Does anyone know how to do this?
Thanks alot!Hi DrEaMON,
1.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_la-lz_115x.asp
LIKE statement syntax
2.
select UserName
from Users
where UserName like '[^A-Z]%'|||Thank you! That's exactly what I needed.
You learn something new every day huh!?!