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]
>
Showing posts with label default. Show all posts
Showing posts with label default. Show all posts
Friday, March 30, 2012
Locking
Hi all!
Can anyone point me to a doc that describes what the default locking levels
were across different versions of SQL, i.e. row vs. page vs. table, going
all the way back to version 4.2, and what the circumstances would have been
when SQL decides what to use over the other? Guess versions 4.2 and 6.x were
page-locking no matter what if I remember correctly, but want to make sure
before I tell my colleagues rubbish.
Thanks in advance,
Jan Van der EeckenI don't have any documentation but you didn't have true row level locks
until version 7.0. So before that it was Page and after it is Row.
--
Andrew J. Kelly SQL MVP
"Jan Van der Eecken" <jvandereecken@.omam.com> wrote in message
news:u$lROWbWGHA.1228@.TK2MSFTNGP02.phx.gbl...
> Hi all!
> Can anyone point me to a doc that describes what the default locking
> levels were across different versions of SQL, i.e. row vs. page vs. table,
> going all the way back to version 4.2, and what the circumstances would
> have been when SQL decides what to use over the other? Guess versions 4.2
> and 6.x were page-locking no matter what if I remember correctly, but want
> to make sure before I tell my colleagues rubbish.
> Thanks in advance,
> Jan Van der Eecken
>|||Thanks Andrew. I was almost under the impression that it was page-level in
7.0 as well.
Cheers,
Jan
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23LP5XxdWGHA.2376@.TK2MSFTNGP03.phx.gbl...
>I don't have any documentation but you didn't have true row level locks
>until version 7.0. So before that it was Page and after it is Row.
> --
> Andrew J. Kelly SQL MVP
>
> "Jan Van der Eecken" <jvandereecken@.omam.com> wrote in message
> news:u$lROWbWGHA.1228@.TK2MSFTNGP02.phx.gbl...
>> Hi all!
>> Can anyone point me to a doc that describes what the default locking
>> levels were across different versions of SQL, i.e. row vs. page vs.
>> table, going all the way back to version 4.2, and what the circumstances
>> would have been when SQL decides what to use over the other? Guess
>> versions 4.2 and 6.x were page-locking no matter what if I remember
>> correctly, but want to make sure before I tell my colleagues rubbish.
>> Thanks in advance,
>> Jan Van der Eecken
>|||6.5 added some possibility to get some type of row locks for INSERTs. Sorry to be vague, but it was
a long time ago. As I recall, you had to satisfy a number of conditions (probably what indexes you
had) in order to get some concurrency on the page for INSERT operations.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23LP5XxdWGHA.2376@.TK2MSFTNGP03.phx.gbl...
>I don't have any documentation but you didn't have true row level locks until version 7.0. So
>before that it was Page and after it is Row.
> --
> Andrew J. Kelly SQL MVP
>
> "Jan Van der Eecken" <jvandereecken@.omam.com> wrote in message
> news:u$lROWbWGHA.1228@.TK2MSFTNGP02.phx.gbl...
>> Hi all!
>> Can anyone point me to a doc that describes what the default locking levels were across different
>> versions of SQL, i.e. row vs. page vs. table, going all the way back to version 4.2, and what the
>> circumstances would have been when SQL decides what to use over the other? Guess versions 4.2 and
>> 6.x were page-locking no matter what if I remember correctly, but want to make sure before I tell
>> my colleagues rubbish.
>> Thanks in advance,
>> Jan Van der Eecken
>|||Thanks, Tibor
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23dwfZahWGHA.4620@.TK2MSFTNGP04.phx.gbl...
> 6.5 added some possibility to get some type of row locks for INSERTs.
> Sorry to be vague, but it was a long time ago. As I recall, you had to
> satisfy a number of conditions (probably what indexes you had) in order to
> get some concurrency on the page for INSERT operations.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23LP5XxdWGHA.2376@.TK2MSFTNGP03.phx.gbl...
>>I don't have any documentation but you didn't have true row level locks
>>until version 7.0. So before that it was Page and after it is Row.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Jan Van der Eecken" <jvandereecken@.omam.com> wrote in message
>> news:u$lROWbWGHA.1228@.TK2MSFTNGP02.phx.gbl...
>> Hi all!
>> Can anyone point me to a doc that describes what the default locking
>> levels were across different versions of SQL, i.e. row vs. page vs.
>> table, going all the way back to version 4.2, and what the circumstances
>> would have been when SQL decides what to use over the other? Guess
>> versions 4.2 and 6.x were page-locking no matter what if I remember
>> correctly, but want to make sure before I tell my colleagues rubbish.
>> Thanks in advance,
>> Jan Van der Eecken
>>
>|||They called it INSERT-ROW locks in SQL 6.5, but it was not row level
locking. It was just a new type of page lock that allowed multiple inserts
on the same page.
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23dwfZahWGHA.4620@.TK2MSFTNGP04.phx.gbl...
> 6.5 added some possibility to get some type of row locks for INSERTs.
> Sorry to be vague, but it was a long time ago. As I recall, you had to
> satisfy a number of conditions (probably what indexes you had) in order to
> get some concurrency on the page for INSERT operations.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23LP5XxdWGHA.2376@.TK2MSFTNGP03.phx.gbl...
>>I don't have any documentation but you didn't have true row level locks
>>until version 7.0. So before that it was Page and after it is Row.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Jan Van der Eecken" <jvandereecken@.omam.com> wrote in message
>> news:u$lROWbWGHA.1228@.TK2MSFTNGP02.phx.gbl...
>> Hi all!
>> Can anyone point me to a doc that describes what the default locking
>> levels were across different versions of SQL, i.e. row vs. page vs.
>> table, going all the way back to version 4.2, and what the circumstances
>> would have been when SQL decides what to use over the other? Guess
>> versions 4.2 and 6.x were page-locking no matter what if I remember
>> correctly, but want to make sure before I tell my colleagues rubbish.
>> Thanks in advance,
>> Jan Van der Eecken
>>
>|||Thanks for the answers all of you ppl! Got the answer to my colleague
correct after all. But I really appreciate your help, and I really feel
honoured since I only got replies from MVP's..Obviously my question was kind
of obscure.
Nite,
Jan Van der Eecken
Cape Town
South Africa
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%23spoQjlWGHA.4768@.TK2MSFTNGP05.phx.gbl...
> They called it INSERT-ROW locks in SQL 6.5, but it was not row level
> locking. It was just a new type of page lock that allowed multiple inserts
> on the same page.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:%23dwfZahWGHA.4620@.TK2MSFTNGP04.phx.gbl...
>> 6.5 added some possibility to get some type of row locks for INSERTs.
>> Sorry to be vague, but it was a long time ago. As I recall, you had to
>> satisfy a number of conditions (probably what indexes you had) in order
>> to get some concurrency on the page for INSERT operations.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:%23LP5XxdWGHA.2376@.TK2MSFTNGP03.phx.gbl...
>>I don't have any documentation but you didn't have true row level locks
>>until version 7.0. So before that it was Page and after it is Row.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Jan Van der Eecken" <jvandereecken@.omam.com> wrote in message
>> news:u$lROWbWGHA.1228@.TK2MSFTNGP02.phx.gbl...
>> Hi all!
>> Can anyone point me to a doc that describes what the default locking
>> levels were across different versions of SQL, i.e. row vs. page vs.
>> table, going all the way back to version 4.2, and what the
>> circumstances would have been when SQL decides what to use over the
>> other? Guess versions 4.2 and 6.x were page-locking no matter what if I
>> remember correctly, but want to make sure before I tell my colleagues
>> rubbish.
>> Thanks in advance,
>> Jan Van der Eecken
>>
>>
>
Can anyone point me to a doc that describes what the default locking levels
were across different versions of SQL, i.e. row vs. page vs. table, going
all the way back to version 4.2, and what the circumstances would have been
when SQL decides what to use over the other? Guess versions 4.2 and 6.x were
page-locking no matter what if I remember correctly, but want to make sure
before I tell my colleagues rubbish.
Thanks in advance,
Jan Van der EeckenI don't have any documentation but you didn't have true row level locks
until version 7.0. So before that it was Page and after it is Row.
--
Andrew J. Kelly SQL MVP
"Jan Van der Eecken" <jvandereecken@.omam.com> wrote in message
news:u$lROWbWGHA.1228@.TK2MSFTNGP02.phx.gbl...
> Hi all!
> Can anyone point me to a doc that describes what the default locking
> levels were across different versions of SQL, i.e. row vs. page vs. table,
> going all the way back to version 4.2, and what the circumstances would
> have been when SQL decides what to use over the other? Guess versions 4.2
> and 6.x were page-locking no matter what if I remember correctly, but want
> to make sure before I tell my colleagues rubbish.
> Thanks in advance,
> Jan Van der Eecken
>|||Thanks Andrew. I was almost under the impression that it was page-level in
7.0 as well.
Cheers,
Jan
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23LP5XxdWGHA.2376@.TK2MSFTNGP03.phx.gbl...
>I don't have any documentation but you didn't have true row level locks
>until version 7.0. So before that it was Page and after it is Row.
> --
> Andrew J. Kelly SQL MVP
>
> "Jan Van der Eecken" <jvandereecken@.omam.com> wrote in message
> news:u$lROWbWGHA.1228@.TK2MSFTNGP02.phx.gbl...
>> Hi all!
>> Can anyone point me to a doc that describes what the default locking
>> levels were across different versions of SQL, i.e. row vs. page vs.
>> table, going all the way back to version 4.2, and what the circumstances
>> would have been when SQL decides what to use over the other? Guess
>> versions 4.2 and 6.x were page-locking no matter what if I remember
>> correctly, but want to make sure before I tell my colleagues rubbish.
>> Thanks in advance,
>> Jan Van der Eecken
>|||6.5 added some possibility to get some type of row locks for INSERTs. Sorry to be vague, but it was
a long time ago. As I recall, you had to satisfy a number of conditions (probably what indexes you
had) in order to get some concurrency on the page for INSERT operations.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23LP5XxdWGHA.2376@.TK2MSFTNGP03.phx.gbl...
>I don't have any documentation but you didn't have true row level locks until version 7.0. So
>before that it was Page and after it is Row.
> --
> Andrew J. Kelly SQL MVP
>
> "Jan Van der Eecken" <jvandereecken@.omam.com> wrote in message
> news:u$lROWbWGHA.1228@.TK2MSFTNGP02.phx.gbl...
>> Hi all!
>> Can anyone point me to a doc that describes what the default locking levels were across different
>> versions of SQL, i.e. row vs. page vs. table, going all the way back to version 4.2, and what the
>> circumstances would have been when SQL decides what to use over the other? Guess versions 4.2 and
>> 6.x were page-locking no matter what if I remember correctly, but want to make sure before I tell
>> my colleagues rubbish.
>> Thanks in advance,
>> Jan Van der Eecken
>|||Thanks, Tibor
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23dwfZahWGHA.4620@.TK2MSFTNGP04.phx.gbl...
> 6.5 added some possibility to get some type of row locks for INSERTs.
> Sorry to be vague, but it was a long time ago. As I recall, you had to
> satisfy a number of conditions (probably what indexes you had) in order to
> get some concurrency on the page for INSERT operations.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23LP5XxdWGHA.2376@.TK2MSFTNGP03.phx.gbl...
>>I don't have any documentation but you didn't have true row level locks
>>until version 7.0. So before that it was Page and after it is Row.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Jan Van der Eecken" <jvandereecken@.omam.com> wrote in message
>> news:u$lROWbWGHA.1228@.TK2MSFTNGP02.phx.gbl...
>> Hi all!
>> Can anyone point me to a doc that describes what the default locking
>> levels were across different versions of SQL, i.e. row vs. page vs.
>> table, going all the way back to version 4.2, and what the circumstances
>> would have been when SQL decides what to use over the other? Guess
>> versions 4.2 and 6.x were page-locking no matter what if I remember
>> correctly, but want to make sure before I tell my colleagues rubbish.
>> Thanks in advance,
>> Jan Van der Eecken
>>
>|||They called it INSERT-ROW locks in SQL 6.5, but it was not row level
locking. It was just a new type of page lock that allowed multiple inserts
on the same page.
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23dwfZahWGHA.4620@.TK2MSFTNGP04.phx.gbl...
> 6.5 added some possibility to get some type of row locks for INSERTs.
> Sorry to be vague, but it was a long time ago. As I recall, you had to
> satisfy a number of conditions (probably what indexes you had) in order to
> get some concurrency on the page for INSERT operations.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23LP5XxdWGHA.2376@.TK2MSFTNGP03.phx.gbl...
>>I don't have any documentation but you didn't have true row level locks
>>until version 7.0. So before that it was Page and after it is Row.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Jan Van der Eecken" <jvandereecken@.omam.com> wrote in message
>> news:u$lROWbWGHA.1228@.TK2MSFTNGP02.phx.gbl...
>> Hi all!
>> Can anyone point me to a doc that describes what the default locking
>> levels were across different versions of SQL, i.e. row vs. page vs.
>> table, going all the way back to version 4.2, and what the circumstances
>> would have been when SQL decides what to use over the other? Guess
>> versions 4.2 and 6.x were page-locking no matter what if I remember
>> correctly, but want to make sure before I tell my colleagues rubbish.
>> Thanks in advance,
>> Jan Van der Eecken
>>
>|||Thanks for the answers all of you ppl! Got the answer to my colleague
correct after all. But I really appreciate your help, and I really feel
honoured since I only got replies from MVP's..Obviously my question was kind
of obscure.
Nite,
Jan Van der Eecken
Cape Town
South Africa
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%23spoQjlWGHA.4768@.TK2MSFTNGP05.phx.gbl...
> They called it INSERT-ROW locks in SQL 6.5, but it was not row level
> locking. It was just a new type of page lock that allowed multiple inserts
> on the same page.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:%23dwfZahWGHA.4620@.TK2MSFTNGP04.phx.gbl...
>> 6.5 added some possibility to get some type of row locks for INSERTs.
>> Sorry to be vague, but it was a long time ago. As I recall, you had to
>> satisfy a number of conditions (probably what indexes you had) in order
>> to get some concurrency on the page for INSERT operations.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:%23LP5XxdWGHA.2376@.TK2MSFTNGP03.phx.gbl...
>>I don't have any documentation but you didn't have true row level locks
>>until version 7.0. So before that it was Page and after it is Row.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Jan Van der Eecken" <jvandereecken@.omam.com> wrote in message
>> news:u$lROWbWGHA.1228@.TK2MSFTNGP02.phx.gbl...
>> Hi all!
>> Can anyone point me to a doc that describes what the default locking
>> levels were across different versions of SQL, i.e. row vs. page vs.
>> table, going all the way back to version 4.2, and what the
>> circumstances would have been when SQL decides what to use over the
>> other? Guess versions 4.2 and 6.x were page-locking no matter what if I
>> remember correctly, but want to make sure before I tell my colleagues
>> rubbish.
>> Thanks in advance,
>> Jan Van der Eecken
>>
>>
>
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
> >
> >
>
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
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
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
Hi all!
Can anyone point me to a doc that describes what the default locking levels
were across different versions of SQL, i.e. row vs. page vs. table, going
all the way back to version 4.2, and what the circumstances would have been
when SQL decides what to use over the other? Guess versions 4.2 and 6.x were
page-locking no matter what if I remember correctly, but want to make sure
before I tell my colleagues rubbish.
Thanks in advance,
Jan Van der Eecken
I don't have any documentation but you didn't have true row level locks
until version 7.0. So before that it was Page and after it is Row.
Andrew J. Kelly SQL MVP
"Jan Van der Eecken" <jvandereecken@.omam.com> wrote in message
news:u$lROWbWGHA.1228@.TK2MSFTNGP02.phx.gbl...
> Hi all!
> Can anyone point me to a doc that describes what the default locking
> levels were across different versions of SQL, i.e. row vs. page vs. table,
> going all the way back to version 4.2, and what the circumstances would
> have been when SQL decides what to use over the other? Guess versions 4.2
> and 6.x were page-locking no matter what if I remember correctly, but want
> to make sure before I tell my colleagues rubbish.
> Thanks in advance,
> Jan Van der Eecken
>
|||Thanks Andrew. I was almost under the impression that it was page-level in
7.0 as well.
Cheers,
Jan
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23LP5XxdWGHA.2376@.TK2MSFTNGP03.phx.gbl...
>I don't have any documentation but you didn't have true row level locks
>until version 7.0. So before that it was Page and after it is Row.
> --
> Andrew J. Kelly SQL MVP
>
> "Jan Van der Eecken" <jvandereecken@.omam.com> wrote in message
> news:u$lROWbWGHA.1228@.TK2MSFTNGP02.phx.gbl...
>
|||6.5 added some possibility to get some type of row locks for INSERTs. Sorry to be vague, but it was
a long time ago. As I recall, you had to satisfy a number of conditions (probably what indexes you
had) in order to get some concurrency on the page for INSERT operations.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23LP5XxdWGHA.2376@.TK2MSFTNGP03.phx.gbl...
>I don't have any documentation but you didn't have true row level locks until version 7.0. So
>before that it was Page and after it is Row.
> --
> Andrew J. Kelly SQL MVP
>
> "Jan Van der Eecken" <jvandereecken@.omam.com> wrote in message
> news:u$lROWbWGHA.1228@.TK2MSFTNGP02.phx.gbl...
>
|||Thanks, Tibor
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23dwfZahWGHA.4620@.TK2MSFTNGP04.phx.gbl...
> 6.5 added some possibility to get some type of row locks for INSERTs.
> Sorry to be vague, but it was a long time ago. As I recall, you had to
> satisfy a number of conditions (probably what indexes you had) in order to
> get some concurrency on the page for INSERT operations.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23LP5XxdWGHA.2376@.TK2MSFTNGP03.phx.gbl...
>
Can anyone point me to a doc that describes what the default locking levels
were across different versions of SQL, i.e. row vs. page vs. table, going
all the way back to version 4.2, and what the circumstances would have been
when SQL decides what to use over the other? Guess versions 4.2 and 6.x were
page-locking no matter what if I remember correctly, but want to make sure
before I tell my colleagues rubbish.
Thanks in advance,
Jan Van der Eecken
I don't have any documentation but you didn't have true row level locks
until version 7.0. So before that it was Page and after it is Row.
Andrew J. Kelly SQL MVP
"Jan Van der Eecken" <jvandereecken@.omam.com> wrote in message
news:u$lROWbWGHA.1228@.TK2MSFTNGP02.phx.gbl...
> Hi all!
> Can anyone point me to a doc that describes what the default locking
> levels were across different versions of SQL, i.e. row vs. page vs. table,
> going all the way back to version 4.2, and what the circumstances would
> have been when SQL decides what to use over the other? Guess versions 4.2
> and 6.x were page-locking no matter what if I remember correctly, but want
> to make sure before I tell my colleagues rubbish.
> Thanks in advance,
> Jan Van der Eecken
>
|||Thanks Andrew. I was almost under the impression that it was page-level in
7.0 as well.
Cheers,
Jan
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23LP5XxdWGHA.2376@.TK2MSFTNGP03.phx.gbl...
>I don't have any documentation but you didn't have true row level locks
>until version 7.0. So before that it was Page and after it is Row.
> --
> Andrew J. Kelly SQL MVP
>
> "Jan Van der Eecken" <jvandereecken@.omam.com> wrote in message
> news:u$lROWbWGHA.1228@.TK2MSFTNGP02.phx.gbl...
>
|||6.5 added some possibility to get some type of row locks for INSERTs. Sorry to be vague, but it was
a long time ago. As I recall, you had to satisfy a number of conditions (probably what indexes you
had) in order to get some concurrency on the page for INSERT operations.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23LP5XxdWGHA.2376@.TK2MSFTNGP03.phx.gbl...
>I don't have any documentation but you didn't have true row level locks until version 7.0. So
>before that it was Page and after it is Row.
> --
> Andrew J. Kelly SQL MVP
>
> "Jan Van der Eecken" <jvandereecken@.omam.com> wrote in message
> news:u$lROWbWGHA.1228@.TK2MSFTNGP02.phx.gbl...
>
|||Thanks, Tibor
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23dwfZahWGHA.4620@.TK2MSFTNGP04.phx.gbl...
> 6.5 added some possibility to get some type of row locks for INSERTs.
> Sorry to be vague, but it was a long time ago. As I recall, you had to
> satisfy a number of conditions (probably what indexes you had) in order to
> get some concurrency on the page for INSERT operations.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23LP5XxdWGHA.2376@.TK2MSFTNGP03.phx.gbl...
>
Lock_timeout
Hello there,
Is there any way to set a default lock time out for the server with out using the sentence SET LOCK_TIMEOUT 20 ?
Could you explain me step by step ?
Thanks !Please Check sp_configure locks execute this first check the locks space allocated, Increase the locks by executing sp_configure locks 100000 then execute the Store Procedure you will not get the problem.|||Please try with this procedure
Execute sp_configure locks
check the locks allocated
now increase the locks by exec
Execute sp_configure locks 100000
then execute your request you will not get the error|||Thanks !|||I want to eliminate automatically every lock after 10 seconds and I did this:
USE master
go
EXEC sp_configure 'locks', 10000
go
RECONFIGURE WITH OVERRIDE
go
Is this correct ? Do I have to restart sql server ?
Thanks|||If you want to make the locks "go away", why not just ignore them and deal with the consequences later? You can set the transaction isolation level down, and the server will just ignore the locks held by other processes.
This is very dangerous, but it is less dangerous than simply trying to break the existing locks since it only puts your process at risk instead of the whole server.
-PatP|||I want to eliminate automatically every lock after 10 seconds and I did this:
USE master
go
EXEC sp_configure 'locks', 10000
go
RECONFIGURE WITH OVERRIDE
go
Is this correct ? Do I have to restart sql server ?
Thanks
Yes, Exactly
Is there any way to set a default lock time out for the server with out using the sentence SET LOCK_TIMEOUT 20 ?
Could you explain me step by step ?
Thanks !Please Check sp_configure locks execute this first check the locks space allocated, Increase the locks by executing sp_configure locks 100000 then execute the Store Procedure you will not get the problem.|||Please try with this procedure
Execute sp_configure locks
check the locks allocated
now increase the locks by exec
Execute sp_configure locks 100000
then execute your request you will not get the error|||Thanks !|||I want to eliminate automatically every lock after 10 seconds and I did this:
USE master
go
EXEC sp_configure 'locks', 10000
go
RECONFIGURE WITH OVERRIDE
go
Is this correct ? Do I have to restart sql server ?
Thanks|||If you want to make the locks "go away", why not just ignore them and deal with the consequences later? You can set the transaction isolation level down, and the server will just ignore the locks held by other processes.
This is very dangerous, but it is less dangerous than simply trying to break the existing locks since it only puts your process at risk instead of the whole server.
-PatP|||I want to eliminate automatically every lock after 10 seconds and I did this:
USE master
go
EXEC sp_configure 'locks', 10000
go
RECONFIGURE WITH OVERRIDE
go
Is this correct ? Do I have to restart sql server ?
Thanks
Yes, Exactly
Wednesday, March 28, 2012
Lock timeout
Hi all,
I need to set a default value for lock timeout on all
connections. Is there any way to do this without running:
set @.@.lock_timeout = ?
at the beginning of every connection? I'd like a global
setting on this.
Best regardsHi,
I feel there is no setting to control Lock time out at server level.
Thanks
Hari
MCDBA
"Johnny" <anonymous@.discussions.microsoft.com> wrote in message
news:0a5c01c3c8f9$8a88d120$a001280a@.phx.gbl...
> Hi all,
> I need to set a default value for lock timeout on all
> connections. Is there any way to do this without running:
> set @.@.lock_timeout = ?
> at the beginning of every connection? I'd like a global
> setting on this.
> Best regardssql
I need to set a default value for lock timeout on all
connections. Is there any way to do this without running:
set @.@.lock_timeout = ?
at the beginning of every connection? I'd like a global
setting on this.
Best regardsHi,
I feel there is no setting to control Lock time out at server level.
Thanks
Hari
MCDBA
"Johnny" <anonymous@.discussions.microsoft.com> wrote in message
news:0a5c01c3c8f9$8a88d120$a001280a@.phx.gbl...
> Hi all,
> I need to set a default value for lock timeout on all
> connections. Is there any way to do this without running:
> set @.@.lock_timeout = ?
> at the beginning of every connection? I'd like a global
> setting on this.
> Best regardssql
Lock request time out period exceeded
Hi,
What is the default for @.@.LOCK_TIMEOUT?
When it is not set, how long it is?
Thanks,
JanosThe default is -1 which is it won't timeout, it will wait
forever. See the books online topic: SET LOCK_TIMEOUT
for more information.
-Sue
On Mon, 7 Nov 2005 12:12:04 -0700, "Janos Horanszky"
<jhoranszky@.chartelltechnology.com> wrote:
>Hi,
>What is the default for @.@.LOCK_TIMEOUT?
>When it is not set, how long it is?
>Thanks,
>Janos
>|||Thanks Sue.
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:6gf0n1dh5lfvos0ua3ic5sdg08vnsmja27@.4ax.com...
> The default is -1 which is it won't timeout, it will wait
> forever. See the books online topic: SET LOCK_TIMEOUT
> for more information.
> -Sue
> On Mon, 7 Nov 2005 12:12:04 -0700, "Janos Horanszky"
> <jhoranszky@.chartelltechnology.com> wrote:
>>Hi,
>>What is the default for @.@.LOCK_TIMEOUT?
>>When it is not set, how long it is?
>>Thanks,
>>Janos
>sql
What is the default for @.@.LOCK_TIMEOUT?
When it is not set, how long it is?
Thanks,
JanosThe default is -1 which is it won't timeout, it will wait
forever. See the books online topic: SET LOCK_TIMEOUT
for more information.
-Sue
On Mon, 7 Nov 2005 12:12:04 -0700, "Janos Horanszky"
<jhoranszky@.chartelltechnology.com> wrote:
>Hi,
>What is the default for @.@.LOCK_TIMEOUT?
>When it is not set, how long it is?
>Thanks,
>Janos
>|||Thanks Sue.
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:6gf0n1dh5lfvos0ua3ic5sdg08vnsmja27@.4ax.com...
> The default is -1 which is it won't timeout, it will wait
> forever. See the books online topic: SET LOCK_TIMEOUT
> for more information.
> -Sue
> On Mon, 7 Nov 2005 12:12:04 -0700, "Janos Horanszky"
> <jhoranszky@.chartelltechnology.com> wrote:
>>Hi,
>>What is the default for @.@.LOCK_TIMEOUT?
>>When it is not set, how long it is?
>>Thanks,
>>Janos
>sql
Lock request time out period exceeded
Hi,
What is the default for @.@.LOCK_TIMEOUT?
When it is not set, how long it is?
Thanks,
JanosThe default is -1 which is it won't timeout, it will wait
forever. See the books online topic: SET LOCK_TIMEOUT
for more information.
-Sue
On Mon, 7 Nov 2005 12:12:04 -0700, "Janos Horanszky"
<jhoranszky@.chartelltechnology.com> wrote:
>Hi,
>What is the default for @.@.LOCK_TIMEOUT?
>When it is not set, how long it is?
>Thanks,
>Janos
>|||Thanks Sue.
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:6gf0n1dh5lfvos0ua3ic5sdg08vnsmja27@.
4ax.com...
> The default is -1 which is it won't timeout, it will wait
> forever. See the books online topic: SET LOCK_TIMEOUT
> for more information.
> -Sue
> On Mon, 7 Nov 2005 12:12:04 -0700, "Janos Horanszky"
> <jhoranszky@.chartelltechnology.com> wrote:
>
>
What is the default for @.@.LOCK_TIMEOUT?
When it is not set, how long it is?
Thanks,
JanosThe default is -1 which is it won't timeout, it will wait
forever. See the books online topic: SET LOCK_TIMEOUT
for more information.
-Sue
On Mon, 7 Nov 2005 12:12:04 -0700, "Janos Horanszky"
<jhoranszky@.chartelltechnology.com> wrote:
>Hi,
>What is the default for @.@.LOCK_TIMEOUT?
>When it is not set, how long it is?
>Thanks,
>Janos
>|||Thanks Sue.
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:6gf0n1dh5lfvos0ua3ic5sdg08vnsmja27@.
4ax.com...
> The default is -1 which is it won't timeout, it will wait
> forever. See the books online topic: SET LOCK_TIMEOUT
> for more information.
> -Sue
> On Mon, 7 Nov 2005 12:12:04 -0700, "Janos Horanszky"
> <jhoranszky@.chartelltechnology.com> wrote:
>
>
Lock request time out period exceeded
Hi,
What is the default for @.@.LOCK_TIMEOUT?
When it is not set, how long it is?
Thanks,
Janos
The default is -1 which is it won't timeout, it will wait
forever. See the books online topic: SET LOCK_TIMEOUT
for more information.
-Sue
On Mon, 7 Nov 2005 12:12:04 -0700, "Janos Horanszky"
<jhoranszky@.chartelltechnology.com> wrote:
>Hi,
>What is the default for @.@.LOCK_TIMEOUT?
>When it is not set, how long it is?
>Thanks,
>Janos
>
|||Thanks Sue.
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:6gf0n1dh5lfvos0ua3ic5sdg08vnsmja27@.4ax.com...
> The default is -1 which is it won't timeout, it will wait
> forever. See the books online topic: SET LOCK_TIMEOUT
> for more information.
> -Sue
> On Mon, 7 Nov 2005 12:12:04 -0700, "Janos Horanszky"
> <jhoranszky@.chartelltechnology.com> wrote:
>
What is the default for @.@.LOCK_TIMEOUT?
When it is not set, how long it is?
Thanks,
Janos
The default is -1 which is it won't timeout, it will wait
forever. See the books online topic: SET LOCK_TIMEOUT
for more information.
-Sue
On Mon, 7 Nov 2005 12:12:04 -0700, "Janos Horanszky"
<jhoranszky@.chartelltechnology.com> wrote:
>Hi,
>What is the default for @.@.LOCK_TIMEOUT?
>When it is not set, how long it is?
>Thanks,
>Janos
>
|||Thanks Sue.
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:6gf0n1dh5lfvos0ua3ic5sdg08vnsmja27@.4ax.com...
> The default is -1 which is it won't timeout, it will wait
> forever. See the books online topic: SET LOCK_TIMEOUT
> for more information.
> -Sue
> On Mon, 7 Nov 2005 12:12:04 -0700, "Janos Horanszky"
> <jhoranszky@.chartelltechnology.com> wrote:
>
Wednesday, March 21, 2012
Location of default instance SQL server 2005 files
I have just installed SQL Server 2005. I was not given a choice during installation (that I remember seeing anyway) about which drive I wanted to place the default SQL instance on.
It ended up on C: and I needed it on D:.
So my questions are:
Is there a way to move it to D: that is easier than the way you had to go about it in SQL 2000?
Is there a way to do it during setup so I can avoid this in the future?
There is a file called template.ini located at the same folder as setup.exe. In this file, all related command line parameters are discussed when SQL Server 2005 is installed under unattended mode, i.e., command line. You can specify paths to install SQL Server 2005 components.
Under attended mode, i.e., GUI mode, during selecting components to install, clicking the Advanced button will prompt to change paths to install SQL Server 2005 components.
|||
Jiongxiong,
There is no file "template.ini" with the SQL setup program (cd or installed location).
The SQL readme points to a generic windows deployment info page - which is unhelpful.
|||If you're installing SQL Server Express edition you are running an executable called SQLEXPR.EXE. This is a compressed package that when run expands the contents to a temporary folder, runs the installation and then deletes the temporary folder. You can persist the uncompressed contents by running sqlexpr.exe /x - this will prompt you for a folder to save the uncompressed files. Once uncompressed you'll find the template.ini file in this folder (same folder as setup.exe). The parameters that can be passed to setup.exe can also be passed to sqlexpr.exe - the compression tool will pass the parameters to setup.exe when it runs it.
If you're installing an edition other than Express you will find template.ini in the same folder as setup.exe.
Cheers,
Dan
Jiongxiong, you are right! Sorry, I missed the template.ini file somehow. I will see if it works for my clean install tests.
It is simply amazing that M$ can not do simple file management for installation. There are files, they have versions, they need to be copied to a folder from the CD, and some settings stored - how complicated is that? Well if you are M$ you make SQL05 installation into a mass of gobble-gook. SQL Server 05 is not ready for production use until its installation abomination is fixed!
Most people think that SQL Server setup is just about copying files and writing some reg keys. While it's true it's only a small fraction of what setup does. Here is a small sampling of the other things SQL Server setup does.
SQL Server setup checks the configuration of the machine to determine if the machine is in a valid state - which improves the chances of a good installation. Windows Installer doesn't support this.
SQL Server is a multi-instance product. Windows Installer doesn't support this.
SQL Server requires configuration to happen as part of the setup process. Windows Installer doesn't support this. If you've ever performed a MySQL installation a separate tool is launched post setup to do the configuration - although at the end of setup you don't have a working server.
SQL Server cluster install is extremely robust. Every other product I've seen requires you to rerun setup on every node and make sure the configurations are the same. SQL does this for you. Windows Installer doesn't support cluster installs.
SQL Server requires a secure installation. This means that every file/directory/service/reg key is ACL'd to the appropriate service account. Windows Installer doesn't support this.
SQL Server setup supports version and edition upgrade. Windows Installer doesn't support this.
Windows Installer handles very well the copying of files and the writing of reg keys, but it doesn't handle any of the other complexities associated with SQL Server setup. Everywhere I mentioned that Windows Installer doesn't support something it means we've had to build it. I'm not using this as an excuse, but merely highlighting the fact that setup is much more than just copying files and writing reg keys. Anyone who's worked on the setup of a complex product (such as SQL or Visual Studio) will attest to this.
Is our setup perfect? No. But I certainly don't believe it deserves the criticism. There are millions of ways a user can hose their machine and it would be impossible for us to test for every single case. I'm sure you'll be tempted to compare SQL with other products - don't. There are very few products that come to mind that have the same level of complexity that SQL Server does. Again, this is not an excuse for any particular bug that remains in setup. The complexity of SQL Server has grown tremendously from SQL Server 7.0. The setup has had to embrace the increased complexity and deliver a stable and consistent experience. I believe this was achieved.
Cheers,
Dan
Dan:
This is critical tidbit that surely belongs in Embedding SQL Server Express in Custom Applications. That's a 32-page article (from November 2005) at the Microsoft SQL Server Developer Centre, and while it obviously spends time on template.ini, it doesn't tell you how to find it. Luckily for me, I found your reply on this forum.
Please pass this on to Robert Walters, who wrote the article.
Regards
Josh Korn
Location of default instance SQL server 2005 files
I have just installed SQL Server 2005. I was not given a choice during installation (that I remember seeing anyway) about which drive I wanted to place the default SQL instance on.
It ended up on C: and I needed it on D:.
So my questions are:
- Is there a way to move it to D: that is easier than the way you had to go about it in SQL 2000? Is there a way to do it during setup so I can avoid this in the future?
There is a file called template.ini located at the same folder as setup.exe. In this file, all related command line parameters are discussed when SQL Server 2005 is installed under unattended mode, i.e., command line. You can specify paths to install SQL Server 2005 components.
Under attended mode, i.e., GUI mode, during selecting components to install, clicking the Advanced button will prompt to change paths to install SQL Server 2005 components.
|||
Jiongxiong,
There is no file "template.ini" with the SQL setup program (cd or installed location).
The SQL readme points to a generic windows deployment info page - which is unhelpful.
|||If you're installing SQL Server Express edition you are running an executable called SQLEXPR.EXE. This is a compressed package that when run expands the contents to a temporary folder, runs the installation and then deletes the temporary folder. You can persist the uncompressed contents by running sqlexpr.exe /x - this will prompt you for a folder to save the uncompressed files. Once uncompressed you'll find the template.ini file in this folder (same folder as setup.exe). The parameters that can be passed to setup.exe can also be passed to sqlexpr.exe - the compression tool will pass the parameters to setup.exe when it runs it.
If you're installing an edition other than Express you will find template.ini in the same folder as setup.exe.
Cheers,
Dan
Jiongxiong, you are right! Sorry, I missed the template.ini file somehow. I will see if it works for my clean install tests.
It is simply amazing that M$ can not do simple file management for installation. There are files, they have versions, they need to be copied to a folder from the CD, and some settings stored - how complicated is that? Well if you are M$ you make SQL05 installation into a mass of gobble-gook. SQL Server 05 is not ready for production use until its installation abomination is fixed!
Most people think that SQL Server setup is just about copying files and writing some reg keys. While it's true it's only a small fraction of what setup does. Here is a small sampling of the other things SQL Server setup does.
SQL Server setup checks the configuration of the machine to determine if the machine is in a valid state - which improves the chances of a good installation. Windows Installer doesn't support this.
SQL Server is a multi-instance product. Windows Installer doesn't support this.
SQL Server requires configuration to happen as part of the setup process. Windows Installer doesn't support this. If you've ever performed a MySQL installation a separate tool is launched post setup to do the configuration - although at the end of setup you don't have a working server.
SQL Server cluster install is extremely robust. Every other product I've seen requires you to rerun setup on every node and make sure the configurations are the same. SQL does this for you. Windows Installer doesn't support cluster installs.
SQL Server requires a secure installation. This means that every file/directory/service/reg key is ACL'd to the appropriate service account. Windows Installer doesn't support this.
SQL Server setup supports version and edition upgrade. Windows Installer doesn't support this.
Windows Installer handles very well the copying of files and the writing of reg keys, but it doesn't handle any of the other complexities associated with SQL Server setup. Everywhere I mentioned that Windows Installer doesn't support something it means we've had to build it. I'm not using this as an excuse, but merely highlighting the fact that setup is much more than just copying files and writing reg keys. Anyone who's worked on the setup of a complex product (such as SQL or Visual Studio) will attest to this.
Is our setup perfect? No. But I certainly don't believe it deserves the criticism. There are millions of ways a user can hose their machine and it would be impossible for us to test for every single case. I'm sure you'll be tempted to compare SQL with other products - don't. There are very few products that come to mind that have the same level of complexity that SQL Server does. Again, this is not an excuse for any particular bug that remains in setup. The complexity of SQL Server has grown tremendously from SQL Server 7.0. The setup has had to embrace the increased complexity and deliver a stable and consistent experience. I believe this was achieved.
Cheers,
Dan
Dan:
This is critical tidbit that surely belongs in Embedding SQL Server Express in Custom Applications. That's a 32-page article (from November 2005) at the Microsoft SQL Server Developer Centre, and while it obviously spends time on template.ini, it doesn't tell you how to find it. Luckily for me, I found your reply on this forum.
Please pass this on to Robert Walters, who wrote the article.
Regards
Josh Korn
Location of default instance SQL server 2005 files
I have just installed SQL Server 2005. I was not given a choice during installation (that I remember seeing anyway) about which drive I wanted to place the default SQL instance on.
It ended up on C: and I needed it on D:.
So my questions are:
- Is there a way to move it to D: that is easier than the way you had to go about it in SQL 2000? Is there a way to do it during setup so I can avoid this in the future?
There is a file called template.ini located at the same folder as setup.exe. In this file, all related command line parameters are discussed when SQL Server 2005 is installed under unattended mode, i.e., command line. You can specify paths to install SQL Server 2005 components.
Under attended mode, i.e., GUI mode, during selecting components to install, clicking the Advanced button will prompt to change paths to install SQL Server 2005 components.
|||
Jiongxiong,
There is no file "template.ini" with the SQL setup program (cd or installed location).
The SQL readme points to a generic windows deployment info page - which is unhelpful.
|||If you're installing SQL Server Express edition you are running an executable called SQLEXPR.EXE. This is a compressed package that when run expands the contents to a temporary folder, runs the installation and then deletes the temporary folder. You can persist the uncompressed contents by running sqlexpr.exe /x - this will prompt you for a folder to save the uncompressed files. Once uncompressed you'll find the template.ini file in this folder (same folder as setup.exe). The parameters that can be passed to setup.exe can also be passed to sqlexpr.exe - the compression tool will pass the parameters to setup.exe when it runs it.
If you're installing an edition other than Express you will find template.ini in the same folder as setup.exe.
Cheers,
Dan
Jiongxiong, you are right! Sorry, I missed the template.ini file somehow. I will see if it works for my clean install tests.
It is simply amazing that M$ can not do simple file management for installation. There are files, they have versions, they need to be copied to a folder from the CD, and some settings stored - how complicated is that? Well if you are M$ you make SQL05 installation into a mass of gobble-gook. SQL Server 05 is not ready for production use until its installation abomination is fixed!
Most people think that SQL Server setup is just about copying files and writing some reg keys. While it's true it's only a small fraction of what setup does. Here is a small sampling of the other things SQL Server setup does.
SQL Server setup checks the configuration of the machine to determine if the machine is in a valid state - which improves the chances of a good installation. Windows Installer doesn't support this.
SQL Server is a multi-instance product. Windows Installer doesn't support this.
SQL Server requires configuration to happen as part of the setup process. Windows Installer doesn't support this. If you've ever performed a MySQL installation a separate tool is launched post setup to do the configuration - although at the end of setup you don't have a working server.
SQL Server cluster install is extremely robust. Every other product I've seen requires you to rerun setup on every node and make sure the configurations are the same. SQL does this for you. Windows Installer doesn't support cluster installs.
SQL Server requires a secure installation. This means that every file/directory/service/reg key is ACL'd to the appropriate service account. Windows Installer doesn't support this.
SQL Server setup supports version and edition upgrade. Windows Installer doesn't support this.
Windows Installer handles very well the copying of files and the writing of reg keys, but it doesn't handle any of the other complexities associated with SQL Server setup. Everywhere I mentioned that Windows Installer doesn't support something it means we've had to build it. I'm not using this as an excuse, but merely highlighting the fact that setup is much more than just copying files and writing reg keys. Anyone who's worked on the setup of a complex product (such as SQL or Visual Studio) will attest to this.
Is our setup perfect? No. But I certainly don't believe it deserves the criticism. There are millions of ways a user can hose their machine and it would be impossible for us to test for every single case. I'm sure you'll be tempted to compare SQL with other products - don't. There are very few products that come to mind that have the same level of complexity that SQL Server does. Again, this is not an excuse for any particular bug that remains in setup. The complexity of SQL Server has grown tremendously from SQL Server 7.0. The setup has had to embrace the increased complexity and deliver a stable and consistent experience. I believe this was achieved.
Cheers,
Dan
Dan:
This is critical tidbit that surely belongs in Embedding SQL Server Express in Custom Applications. That's a 32-page article (from November 2005) at the Microsoft SQL Server Developer Centre, and while it obviously spends time on template.ini, it doesn't tell you how to find it. Luckily for me, I found your reply on this forum.
Please pass this on to Robert Walters, who wrote the article.
Regards
Josh Korn
Monday, March 12, 2012
localized messages for SQL SERVER 2000
I found a usefull link to my problem : http://support.microsoft.com/Default.aspx?id=277535
But I am unable to find the s80xLang.exe file anywhere. I have looked through all my DVDs and nothing.
Anyone know where to look or download it from ?
The KB article mentions that you need to contact Customer Support to be able to download this. That wording implies the file is not available for public download. If you use email support to open a case with CSS, I'm pretty sure they will either send the file to you or provide you with the path to the download.|||All the developper email contacts costs money for support. I called support and they gave me the same response : I need to give a credit card to the routing agent and the support tech will decide if it's free or not after we resolve the issue. I have sent an email through our partner account and am waiting for a response.
I noticed that a lot of people are looking for an answer on this topic.
|||Anyone has had success in obtaining the localization s80xLang.exe file?
What's the point of Microsoft for not making this file public (or better, include it on the original product CD)?
|||Hi Antonio,
I finally called microsoft with our partner ID because we have so many free calls per year we can use. After speaking to a SQL specialist, he sent me the file. They were supposed to make the file public on the web site (it's available for them internally) but not for the rest of us.
You can give them a call and they will send you the file. I was not charged for the support call since it's their mistake.
Good luck and take care
|||All right, I've got the localization file (thanks, Marco!).... only to discover that it doesn't work with SQLServer 2005!
The problem is:
1 - for the script to run it must have permission to update the system table sysmessage;
2 - up to SQL 2000 this was achieved by issuing "sp_configure 'allow updates',1" or starting SQL with the -m option;
3 - SQL 2005 explicity states that updates to system tables are not allowed and that "sp-configure allow updates" is outdated and won't work (http://msdn2.microsoft.com/en-us/library/ms144262.aspx). Running the script in single-user mode (starting SQL with the -m option) doesn't work either, since "updates to system tables are not allowed".
Any ideas or further help will be appreciated.
Regards,
Antonio Plais - Brazil
|||Further developments on localizing SQL 2005 messages:
1 - I'm developing an application using SQL Express 2005, and I'd like to show SQL messages in Brazilian Portuguese (one of the SQL 2005 supported languages);
2 - The solution pointed by Microsoft support doesn't work on SQL 2005;
3 - Localized Brazilian SQL Express 2005 Standard can be downloaded from Microsoft, while SQL Express 2005 Advanced is available only in English;
4 - Standard edition doesn't install full-text search; trying to install it from Advanced edition (after installing the Brazilian standard version) doesn't work;
5 - So, until Microsoft gently makes available a Brazilian Advanced version, I'll have to choose between localized messages or full-text search;
How hard Microsoft can make our lives!
Regards,
Antonio Plais - Brazil
localized messages for SQL SERVER 2000
I found a usefull link to my problem : http://support.microsoft.com/Default.aspx?id=277535
But I am unable to find the s80xLang.exe file anywhere. I have looked through all my DVDs and nothing.
Anyone know where to look or download it from ?
The KB article mentions that you need to contact Customer Support to be able to download this. That wording implies the file is not available for public download. If you use email support to open a case with CSS, I'm pretty sure they will either send the file to you or provide you with the path to the download.|||All the developper email contacts costs money for support. I called support and they gave me the same response : I need to give a credit card to the routing agent and the support tech will decide if it's free or not after we resolve the issue. I have sent an email through our partner account and am waiting for a response.
I noticed that a lot of people are looking for an answer on this topic.
|||Anyone has had success in obtaining the localization s80xLang.exe file?
What's the point of Microsoft for not making this file public (or better, include it on the original product CD)?
|||Hi Antonio,
I finally called microsoft with our partner ID because we have so many free calls per year we can use. After speaking to a SQL specialist, he sent me the file. They were supposed to make the file public on the web site (it's available for them internally) but not for the rest of us.
You can give them a call and they will send you the file. I was not charged for the support call since it's their mistake.
Good luck and take care
|||All right, I've got the localization file (thanks, Marco!).... only to discover that it doesn't work with SQLServer 2005!
The problem is:
1 - for the script to run it must have permission to update the system table sysmessage;
2 - up to SQL 2000 this was achieved by issuing "sp_configure 'allow updates',1" or starting SQL with the -m option;
3 - SQL 2005 explicity states that updates to system tables are not allowed and that "sp-configure allow updates" is outdated and won't work (http://msdn2.microsoft.com/en-us/library/ms144262.aspx). Running the script in single-user mode (starting SQL with the -m option) doesn't work either, since "updates to system tables are not allowed".
Any ideas or further help will be appreciated.
Regards,
Antonio Plais - Brazil
|||Further developments on localizing SQL 2005 messages:
1 - I'm developing an application using SQL Express 2005, and I'd like to show SQL messages in Brazilian Portuguese (one of the SQL 2005 supported languages);
2 - The solution pointed by Microsoft support doesn't work on SQL 2005;
3 - Localized Brazilian SQL Express 2005 Standard can be downloaded from Microsoft, while SQL Express 2005 Advanced is available only in English;
4 - Standard edition doesn't install full-text search; trying to install it from Advanced edition (after installing the Brazilian standard version) doesn't work;
5 - So, until Microsoft gently makes available a Brazilian Advanced version, I'll have to choose between localized messages or full-text search;
How hard Microsoft can make our lives!
Regards,
Antonio Plais - Brazil
Friday, February 24, 2012
Local connection failure: please help
I Have SQL Server 2000 local installed on a Windows XP SP2 machine,
Dutch version.
SQL Server first connected fine via the default settings 'Use Windows
Authentication'. I use it to test VB.Net practices, which went fine
for a while. I am busy preparing for the 70-310 exam.
Then I had problems with a VB.Net (a webservice) application
connecting to SQL server local. Via a newsgroup someone advised me to
change the login to 'Use SQL Server Authentication'. I used the
default password 'sa' and no password. I couldn't get any connection.
I tried with 'Administrator', but no connection.
When I tried to set it back to 'Use Windows Authentication' I get the
message Connection Failed, check SQL Server registration properties.
When I tried to open the properties of the local SQL Server (LOCAL)
(Windows NT) via the Enterprise Manager I get error 1069. I tried
several usernames, nothing helps. I didn't change the default, so it
should be 'sa' without a password?
Please help, I am stuck for days now.
RoyWell, the only solution was to re-install SQL Server and restore the
databases...
On Thu, 20 Jul 2006 14:16:09 +0200, RC <roycoumans@.hotmail.com> wrote:
>I Have SQL Server 2000 local installed on a Windows XP SP2 machine,
>Dutch version.
>SQL Server first connected fine via the default settings 'Use Windows
>Authentication'. I use it to test VB.Net practices, which went fine
>for a while. I am busy preparing for the 70-310 exam.
>Then I had problems with a VB.Net (a webservice) application
>connecting to SQL server local. Via a newsgroup someone advised me to
>change the login to 'Use SQL Server Authentication'. I used the
>default password 'sa' and no password. I couldn't get any connection.
>I tried with 'Administrator', but no connection.
>When I tried to set it back to 'Use Windows Authentication' I get the
>message Connection Failed, check SQL Server registration properties.
>When I tried to open the properties of the local SQL Server (LOCAL)
>(Windows NT) via the Enterprise Manager I get error 1069. I tried
>several usernames, nothing helps. I didn't change the default, so it
>should be 'sa' without a password?
>Please help, I am stuck for days now.
>Roy
Dutch version.
SQL Server first connected fine via the default settings 'Use Windows
Authentication'. I use it to test VB.Net practices, which went fine
for a while. I am busy preparing for the 70-310 exam.
Then I had problems with a VB.Net (a webservice) application
connecting to SQL server local. Via a newsgroup someone advised me to
change the login to 'Use SQL Server Authentication'. I used the
default password 'sa' and no password. I couldn't get any connection.
I tried with 'Administrator', but no connection.
When I tried to set it back to 'Use Windows Authentication' I get the
message Connection Failed, check SQL Server registration properties.
When I tried to open the properties of the local SQL Server (LOCAL)
(Windows NT) via the Enterprise Manager I get error 1069. I tried
several usernames, nothing helps. I didn't change the default, so it
should be 'sa' without a password?
Please help, I am stuck for days now.
RoyWell, the only solution was to re-install SQL Server and restore the
databases...
On Thu, 20 Jul 2006 14:16:09 +0200, RC <roycoumans@.hotmail.com> wrote:
>I Have SQL Server 2000 local installed on a Windows XP SP2 machine,
>Dutch version.
>SQL Server first connected fine via the default settings 'Use Windows
>Authentication'. I use it to test VB.Net practices, which went fine
>for a while. I am busy preparing for the 70-310 exam.
>Then I had problems with a VB.Net (a webservice) application
>connecting to SQL server local. Via a newsgroup someone advised me to
>change the login to 'Use SQL Server Authentication'. I used the
>default password 'sa' and no password. I couldn't get any connection.
>I tried with 'Administrator', but no connection.
>When I tried to set it back to 'Use Windows Authentication' I get the
>message Connection Failed, check SQL Server registration properties.
>When I tried to open the properties of the local SQL Server (LOCAL)
>(Windows NT) via the Enterprise Manager I get error 1069. I tried
>several usernames, nothing helps. I didn't change the default, so it
>should be 'sa' without a password?
>Please help, I am stuck for days now.
>Roy
Monday, February 20, 2012
Local administrators was removed from SQL server logins, SQL Agent is not running
Colleagues,
I am experiencing problems described in (MSSQL 2000):
http://support.microsoft.com/default.aspx?scid=kb;en-us;237604
My problem is, that I have no MSSQL account I can use to solve the problem,
I cannot access SQL Servers Logins.
What can I do? Could please somebody help me?
Best regards,
PeterSQL Server Agent has to have an Administrative account. I would recommend
that the agent account be a local administrator on the server with far
reaching access accross the network. The only suggestion that I have is to
try and locate someone who can grant you sa or a minimum Security Admin for
this instance. One thing you might try is to determine if the console account
for the server is a user in SQL. I have always used the console account and
password as the SA on that SQL instance as well as the account that the
agent/service uses which is I believe to be quite common. Might get lucky.
(i.e If there server console name is Server1 and the console login in
P@.ssw0rd, then use that to try and login through Query analyizer. If it lets
you in, try editing the instance registration in Enterprise Manager and see
what kind of access that account has.)
Good Luck.
"Peter Rus" wrote:
> Colleagues,
> I am experiencing problems described in (MSSQL 2000):
> http://support.microsoft.com/default.aspx?scid=kb;en-us;237604
> My problem is, that I have no MSSQL account I can use to solve the problem,
> I cannot access SQL Servers Logins.
> What can I do? Could please somebody help me?
> Best regards,
> Peter
>
>
I am experiencing problems described in (MSSQL 2000):
http://support.microsoft.com/default.aspx?scid=kb;en-us;237604
My problem is, that I have no MSSQL account I can use to solve the problem,
I cannot access SQL Servers Logins.
What can I do? Could please somebody help me?
Best regards,
PeterSQL Server Agent has to have an Administrative account. I would recommend
that the agent account be a local administrator on the server with far
reaching access accross the network. The only suggestion that I have is to
try and locate someone who can grant you sa or a minimum Security Admin for
this instance. One thing you might try is to determine if the console account
for the server is a user in SQL. I have always used the console account and
password as the SA on that SQL instance as well as the account that the
agent/service uses which is I believe to be quite common. Might get lucky.
(i.e If there server console name is Server1 and the console login in
P@.ssw0rd, then use that to try and login through Query analyizer. If it lets
you in, try editing the instance registration in Enterprise Manager and see
what kind of access that account has.)
Good Luck.
"Peter Rus" wrote:
> Colleagues,
> I am experiencing problems described in (MSSQL 2000):
> http://support.microsoft.com/default.aspx?scid=kb;en-us;237604
> My problem is, that I have no MSSQL account I can use to solve the problem,
> I cannot access SQL Servers Logins.
> What can I do? Could please somebody help me?
> Best regards,
> Peter
>
>
Subscribe to:
Posts (Atom)