Can't select from 1 table

Any ideas why I can't select anything from 1 table? If I try to execute a
select statement (even Top 1), the statement just hangs. We constantly have
to restart SQL Server to "free" up the problem, but it keeps coming back.
Any ideas what's going on?
Thanks.
Sql 2000
Have your tried to select data from the table using NOLOCK hint?
There can be some blocking problem (some locks on your table that do not
allow other locks). Use sp_lock system stored procedure to find the locks on
your table (especially look for X - eXclusive locks, identify your table by
object_id - use OBJECT_ID function to know the ID). Then you can use sp_who
system stored procedure to find the process (its spid is returned by
sp_lock) that puts those locks and finally you can kill the proper session
(using kill statement). You cal also use Enterprise Manager and its
Management section.
The remedies for blocking:
- always close (commit or rollback) transactions,
- keep transactions short,
- use the proper transaction isolation level.
If it is blocking problem, try to isolate the query or procedure causing the
problem (use Profiler to catch batch executions, locks being required and
lock escalations).
Regards
Pawel Potasinski
Uytkownik <-> napisa w wiadomoci
news:OPD%232lT0HHA.1484@.TK2MSFTNGP06.phx.gbl...
> Any ideas why I can't select anything from 1 table? If I try to execute a
> select statement (even Top 1), the statement just hangs. We constantly
> have to restart SQL Server to "free" up the problem, but it keeps coming
> back.
> Any ideas what's going on?
> Thanks.
> Sql 2000
>
|||Thanks...we just found that there was a lock on the table by a process id,
but this has happened several times in the last few days, whereas it never
used to happen.
Still looking into it. Thanks for the info.
"Pawel Potasinski" <pawel.potasinski@.gmail.com> wrote in message
news:eUxUwtT0HHA.4816@.TK2MSFTNGP04.phx.gbl...
> Have your tried to select data from the table using NOLOCK hint?
> There can be some blocking problem (some locks on your table that do not
> allow other locks). Use sp_lock system stored procedure to find the locks
> on your table (especially look for X - eXclusive locks, identify your
> table by object_id - use OBJECT_ID function to know the ID). Then you can
> use sp_who system stored procedure to find the process (its spid is
> returned by sp_lock) that puts those locks and finally you can kill the
> proper session (using kill statement). You cal also use Enterprise Manager
> and its Management section.
> The remedies for blocking:
> - always close (commit or rollback) transactions,
> - keep transactions short,
> - use the proper transaction isolation level.
> If it is blocking problem, try to isolate the query or procedure causing
> the problem (use Profiler to catch batch executions, locks being required
> and lock escalations).
> --
> Regards
> Pawel Potasinski
>
> Uytkownik <-> napisa w wiadomoci
> news:OPD%232lT0HHA.1484@.TK2MSFTNGP06.phx.gbl...
>

Can't select from 1 table

Any ideas why I can't select anything from 1 table? If I try to execute a
select statement (even Top 1), the statement just hangs. We constantly have
to restart SQL Server to "free" up the problem, but it keeps coming back.
Any ideas what's going on?
Thanks.
Sql 2000Have your tried to select data from the table using NOLOCK hint?
There can be some blocking problem (some locks on your table that do not
allow other locks). Use sp_lock system stored procedure to find the locks on
your table (especially look for X - eXclusive locks, identify your table by
object_id - use OBJECT_ID function to know the ID). Then you can use sp_who
system stored procedure to find the process (its spid is returned by
sp_lock) that puts those locks and finally you can kill the proper session
(using kill statement). You cal also use Enterprise Manager and its
Management section.
The remedies for blocking:
- always close (commit or rollback) transactions,
- keep transactions short,
- use the proper transaction isolation level.
If it is blocking problem, try to isolate the query or procedure causing the
problem (use Profiler to catch batch executions, locks being required and
lock escalations).
--
Regards
Pawel Potasinski
U¿ytkownik <-> napisa³ w wiadomo¶ci
news:OPD%232lT0HHA.1484@.TK2MSFTNGP06.phx.gbl...
> Any ideas why I can't select anything from 1 table? If I try to execute a
> select statement (even Top 1), the statement just hangs. We constantly
> have to restart SQL Server to "free" up the problem, but it keeps coming
> back.
> Any ideas what's going on?
> Thanks.
> Sql 2000
>|||Thanks...we just found that there was a lock on the table by a process id,
but this has happened several times in the last few days, whereas it never
used to happen.
Still looking into it. Thanks for the info.
"Pawel Potasinski" <pawel.potasinski@.gmail.com> wrote in message
news:eUxUwtT0HHA.4816@.TK2MSFTNGP04.phx.gbl...
> Have your tried to select data from the table using NOLOCK hint?
> There can be some blocking problem (some locks on your table that do not
> allow other locks). Use sp_lock system stored procedure to find the locks
> on your table (especially look for X - eXclusive locks, identify your
> table by object_id - use OBJECT_ID function to know the ID). Then you can
> use sp_who system stored procedure to find the process (its spid is
> returned by sp_lock) that puts those locks and finally you can kill the
> proper session (using kill statement). You cal also use Enterprise Manager
> and its Management section.
> The remedies for blocking:
> - always close (commit or rollback) transactions,
> - keep transactions short,
> - use the proper transaction isolation level.
> If it is blocking problem, try to isolate the query or procedure causing
> the problem (use Profiler to catch batch executions, locks being required
> and lock escalations).
> --
> Regards
> Pawel Potasinski
>
> U¿ytkownik <-> napisa³ w wiadomo¶ci
> news:OPD%232lT0HHA.1484@.TK2MSFTNGP06.phx.gbl...
>> Any ideas why I can't select anything from 1 table? If I try to execute a
>> select statement (even Top 1), the statement just hangs. We constantly
>> have to restart SQL Server to "free" up the problem, but it keeps coming
>> back.
>> Any ideas what's going on?
>> Thanks.
>> Sql 2000
>

Can't select from 1 table

Any ideas why I can't select anything from 1 table? If I try to execute a
select statement (even Top 1), the statement just hangs. We constantly have
to restart SQL Server to "free" up the problem, but it keeps coming back.
Any ideas what's going on?
Thanks.
Sql 2000Have your tried to select data from the table using NOLOCK hint?
There can be some blocking problem (some locks on your table that do not
allow other locks). Use sp_lock system stored procedure to find the locks on
your table (especially look for X - eXclusive locks, identify your table by
object_id - use OBJECT_ID function to know the ID). Then you can use sp_who
system stored procedure to find the process (its spid is returned by
sp_lock) that puts those locks and finally you can kill the proper session
(using kill statement). You cal also use Enterprise Manager and its
Management section.
The remedies for blocking:
- always close (commit or rollback) transactions,
- keep transactions short,
- use the proper transaction isolation level.
If it is blocking problem, try to isolate the query or procedure causing the
problem (use Profiler to catch batch executions, locks being required and
lock escalations).
Regards
Pawel Potasinski
Uytkownik <-> napisa w wiadomoci
news:OPD%232lT0HHA.1484@.TK2MSFTNGP06.phx.gbl...
> Any ideas why I can't select anything from 1 table? If I try to execute a
> select statement (even Top 1), the statement just hangs. We constantly
> have to restart SQL Server to "free" up the problem, but it keeps coming
> back.
> Any ideas what's going on?
> Thanks.
> Sql 2000
>|||Thanks...we just found that there was a lock on the table by a process id,
but this has happened several times in the last few days, whereas it never
used to happen.
Still looking into it. Thanks for the info.
"Pawel Potasinski" <pawel.potasinski@.gmail.com> wrote in message
news:eUxUwtT0HHA.4816@.TK2MSFTNGP04.phx.gbl...
> Have your tried to select data from the table using NOLOCK hint?
> There can be some blocking problem (some locks on your table that do not
> allow other locks). Use sp_lock system stored procedure to find the locks
> on your table (especially look for X - eXclusive locks, identify your
> table by object_id - use OBJECT_ID function to know the ID). Then you can
> use sp_who system stored procedure to find the process (its spid is
> returned by sp_lock) that puts those locks and finally you can kill the
> proper session (using kill statement). You cal also use Enterprise Manager
> and its Management section.
> The remedies for blocking:
> - always close (commit or rollback) transactions,
> - keep transactions short,
> - use the proper transaction isolation level.
> If it is blocking problem, try to isolate the query or procedure causing
> the problem (use Profiler to catch batch executions, locks being required
> and lock escalations).
> --
> Regards
> Pawel Potasinski
>
> Uytkownik <-> napisa w wiadomoci
> news:OPD%232lT0HHA.1484@.TK2MSFTNGP06.phx.gbl...
>

Cant get windows authentication in Mixed Mode to work .... any ideas?

Hey all,
I've got SQL 2K running in mixed mode. I created a windows acct (mytest) in the administrator group, and then created a corresponding SQL account (mytest) using the windows authentication. In the EM it comes up as a "Windows User" type of acct. (The account is not a domain account but a workstation one).

I've configured my app to use that account for access to the database but I get the following error message when I send the connection request to the datasource:

Connection Failed:
SQL State:28000
SQL Server Error: 18456
Login failed for user myworkstation\mytest

I can connect just fine using the sql accounts, but it doesn't want to play nice when I need the windows authentication portion to work.
What exactly am I doing wrong?
Any input is greatly appreciated !
Regards,
TammyA couple of things come to mind...

1. If mytest is a member of the workstation Administrators group, then you do not (necessarily) need a separate SQL account for it. The BUILTIN\Administrators account permits all members of the local Administrators group to have access (in the default config anyway).

2. If you have denied access to the BUILTIN\Administrators group, then myWorkstation\mytest will not have access (Deny trumps permit).

3. Just a head check: you said that myworkstation\mytest was NOT a domain account (when you set it up). You didn't by chance login under the domain?

Just my $ .02. It's not much, I'll grant you, but it was what I could come up with.

hmscott|||Mr. Scott !
I'm such an idiot ....
I had forgotten that I denied access to the builtin Administrator account. I turned it back on and VOILA !
Mr. Scott - 1
Tammy - 0
Gracias !

Originally posted by hmscott
A couple of things come to mind...

1. If mytest is a member of the workstation Administrators group, then you do not (necessarily) need a separate SQL account for it. The BUILTIN\Administrators account permits all members of the local Administrators group to have access (in the default config anyway).

2. If you have denied access to the BUILTIN\Administrators group, then myWorkstation\mytest will not have access (Deny trumps permit).

3. Just a head check: you said that myworkstation\mytest was NOT a domain account (when you set it up). You didn't by chance login under the domain?

Just my $ .02. It's not much, I'll grant you, but it was what I could come up with.

hmscott