Showing posts with label sql2k. Show all posts
Showing posts with label sql2k. Show all posts

Sunday, March 11, 2012

Blocking problem

SQL2K, sp3
Active Server Pages front-end

I am having a blocking problem with a spid executing a stored
procedure that does no updates. When I look at the locks the blocking
spid has, some of them have lock mode "IS". Looking in BOL, I see "S"
is shared, but I don't see "IS". Can anyone tell me what lock mode
"IS" is?

And how could a stored procedure that does no updates be guilty of
blocking?
There are no cursors in it.

Thanks![posted and mailed, please reply in news]

Hal (hforman1@.cfl.rr.com) writes:
> I am having a blocking problem with a spid executing a stored
> procedure that does no updates. When I look at the locks the blocking
> spid has, some of them have lock mode "IS". Looking in BOL, I see "S"
> is shared, but I don't see "IS". Can anyone tell me what lock mode
> "IS" is?

Intent Shared. If memory serves, if a process has a lock on row level,
it will also take out an intent lock on table level, at least under
some circumstances.

> And how could a stored procedure that does no updates be guilty of
> blocking?

It could block a writer that wishes to update one of the rows that the
reader locks.

To get a better hold of the locking situation, it's probably better to
lock at the blocking processes, and see what lock it is blocked on. I
have a routine on my web site that is conventient for this kind of thing.
You get all active processes (and if a process holds a lock it's active
by definition), their locks and their current statements. Spot the blocked
processes and lock for locks with status WAIY. You find it at
http://www.sommarskog.se/sqlutil/aba_lockinfo.html.

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

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

Sunday, February 12, 2012

Bizarre login behavior.

sql2k
sp3
I have two db's, each with a WINNT login named DBATest. DBATest is a member
of Security Admins as well as db_accessadmin and db_securityadmin in both
DB's. However, in one DB he can see the Users and Roles, and in the other he
cant. How can it be?
TIA, ChrisRIt turned out to be that Public was granted Select on all the System tables
in one DB but not the other. It would seem that if a user was added to
db_accessadmin and db_securityadmin, something like a trigger would make sure
the correct systems tables had Select, but oh well.
"ChrisR" wrote:
> sql2k
> sp3
> I have two db's, each with a WINNT login named DBATest. DBATest is a member
> of Security Admins as well as db_accessadmin and db_securityadmin in both
> DB's. However, in one DB he can see the Users and Roles, and in the other he
> cant. How can it be?
> TIA, ChrisR

Friday, February 10, 2012

Bit or character data type?

SQL2K
SP4
Im creating a DB that has several Bit coulmns. The data is coming from a
mainframe. When one stored proc is run, the result set needs to have a "y",
an "n", or a " '' " (space) as thats the way current systems work. Another
proc needs to return "Eligible" or "Not eligible". I had originally thought
of using a Bit fields, but now I'm wondering why. It seems to me that if I
use Bit, I then need to convert it to display one of the things just listed.
Whereas if I use character coulmns and add check constraints to the to make
sure only certain values can be added, that conversion doesnt need to occur
when the data is being retrieved from the DB. Id have to imagine Im not the
first one to think of this as no app will probably ever return a 1 or a 0, so
Im wondering what others thoughts on the matter are?A couple of things to consider, a bit column by itself takes up one byte.
If eight of them are together in a table you can save some space. To put
"Not eligible" takes 12 or 14 bytes depending on char or varchar. Next bit
has a special behavior that says any integer other than 0 is 1. In the end
I like to format results in the reporting tool so I would generally choose a
tinyint with a check constraint. But in the case of 'y', 'n', or space
char(1) would be pretty effective.
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:57F596FC-365B-4EF5-907E-041498A0D11B@.microsoft.com...
> SQL2K
> SP4
> Im creating a DB that has several Bit coulmns. The data is coming from a
> mainframe. When one stored proc is run, the result set needs to have a
> "y",
> an "n", or a " '' " (space) as thats the way current systems work. Another
> proc needs to return "Eligible" or "Not eligible". I had originally
> thought
> of using a Bit fields, but now I'm wondering why. It seems to me that if I
> use Bit, I then need to convert it to display one of the things just
> listed.
> Whereas if I use character coulmns and add check constraints to the to
> make
> sure only certain values can be added, that conversion doesnt need to
> occur
> when the data is being retrieved from the DB. Id have to imagine Im not
> the
> first one to think of this as no app will probably ever return a 1 or a 0,
> so
> Im wondering what others thoughts on the matter are?|||Chris,
Eight or less bit columns willl occupy only one byte, therefore the bit type
should be the prefered choice if you have multiple such columns in the same
table. From SQL Server books on-line:
"Microsoft® SQL ServerT optimizes the storage used for bit columns. If there
are 8 or fewer bit columns in a table, the columns are stored as 1 byte. If
there are from 9 through 16 bit columns, they are stored as 2 bytes, and so
on."
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ba-bz_2it0.asp
Leo
"Danny" <nomailbox@.nowhere.com> wrote in message
news:Uiqfg.13100$lN5.1551@.trnddc04...
>A couple of things to consider, a bit column by itself takes up one byte.
>If eight of them are together in a table you can save some space. To put
>"Not eligible" takes 12 or 14 bytes depending on char or varchar. Next bit
>has a special behavior that says any integer other than 0 is 1. In the end
>I like to format results in the reporting tool so I would generally choose
>a tinyint with a check constraint. But in the case of 'y', 'n', or space
>char(1) would be pretty effective.
> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
> news:57F596FC-365B-4EF5-907E-041498A0D11B@.microsoft.com...
>> SQL2K
>> SP4
>> Im creating a DB that has several Bit coulmns. The data is coming from a
>> mainframe. When one stored proc is run, the result set needs to have a
>> "y",
>> an "n", or a " '' " (space) as thats the way current systems work.
>> Another
>> proc needs to return "Eligible" or "Not eligible". I had originally
>> thought
>> of using a Bit fields, but now I'm wondering why. It seems to me that if
>> I
>> use Bit, I then need to convert it to display one of the things just
>> listed.
>> Whereas if I use character coulmns and add check constraints to the to
>> make
>> sure only certain values can be added, that conversion doesnt need to
>> occur
>> when the data is being retrieved from the DB. Id have to imagine Im not
>> the
>> first one to think of this as no app will probably ever return a 1 or a
>> 0, so
>> Im wondering what others thoughts on the matter are?
>