exec sp_who2 'active' - Status = Sleeping?

OK... I'm getting confused...
I'm executing:
exec sp_who2 'active'
And getting the below results:
SPID Status Login
-- -- --
198 sleeping Domain\User
...
...
With that said, can someone please refresh my memory about sleeping SPID's
status? How can it be active but sleeping?
Thanks,
MichaelI ask because when I query sysprocesses, I see CPU and physical I/O increase
while it's sleeping...
SYSPROCESSES ServerName 134219768
**********************************
spid cpu physical_io
-- -- --
69 188 135499
69 79 135499
69 141 135499
69 172 135499
69 218 135499
69 187 135499
69 171 135499
69 172 135499
Thanks!|||Hi Batman
There is no value 'active'.
Take a look at the code for sp_who2. You'll see that if you specify
'active', it eliminates spids where status='sleeping' and command is one of
a small list of values, including 'AWAITING COMMAND', and there is nothing
blocking. So a spid can be considered active if it is currently waiting for
some resource. What does the COMMAND column report?
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"BATMAN" <BATMAN@.discussions.microsoft.com> wrote in message
news:073AFE2C-C957-4978-8AFF-420C5C7A6D86@.microsoft.com...
> OK... I'm getting confused...
> I'm executing:
> exec sp_who2 'active'
> And getting the below results:
> SPID Status Login
> -- -- --
> 198 sleeping Domain\User
> ...
> ...
> With that said, can someone please refresh my memory about sleeping SPID's
> status? How can it be active but sleeping?
> Thanks,
> Michael|||The COMMAND column says "SELECT"...
Not to mention, we use the sp_blocker_pss80 sproc and is shows the same
status and command.
Again it shows sleeping but the cumulative CPU and I/O grew over a minute
and then disapeared...|||A process can sleep occasionally, but still be an active process, and use
system resources.
Perhaps what you mean by 'active' and what sp_who2 means by active are not
the same thing.
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Batman" <Batman@.discussions.microsoft.com> wrote in message
news:B63A566F-E9B6-4754-90EB-E2B2DD17D938@.microsoft.com...
> The COMMAND column says "SELECT"...
> Not to mention, we use the sp_blocker_pss80 sproc and is shows the same
> status and command.
> Again it shows sleeping but the cumulative CPU and I/O grew over a minute
> and then disapeared...
>
>
>

Exclamation Message in Security logins

Hi,
Ok maybe I posted this in the wrong thread before.
I restored 4 databases onto another SQL server and added
sql logins. When I click on a login under
Security I get this Exclamation message?
"One or more databases are inaccessible and will not be displayed in the
database access tab"
I don't understand this message and couldn't find anything on it.
thanks
gv
gv wrote:
> Hi,
> Ok maybe I posted this in the wrong thread before.
> I restored 4 databases onto another SQL server and added
> sql logins. When I click on a login under
> Security I get this Exclamation message?
> "One or more databases are inaccessible and will not be displayed in the
> database access tab"
> I don't understand this message and couldn't find anything on it.
> thanks
> gv
That means one or more of your databases are unavailable, possibly in
Standby or Suspect mode...
|||gv wrote:
> Hi,
> Ok maybe I posted this in the wrong thread before.
> I restored 4 databases onto another SQL server and added
> sql logins. When I click on a login under
> Security I get this Exclamation message?
> "One or more databases are inaccessible and will not be displayed in the
> database access tab"
> I don't understand this message and couldn't find anything on it.
> thanks
> gv
That means one or more of your databases are unavailable, possibly in
Standby or Suspect mode...
|||Thank You!!!!
:<)
"gv" <viator.gerry@.gmail.com> wrote in message
news:uPoo3Rj7GHA.140@.TK2MSFTNGP05.phx.gbl...
> Hi,
> Ok maybe I posted this in the wrong thread before.
> I restored 4 databases onto another SQL server and added
> sql logins. When I click on a login under
> Security I get this Exclamation message?
> "One or more databases are inaccessible and will not be displayed in the
> database access tab"
> I don't understand this message and couldn't find anything on it.
> thanks
> gv
>
>

Exclamation Message in Security logins

Hi,
Ok maybe I posted this in the wrong thread before.
I restored 4 databases onto another SQL server and added
sql logins. When I click on a login under
Security I get this Exclamation message?
"One or more databases are inaccessible and will not be displayed in the
database access tab"
I don't understand this message and couldn't find anything on it.
thanks
gvgv wrote:
> Hi,
> Ok maybe I posted this in the wrong thread before.
> I restored 4 databases onto another SQL server and added
> sql logins. When I click on a login under
> Security I get this Exclamation message?
> "One or more databases are inaccessible and will not be displayed in the
> database access tab"
> I don't understand this message and couldn't find anything on it.
> thanks
> gv
That means one or more of your databases are unavailable, possibly in
Standby or Suspect mode...|||gv wrote:
> Hi,
> Ok maybe I posted this in the wrong thread before.
> I restored 4 databases onto another SQL server and added
> sql logins. When I click on a login under
> Security I get this Exclamation message?
> "One or more databases are inaccessible and will not be displayed in the
> database access tab"
> I don't understand this message and couldn't find anything on it.
> thanks
> gv
That means one or more of your databases are unavailable, possibly in
Standby or Suspect mode...|||Thank You!!!!
:< )
"gv" <viator.gerry@.gmail.com> wrote in message
news:uPoo3Rj7GHA.140@.TK2MSFTNGP05.phx.gbl...
> Hi,
> Ok maybe I posted this in the wrong thread before.
> I restored 4 databases onto another SQL server and added
> sql logins. When I click on a login under
> Security I get this Exclamation message?
> "One or more databases are inaccessible and will not be displayed in the
> database access tab"
> I don't understand this message and couldn't find anything on it.
> thanks
> gv
>
>

Exclamation Message in Security logins

Hi,
Ok maybe I posted this in the wrong thread before.
I restored 4 databases onto another SQL server and added
sql logins. When I click on a login under
Security I get this Exclamation message?
"One or more databases are inaccessible and will not be displayed in the
database access tab"
I don't understand this message and couldn't find anything on it.
thanks
gvgv wrote:
> Hi,
> Ok maybe I posted this in the wrong thread before.
> I restored 4 databases onto another SQL server and added
> sql logins. When I click on a login under
> Security I get this Exclamation message?
> "One or more databases are inaccessible and will not be displayed in the
> database access tab"
> I don't understand this message and couldn't find anything on it.
> thanks
> gv
That means one or more of your databases are unavailable, possibly in
Standby or Suspect mode...|||gv wrote:
> Hi,
> Ok maybe I posted this in the wrong thread before.
> I restored 4 databases onto another SQL server and added
> sql logins. When I click on a login under
> Security I get this Exclamation message?
> "One or more databases are inaccessible and will not be displayed in the
> database access tab"
> I don't understand this message and couldn't find anything on it.
> thanks
> gv
That means one or more of your databases are unavailable, possibly in
Standby or Suspect mode...|||Thank You!!!!
:<)
"gv" <viator.gerry@.gmail.com> wrote in message
news:uPoo3Rj7GHA.140@.TK2MSFTNGP05.phx.gbl...
> Hi,
> Ok maybe I posted this in the wrong thread before.
> I restored 4 databases onto another SQL server and added
> sql logins. When I click on a login under
> Security I get this Exclamation message?
> "One or more databases are inaccessible and will not be displayed in the
> database access tab"
> I don't understand this message and couldn't find anything on it.
> thanks
> gv
>
>

Exchange of login for database user

I have a database with 2 users - 'dbo' and 'user1'.

Currently:
=> 'dbo' is tied to login - 'login1'
=> 'user1' is not tied to any login.

I want to change the login tied to the database users to:
=> 'dbo' tied to no login
=> 'user1' tied to 'login1'

I try to use:
sp_change_users_login 'Update_One', 'user1', 'login1'
But it give me an error said 'login1' is already tied to a user.

Then I try to remove the login tied to 'dbo' with this:
sp_change_users_login 'Update_One', 'dbo', ''
Then I get another error said that 'dbo' is a forbidden value for the
login name parameter.

--

Previously, all the while 'dbo' is not tied to any login and 'user1' is
tied to 'login1', but yesterday I restore the DB from the backup file
bring back from customer side, then I saw the login changed to the one
I mention above, I'm not sure whether is the DBA from customer side
change it or what, but is there any way for me to change the login back
to the state I want?

Thanks.

Peter CCHPeter CCH (petercch.wodoy@.gmail.com) writes:
> I have a database with 2 users - 'dbo' and 'user1'.
> Currently:
>=> 'dbo' is tied to login - 'login1'
>=> 'user1' is not tied to any login.
> I want to change the login tied to the database users to:
>=> 'dbo' tied to no login
>=> 'user1' tied to 'login1'

dbo is always tied to a login, since dbo is the database owner. But
you can change database owner with sp_changedbowner. Once you've done
that, you can connect user1 to login1.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I tried in this sequence:
1. sp_changedbowner 'user1'
2. sp_change_users_login 'Update_One', 'user1', 'login1'

But "2" still give me the same error:
----------
Server: Msg 15063, Level 16, State 1, Procedure sp_change_users_login,
Line 104
The login already has an account under a different user name.
----------

This is the view in database tree in Enterprise Manager when I click on
the user section (Databases > dataBaseName > Users), there's 2 users in
list.

Name Login Name Database Access
-----------------------
user1 Permit
dbo login1 Permit

Previously, for so many times I restore the backup DB obtained from
customer site to my server, it have this view

Name Login Name Database Access
-----------------------
user1 login1 Permit
dbo Permit

But yesterday once I restore it, the login1 is not tied to user1
anymore, instead, it tied to dbo.

Peter CCH|||Peter CCH (petercch.wodoy@.gmail.com) writes:
> I tried in this sequence:
> 1. sp_changedbowner 'user1'
> 2. sp_change_users_login 'Update_One', 'user1', 'login1'
> But "2" still give me the same error:
> ----------
> Server: Msg 15063, Level 16, State 1, Procedure sp_change_users_login,
> Line 104
> The login already has an account under a different user name.
> ----------

Of course. Since login1 owns the database, login1 maps to the user
dbo, and cannot map to the user user1. You need to change to a different
owner, for instance sa.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||You mean change the DB owner to 'sa', then only maps 'login1' to
'user1'?
I don't really understand what you mean.

Like this?
1. sp_changedbowner 'sa'
2. sp_changeuserslogin 'Update_One', 'user1', 'login1'

Please correct me if I'm wrong.

Thanks a lot.

Peter CCH|||Peter CCH (petercch.wodoy@.gmail.com) writes:
> You mean change the DB owner to 'sa', then only maps 'login1' to
> 'user1'?
> I don't really understand what you mean.

Yes. If you want login1 to map to user1 in the database, then someone
else must own the database than login1. The database owner always maps
to dbo.

> Like this?
> 1. sp_changedbowner 'sa'
> 2. sp_changeuserslogin 'Update_One', 'user1', 'login1'

Yes.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I tried the code, it still give me the same error.
I just notice there's something different for current state and before
the problem exist state:

I expand the SQL Server tree node, Security > Logins
Then I open the properties page of "login1" and go to "Database Access"
tab.

This is what it looks now:

Permit Database User
---------------
(checked) DB1 user1
(checked) DB2 user1
(checked) DB3 dbo

All that 3 database have "public" and "db_owner" role checked.

Before the problem occured, it looks like this:

Permit Database User
---------------
(checked) DB1 user1
(checked) DB2 user1
(checked) DB3 user1

For "DB 3" ... last time "User" column is "user1", but now it had
changed to "dbo".

QUESTION: Is there any way for me to change the "dbo" at column "User"
back to "user1"?

By the way, I login to the SQL Server with "login1".

Thanks.

Peter CCH|||Peter CCH (petercch.wodoy@.gmail.com) writes:
> I tried the code, it still give me the same error.
> I just notice there's something different for current state and before
> the problem exist state:
> I expand the SQL Server tree node, Security > Logins
> Then I open the properties page of "login1" and go to "Database Access"
> tab.
> This is what it looks now:
> Permit Database User
> ---------------
> (checked) DB1 user1
> (checked) DB2 user1
> (checked) DB3 dbo

Thus login1 is still the owner of DB3. Did you run sp_changedbowner in
DB3?

What does sp_helpdb say?

What does DB3..sp_helpuser say when run it in DB3?

> QUESTION: Is there any way for me to change the "dbo" at column "User"
> back to "user1"?

Yes, change the database owner of the database to anyone else by user1.
Then use sp_change_users_login, or simply drop and re-add user.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||OK, got you. Tested and working.

Now "user1" is tied to "login1".
But the user "dbo" still tied to a login, is this a must?
Cause last time the user "dbo" is not tied to any login.

Expanding SQL Server tree node, Database > (database name) > Users

Here is what it looks like now:

Name Login Name Database Access
-----------------------
user1 login1 Permit
dbo sa Permit

Last time, it is shown like below:

Name Login Name Database Access
-----------------------
user1 login1 Permit
dbo Permit

I try to use:
sp_change_users_login 'Update_One', 'dbo', ''
thinking of making the "dbo" have no login tie to it, but I got the
following error message:
================================
Server: Msg 15287, Level 16, State 1, Procedure sp_change_users_login,
Line 39
Terminating this procedure. 'dbo' is a forbidden value for the login
name parameter in this procedure.
================================

QUESTION: Is it possible to make the "dbo" have no login tie to it?

Thanks.

Peter CCH|||Peter CCH (petercch.wodoy@.gmail.com) writes:
> Now "user1" is tied to "login1".
> But the user "dbo" still tied to a login, is this a must?

Yes. A database must be owned by someone.

> Cause last time the user "dbo" is not tied to any login.

You get some funny things going when you move a database from one server
to another, but this is anomaly that should be corrected.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks for the info.
But I'm curious on one thing, since a database must be own by a user,
how come when I try to create a new database, the "dbo" user is not
tied to any login?

I tried create a new database, then expand the tree node and look at
the "Users" section, then I saw this:

Name Login Name Database Access
----------------------
dbo Permit

Above is the "Users" section of a newly created database on my PC, for
the purpose of to confirm whether a user must tied to a login, I just
wondering why the "dbo" is not tied to any login.

Any reason in behind?

Thanks.

Peter CCH|||Peter CCH (petercch.wodoy@.gmail.com) writes:
> But I'm curious on one thing, since a database must be own by a user,
> how come when I try to create a new database, the "dbo" user is not
> tied to any login?
> I tried create a new database, then expand the tree node and look at
> the "Users" section, then I saw this:
> Name Login Name Database Access
> ----------------------
> dbo Permit
> Above is the "Users" section of a newly created database on my PC, for
> the purpose of to confirm whether a user must tied to a login, I just
> wondering why the "dbo" is not tied to any login.

It appears that you only see a login name, if this is an SQL Server
login. So if the database is owned by a Windows login, you don't see
this. The same thing happens if you say "sp_grantdbaccess DOMAIN\user"
to permit access for a windows login, the LoginName column is NULL.

But that is only the trick of the eye. If you would try to add the
Windows user that owns the database, you would be told that is is
already there.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

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

Exchange data

Hi,
Is there a wahy to get AD user info in a SP?
I need a list of AD users with name, login name and email.
Thanks.
George.Hi.
I think there is not a function or something like that in SQL2005 that
retrieve all user from you Active Domain Controler (I'm not quiet sure about
that).
You can only get information abour your principals:
SELECT * FROM sys.server_principals
SELECT suser_name()
For advanced solution you must use CLR to get information about Active
Domain users.
Mladen.
"Microsoft" <g2@.bla.cl> wrote in message
news:OzjHniWOHHA.3668@.TK2MSFTNGP02.phx.gbl...
> Hi,
> Is there a wahy to get AD user info in a SP?
> I need a list of AD users with name, login name and email.
> Thanks.
> George.
>|||Microsoft wrote:
> Hi,
> Is there a wahy to get AD user info in a SP?
> I need a list of AD users with name, login name and email.
Getting the name and login name is possible. But since email is a
multivalued field, it can't be done out of the box.
http://articles.techrepublic.com.co...11-5259887.html|||Hi.
EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5',
'ADSDSOObject', 'adsdatasource'
SELECT *
FROM OPENQUERY (ADSI, 'SELECT givenName, sn FROM ''LDAP://
DC=DOMAIN,DC=COM''
where objectcategory=''User''')
After time of trying to do best thing in my query (and I succed), i found
this one (this is very useful):
http://www.atlantamdf.com/presentat...01_examples.txt
Second solution is, as I say earlier, to do some code in C# and call it from
SQL as assembly (try to modify this as you wish):
string rootQuery = LDAP://DC=DOMAINNAME, DC=COM;
DirectoryEntry root = new DirectoryEntry(rootQuery);
DirectorySearcher dirSearch = new DirectorySearcher(root);
//dirSearch.Filter = String.Format("(cn={0})",
"Administrators");
dirSearch.Filter = String.Format("(objectClass=user)");
SearchResultCollection results = dirSearch.FindAll();
ArrayList userNames = new ArrayList();
if (results != null)
{
foreach (SearchResult result in results)
{
for (int i = 0; i < result.Properties["name"].Count;
i++)
{
string user = (string)result.Properties["name"][i];
Console.WriteLine(user);
}
}
}
Console.ReadLine();
Mladen.
"Microsoft" <g2@.bla.cl> wrote in message
news:OzjHniWOHHA.3668@.TK2MSFTNGP02.phx.gbl...
> Hi,
> Is there a wahy to get AD user info in a SP?
> I need a list of AD users with name, login name and email.
> Thanks.
> George.
>

Exchange data

Hi,
Is there a wahy to get AD user info in a SP?
I need a list of AD users with name, login name and email.
Thanks.
George.
Microsoft wrote:
> Hi,
> Is there a wahy to get AD user info in a SP?
> I need a list of AD users with name, login name and email.
Getting the name and login name is possible. But since email is a
multivalued field, it can't be done out of the box.
http://articles.techrepublic.com.com/5100-6345_11-5259887.html

Exchange data

Hi,
Is there a wahy to get AD user info in a SP?
I need a list of AD users with name, login name and email.
Thanks.
George.Hi.
I think there is not a function or something like that in SQL2005 that
retrieve all user from you Active Domain Controler (I'm not quiet sure about
that).
You can only get information abour your principals:
SELECT * FROM sys.server_principals
SELECT suser_name()
For advanced solution you must use CLR to get information about Active
Domain users.
Mladen.
"Microsoft" <g2@.bla.cl> wrote in message
news:OzjHniWOHHA.3668@.TK2MSFTNGP02.phx.gbl...
> Hi,
> Is there a wahy to get AD user info in a SP?
> I need a list of AD users with name, login name and email.
> Thanks.
> George.
>|||Microsoft wrote:
> Hi,
> Is there a wahy to get AD user info in a SP?
> I need a list of AD users with name, login name and email.
Getting the name and login name is possible. But since email is a
multivalued field, it can't be done out of the box.
http://articles.techrepublic.com.com/5100-6345_11-5259887.html|||Hi.
EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5',
'ADSDSOObject', 'adsdatasource'
SELECT *
FROM OPENQUERY (ADSI, 'SELECT givenName, sn FROM ''LDAP://
DC=DOMAIN,DC=COM''
where objectcategory=''User''')
After time of trying to do best thing in my query (and I succed), i found
this one (this is very useful):
http://www.atlantamdf.com/presentations/AtlantaMDF_111201_examples.txt
Second solution is, as I say earlier, to do some code in C# and call it from
SQL as assembly (try to modify this as you wish):
string rootQuery = LDAP://DC=DOMAINNAME, DC=COM;
DirectoryEntry root = new DirectoryEntry(rootQuery);
DirectorySearcher dirSearch = new DirectorySearcher(root);
//dirSearch.Filter = String.Format("(cn={0})",
"Administrators");
dirSearch.Filter = String.Format("(objectClass=user)");
SearchResultCollection results = dirSearch.FindAll();
ArrayList userNames = new ArrayList();
if (results != null)
{
foreach (SearchResult result in results)
{
for (int i = 0; i < result.Properties["name"].Count;
i++)
{
string user = (string)result.Properties["name"][i];
Console.WriteLine(user);
}
}
}
Console.ReadLine();
Mladen.
"Microsoft" <g2@.bla.cl> wrote in message
news:OzjHniWOHHA.3668@.TK2MSFTNGP02.phx.gbl...
> Hi,
> Is there a wahy to get AD user info in a SP?
> I need a list of AD users with name, login name and email.
> Thanks.
> George.
>

Exception occurred

Hi,
I installed SQL Server 2000 Dev Edition on a Windows XP SP2, but whenever I
login, and try to open a database's table, or to re-set the SA's password,
(or maybe other command), I will always get this error message,
ODBC: Msg 0, Level 19, State 1
SqlDumpExceptionHandler: Process 52 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
Anybody knows what this is and how to overcome?
I have applied the SP3 on the SQL Server.
And my SA's password was set to NULL.
Thanks in advance
FrankYou should contact Microsoft PSS to assist you with this problem.
--
Ryan Stonecipher
Microsoft Sql Server Storage Engine, DBCC
This posting is provided "AS IS" with no warranties, and confers no rights.
"Frank" <wangping67@.163.com> wrote in message
news:emysEIIBFHA.3120@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I installed SQL Server 2000 Dev Edition on a Windows XP SP2, but whenever
> I login, and try to open a database's table, or to re-set the SA's
> password, (or maybe other command), I will always get this error message,
> ODBC: Msg 0, Level 19, State 1
> SqlDumpExceptionHandler: Process 52 generated fatal exception c0000005
> EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
> Anybody knows what this is and how to overcome?
> I have applied the SP3 on the SQL Server.
> And my SA's password was set to NULL.
> Thanks in advance
> Frank
>