Blocks

Hello,Why an sp autoblock '
If exec my sp and view the connections with sp_who i watch te id of my
connection in the column blk of the result.
Why succedes this '
Thanks in advance.Check out:
http://support.microsoft.com/default.aspx/kb/906344
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Luis Tarzia" <tarzialuis@.ciudad.com.ar> wrote in message
news:euJLKt%23xGHA.4840@.TK2MSFTNGP04.phx.gbl...
Hello,Why an sp autoblock '
If exec my sp and view the connections with sp_who i watch te id of my
connection in the column blk of the result.
Why succedes this '
Thanks in advance.|||Thanks !!!
But,what i configured in the server because the time of blocked is over 20
seconds ?
Thanks in advance.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23mmGQy%23xGHA.2516@.TK2MSFTNGP06.phx.gbl...
> Check out:
> http://support.microsoft.com/default.aspx/kb/906344
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Luis Tarzia" <tarzialuis@.ciudad.com.ar> wrote in message
> news:euJLKt%23xGHA.4840@.TK2MSFTNGP04.phx.gbl...
> Hello,Why an sp autoblock '
> If exec my sp and view the connections with sp_who i watch te id of my
> connection in the column blk of the result.
> Why succedes this '
> Thanks in advance.
>|||You'll have to tell us what you "configured".
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Luis Tarzia" <tarzialuis@.ciudad.com.ar> wrote in message
news:eidPzzAyGHA.3844@.TK2MSFTNGP06.phx.gbl...
Thanks !!!
But,what i configured in the server because the time of blocked is over 20
seconds ?
Thanks in advance.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23mmGQy%23xGHA.2516@.TK2MSFTNGP06.phx.gbl...
> Check out:
> http://support.microsoft.com/default.aspx/kb/906344
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Luis Tarzia" <tarzialuis@.ciudad.com.ar> wrote in message
> news:euJLKt%23xGHA.4840@.TK2MSFTNGP04.phx.gbl...
> Hello,Why an sp autoblock '
> If exec my sp and view the connections with sp_who i watch te id of my
> connection in the column blk of the result.
> Why succedes this '
> Thanks in advance.
>

Blocking...

Hi just read an article on the internet that states
"A quick way to resolve a blocking problem is to disconnect the
connection blocking the rest of the users. All you have to do is ask
your database administrator to execute the KILL statement. Obviously,
this is not a permanent solution, and won't really help anything except
take care of an immediate need."
Now when i run sp_who ... i see that the spid that is blocking is the
same as the spid itself...
Now the above article states that killing the one that is blocking the
rest of the other spids is ok ...
But what if its the same SPID that is blocking ...what does it signify
& whats the solution
ThanksI'm not too sure what version of SQL Server you're using, but if it is SQL
2000 sp4, this might be related to latch waits which now has enhanced
reporting: http://support.microsoft.com/default.aspx/kb/906344.
Alternatively it might be a parallel operation across multiple processors.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||On 31 Aug 2006 06:59:03 -0700, Double_B wrote:
>Hi just read an article on the internet that states
>"A quick way to resolve a blocking problem is to disconnect the
>connection blocking the rest of the users. All you have to do is ask
>your database administrator to execute the KILL statement. Obviously,
>this is not a permanent solution, and won't really help anything except
>take care of an immediate need."
Hi Double_B,
Also keep in mind that the transaction you kill has to rollback all
changes before it will release any locks. If it''s mainly a reporting
job, that will probably be pretty fast - but if you rollback a job that
has been changing table data for the last two hours, you can expect the
rollback to take as least as long!
--
Hugo Kornelis, SQL Server MVP

blocking

I am trying to create a SQL Profiler trace.
I am confused because the Sessions event class only has the Existing
connection event, and no Connect or Disconnect events.
Why is this? Microsoft support articles like #224453 suggest including the
Connect and Disconnect events in a trace, but I can't find them (or some
others).
Thanks
BillLooks like that article is talking about SQL Server 7.0 Profiler. And the
same is not applicable to SQL Server 2000 Profiler. In SQL Server 2000
Profiler, you should be using the 'Audit Login' and 'Audit Logout' evernts
of the 'Security Audit' event class.
--
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"bille" <belgie@.datamti.com> wrote in message
news:OkCcgHQaEHA.212@.TK2MSFTNGP12.phx.gbl...
> I am trying to create a SQL Profiler trace.
> I am confused because the Sessions event class only has the Existing
> connection event, and no Connect or Disconnect events.
> Why is this? Microsoft support articles like #224453 suggest including
the
> Connect and Disconnect events in a trace, but I can't find them (or some
> others).
> Thanks
> Bill
>|||What you want is under the Security Audit Event Class. Look for Audit Login
and Audit Logout
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"bille" <belgie@.datamti.com> wrote in message
news:OkCcgHQaEHA.212@.TK2MSFTNGP12.phx.gbl...
> I am trying to create a SQL Profiler trace.
> I am confused because the Sessions event class only has the Existing
> connection event, and no Connect or Disconnect events.
> Why is this? Microsoft support articles like #224453 suggest including
the
> Connect and Disconnect events in a trace, but I can't find them (or some
> others).
> Thanks
> Bill
>

BLock records using UPDLOCK

Hi all,
here my question :
I have 2 applications.

connection 1.
one does select max(grp) from orv and one does select max(grp) from orh. orh is the historical file from orv. We did this to know which is the greather grp between these 2 files.
After having did this, we add 1 at grp field.
we insert into orv the record max(grp) + 1

connection 2.
an other application could insert at the same time record in this table orv with same parameters.

my problem is the following. I need to block record in orv table either on the select ( connection 1) or Insert ( connection 2) to avoid having select max(grp) + 1 on orv at connection 1 and Insert a record into orv at connection 2.
I believe I need to use HOLDLOCK, UPDLOCK.
but I have not the habitude to use them.

Can I do this ?
connection 1
select max(grp) from orv WITH HOLDLOCK
connection 2
what should I use to avoid lock when I need to insert into orv. ?

thanks for your quick answerCan you explain what you are trying to do from a business or "real world" perspective? It sounds to me like you've dropped into the technology so far that you've lost sight of the original goal, and are making this a lot harder than it needs to be!

-PatP

Block MS Office Connection

SQL Server 2000.
Is there a simple way to block MS Office connections to a SQL Server database?
Thanks,
TomEasy, dont give them a login to use.
"T Morris" <TMorris@.discussions.microsoft.com> wrote in message
news:1C16D499-3B03-4252-A474-042650A67385@.microsoft.com...
> SQL Server 2000.
> Is there a simple way to block MS Office connections to a SQL Server
> database?
>
> Thanks,
> Tom|||Yes, disable the network adapter.
The long answer is no. If the user has a valid SQL Server username/password
(or) their network identity or group has access to the server/database, then
there are many applications, including Office products, that can be used to
access the data in the database.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"T Morris" <TMorris@.discussions.microsoft.com> wrote in message
news:1C16D499-3B03-4252-A474-042650A67385@.microsoft.com...
> SQL Server 2000.
> Is there a simple way to block MS Office connections to a SQL Server
> database?
>
> Thanks,
> Tom|||Hi Tom
One option may be to use an Application Role for your own application and
only grant the privileges needed to access the tables to this role.
If you restrict access to be only by stored procedures and don't use dynamic
SQL then your users will only be able to execute these procedures and not
access the data directly regardless of the application they are using.
John
"T Morris" wrote:
> SQL Server 2000.
> Is there a simple way to block MS Office connections to a SQL Server database?
>
> Thanks,
> Tom

Block MS Office Connection

SQL Server 2000.
Is there a simple way to block MS Office connections to a SQL Server databas
e?
Thanks,
TomEasy, dont give them a login to use.
"T Morris" <TMorris@.discussions.microsoft.com> wrote in message
news:1C16D499-3B03-4252-A474-042650A67385@.microsoft.com...
> SQL Server 2000.
> Is there a simple way to block MS Office connections to a SQL Server
> database?
>
> Thanks,
> Tom|||Yes, disable the network adapter.
The long answer is no. If the user has a valid SQL Server username/password
(or) their network identity or group has access to the server/database, then
there are many applications, including Office products, that can be used to
access the data in the database.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"T Morris" <TMorris@.discussions.microsoft.com> wrote in message
news:1C16D499-3B03-4252-A474-042650A67385@.microsoft.com...
> SQL Server 2000.
> Is there a simple way to block MS Office connections to a SQL Server
> database?
>
> Thanks,
> Tom|||Hi Tom
One option may be to use an Application Role for your own application and
only grant the privileges needed to access the tables to this role.
If you restrict access to be only by stored procedures and don't use dynamic
SQL then your users will only be able to execute these procedures and not
access the data directly regardless of the application they are using.
John
"T Morris" wrote:

> SQL Server 2000.
> Is there a simple way to block MS Office connections to a SQL Server datab
ase?
>
> Thanks,
> Tom