Help with sp_lock output

I am trying to figure out something in the dump of my sp_lock output
(sql server 2000)
spid Db Objectid IndId Type
Resource Mode Status
373 QADB 0 0 PAG
1:204097 S WAIT
What does an object id = 0 mean? i thought this always show the id of
an actual table
Hi Derek,
The output you provided means that a shared lock is held on a page, not on a
table, hence the 0 value for objectid. Resource column states that the lock
is held on file id 1 page number 204097.
To easily work with locks and/or deadlocks, I suggest you try out the tool
called SQL Deadlock Detector. It monitors your database for locks and
deadlocks and
provides complete information on captured events. It tells you everything
you need to know (locked objects, blocked statements, blocking statements,
etc.) to solve your blocking/deadlock problems. The great thing about this
tool is it's event diagram which makes it exremely easy to see what exactly
is going on.
You can download it from here:
http://lakesidesql.com/downloads/DLD2/2_0_2007_809/DeadlockDetector2_Setup_08-09-2007.zip.
I've been using it for quite a while now (I purchased it) and find it very
handy and useful.
HTH.
"Derek" <gepetto_2000@.yahoo.com> wrote in message
news:1187376178.875402.209170@.d55g2000hsg.googlegr oups.com...
>I am trying to figure out something in the dump of my sp_lock output
> (sql server 2000)
> spid Db Objectid IndId Type
> Resource Mode Status
> 373 QADB 0 0 PAG
> 1:204097 S WAIT
> What does an object id = 0 mean? i thought this always show the id of
> an actual table
>
sql

Help with sp_lock output

I am trying to figure out something in the dump of my sp_lock output
(sql server 2000)
spid Db Objectid IndId Type
Resource Mode Status
373 QADB 0 0 PAG
1:204097 S WAIT
What does an object id = 0 mean? i thought this always show the id of
an actual tableHi Derek,
The output you provided means that a shared lock is held on a page, not on a
table, hence the 0 value for objectid. Resource column states that the lock
is held on file id 1 page number 204097.
To easily work with locks and/or deadlocks, I suggest you try out the tool
called SQL Deadlock Detector. It monitors your database for locks and
deadlocks and
provides complete information on captured events. It tells you everything
you need to know (locked objects, blocked statements, blocking statements,
etc.) to solve your blocking/deadlock problems. The great thing about this
tool is it's event diagram which makes it exremely easy to see what exactly
is going on.
You can download it from here:
http://lakesidesql.com/downloads/DL...br />
007.zip.
I've been using it for quite a while now (I purchased it) and find it very
handy and useful.
HTH.
"Derek" <gepetto_2000@.yahoo.com> wrote in message
news:1187376178.875402.209170@.d55g2000hsg.googlegroups.com...
>I am trying to figure out something in the dump of my sp_lock output
> (sql server 2000)
> spid Db Objectid IndId Type
> Resource Mode Status
> 373 QADB 0 0 PAG
> 1:204097 S WAIT
> What does an object id = 0 mean? i thought this always show the id of
> an actual table
>

Help with sp_lock output

I am trying to figure out something in the dump of my sp_lock output
(sql server 2000)
spid Db Objectid IndId Type
Resource Mode Status
373 QADB 0 0 PAG
1:204097 S WAIT
What does an object id = 0 mean? i thought this always show the id of
an actual tableHi Derek,
The output you provided means that a shared lock is held on a page, not on a
table, hence the 0 value for objectid. Resource column states that the lock
is held on file id 1 page number 204097.
To easily work with locks and/or deadlocks, I suggest you try out the tool
called SQL Deadlock Detector. It monitors your database for locks and
deadlocks and
provides complete information on captured events. It tells you everything
you need to know (locked objects, blocked statements, blocking statements,
etc.) to solve your blocking/deadlock problems. The great thing about this
tool is it's event diagram which makes it exremely easy to see what exactly
is going on.
You can download it from here:
http://lakesidesql.com/downloads/DLD2/2_0_2007_809/DeadlockDetector2_Setup_08-09-2007.zip.
I've been using it for quite a while now (I purchased it) and find it very
handy and useful.
HTH.
"Derek" <gepetto_2000@.yahoo.com> wrote in message
news:1187376178.875402.209170@.d55g2000hsg.googlegroups.com...
>I am trying to figure out something in the dump of my sp_lock output
> (sql server 2000)
> spid Db Objectid IndId Type
> Resource Mode Status
> 373 QADB 0 0 PAG
> 1:204097 S WAIT
> What does an object id = 0 mean? i thought this always show the id of
> an actual table
>

Help with Security Model

Hi all,

I need some sugestions from all of you about setting up security model in
our SQL2000 box.

The server was setup using Mixed mode. However, all the applications
(web and MS access) access the server using "sa" userid.

There are several databases in our server. Ex: (DB1,DB2,DB3,DB4 and DB5)

Application 1: need read/write access to DB1,DB2 and DB3
Application 2: need read/write access to DB5
Application 3: need read/write access to DB4 and DB3

Should I set up three userids and give them the dbo access to those
database that they need to use?

Does that make any sense to you?

Thank you for all your suggestionThe 'sa' account should NEVER be used by ANY application or user (other than the DBA, and then carefully.)

Use three different UID/Pwd for the applications and GRANT permissions that way.|||In addition, unless they really NEED dbo, just GRANT the minimum they need.|||I'm a firm believer in the "principle of least privlege", meaning you create as many accounts as you need and only give each one the privleges that it needs to do its job. Check out the predefined database roles (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_help_5omd.asp) to see if your accounts even need dbo, or if a combination of db_datareader and db_datawriter would do.

-PatP|||I take it a bit further than data_reader/writer and grant Select/Insert/Update/Delete permissions as required. For instance, a financial application allows Inserts but NO updates. Once a record has been inserted, that's the way it stays. Corrections are made by inserting another record to adjust the transaction (along with an explanation.) Auditors seem to prefer this for following the money trail. Good thing I'm sa... :)|||Thank you for all your suggestions.

Help with returning too much data

I am running the below query and getting back results that have the word
"mode" in it. Isn't the keyword CONTAINS supposed to treat my search
expression as one word? Can someone show me what is wrong with this query so
that it returns only records that have the exact search expression "mode-4"
in it? Thank you.
SELECT MyFields
FROM MyTable M
LEFT JOIN Table1 T1 ON T1.Field1 = M.Field1
LEFT JOIN Table2 T2 ON T2.Field1 = M.Field2
LEFT JOIN Table3 T3 ON T3.Field1 = M.Field3
LEFT JOIN Table4 T4 ON T4.Field1 = M.Field4
WHERE CONTAINS( M.* , '"mode-4"' ) ORDER BY M.Field1
are the fields in Table1, Table2, Table3, Table4 and MyFields fulltext
indexed or are they integer values?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
news:82826E0A-C56B-4B47-B811-95F8F7075530@.microsoft.com...
>I am running the below query and getting back results that have the word
> "mode" in it. Isn't the keyword CONTAINS supposed to treat my search
> expression as one word? Can someone show me what is wrong with this query
> so
> that it returns only records that have the exact search expression
> "mode-4"
> in it? Thank you.
> SELECT MyFields
> FROM MyTable M
> LEFT JOIN Table1 T1 ON T1.Field1 = M.Field1
> LEFT JOIN Table2 T2 ON T2.Field1 = M.Field2
> LEFT JOIN Table3 T3 ON T3.Field1 = M.Field3
> LEFT JOIN Table4 T4 ON T4.Field1 = M.Field4
> WHERE CONTAINS( M.* , '"mode-4"' ) ORDER BY M.Field1
|||Not sure what you mean by "are they integer values", but the table that
contains MyFields is full-text indexed. Do tables 1,2,3, and 4 need to be
full-text indexed? Course, I'm thinking yes since you asked the question
"Hilary Cotter" wrote:

> are the fields in Table1, Table2, Table3, Table4 and MyFields fulltext
> indexed or are they integer values?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
> news:82826E0A-C56B-4B47-B811-95F8F7075530@.microsoft.com...
>
>
|||Perhaps if you could post the schema. For the record mode-4 is indexed and
queried two separate words. If 4 is not in your noise word list this should
work.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
news:E3738AD4-7A01-40C2-95DB-A968D423B247@.microsoft.com...[vbcol=seagreen]
> Not sure what you mean by "are they integer values", but the table that
> contains MyFields is full-text indexed. Do tables 1,2,3, and 4 need to be
> full-text indexed? Course, I'm thinking yes since you asked the question
>
> "Hilary Cotter" wrote:
|||Sorry, but our company specifically prohibits posting any schema details in
newsgroups, but should the order be:
1. Remove words from the noise list.
2. Create the full-text index.
I'm wondering that since I created the index before removing the 4 from the
noise list, that it may be the reason my search is not working.
"Hilary Cotter" wrote:

> Perhaps if you could post the schema. For the record mode-4 is indexed and
> queried two separate words. If 4 is not in your noise word list this should
> work.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
> news:E3738AD4-7A01-40C2-95DB-A968D423B247@.microsoft.com...
>
>
|||OK, let me guess your schema then from what you have posted.
Create MyFields(PK int not null identity primary key, Fields1 char(20),
Field2 char(20), Field3 char(20), Field4 char(20))
Create T1 (pk int not null references MyFields(PK), Field1 char(20))
Create T2 (pk int not null references MyFields(PK), Field2 char(20))
Create T3 (pk int not null references MyFields(PK), Field3 char(20))
Create T4 (pk int not null references MyFields(PK), Field4 char(20))
This is kind of critical as I think your join condition is all wrong.
But you are correct with a search on mode-4 and you have removed 4 from your
noise word list after building your index you will not get correct results.
In fact you should get fewer results which makes me wonder about your join
condition.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
news:15E5FCB3-0A36-4B0E-97D0-C0E62376B759@.microsoft.com...[vbcol=seagreen]
> Sorry, but our company specifically prohibits posting any schema details
> in
> newsgroups, but should the order be:
> 1. Remove words from the noise list.
> 2. Create the full-text index.
> I'm wondering that since I created the index before removing the 4 from
> the
> noise list, that it may be the reason my search is not working.
> "Hilary Cotter" wrote:
|||The join condition is there because there are many other items that we are
building a where clause on. I simplified the query to what I thought was most
necessary and only included the joins to be true to my actual query, but with
what you have said about creating the full-text after removing the number 4
from the noise file, now I do not think they matter. If I could show you the
actual schema, I think you would agree with me.
Thank you very much for your time. I'll recreate the full-text index, after
removing any noise words, and see how it works for me then.
"Hilary Cotter" wrote:

> OK, let me guess your schema then from what you have posted.
> Create MyFields(PK int not null identity primary key, Fields1 char(20),
> Field2 char(20), Field3 char(20), Field4 char(20))
> Create T1 (pk int not null references MyFields(PK), Field1 char(20))
> Create T2 (pk int not null references MyFields(PK), Field2 char(20))
> Create T3 (pk int not null references MyFields(PK), Field3 char(20))
> Create T4 (pk int not null references MyFields(PK), Field4 char(20))
> This is kind of critical as I think your join condition is all wrong.
> But you are correct with a search on mode-4 and you have removed 4 from your
> noise word list after building your index you will not get correct results.
> In fact you should get fewer results which makes me wonder about your join
> condition.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
> news:15E5FCB3-0A36-4B0E-97D0-C0E62376B759@.microsoft.com...
>
>
|||MS changed the worbreaker in windows 2003, to contain what I consider
is now a bug.
The hyphen in 'mode-4' is actually now used to split the phrase into 2
words, therefore doing an OR search, hence returning every record with
'mode' OR '4' in it, which I expect will be quite a few.
On windows 2000, this worked properly to join the word, as a hyphen is
actually supposed to in text.
Eventually I got around it by replacing all hyphens in the indexed
text with HYP ie 'modeHYP4'. You need to replace any hyphens that
users enter in the search to the same.
Lots of our product skus had hyphens in so it was causing all sorts of
problems, 21-500 was returning thousands of results instead of 1.
Hope this helps...
On 14 Feb, 19:35, Mike Collins <MikeColl...@.discussions.microsoft.com>
wrote:
> The join condition is there because there are many other items that we are
> building a where clause on. I simplified the query to what I thought was most
> necessary and only included the joins to be true to my actual query, but with
> what you have said about creating the full-text after removing the number 4
> from the noise file, now I do not think they matter. If I could show you the
> actual schema, I think you would agree with me.