Enterprise Manager Query Tool

We have noticed some random failures to write data to some
of our databases. In the
research that I have done I think it is being caused by
users querying the tables
with Enterprise Manager's Query Tool. I am seeing table
level locks that are granted
against the table in Mode IS. I am interpreting this to
mean that there is an 'Shared
Intent' Lock on that table. Which to my understanding
means that anyone can continue
to read but that I have also declared an intent to update
the table and therefore
requests for exclusive locks for update, insert or delete
operations would block
until my intent lock is removed. If the block occurs for
too long the blocked call
would then timeout. I believe that Enterprise Manager is
creating this type of lock
because the user interface allows the data that is
displayed to be updated and if the
Intent lock was not declared then data syncronization
issues would occur.
For now I have instructed people who access the tables to
use Query Analyzer since it
will only lock when queries that require the lock are
executed and will not hold the
lock while the user browses the results.
My questions are as follows:
1) Is my interpretation of the IS Mode Table lock correct?
2) Is there a way to prevent Enterprise Manager from
locking the table in this way
(i.e. make the query results read-only)?
3) If the user that is performing the query only has
dbreader privledges then why is
the Intent lock being created?
Thanxshttp://vyaskn.tripod.com/sql_enterp...er_or_t-sql.htm
"" <anonymous@.discussions.microsoft.com> wrote in message
news:535b01c4003c$1ec4aca0$a301280a@.phx.gbl...
> We have noticed some random failures to write data to some
> of our databases. In the
> research that I have done I think it is being caused by
> users querying the tables
> with Enterprise Manager's Query Tool. I am seeing table
> level locks that are granted
> against the table in Mode IS. I am interpreting this to
> mean that there is an 'Shared
> Intent' Lock on that table. Which to my understanding
> means that anyone can continue
> to read but that I have also declared an intent to update
> the table and therefore
> requests for exclusive locks for update, insert or delete
> operations would block
> until my intent lock is removed. If the block occurs for
> too long the blocked call
> would then timeout. I believe that Enterprise Manager is
> creating this type of lock
> because the user interface allows the data that is
> displayed to be updated and if the
> Intent lock was not declared then data syncronization
> issues would occur.
> For now I have instructed people who access the tables to
> use Query Analyzer since it
> will only lock when queries that require the lock are
> executed and will not hold the
> lock while the user browses the results.
> My questions are as follows:
> 1) Is my interpretation of the IS Mode Table lock correct?
> 2) Is there a way to prevent Enterprise Manager from
> locking the table in this way
> (i.e. make the query results read-only)?
> 3) If the user that is performing the query only has
> dbreader privledges then why is
> the Intent lock being created?
> Thanxs

Enterprise Manager Query Tool

We have noticed some random failures to write data to some
of our databases. In the
research that I have done I think it is being caused by
users querying the tables
with Enterprise Manager's Query Tool. I am seeing table
level locks that are granted
against the table in Mode IS. I am interpreting this to
mean that there is an 'Shared
Intent' Lock on that table. Which to my understanding
means that anyone can continue
to read but that I have also declared an intent to update
the table and therefore
requests for exclusive locks for update, insert or delete
operations would block
until my intent lock is removed. If the block occurs for
too long the blocked call
would then timeout. I believe that Enterprise Manager is
creating this type of lock
because the user interface allows the data that is
displayed to be updated and if the
Intent lock was not declared then data syncronization
issues would occur.
For now I have instructed people who access the tables to
use Query Analyzer since it
will only lock when queries that require the lock are
executed and will not hold the
lock while the user browses the results.
My questions are as follows:
1) Is my interpretation of the IS Mode Table lock correct?
2) Is there a way to prevent Enterprise Manager from
locking the table in this way
(i.e. make the query results read-only)?
3) If the user that is performing the query only has
dbreader privledges then why is
the Intent lock being created?
Thanxshttp://vyaskn.tripod.com/sql_enterprise_manager_or_t-sql.htm
":)" <anonymous@.discussions.microsoft.com> wrote in message
news:535b01c4003c$1ec4aca0$a301280a@.phx.gbl...
> We have noticed some random failures to write data to some
> of our databases. In the
> research that I have done I think it is being caused by
> users querying the tables
> with Enterprise Manager's Query Tool. I am seeing table
> level locks that are granted
> against the table in Mode IS. I am interpreting this to
> mean that there is an 'Shared
> Intent' Lock on that table. Which to my understanding
> means that anyone can continue
> to read but that I have also declared an intent to update
> the table and therefore
> requests for exclusive locks for update, insert or delete
> operations would block
> until my intent lock is removed. If the block occurs for
> too long the blocked call
> would then timeout. I believe that Enterprise Manager is
> creating this type of lock
> because the user interface allows the data that is
> displayed to be updated and if the
> Intent lock was not declared then data syncronization
> issues would occur.
> For now I have instructed people who access the tables to
> use Query Analyzer since it
> will only lock when queries that require the lock are
> executed and will not hold the
> lock while the user browses the results.
> My questions are as follows:
> 1) Is my interpretation of the IS Mode Table lock correct?
> 2) Is there a way to prevent Enterprise Manager from
> locking the table in this way
> (i.e. make the query results read-only)?
> 3) If the user that is performing the query only has
> dbreader privledges then why is
> the Intent lock being created?
> Thanxs

enterprise manager entries

I create SQL Server Groups in Enterprise manager and for
some reason and at what seems like random times they all
go away. I see a blank list under SQL Server Groups.
I checked the registry entry below and all SQL Server
Groups that I created are there.
HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL
Server\80\Tools\SQLEW\Registered Servers X\SQL Server
Group
With that information, is there a way to hook it back
together so what is in the registry is what is displayed
in enterprise manager for SQL Server?
Thanks
RickRick,
Strange!What service pack and OS are you on?
--
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"Rick Allison" <allisonrja@.hotmail.com> wrote in message
news:edc3f8oWDHA.2056@.TK2MSFTNGP11.phx.gbl...
> I create SQL Server Groups in Enterprise manager and for
> some reason and at what seems like random times they all
> go away. I see a blank list under SQL Server Groups.
> I checked the registry entry below and all SQL Server
> Groups that I created are there.
> HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL
> Server\80\Tools\SQLEW\Registered Servers X\SQL Server
> Group
> With that information, is there a way to hook it back
> together so what is in the registry is what is displayed
> in enterprise manager for SQL Server?
> Thanks
> Rick
>|||Dinesh,
I am running Windows XP and enterprise manager version 8.0. (at least
that's what is says in help about)
Does that help?
Rick
"Dinesh.T.K" <tkdinesh@.nospam.mail.tkdinesh.com> wrote in message
news:uZ6mdGpWDHA.1744@.TK2MSFTNGP12.phx.gbl...
> Rick,
> Strange!What service pack and OS are you on?
> --
> Dinesh.
> SQL Server FAQ at
> http://www.tkdinesh.com
> "Rick Allison" <allisonrja@.hotmail.com> wrote in message
> news:edc3f8oWDHA.2056@.TK2MSFTNGP11.phx.gbl...
> > I create SQL Server Groups in Enterprise manager and for
> > some reason and at what seems like random times they all
> > go away. I see a blank list under SQL Server Groups.
> >
> > I checked the registry entry below and all SQL Server
> > Groups that I created are there.
> > HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL
> > Server\80\Tools\SQLEW\Registered Servers X\SQL Server
> > Group
> >
> > With that information, is there a way to hook it back
> > together so what is in the registry is what is displayed
> > in enterprise manager for SQL Server?
> >
> > Thanks
> >
> > Rick
> >
> >
>|||Rick,
Thanks but I need the service pack(sp) info. too to report this case in mvp
newsgroup.You can find the sp info by executing the below command in Query
Analyzer
SELECT SERVERPROPERTY('PRODUCTLEVEL')
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"Rick Allison" <allisonrja@.hotmail.com> wrote in message
news:%23umCGTpWDHA.1900@.TK2MSFTNGP10.phx.gbl...
> Dinesh,
> I am running Windows XP and enterprise manager version 8.0. (at least
> that's what is says in help about)
> Does that help?
> Rick
> "Dinesh.T.K" <tkdinesh@.nospam.mail.tkdinesh.com> wrote in message
> news:uZ6mdGpWDHA.1744@.TK2MSFTNGP12.phx.gbl...
> > Rick,
> >
> > Strange!What service pack and OS are you on?
> >
> > --
> > Dinesh.
> > SQL Server FAQ at
> > http://www.tkdinesh.com
> >
> > "Rick Allison" <allisonrja@.hotmail.com> wrote in message
> > news:edc3f8oWDHA.2056@.TK2MSFTNGP11.phx.gbl...
> > > I create SQL Server Groups in Enterprise manager and for
> > > some reason and at what seems like random times they all
> > > go away. I see a blank list under SQL Server Groups.
> > >
> > > I checked the registry entry below and all SQL Server
> > > Groups that I created are there.
> > > HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL
> > > Server\80\Tools\SQLEW\Registered Servers X\SQL Server
> > > Group
> > >
> > > With that information, is there a way to hook it back
> > > together so what is in the registry is what is displayed
> > > in enterprise manager for SQL Server?
> > >
> > > Thanks
> > >
> > > Rick
> > >
> > >
> >
> >
>|||Rick,
Thanks.Can you try upgrading to SP3 or latest SP3a?the download is available
at http://www.microsoft.com/sql/downloads/2000/sp3.asp.
The reason for my advice is , may be, its due to the slammer worm.More
details
http://www.microsoft.com/sql/techinfo/administration/2000/security/slammer.asp
Even if you report this error(or bug), the first suggestion would be to go
in for the latest service pack.So do a test of your app. in a test env. with
SP3(a) and if okay, then go for the same with prod.
--
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"Rick Allison" <allisonrja@.hotmail.com> wrote in message
news:ObqkmvpWDHA.1872@.TK2MSFTNGP12.phx.gbl...
> Dinesh,
> The query returned - SP2
> Rick
> "Dinesh.T.K" <tkdinesh@.nospam.mail.tkdinesh.com> wrote in message
> news:OANx1WpWDHA.1900@.TK2MSFTNGP10.phx.gbl...
> > Rick,
> >
> > Thanks but I need the service pack(sp) info. too to report this case in
> mvp
> > newsgroup.You can find the sp info by executing the below command in
Query
> > Analyzer
> >
> > SELECT SERVERPROPERTY('PRODUCTLEVEL')
> >
> >
> > --
> > Dinesh.
> > SQL Server FAQ at
> > http://www.tkdinesh.com
> >
> > "Rick Allison" <allisonrja@.hotmail.com> wrote in message
> > news:%23umCGTpWDHA.1900@.TK2MSFTNGP10.phx.gbl...
> > > Dinesh,
> > >
> > > I am running Windows XP and enterprise manager version 8.0. (at least
> > > that's what is says in help about)
> > >
> > > Does that help?
> > >
> > > Rick
> > > "Dinesh.T.K" <tkdinesh@.nospam.mail.tkdinesh.com> wrote in message
> > > news:uZ6mdGpWDHA.1744@.TK2MSFTNGP12.phx.gbl...
> > > > Rick,
> > > >
> > > > Strange!What service pack and OS are you on?
> > > >
> > > > --
> > > > Dinesh.
> > > > SQL Server FAQ at
> > > > http://www.tkdinesh.com
> > > >
> > > > "Rick Allison" <allisonrja@.hotmail.com> wrote in message
> > > > news:edc3f8oWDHA.2056@.TK2MSFTNGP11.phx.gbl...
> > > > > I create SQL Server Groups in Enterprise manager and for
> > > > > some reason and at what seems like random times they all
> > > > > go away. I see a blank list under SQL Server Groups.
> > > > >
> > > > > I checked the registry entry below and all SQL Server
> > > > > Groups that I created are there.
> > > > > HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL
> > > > > Server\80\Tools\SQLEW\Registered Servers X\SQL Server
> > > > > Group
> > > > >
> > > > > With that information, is there a way to hook it back
> > > > > together so what is in the registry is what is displayed
> > > > > in enterprise manager for SQL Server?
> > > > >
> > > > > Thanks
> > > > >
> > > > > Rick
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||Dinesh,
Okay - I'll go try.
I am, however, a developer in a large corporation and I use enterprise
manager to point to many different SQL Server databases, test and
production. Do I need to have each database updated or can I just update
enterprise manager that is loaded on my computer?
If I need to do each database, that is not a simple task. (sigh)
Thanks for all your help.
Rick
"Dinesh.T.K" <tkdinesh@.nospam.mail.tkdinesh.com> wrote in message
news:O%23FGn4pWDHA.3404@.tk2msftngp13.phx.gbl...
> Rick,
> Thanks.Can you try upgrading to SP3 or latest SP3a?the download is
available
> at http://www.microsoft.com/sql/downloads/2000/sp3.asp.
> The reason for my advice is , may be, its due to the slammer worm.More
> details
>
http://www.microsoft.com/sql/techinfo/administration/2000/security/slammer.asp
> Even if you report this error(or bug), the first suggestion would be to go
> in for the latest service pack.So do a test of your app. in a test env.
with
> SP3(a) and if okay, then go for the same with prod.
> --
> Dinesh.
> SQL Server FAQ at
> http://www.tkdinesh.com
> "Rick Allison" <allisonrja@.hotmail.com> wrote in message
> news:ObqkmvpWDHA.1872@.TK2MSFTNGP12.phx.gbl...
> > Dinesh,
> >
> > The query returned - SP2
> >
> > Rick
> >
> > "Dinesh.T.K" <tkdinesh@.nospam.mail.tkdinesh.com> wrote in message
> > news:OANx1WpWDHA.1900@.TK2MSFTNGP10.phx.gbl...
> > > Rick,
> > >
> > > Thanks but I need the service pack(sp) info. too to report this case
in
> > mvp
> > > newsgroup.You can find the sp info by executing the below command in
> Query
> > > Analyzer
> > >
> > > SELECT SERVERPROPERTY('PRODUCTLEVEL')
> > >
> > >
> > > --
> > > Dinesh.
> > > SQL Server FAQ at
> > > http://www.tkdinesh.com
> > >
> > > "Rick Allison" <allisonrja@.hotmail.com> wrote in message
> > > news:%23umCGTpWDHA.1900@.TK2MSFTNGP10.phx.gbl...
> > > > Dinesh,
> > > >
> > > > I am running Windows XP and enterprise manager version 8.0. (at
least
> > > > that's what is says in help about)
> > > >
> > > > Does that help?
> > > >
> > > > Rick
> > > > "Dinesh.T.K" <tkdinesh@.nospam.mail.tkdinesh.com> wrote in message
> > > > news:uZ6mdGpWDHA.1744@.TK2MSFTNGP12.phx.gbl...
> > > > > Rick,
> > > > >
> > > > > Strange!What service pack and OS are you on?
> > > > >
> > > > > --
> > > > > Dinesh.
> > > > > SQL Server FAQ at
> > > > > http://www.tkdinesh.com
> > > > >
> > > > > "Rick Allison" <allisonrja@.hotmail.com> wrote in message
> > > > > news:edc3f8oWDHA.2056@.TK2MSFTNGP11.phx.gbl...
> > > > > > I create SQL Server Groups in Enterprise manager and for
> > > > > > some reason and at what seems like random times they all
> > > > > > go away. I see a blank list under SQL Server Groups.
> > > > > >
> > > > > > I checked the registry entry below and all SQL Server
> > > > > > Groups that I created are there.
> > > > > > HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL
> > > > > > Server\80\Tools\SQLEW\Registered Servers X\SQL Server
> > > > > > Group
> > > > > >
> > > > > > With that information, is there a way to hook it back
> > > > > > together so what is in the registry is what is displayed
> > > > > > in enterprise manager for SQL Server?
> > > > > >
> > > > > > Thanks
> > > > > >
> > > > > > Rick
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>