locking

How does SS2000 lock by default for a select, update and delete statement.
Is it row level, page, table?
Thanks,
DanIt depends. Default is row level, but optimizer may escalate it to page
or table level locking if it see fit. You can force it to stay at row
level by using query hints. Check query hints in BOL
Eric Li
SQL DBA
MCDBA
Dan wrote:

> How does SS2000 lock by default for a select, update and delete statement.
> Is it row level, page, table?
> Thanks,
> Dan
>|||Eric is correct but I want to make one slight comment. SQL Server never
escalates to a page level lock from a row level. All escalation is always
to table level if it occurs. But it can choose to take out a page or table
level lock in the first place.
Andrew J. Kelly
SQL Server MVP
"Eric.Li" <anonymous@.microsoftnews.org> wrote in message
news:OMU0VgBSEHA.1392@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> It depends. Default is row level, but optimizer may escalate it to page
> or table level locking if it see fit. You can force it to stay at row
> level by using query hints. Check query hints in BOL
> --
> Eric Li
> SQL DBA
> MCDBA
> Dan wrote:
>
statement.[vbcol=seagreen]|||It appears to select the type of locking largely based on the number of
records you are processing. If you processing a large number of records,
taking out a page / table lock is more resource friendly than individual row
locks. Having said that, it's possible to force the use of a particular
locking mechanism with hints.
Peter Yeoh
http://www.yohz.com
Need smaller backup files? Try MiniSQLBackup
"Dan" <ddonahue@.archermalmo.com> wrote in message
news:O7ngAXBSEHA.2216@.TK2MSFTNGP12.phx.gbl...
> How does SS2000 lock by default for a select, update and delete statement.
> Is it row level, page, table?
> Thanks,
> Dan
>|||If I also may add, SQL does not necessarily lock at the row level... One of
the recent improvements in the locking code for SQL is that the lock manager
takes into account how busy the object ( table for instance) has been
lately...
For instance, you are updating all of the rows in a table. If the table has
been very busy lately, SQL will choose a lower level lock ( page or row
level). This will cause your update to run more slowly ( because there will
be many more calls to the lock manager) , but overall concurrency will be
improved, since you will not be locking rows until right before you make the
update.
If the table has not been used lately, then you may get a whole table
lock... your update runs faster (due to the single lock manager call), and
no one else is hurt, since the table has not been used recently.
However in a busy multi user environment, lower level locks are most often
used...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Dan" <ddonahue@.archermalmo.com> wrote in message
news:O7ngAXBSEHA.2216@.TK2MSFTNGP12.phx.gbl...
> How does SS2000 lock by default for a select, update and delete statement.
> Is it row level, page, table?
> Thanks,
> Dan
>|||Thanks Eric, Andrew, Peter and Wayne. I understand it better now and I'll do
some more reading.
Dan
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:egQfZvJSEHA.1160@.TK2MSFTNGP09.phx.gbl...
> If I also may add, SQL does not necessarily lock at the row level... One
of
> the recent improvements in the locking code for SQL is that the lock
manager
> takes into account how busy the object ( table for instance) has been
> lately...
> For instance, you are updating all of the rows in a table. If the table
has
> been very busy lately, SQL will choose a lower level lock ( page or row
> level). This will cause your update to run more slowly ( because there
will
> be many more calls to the lock manager) , but overall concurrency will be
> improved, since you will not be locking rows until right before you make
the
> update.
> If the table has not been used lately, then you may get a whole table
> lock... your update runs faster (due to the single lock manager call), and
> no one else is hurt, since the table has not been used recently.
> However in a busy multi user environment, lower level locks are most often
> used...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Dan" <ddonahue@.archermalmo.com> wrote in message
> news:O7ngAXBSEHA.2216@.TK2MSFTNGP12.phx.gbl...
statement.[vbcol=seagreen]
>

Locking

I have set the Isolation Level to Read Uncommitted but the tables are still
locked after i have run a select in Query Analyzer, can anyone explain why
this is 'Can you provide more details? Hoe did you set the isolation level. Can you reproduce this from two
connection in query analyzer?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Clive Godden" <clive.godden@.peopleworld.co.uk> wrote in message
news:eGpQzej$EHA.2572@.tk2msftngp13.phx.gbl...
>I have set the Isolation Level to Read Uncommitted but the tables are still
> locked after i have run a select in Query Analyzer, can anyone explain why
> this is '
>|||All i did was run this
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
go
begin transaction
select * from gipsilicence
commit transaction
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||That doesn't help us reproduce the problem as this is only one connection, and I assume that the one
connection doesn't block itself. Are you saying that this connection is blocking another connection,
or is it blocked by another connection? NOLOCK should not acquire shared locks and should not honor
exclusive locks, hence I'm asking as much details as possible, preferably a repro.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Clive Godden" <clive.godden@.peopleworld.co.uk> wrote in message
news:eqoQm3j$EHA.2136@.TK2MSFTNGP10.phx.gbl...
> All i did was run this
> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> go
> begin transaction
> select * from gipsilicence
> commit transaction
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!sql

Locking

It appears that when we issue an UPDATE statement on a table, it locks the
WHOLE table so that any other users can not complete SELECT queries on the
table (without using "WITH (nolock)" in the SELECT statement)
Is there anyway to tell SQL SERVER to not lock the WHOLE table when doing an
update, so that maybe it takes longer, but it only locks 1 row or page at a
time?Mike
The optimiser escalates to a full table lock when it thinks it will be more
efficient way of processing the data than using page locks. There are a
number of things you can do if you think it should not be performing a table
lock.
Are your statistics up to date? If you don't regularly update statistics,
the optimiser is working with at least one hand tied behind it's back. If the
statistics are not up tp date it may be making incorrect choices.
Do you have suitable indexes? If you have indexes on the data you are
updating (especially a clustered index) it will be easier for the optimiser
to use page locks rather than table locks, if suitable.
It may be you are updating enough of the table that a table lock is the
correct option. In this case you can split up your updates into smaller
transactions, small enough that the optimiser uses page locks.
Hope this helps
John
"Mike" wrote:
> It appears that when we issue an UPDATE statement on a table, it locks the
> WHOLE table so that any other users can not complete SELECT queries on the
> table (without using "WITH (nolock)" in the SELECT statement)
> Is there anyway to tell SQL SERVER to not lock the WHOLE table when doing an
> update, so that maybe it takes longer, but it only locks 1 row or page at a
> time?
>
>|||Forgive my ignorance, but how do I get my statistics up to date? I am not
experienced in SQL Server administration.
I am using the pubs database in a new installation of SQL Server.
When I issue:
UPDATE authors set au_lname = 'a'
and then jump over to Enterprise Manager and right-click on "Current
Activity" under Management and select Refresh, then I go to Locks/Object and
pubs.dbo.authors, there is 1 row where Lock Type is "TAB" and a bunch with
Lock Type "PAG".
If I add "with (rowlock)" to the UPDATE statement, there is still 1 TAB lock
but less PAG locks.
(I inserted thousands more rows into authors than it comes with to give me
time to go to Ent. Mgr and select Refresh while the update is running)
There is a compound index on au_lname and au_fname.
"John Bandettini" <JohnBandettini@.discussions.microsoft.com> wrote in
message news:D12A67EA-93C0-48F4-A45B-B4A3E9A48218@.microsoft.com...
> Mike
> The optimiser escalates to a full table lock when it thinks it will be
> more
> efficient way of processing the data than using page locks. There are a
> number of things you can do if you think it should not be performing a
> table
> lock.
> Are your statistics up to date? If you don't regularly update statistics,
> the optimiser is working with at least one hand tied behind it's back. If
> the
> statistics are not up tp date it may be making incorrect choices.
> Do you have suitable indexes? If you have indexes on the data you are
> updating (especially a clustered index) it will be easier for the
> optimiser
> to use page locks rather than table locks, if suitable.
> It may be you are updating enough of the table that a table lock is the
> correct option. In this case you can split up your updates into smaller
> transactions, small enough that the optimiser uses page locks.
> Hope this helps
> John
> "Mike" wrote:
>> It appears that when we issue an UPDATE statement on a table, it locks
>> the
>> WHOLE table so that any other users can not complete SELECT queries on
>> the
>> table (without using "WITH (nolock)" in the SELECT statement)
>> Is there anyway to tell SQL SERVER to not lock the WHOLE table when doing
>> an
>> update, so that maybe it takes longer, but it only locks 1 row or page at
>> a
>> time?
>>|||I think you see the IX (intent exclusive) lock on the table.
That is not a real lock, it just tells the the engine that there is a execute
or update lock somewhere in the table.
Normally SQLServer will use row locks. The only way you get a real table
lock with this statement is if you have forbidden row and page locks to a
clustered index on the table. I doubt you have been playing with the
sp_indexoption, so that seems unlikely.
Joachim.
On Fri, 21 Jan 2005 11:28:34 -0500, "Mike W" <mikeotown@.nospam.msn.com>
wrote:
>Forgive my ignorance, but how do I get my statistics up to date? I am not
>experienced in SQL Server administration.
>I am using the pubs database in a new installation of SQL Server.
>When I issue:
>UPDATE authors set au_lname = 'a'
>and then jump over to Enterprise Manager and right-click on "Current
>Activity" under Management and select Refresh, then I go to Locks/Object and
>pubs.dbo.authors, there is 1 row where Lock Type is "TAB" and a bunch with
>Lock Type "PAG".
>If I add "with (rowlock)" to the UPDATE statement, there is still 1 TAB lock
>but less PAG locks.
>(I inserted thousands more rows into authors than it comes with to give me
>time to go to Ent. Mgr and select Refresh while the update is running)
>There is a compound index on au_lname and au_fname.
>"John Bandettini" <JohnBandettini@.discussions.microsoft.com> wrote in
>message news:D12A67EA-93C0-48F4-A45B-B4A3E9A48218@.microsoft.com...
>> Mike
>> The optimiser escalates to a full table lock when it thinks it will be
>> more
>> efficient way of processing the data than using page locks. There are a
>> number of things you can do if you think it should not be performing a
>> table
>> lock.
>> Are your statistics up to date? If you don't regularly update statistics,
>> the optimiser is working with at least one hand tied behind it's back. If
>> the
>> statistics are not up tp date it may be making incorrect choices.
>> Do you have suitable indexes? If you have indexes on the data you are
>> updating (especially a clustered index) it will be easier for the
>> optimiser
>> to use page locks rather than table locks, if suitable.
>> It may be you are updating enough of the table that a table lock is the
>> correct option. In this case you can split up your updates into smaller
>> transactions, small enough that the optimiser uses page locks.
>> Hope this helps
>> John
>> "Mike" wrote:
>> It appears that when we issue an UPDATE statement on a table, it locks
>> the
>> WHOLE table so that any other users can not complete SELECT queries on
>> the
>> table (without using "WITH (nolock)" in the SELECT statement)
>> Is there anyway to tell SQL SERVER to not lock the WHOLE table when doing
>> an
>> update, so that maybe it takes longer, but it only locks 1 row or page at
>> a
>> time?
>>
>
--
This post is free post; you can redistribute it and/or
modify it under the terms of the GNU General Public License
as published by the Free Software Foundation; either version 2
of the License, or (at your option) any later version.
Joachim Verhagen
http://www.xs4all.nl/~jcdverha/ (Science Jokes)|||Mike
You can update statistics automatically by enabling the auto update
statistics option. You can do this through enterprise manager. Right click on
the database you want to set the option for and choose properties. On the
option page of properties you can turn this option on and off. You can also
turn the option on or off through query analyser using sp_dboption. (See BOL
for details).
You can use dbcc show_statistics to see how up to date your statistics are.
Statistics also get updated when you rebuild a clustered index.
Hope this helps
John
"Mike W" wrote:
> Forgive my ignorance, but how do I get my statistics up to date? I am not
> experienced in SQL Server administration.
> I am using the pubs database in a new installation of SQL Server.
> When I issue:
> UPDATE authors set au_lname = 'a'
> and then jump over to Enterprise Manager and right-click on "Current
> Activity" under Management and select Refresh, then I go to Locks/Object and
> pubs.dbo.authors, there is 1 row where Lock Type is "TAB" and a bunch with
> Lock Type "PAG".
> If I add "with (rowlock)" to the UPDATE statement, there is still 1 TAB lock
> but less PAG locks.
> (I inserted thousands more rows into authors than it comes with to give me
> time to go to Ent. Mgr and select Refresh while the update is running)
> There is a compound index on au_lname and au_fname.
> "John Bandettini" <JohnBandettini@.discussions.microsoft.com> wrote in
> message news:D12A67EA-93C0-48F4-A45B-B4A3E9A48218@.microsoft.com...
> > Mike
> >
> > The optimiser escalates to a full table lock when it thinks it will be
> > more
> > efficient way of processing the data than using page locks. There are a
> > number of things you can do if you think it should not be performing a
> > table
> > lock.
> >
> > Are your statistics up to date? If you don't regularly update statistics,
> > the optimiser is working with at least one hand tied behind it's back. If
> > the
> > statistics are not up tp date it may be making incorrect choices.
> >
> > Do you have suitable indexes? If you have indexes on the data you are
> > updating (especially a clustered index) it will be easier for the
> > optimiser
> > to use page locks rather than table locks, if suitable.
> >
> > It may be you are updating enough of the table that a table lock is the
> > correct option. In this case you can split up your updates into smaller
> > transactions, small enough that the optimiser uses page locks.
> >
> > Hope this helps
> >
> > John
> >
> > "Mike" wrote:
> >
> >> It appears that when we issue an UPDATE statement on a table, it locks
> >> the
> >> WHOLE table so that any other users can not complete SELECT queries on
> >> the
> >> table (without using "WITH (nolock)" in the SELECT statement)
> >>
> >> Is there anyway to tell SQL SERVER to not lock the WHOLE table when doing
> >> an
> >> update, so that maybe it takes longer, but it only locks 1 row or page at
> >> a
> >> time?
> >>
> >>
> >>
>
>

Locking

Why does a simple SELECT statement take an Intent-Exclusive lock? Surely
there is no need to even think about excluding anything else when just
reading. For example SELECT * from pubs..authors aquires two locks - an
Intent-Exclusive table level lock on the clustered index and another
Intent-Exclusive table on the non-clustered index.
I believe this is excessive. Can anyone please explain?
Thanks in advance,
Tim G-JTim
The very simple reason is to stop other processes from updating the data
while you are processing it. This is an effect of isolation levels. If you
don't mind data being updated while you are processing it you can change the
isolation level. Look at isolation levels in Books on line for a full
explaination.
Hope this helps
John
"Tim G-J" wrote:
> Why does a simple SELECT statement take an Intent-Exclusive lock? Surely
> there is no need to even think about excluding anything else when just
> reading. For example SELECT * from pubs..authors aquires two locks - an
> Intent-Exclusive table level lock on the clustered index and another
> Intent-Exclusive table on the non-clustered index.
> I believe this is excessive. Can anyone please explain?
> Thanks in advance,
> Tim G-J|||Thanks John,
Roll on Yukon and optimistic locking.
Tim
"John Bandettini" wrote:
> Tim
> The very simple reason is to stop other processes from updating the data
> while you are processing it. This is an effect of isolation levels. If you
> don't mind data being updated while you are processing it you can change the
> isolation level. Look at isolation levels in Books on line for a full
> explaination.
> Hope this helps
> John
> "Tim G-J" wrote:
> > Why does a simple SELECT statement take an Intent-Exclusive lock? Surely
> > there is no need to even think about excluding anything else when just
> > reading. For example SELECT * from pubs..authors aquires two locks - an
> > Intent-Exclusive table level lock on the clustered index and another
> > Intent-Exclusive table on the non-clustered index.
> >
> > I believe this is excessive. Can anyone please explain?
> >
> > Thanks in advance,
> > Tim G-J

locking

How does SS2000 lock by default for a select, update and delete statement.
Is it row level, page, table?
Thanks,
DanIt depends. Default is row level, but optimizer may escalate it to page
or table level locking if it see fit. You can force it to stay at row
level by using query hints. Check query hints in BOL
--
Eric Li
SQL DBA
MCDBA
Dan wrote:
> How does SS2000 lock by default for a select, update and delete statement.
> Is it row level, page, table?
> Thanks,
> Dan
>|||Eric is correct but I want to make one slight comment. SQL Server never
escalates to a page level lock from a row level. All escalation is always
to table level if it occurs. But it can choose to take out a page or table
level lock in the first place.
--
Andrew J. Kelly
SQL Server MVP
"Eric.Li" <anonymous@.microsoftnews.org> wrote in message
news:OMU0VgBSEHA.1392@.TK2MSFTNGP09.phx.gbl...
> It depends. Default is row level, but optimizer may escalate it to page
> or table level locking if it see fit. You can force it to stay at row
> level by using query hints. Check query hints in BOL
> --
> Eric Li
> SQL DBA
> MCDBA
> Dan wrote:
> > How does SS2000 lock by default for a select, update and delete
statement.
> > Is it row level, page, table?
> >
> > Thanks,
> >
> > Dan
> >
> >|||It appears to select the type of locking largely based on the number of
records you are processing. If you processing a large number of records,
taking out a page / table lock is more resource friendly than individual row
locks. Having said that, it's possible to force the use of a particular
locking mechanism with hints.
Peter Yeoh
http://www.yohz.com
Need smaller backup files? Try MiniSQLBackup
"Dan" <ddonahue@.archermalmo.com> wrote in message
news:O7ngAXBSEHA.2216@.TK2MSFTNGP12.phx.gbl...
> How does SS2000 lock by default for a select, update and delete statement.
> Is it row level, page, table?
> Thanks,
> Dan
>|||If I also may add, SQL does not necessarily lock at the row level... One of
the recent improvements in the locking code for SQL is that the lock manager
takes into account how busy the object ( table for instance) has been
lately...
For instance, you are updating all of the rows in a table. If the table has
been very busy lately, SQL will choose a lower level lock ( page or row
level). This will cause your update to run more slowly ( because there will
be many more calls to the lock manager) , but overall concurrency will be
improved, since you will not be locking rows until right before you make the
update.
If the table has not been used lately, then you may get a whole table
lock... your update runs faster (due to the single lock manager call), and
no one else is hurt, since the table has not been used recently.
However in a busy multi user environment, lower level locks are most often
used...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Dan" <ddonahue@.archermalmo.com> wrote in message
news:O7ngAXBSEHA.2216@.TK2MSFTNGP12.phx.gbl...
> How does SS2000 lock by default for a select, update and delete statement.
> Is it row level, page, table?
> Thanks,
> Dan
>|||Thanks Eric, Andrew, Peter and Wayne. I understand it better now and I'll do
some more reading.
Dan
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:egQfZvJSEHA.1160@.TK2MSFTNGP09.phx.gbl...
> If I also may add, SQL does not necessarily lock at the row level... One
of
> the recent improvements in the locking code for SQL is that the lock
manager
> takes into account how busy the object ( table for instance) has been
> lately...
> For instance, you are updating all of the rows in a table. If the table
has
> been very busy lately, SQL will choose a lower level lock ( page or row
> level). This will cause your update to run more slowly ( because there
will
> be many more calls to the lock manager) , but overall concurrency will be
> improved, since you will not be locking rows until right before you make
the
> update.
> If the table has not been used lately, then you may get a whole table
> lock... your update runs faster (due to the single lock manager call), and
> no one else is hurt, since the table has not been used recently.
> However in a busy multi user environment, lower level locks are most often
> used...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Dan" <ddonahue@.archermalmo.com> wrote in message
> news:O7ngAXBSEHA.2216@.TK2MSFTNGP12.phx.gbl...
> > How does SS2000 lock by default for a select, update and delete
statement.
> > Is it row level, page, table?
> >
> > Thanks,
> >
> > Dan
> >
> >
>

Locking

I have set the Isolation Level to Read Uncommitted but the tables are still
locked after i have run a select in Query Analyzer, can anyone explain why
this is ?
Can you provide more details? Hoe did you set the isolation level. Can you reproduce this from two
connection in query analyzer?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Clive Godden" <clive.godden@.peopleworld.co.uk> wrote in message
news:eGpQzej$EHA.2572@.tk2msftngp13.phx.gbl...
>I have set the Isolation Level to Read Uncommitted but the tables are still
> locked after i have run a select in Query Analyzer, can anyone explain why
> this is ?
>
|||All i did was run this
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
go
begin transaction
select * from gipsilicence
commit transaction
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||That doesn't help us reproduce the problem as this is only one connection, and I assume that the one
connection doesn't block itself. Are you saying that this connection is blocking another connection,
or is it blocked by another connection? NOLOCK should not acquire shared locks and should not honor
exclusive locks, hence I'm asking as much details as possible, preferably a repro.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Clive Godden" <clive.godden@.peopleworld.co.uk> wrote in message
news:eqoQm3j$EHA.2136@.TK2MSFTNGP10.phx.gbl...
> All i did was run this
> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> go
> begin transaction
> select * from gipsilicence
> commit transaction
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!

locking

How does SS2000 lock by default for a select, update and delete statement.
Is it row level, page, table?
Thanks,
Dan
It depends. Default is row level, but optimizer may escalate it to page
or table level locking if it see fit. You can force it to stay at row
level by using query hints. Check query hints in BOL
Eric Li
SQL DBA
MCDBA
Dan wrote:

> How does SS2000 lock by default for a select, update and delete statement.
> Is it row level, page, table?
> Thanks,
> Dan
>
|||Eric is correct but I want to make one slight comment. SQL Server never
escalates to a page level lock from a row level. All escalation is always
to table level if it occurs. But it can choose to take out a page or table
level lock in the first place.
Andrew J. Kelly
SQL Server MVP
"Eric.Li" <anonymous@.microsoftnews.org> wrote in message
news:OMU0VgBSEHA.1392@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> It depends. Default is row level, but optimizer may escalate it to page
> or table level locking if it see fit. You can force it to stay at row
> level by using query hints. Check query hints in BOL
> --
> Eric Li
> SQL DBA
> MCDBA
> Dan wrote:
statement.[vbcol=seagreen]
|||It appears to select the type of locking largely based on the number of
records you are processing. If you processing a large number of records,
taking out a page / table lock is more resource friendly than individual row
locks. Having said that, it's possible to force the use of a particular
locking mechanism with hints.
Peter Yeoh
http://www.yohz.com
Need smaller backup files? Try MiniSQLBackup
"Dan" <ddonahue@.archermalmo.com> wrote in message
news:O7ngAXBSEHA.2216@.TK2MSFTNGP12.phx.gbl...
> How does SS2000 lock by default for a select, update and delete statement.
> Is it row level, page, table?
> Thanks,
> Dan
>
|||If I also may add, SQL does not necessarily lock at the row level... One of
the recent improvements in the locking code for SQL is that the lock manager
takes into account how busy the object ( table for instance) has been
lately...
For instance, you are updating all of the rows in a table. If the table has
been very busy lately, SQL will choose a lower level lock ( page or row
level). This will cause your update to run more slowly ( because there will
be many more calls to the lock manager) , but overall concurrency will be
improved, since you will not be locking rows until right before you make the
update.
If the table has not been used lately, then you may get a whole table
lock... your update runs faster (due to the single lock manager call), and
no one else is hurt, since the table has not been used recently.
However in a busy multi user environment, lower level locks are most often
used...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Dan" <ddonahue@.archermalmo.com> wrote in message
news:O7ngAXBSEHA.2216@.TK2MSFTNGP12.phx.gbl...
> How does SS2000 lock by default for a select, update and delete statement.
> Is it row level, page, table?
> Thanks,
> Dan
>
|||Thanks Eric, Andrew, Peter and Wayne. I understand it better now and I'll do
some more reading.
Dan
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:egQfZvJSEHA.1160@.TK2MSFTNGP09.phx.gbl...
> If I also may add, SQL does not necessarily lock at the row level... One
of
> the recent improvements in the locking code for SQL is that the lock
manager
> takes into account how busy the object ( table for instance) has been
> lately...
> For instance, you are updating all of the rows in a table. If the table
has
> been very busy lately, SQL will choose a lower level lock ( page or row
> level). This will cause your update to run more slowly ( because there
will
> be many more calls to the lock manager) , but overall concurrency will be
> improved, since you will not be locking rows until right before you make
the[vbcol=seagreen]
> update.
> If the table has not been used lately, then you may get a whole table
> lock... your update runs faster (due to the single lock manager call), and
> no one else is hurt, since the table has not been used recently.
> However in a busy multi user environment, lower level locks are most often
> used...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Dan" <ddonahue@.archermalmo.com> wrote in message
> news:O7ngAXBSEHA.2216@.TK2MSFTNGP12.phx.gbl...
statement.
>
sql

Locking

Why does a simple SELECT statement take an Intent-Exclusive lock? Surely
there is no need to even think about excluding anything else when just
reading. For example SELECT * from pubs..authors aquires two locks - an
Intent-Exclusive table level lock on the clustered index and another
Intent-Exclusive table on the non-clustered index.
I believe this is excessive. Can anyone please explain?
Thanks in advance,
Tim G-J
Tim
The very simple reason is to stop other processes from updating the data
while you are processing it. This is an effect of isolation levels. If you
don't mind data being updated while you are processing it you can change the
isolation level. Look at isolation levels in Books on line for a full
explaination.
Hope this helps
John
"Tim G-J" wrote:

> Why does a simple SELECT statement take an Intent-Exclusive lock? Surely
> there is no need to even think about excluding anything else when just
> reading. For example SELECT * from pubs..authors aquires two locks - an
> Intent-Exclusive table level lock on the clustered index and another
> Intent-Exclusive table on the non-clustered index.
> I believe this is excessive. Can anyone please explain?
> Thanks in advance,
> Tim G-J
|||Thanks John,
Roll on Yukon and optimistic locking.
Tim
"John Bandettini" wrote:
[vbcol=seagreen]
> Tim
> The very simple reason is to stop other processes from updating the data
> while you are processing it. This is an effect of isolation levels. If you
> don't mind data being updated while you are processing it you can change the
> isolation level. Look at isolation levels in Books on line for a full
> explaination.
> Hope this helps
> John
> "Tim G-J" wrote:

Locking

It appears that when we issue an UPDATE statement on a table, it locks the
WHOLE table so that any other users can not complete SELECT queries on the
table (without using "WITH (nolock)" in the SELECT statement)
Is there anyway to tell SQL SERVER to not lock the WHOLE table when doing an
update, so that maybe it takes longer, but it only locks 1 row or page at a
time?
Mike
The optimiser escalates to a full table lock when it thinks it will be more
efficient way of processing the data than using page locks. There are a
number of things you can do if you think it should not be performing a table
lock.
Are your statistics up to date? If you don't regularly update statistics,
the optimiser is working with at least one hand tied behind it's back. If the
statistics are not up tp date it may be making incorrect choices.
Do you have suitable indexes? If you have indexes on the data you are
updating (especially a clustered index) it will be easier for the optimiser
to use page locks rather than table locks, if suitable.
It may be you are updating enough of the table that a table lock is the
correct option. In this case you can split up your updates into smaller
transactions, small enough that the optimiser uses page locks.
Hope this helps
John
"Mike" wrote:

> It appears that when we issue an UPDATE statement on a table, it locks the
> WHOLE table so that any other users can not complete SELECT queries on the
> table (without using "WITH (nolock)" in the SELECT statement)
> Is there anyway to tell SQL SERVER to not lock the WHOLE table when doing an
> update, so that maybe it takes longer, but it only locks 1 row or page at a
> time?
>
>
|||Forgive my ignorance, but how do I get my statistics up to date? I am not
experienced in SQL Server administration.
I am using the pubs database in a new installation of SQL Server.
When I issue:
UPDATE authors set au_lname = 'a'
and then jump over to Enterprise Manager and right-click on "Current
Activity" under Management and select Refresh, then I go to Locks/Object and
pubs.dbo.authors, there is 1 row where Lock Type is "TAB" and a bunch with
Lock Type "PAG".
If I add "with (rowlock)" to the UPDATE statement, there is still 1 TAB lock
but less PAG locks.
(I inserted thousands more rows into authors than it comes with to give me
time to go to Ent. Mgr and select Refresh while the update is running)
There is a compound index on au_lname and au_fname.
"John Bandettini" <JohnBandettini@.discussions.microsoft.com> wrote in
message news:D12A67EA-93C0-48F4-A45B-B4A3E9A48218@.microsoft.com...[vbcol=seagreen]
> Mike
> The optimiser escalates to a full table lock when it thinks it will be
> more
> efficient way of processing the data than using page locks. There are a
> number of things you can do if you think it should not be performing a
> table
> lock.
> Are your statistics up to date? If you don't regularly update statistics,
> the optimiser is working with at least one hand tied behind it's back. If
> the
> statistics are not up tp date it may be making incorrect choices.
> Do you have suitable indexes? If you have indexes on the data you are
> updating (especially a clustered index) it will be easier for the
> optimiser
> to use page locks rather than table locks, if suitable.
> It may be you are updating enough of the table that a table lock is the
> correct option. In this case you can split up your updates into smaller
> transactions, small enough that the optimiser uses page locks.
> Hope this helps
> John
> "Mike" wrote:
|||I think you see the IX (intent exclusive) lock on the table.
That is not a real lock, it just tells the the engine that there is a execute
or update lock somewhere in the table.
Normally SQLServer will use row locks. The only way you get a real table
lock with this statement is if you have forbidden row and page locks to a
clustered index on the table. I doubt you have been playing with the
sp_indexoption, so that seems unlikely.
Joachim.
On Fri, 21 Jan 2005 11:28:34 -0500, "Mike W" <mikeotown@.nospam.msn.com>
wrote:

>Forgive my ignorance, but how do I get my statistics up to date? I am not
>experienced in SQL Server administration.
>I am using the pubs database in a new installation of SQL Server.
>When I issue:
>UPDATE authors set au_lname = 'a'
>and then jump over to Enterprise Manager and right-click on "Current
>Activity" under Management and select Refresh, then I go to Locks/Object and
>pubs.dbo.authors, there is 1 row where Lock Type is "TAB" and a bunch with
>Lock Type "PAG".
>If I add "with (rowlock)" to the UPDATE statement, there is still 1 TAB lock
>but less PAG locks.
>(I inserted thousands more rows into authors than it comes with to give me
>time to go to Ent. Mgr and select Refresh while the update is running)
>There is a compound index on au_lname and au_fname.
>"John Bandettini" <JohnBandettini@.discussions.microsoft.com> wrote in
>message news:D12A67EA-93C0-48F4-A45B-B4A3E9A48218@.microsoft.com...
>
This post is free post; you can redistribute it and/or
modify it under the terms of the GNU General Public License
as published by the Free Software Foundation; either version 2
of the License, or (at your option) any later version.
Joachim Verhagen
http://www.xs4all.nl/~jcdverha/ (Science Jokes)
|||Mike
You can update statistics automatically by enabling the auto update
statistics option. You can do this through enterprise manager. Right click on
the database you want to set the option for and choose properties. On the
option page of properties you can turn this option on and off. You can also
turn the option on or off through query analyser using sp_dboption. (See BOL
for details).
You can use dbcc show_statistics to see how up to date your statistics are.
Statistics also get updated when you rebuild a clustered index.
Hope this helps
John
"Mike W" wrote:

> Forgive my ignorance, but how do I get my statistics up to date? I am not
> experienced in SQL Server administration.
> I am using the pubs database in a new installation of SQL Server.
> When I issue:
> UPDATE authors set au_lname = 'a'
> and then jump over to Enterprise Manager and right-click on "Current
> Activity" under Management and select Refresh, then I go to Locks/Object and
> pubs.dbo.authors, there is 1 row where Lock Type is "TAB" and a bunch with
> Lock Type "PAG".
> If I add "with (rowlock)" to the UPDATE statement, there is still 1 TAB lock
> but less PAG locks.
> (I inserted thousands more rows into authors than it comes with to give me
> time to go to Ent. Mgr and select Refresh while the update is running)
> There is a compound index on au_lname and au_fname.
> "John Bandettini" <JohnBandettini@.discussions.microsoft.com> wrote in
> message news:D12A67EA-93C0-48F4-A45B-B4A3E9A48218@.microsoft.com...
>
>

Lock hint question

Hi All,

I have a question about lock hint for you :

If the first user currently run a select command
with share lock and hold it. What kind of
lock (lock hint) should be used by the second user
in the select command (from the same table) so that
this command will wait until the first user releases
the lock ?

I have tried using tablock, tablockx, xlock and
updlock hint in the select command for the second
user, but it is not successful. Below is my
unsuccessful test :

The first user :
Begin Transaction
select fprefix from ut1 where fprefix = '000'

The second user :
select * from ut1 with (tablockx)

Please help me

Thanks in advance

Anita Hery

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!> The first user :
> Begin Transaction
> select fprefix from ut1 where fprefix = '000'
> The second user :
> select * from ut1 with (tablockx)

With the default READ COMMITTED transaction isolation level, any locks
acquired during the select will be released when the select completes. The
second user will then be able to acquire any type of lock on the table.

The example below will override the default isolation level:

The first user :
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRAN
SELECT fprefix FROM ut1 WHERE fprefix = '000'

The second user :
SELECT * FROM ut1 WITH (TABLOCKX)

Of course, this approach reduces concurrency. See the Books Online
<tsqlref.chm::/ts_set-set_74bw.htm> for more information.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Anita" <anonymous@.devdex.com> wrote in message
news:4005dee1$0$70304$75868355@.news.frii.net...
> Hi All,
> I have a question about lock hint for you :
> If the first user currently run a select command
> with share lock and hold it. What kind of
> lock (lock hint) should be used by the second user
> in the select command (from the same table) so that
> this command will wait until the first user releases
> the lock ?
> I have tried using tablock, tablockx, xlock and
> updlock hint in the select command for the second
> user, but it is not successful. Below is my
> unsuccessful test :
> The first user :
> Begin Transaction
> select fprefix from ut1 where fprefix = '000'
> The second user :
> select * from ut1 with (tablockx)
> Please help me
> Thanks in advance
> Anita Hery
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!|||Dan Guzman,

Yes, now it is successful with SERIALIZABLE hint.
Many thanks for your reply

Regards,

Anita Hery

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

LOCK & SELECT Behavior

I do a transactional INSERT on a table (using VB COM+ as middle tier calling
SPs) and then immediately do a SELECT on that same table (using VB COM+ to
call the SP and return data for display) to display info from the table. On
my development machine everything works fine, but I've noticed on the
production server that often, the returned dataset from the SELECT query
returns no rows (even though the old and new data are in the database as I
look later - the INSERT did work). I'm thinking since the INSERT and SELECT
are almost called simultaneously that this might have something to do with
transactional locking and that I should use WITH (NOLOCK) for the SELECT
statement.
When a SELECT is executed and the table is temporarily locked because a
transaction is still in process, is the behavior just to return no rows? or
should the rows be returned eventually? Is there any type of notification
when this happens? Right now, I get no errors, nothing in the event log, and
just don't get the rows I expect back to display even though they are there.
Thanks for any tips.What exactly do you mean by "do a transactional Insert"? Are the Insert and
Select done from the same connection or two different ones?
If the row is locked then a SELECT will be blocked unless you are using
NOLOCK or Read Uncommitted already. Can you give a little more details on
exactly what code you are calling and how?
Andrew J. Kelly SQL MVP
"Don Miller" <nospam@.nospam.com> wrote in message
news:%23UGy9H$2FHA.700@.TK2MSFTNGP15.phx.gbl...
>I do a transactional INSERT on a table (using VB COM+ as middle tier
>calling
> SPs) and then immediately do a SELECT on that same table (using VB COM+ to
> call the SP and return data for display) to display info from the table.
> On
> my development machine everything works fine, but I've noticed on the
> production server that often, the returned dataset from the SELECT query
> returns no rows (even though the old and new data are in the database as I
> look later - the INSERT did work). I'm thinking since the INSERT and
> SELECT
> are almost called simultaneously that this might have something to do with
> transactional locking and that I should use WITH (NOLOCK) for the SELECT
> statement.
> When a SELECT is executed and the table is temporarily locked because a
> transaction is still in process, is the behavior just to return no rows?
> or
> should the rows be returned eventually? Is there any type of notification
> when this happens? Right now, I get no errors, nothing in the event log,
> and
> just don't get the rows I expect back to display even though they are
> there.
> Thanks for any tips.
>|||The entire process of entering data from my ASP web app is transactional,
that is, if the INSERT fails (e.g. a typo error in an SP or a VBscript or
ASP error) any changes to the database will be automatically rolled back. My
VB COM+ "write" components "use transactions", my ASP code
"requires_transaction".
The INSERT and SELECT are done from different components and different
connections (same login to SQLServer though).
I haven't been using NOLOCK or Read Uncommitted in any of my SELECTs.
As far as my code, somebody fills out a web form, I pass the form to the VB
COM, that access an SP to insert the data. The same method that does the
INSERT and returns a string to the caller also calls another component that
does the SELECT, formats the returned rows as HTML and is eventually
inserted asynchronously into an existing web page (Ajax).
Since my post I've tried the NOLOCK with the SELECT statement and now the
correct rows appear more often than they did but not consistently. If I
refresh the view, all of the target rows are there including the new row. I
just don't know why the SELECT statement does not return rows when it
should.
When you say the SELECT will be blocked, what does that mean? No rows
returned, or the rows returned by a few microseconds later when the row is
not locked, or is some error logged?
Thanks for helping me out on this one.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:e2ruoQ$2FHA.3880@.TK2MSFTNGP12.phx.gbl...
> What exactly do you mean by "do a transactional Insert"? Are the Insert
and
> Select done from the same connection or two different ones?
> If the row is locked then a SELECT will be blocked unless you are using
> NOLOCK or Read Uncommitted already. Can you give a little more details on
> exactly what code you are calling and how?
> --
> Andrew J. Kelly SQL MVP
>
> "Don Miller" <nospam@.nospam.com> wrote in message
> news:%23UGy9H$2FHA.700@.TK2MSFTNGP15.phx.gbl...
to
I
with
notification
>|||> Since my post I've tried the NOLOCK with the SELECT statement and now the
> correct rows appear more often than they did but not consistently. If I
> refresh the view, all of the target rows are there including the new row.
> I
> just don't know why the SELECT statement does not return rows when it
> should.
What view? Are you using a view on a data set? Is so then it certainly
won't just appear. If it is a SQL Server view then what does the code look
like that creates the view?

> When you say the SELECT will be blocked, what does that mean? No rows
> returned, or the rows returned by a few microseconds later when the row is
> not locked, or is some error logged?
If the row that was inserted is still in an open transaction (the outer most
commit has not been issued yet) the select will wait at any locked rows
before it will read and return the data. If the commit is happening just
before the select is run it may only wait a very brief time if at all. But
it should not return any data if the row to be read is in an open
transaction and has been modified in any way. I suspect there is more to
this than it seems when it comes to how you are reading and inserting the
data. I would like to know more of these views and how many layers are in
between the com objects and the actual data.
Andrew J. Kelly SQL MVP
"Don Miller" <nospam@.nospam.com> wrote in message
news:e4oZre$2FHA.3020@.TK2MSFTNGP15.phx.gbl...
> The entire process of entering data from my ASP web app is transactional,
> that is, if the INSERT fails (e.g. a typo error in an SP or a VBscript or
> ASP error) any changes to the database will be automatically rolled back.
> My
> VB COM+ "write" components "use transactions", my ASP code
> "requires_transaction".
> The INSERT and SELECT are done from different components and different
> connections (same login to SQLServer though).
> I haven't been using NOLOCK or Read Uncommitted in any of my SELECTs.
> As far as my code, somebody fills out a web form, I pass the form to the
> VB
> COM, that access an SP to insert the data. The same method that does the
> INSERT and returns a string to the caller also calls another component
> that
> does the SELECT, formats the returned rows as HTML and is eventually
> inserted asynchronously into an existing web page (Ajax).
> Since my post I've tried the NOLOCK with the SELECT statement and now the
> correct rows appear more often than they did but not consistently. If I
> refresh the view, all of the target rows are there including the new row.
> I
> just don't know why the SELECT statement does not return rows when it
> should.
> When you say the SELECT will be blocked, what does that mean? No rows
> returned, or the rows returned by a few microseconds later when the row is
> not locked, or is some error logged?
> Thanks for helping me out on this one.
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:e2ruoQ$2FHA.3880@.TK2MSFTNGP12.phx.gbl...
> and
> to
> I
> with
> notification
>sql

lock

Hi,
i have a big problem , i work with sql 2000 on windows 2000.
When user do a select against my database sqlserver lock all the table and nonoe can work.
Haw can i change the isolation level for a ropw and for all the db.
Thanks.That's quite a heavy lock. What client/operation are you using?
Too me, sounds someone restricted the database: a db can be restricted to allow one single user at a time. Did you check that? It's in the database properties, Options tab.|||have you isolated the problematic code using profiler? somebody using nasty table hint or big long transaction? got code?|||Sounds like your user's query is doing a table scan. Get his query, and see if any indexes will help it. An index is a pre-requisite for row locking.

Locate backup file - can't see other drives

In MS SQL 2005 when you select "Restore Database" and click "From
Device", you get a directory explorer where you can select a backup
file. In that explorer window ("Locate Backup File") I can't see any
other drives than the one where my MS SQL Server is installed.
Is it just my system or is this by design?
Thanks for any ideas!
It could be that your SQL Server is clustered and you're seeing only the
disks on which SQL Server has a dependency.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
"lanfear" <v_koski@.hotmail.com> wrote in message
news:1183726609.006262.23360@.q75g2000hsh.googlegro ups.com...
In MS SQL 2005 when you select "Restore Database" and click "From
Device", you get a directory explorer where you can select a backup
file. In that explorer window ("Locate Backup File") I can't see any
other drives than the one where my MS SQL Server is installed.
Is it just my system or is this by design?
Thanks for any ideas!
|||Hi
"lanfear" wrote:

> In MS SQL 2005 when you select "Restore Database" and click "From
> Device", you get a directory explorer where you can select a backup
> file. In that explorer window ("Locate Backup File") I can't see any
> other drives than the one where my MS SQL Server is installed.
> Is it just my system or is this by design?
> Thanks for any ideas!
>
Normally the dialog would default to a location and you could browse from
there depending on your permissions to see the directories. You could try
typing the path in yourself. With a cluster you would only see the drives
that have been tagged as cluster resources.
John
|||Are the other drives in question local? I
Anthony E. Castro - MCDBA
"lanfear" wrote:

> In MS SQL 2005 when you select "Restore Database" and click "From
> Device", you get a directory explorer where you can select a backup
> file. In that explorer window ("Locate Backup File") I can't see any
> other drives than the one where my MS SQL Server is installed.
> Is it just my system or is this by design?
> Thanks for any ideas!
>
|||Wow, bullseye! It's a two node cluster where only the E drive is in
the resource group. I suppose there isn't a way to have other drives
displayed when it's a clustered service.
Thanks a lot for the fast reply, now I don't have to scratch my head
anymore. Thanks John & Anthony aswell.
/lf
On Jul 6, 3:23 pm, "Tom Moreau" <t...@.dont.spam.me.cips.ca> wrote:
> It could be that your SQL Server is clustered and you're seeing only the
> disks on which SQL Server has a dependency.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> "lanfear" <v_ko...@.hotmail.com> wrote in message
> news:1183726609.006262.23360@.q75g2000hsh.googlegro ups.com...
> In MS SQL 2005 when you select "Restore Database" and click "From
> Device", you get a directory explorer where you can select a backup
> file. In that explorer window ("Locate Backup File") I can't see any
> other drives than the one where my MS SQL Server is installed.
> Is it just my system or is this by design?
> Thanks for any ideas!
|||Just got back from vacation - sorry for the late follow-up. I don't think
there is a way to get that in SSMS.
That said, I'm concerned that there is only one disk dependency - drive E: -
for your clustered instance. Typically, you have one disk for data and
another for logs.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"lanfear" <v_koski@.hotmail.com> wrote in message
news:1183732767.641961.258190@.q75g2000hsh.googlegr oups.com...
Wow, bullseye! It's a two node cluster where only the E drive is in
the resource group. I suppose there isn't a way to have other drives
displayed when it's a clustered service.
Thanks a lot for the fast reply, now I don't have to scratch my head
anymore. Thanks John & Anthony aswell.
/lf
On Jul 6, 3:23 pm, "Tom Moreau" <t...@.dont.spam.me.cips.ca> wrote:
> It could be that your SQL Server is clustered and you're seeing only the
> disks on which SQL Server has a dependency.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> "lanfear" <v_ko...@.hotmail.com> wrote in message
> news:1183726609.006262.23360@.q75g2000hsh.googlegro ups.com...
> In MS SQL 2005 when you select "Restore Database" and click "From
> Device", you get a directory explorer where you can select a backup
> file. In that explorer window ("Locate Backup File") I can't see any
> other drives than the one where my MS SQL Server is installed.
> Is it just my system or is this by design?
> Thanks for any ideas!

Locate backup file - can't see other drives

In MS SQL 2005 when you select "Restore Database" and click "From
Device", you get a directory explorer where you can select a backup
file. In that explorer window ("Locate Backup File") I can't see any
other drives than the one where my MS SQL Server is installed.
Is it just my system or is this by design?
Thanks for any ideas!It could be that your SQL Server is clustered and you're seeing only the
disks on which SQL Server has a dependency.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
"lanfear" <v_koski@.hotmail.com> wrote in message
news:1183726609.006262.23360@.q75g2000hsh.googlegroups.com...
In MS SQL 2005 when you select "Restore Database" and click "From
Device", you get a directory explorer where you can select a backup
file. In that explorer window ("Locate Backup File") I can't see any
other drives than the one where my MS SQL Server is installed.
Is it just my system or is this by design?
Thanks for any ideas!|||Hi
"lanfear" wrote:

> In MS SQL 2005 when you select "Restore Database" and click "From
> Device", you get a directory explorer where you can select a backup
> file. In that explorer window ("Locate Backup File") I can't see any
> other drives than the one where my MS SQL Server is installed.
> Is it just my system or is this by design?
> Thanks for any ideas!
>
Normally the dialog would default to a location and you could browse from
there depending on your permissions to see the directories. You could try
typing the path in yourself. With a cluster you would only see the drives
that have been tagged as cluster resources.
John|||Are the other drives in question local? I
--
Anthony E. Castro - MCDBA
"lanfear" wrote:

> In MS SQL 2005 when you select "Restore Database" and click "From
> Device", you get a directory explorer where you can select a backup
> file. In that explorer window ("Locate Backup File") I can't see any
> other drives than the one where my MS SQL Server is installed.
> Is it just my system or is this by design?
> Thanks for any ideas!
>|||Wow, bullseye! It's a two node cluster where only the E drive is in
the resource group. I suppose there isn't a way to have other drives
displayed when it's a clustered service.
Thanks a lot for the fast reply, now I don't have to scratch my head
anymore. Thanks John & Anthony aswell.
/lf
On Jul 6, 3:23 pm, "Tom Moreau" <t...@.dont.spam.me.cips.ca> wrote:
> It could be that your SQL Server is clustered and you're seeing only the
> disks on which SQL Server has a dependency.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> "lanfear" <v_ko...@.hotmail.com> wrote in message
> news:1183726609.006262.23360@.q75g2000hsh.googlegroups.com...
> In MS SQL 2005 when you select "Restore Database" and click "From
> Device", you get a directory explorer where you can select a backup
> file. In that explorer window ("Locate Backup File") I can't see any
> other drives than the one where my MS SQL Server is installed.
> Is it just my system or is this by design?
> Thanks for any ideas!|||Just got back from vacation - sorry for the late follow-up. I don't think
there is a way to get that in SSMS.
That said, I'm concerned that there is only one disk dependency - drive E: -
for your clustered instance. Typically, you have one disk for data and
another for logs.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"lanfear" <v_koski@.hotmail.com> wrote in message
news:1183732767.641961.258190@.q75g2000hsh.googlegroups.com...
Wow, bullseye! It's a two node cluster where only the E drive is in
the resource group. I suppose there isn't a way to have other drives
displayed when it's a clustered service.
Thanks a lot for the fast reply, now I don't have to scratch my head
anymore. Thanks John & Anthony aswell.
/lf
On Jul 6, 3:23 pm, "Tom Moreau" <t...@.dont.spam.me.cips.ca> wrote:
> It could be that your SQL Server is clustered and you're seeing only the
> disks on which SQL Server has a dependency.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> "lanfear" <v_ko...@.hotmail.com> wrote in message
> news:1183726609.006262.23360@.q75g2000hsh.googlegroups.com...
> In MS SQL 2005 when you select "Restore Database" and click "From
> Device", you get a directory explorer where you can select a backup
> file. In that explorer window ("Locate Backup File") I can't see any
> other drives than the one where my MS SQL Server is installed.
> Is it just my system or is this by design?
> Thanks for any ideas!

Locate backup file - can't see other drives

In MS SQL 2005 when you select "Restore Database" and click "From
Device", you get a directory explorer where you can select a backup
file. In that explorer window ("Locate Backup File") I can't see any
other drives than the one where my MS SQL Server is installed.
Is it just my system or is this by design?
Thanks for any ideas!It could be that your SQL Server is clustered and you're seeing only the
disks on which SQL Server has a dependency.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
"lanfear" <v_koski@.hotmail.com> wrote in message
news:1183726609.006262.23360@.q75g2000hsh.googlegroups.com...
In MS SQL 2005 when you select "Restore Database" and click "From
Device", you get a directory explorer where you can select a backup
file. In that explorer window ("Locate Backup File") I can't see any
other drives than the one where my MS SQL Server is installed.
Is it just my system or is this by design?
Thanks for any ideas!|||Hi
"lanfear" wrote:
> In MS SQL 2005 when you select "Restore Database" and click "From
> Device", you get a directory explorer where you can select a backup
> file. In that explorer window ("Locate Backup File") I can't see any
> other drives than the one where my MS SQL Server is installed.
> Is it just my system or is this by design?
> Thanks for any ideas!
>
Normally the dialog would default to a location and you could browse from
there depending on your permissions to see the directories. You could try
typing the path in yourself. With a cluster you would only see the drives
that have been tagged as cluster resources.
John|||Are the other drives in question local? I
--
Anthony E. Castro - MCDBA
"lanfear" wrote:
> In MS SQL 2005 when you select "Restore Database" and click "From
> Device", you get a directory explorer where you can select a backup
> file. In that explorer window ("Locate Backup File") I can't see any
> other drives than the one where my MS SQL Server is installed.
> Is it just my system or is this by design?
> Thanks for any ideas!
>|||Wow, bullseye! :) It's a two node cluster where only the E drive is in
the resource group. I suppose there isn't a way to have other drives
displayed when it's a clustered service.
Thanks a lot for the fast reply, now I don't have to scratch my head
anymore. Thanks John & Anthony aswell.
/lf
On Jul 6, 3:23 pm, "Tom Moreau" <t...@.dont.spam.me.cips.ca> wrote:
> It could be that your SQL Server is clustered and you're seeing only the
> disks on which SQL Server has a dependency.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> "lanfear" <v_ko...@.hotmail.com> wrote in message
> news:1183726609.006262.23360@.q75g2000hsh.googlegroups.com...
> In MS SQL 2005 when you select "Restore Database" and click "From
> Device", you get a directory explorer where you can select a backup
> file. In that explorer window ("Locate Backup File") I can't see any
> other drives than the one where my MS SQL Server is installed.
> Is it just my system or is this by design?
> Thanks for any ideas!|||Just got back from vacation - sorry for the late follow-up. I don't think
there is a way to get that in SSMS.
That said, I'm concerned that there is only one disk dependency - drive E: -
for your clustered instance. Typically, you have one disk for data and
another for logs.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"lanfear" <v_koski@.hotmail.com> wrote in message
news:1183732767.641961.258190@.q75g2000hsh.googlegroups.com...
Wow, bullseye! :) It's a two node cluster where only the E drive is in
the resource group. I suppose there isn't a way to have other drives
displayed when it's a clustered service.
Thanks a lot for the fast reply, now I don't have to scratch my head
anymore. Thanks John & Anthony aswell.
/lf
On Jul 6, 3:23 pm, "Tom Moreau" <t...@.dont.spam.me.cips.ca> wrote:
> It could be that your SQL Server is clustered and you're seeing only the
> disks on which SQL Server has a dependency.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> "lanfear" <v_ko...@.hotmail.com> wrote in message
> news:1183726609.006262.23360@.q75g2000hsh.googlegroups.com...
> In MS SQL 2005 when you select "Restore Database" and click "From
> Device", you get a directory explorer where you can select a backup
> file. In that explorer window ("Locate Backup File") I can't see any
> other drives than the one where my MS SQL Server is installed.
> Is it just my system or is this by design?
> Thanks for any ideas!

Local variables in stored proc

How do you declare and then SELECT a value for a local variable within
stored procedure, increment the value and then use in an Insert
statement? Thanks

Any sites that explain this syntax for SQL Server 2000? Thanks
hals_left

CREATE PROCEDURE [dbo].[InsertQualUnit]
@.QualRef tinyint,
@.UnitRef tinyint,
@.UnitGroupRef tinyint,

// this needs to be a local var not an output param, how ?
@.UnitPosition tinyint Output

AS

// Assign a value to the the variable from a SELECT query, how ?
SELECT @.UnitPosition= SELECT MAX(UnitPosition) FROM tblUnitGroup
WHERE QualRef=@.QualRef AND UnitRef=@.UnitRef AND
UnitGroupRef=@.UnitGroupRef

// inc the value
@.UnitPosition+=1

// Use the new value in another SQL statement
INSERT INTO tblQualUnits ( QualRef, UnitRef, UnitGroupRef ,
UnitPosition )
VALUES ( @.QualRef, @.UnitRef, @.UnitGroupRef , @.UnitPosition)
GO-- MODIFIED STORED PROC:
CREATE PROCEDURE [dbo].[InsertQualUnit]
@.QualRef tinyint,
@.UnitRef tinyint,
@.UnitGroupRef tinyint

AS

-- this needs to be a local var not an output param, how ?
declare @.UnitPosition tinyint

-- Assign a value to the the variable from a SELECT query, how ?
SELECT @.UnitPosition= MAX(UnitPosition)
FROM tblUnitGroup
WHERE QualRef=@.QualRef AND UnitRef=@.UnitRef AND
UnitGroupRef=@.UnitGroupRef

-- inc the value
select @.UnitPosition=@.UnitPosition+1

-- Use the new value in another SQL statement
INSERT INTO tblQualUnits ( QualRef, UnitRef, UnitGroupRef ,
UnitPosition )
VALUES ( @.QualRef, @.UnitRef, @.UnitGroupRef , @.UnitPosition)|||See below and the following link.
http://www.google.co.uk/groups?selm...%40giganews.com

But your proc looks a bit strange. Why not just make the key (qualref,
unitref, unitgroupref) and then increment a quantity column? Like:

UPDATE tblQualUnits
SET unit_quantity = unit_quantity + 1
WHERE qualref = @.qualref
AND unitref = @.unitref
AND unitgroupref = @.unitgroupref

Otherwise your table is just an accumulator of redundant rows.

CREATE PROCEDURE [dbo].[InsertQualUnit]
@.qualref TINYINT,
@.unitref TINYINT,
@.unitgroupref TINYINT
AS

INSERT INTO tblQualUnits (qualref, unitref, unitgroupref, unitposition)

SELECT @.qualref, @.unitref, @.unitgroupref,
COALESCE(MAX(unitposition),0)+1
FROM tblUnitGroup
WHERE qualref = @.qualref
AND unitref = @.unitref
AND unitgroupref = @.unitgroupref
GO

--
David Portas
SQL Server MVP
--|||Thankyou for the quick reply.|||Thanks

local variable in select statement

Hello to all,
are there that have already used a select like the
following to catch backup history rows from different SQL
server defined as linked server
select *
from @.my_system.msdb.dbo.sysdbmaintplan_history
where .........
the local variable @...... set using a cursor seams to be
not sintactically correct.
Any idea
Thanks marinoYou need to use dynamic sql if the servername changes
e.g.
declare @.cmd nvarchar(500)
set @.cmd = N'select * from ' + @.my_system +
N'.msdb.dbo.sysdbmaintplan_history'
exec sp_executesql @.cmd
For more on dynamic sql see
http://www.algonet.se/~sommar/dynamic_sql.html
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Marino Prandini" <marino_prandini@.hotmail.com> wrote in message
news:181101c3aeb4$3af029b0$a601280a@.phx.gbl...
Hello to all,
are there that have already used a select like the
following to catch backup history rows from different SQL
server defined as linked server
select *
from @.my_system.msdb.dbo.sysdbmaintplan_history
where .........
the local variable @...... set using a cursor seams to be
not sintactically correct.
Any idea
Thanks marino

Local Temporary Tables

I have created a local temporary table (#Temp) and placed data in it.

When I do:
SELECT * #Temp

The result is a set of rows with no columns.

The SELECT statement is in the same procedure as the creation of #Temp; so I
don't believe this is a scoping issue. Does that make sense?

Thank you in advance,
EricBeringer wrote:
> I have created a local temporary table (#Temp) and placed data in it.
> When I do:
> SELECT * #Temp
> The result is a set of rows with no columns.
> The SELECT statement is in the same procedure as the creation of #Temp; so I
> don't believe this is a scoping issue. Does that make sense?
> Thank you in advance,
> Eric

No, it doesn't make sense. First, your code above is invalid SQL.
Second, you don't provide any DDL or sample data for us to reproduce the
problem.

Zach|||Please be patient with me. I'm learning how to do this stuff by myelf and
just begining! :)

At anyrate here is an example:
ALTER PROCEDURE proc1

AS

SET NOCOUNT ON

CREATE TABLE #TempTable(my_text CHAR(10))

INSERT INTO #TempTable(my_text) VALUES ('test')

INSERT INTO #TempTable(my_text) VALUES ('test2')

INSERT INTO #TempTable(my_text) VALUES ('test3')

SELECT * FROM #TempTable

The result (visually in Access 2002, in datasheet view) after executing the
procedure is simply a row header with three rows and no columns. Of note,
if the SET NOCOUNT ON is commented out there is nothing.

Thanks again,

Eric

"nib" <individual_news@.nibsworld.com> wrote in message
news:2tqnlcF21lmapU2@.uni-berlin.de...
> Beringer wrote:
>> I have created a local temporary table (#Temp) and placed data in it.
>>
>> When I do:
>> SELECT * #Temp
>>
>> The result is a set of rows with no columns.
>>
>> The SELECT statement is in the same procedure as the creation of #Temp;
>> so I don't believe this is a scoping issue. Does that make sense?
>>
>> Thank you in advance,
>> Eric
>>
>>
> No, it doesn't make sense. First, your code above is invalid SQL. Second,
> you don't provide any DDL or sample data for us to reproduce the problem.
> Zach|||I do similar stored procedures like this all the time. Except I create the
SP via Enterprise Manager and Check Syntax etc . Have you tried executing
this from Query Analyzer just to see if it works? Or just create a stored
procedure in Enterprise Manager and EXECUTE it from Query Analyzer...

The column in your example should have one column heading and three rows of
data (if I am reading it correctly).

Is it generating any errors?

Barry

"Beringer" <borden_eric@.invalid.com> wrote in message
news:l1Xdd.56381$kz3.16039@.fed1read02...
> Please be patient with me. I'm learning how to do this stuff by myelf and
> just begining! :)
> At anyrate here is an example:
> ALTER PROCEDURE proc1
> AS
> SET NOCOUNT ON
> CREATE TABLE #TempTable(my_text CHAR(10))
> INSERT INTO #TempTable(my_text) VALUES ('test')
> INSERT INTO #TempTable(my_text) VALUES ('test2')
> INSERT INTO #TempTable(my_text) VALUES ('test3')
> SELECT * FROM #TempTable
> The result (visually in Access 2002, in datasheet view) after executing
> the procedure is simply a row header with three rows and no columns. Of
> note, if the SET NOCOUNT ON is commented out there is nothing.
> Thanks again,
> Eric
> "nib" <individual_news@.nibsworld.com> wrote in message
> news:2tqnlcF21lmapU2@.uni-berlin.de...
>> Beringer wrote:
>>> I have created a local temporary table (#Temp) and placed data in it.
>>>
>>> When I do:
>>> SELECT * #Temp
>>>
>>> The result is a set of rows with no columns.
>>>
>>> The SELECT statement is in the same procedure as the creation of #Temp;
>>> so I don't believe this is a scoping issue. Does that make sense?
>>>
>>> Thank you in advance,
>>> Eric
>>>
>>>
>>
>> No, it doesn't make sense. First, your code above is invalid SQL. Second,
>> you don't provide any DDL or sample data for us to reproduce the problem.
>>
>> Zach

Local table and OpenQuery

I created a stored procedure like this:

CREATE PROCEDURE SP
AS
BEGIN
CREATE TABLE #T( C INT )
INSERT INTO #T(C) VALUES (1)
SELECT * FROM #T
END

When I call it this way: EXEC SP, it works ok.

But when I do it like this:
SELECT * FROM OPENQUERY( MYSERVER, 'EXEC SP')
I receive an error: Invalid object name '#T'
Why?...

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Evgeny Gopengauz <evgop@.ucs.ru> wrote in message news:<4119d7ae$0$14493$c397aba@.news.newsgroups.ws>...
> I created a stored procedure like this:
> CREATE PROCEDURE SP
> AS
> BEGIN
> CREATE TABLE #T( C INT )
> INSERT INTO #T(C) VALUES (1)
> SELECT * FROM #T
> END
> When I call it this way: EXEC SP, it works ok.
> But when I do it like this:
> SELECT * FROM OPENQUERY( MYSERVER, 'EXEC SP')
> I receive an error: Invalid object name '#T'
> Why?...
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

It looks like this is another multi-posted question - if you want
feedback from different groups, then please post to all of them at
once with a single posting. Having said that, you might find Erland's
recent explanation in another thread useful:

http://groups.google.com/groups?hl=...es.ms-sqlserver

Also, if you are trying to do something with the output of a stored
procedure, this article (also from Erland) should point you in the
right direction:

http://www.sommarskog.se/share_data.html

Simon|||Simon! Thank you for your references, I just found the accurate solution
for my troubles.

Concerning with my mutli-posting... I'm sorry but I have no an ability
to post to the comp.databases.ms-sqlserver and
microsoft.public.sqlserver.programming simultaneously (at the same
message) because this conference is available for me through
www.developersdex.com, but microsoft.public through NNTP. Excuse me
please, hope it will never happen again, I will use a single conference
for each single quiestion.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!