Lock Type

Hello there,

Example

I pass this statement to the database:

SELECT FunctionName,FunctionDescription FROM Functions WHERE FunctionID = 21

Nothing special here.

Now i check in the Enterprise Manager in the Managment->Current Activity->Locks/Process ID Map and see this for my process

Object: LISPROD
Lock Type: DB
Mode: S
Status: Grant
Owner Sess
Index:
Resource:

My question now, is the LockType. Isn't this a bit too much. A full database lock while the query is only on one table.
All locks in the database seem to have this behaviour.

Cheers Erik.The lock type u see in
Enterprise Manager in the
Managment->Current Activity->Locks/Process ID

is DB...means database level locking.

Its not bacause u have fired a query, but Quey analyzer locks it as soon as u select a database form the drop down combo...

When we generally fire a query DB lavel locking is not used but Table level locking is used.

u can change it to page lavel or row level locking if required...but that approach has its own implications.

Naveen Mehta.

Locate SQL Server info

Hello, I'm looking for info on how/where to find the following info about
our SQL Server:
- SQL Server version type (enterprise, standard, etc.)
- SQL Server character set
- SQL Server sort order
- SQL Server files location
- SQL Server master database location
- SQL Server tempdb data location
- SQL Server tempdb log location
The reason I am looking for this is that I have adopted an SQL server for
SMS, and I am needing to collect all pertinent info and settings for
disaster recovery. TIA.
--
Hermes> - SQL Server version type (enterprise, standard, etc.)
select @.@.version
> - SQL Server character set
> - SQL Server sort order
sp_helpsort
> - SQL Server files location
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Setup
> - SQL Server master database location
> - SQL Server tempdb data location
> - SQL Server tempdb log location
sp_helpdb your_database
"hermes" <hermes@.messenger.com> wrote in message
news:epFYIwqsDHA.2360@.TK2MSFTNGP10.phx.gbl...
> Hello, I'm looking for info on how/where to find the following info about
> our SQL Server:
> - SQL Server version type (enterprise, standard, etc.)
> - SQL Server character set
> - SQL Server sort order
> - SQL Server files location
> - SQL Server master database location
> - SQL Server tempdb data location
> - SQL Server tempdb log location
> The reason I am looking for this is that I have adopted an SQL server for
> SMS, and I am needing to collect all pertinent info and settings for
> disaster recovery. TIA.
> --
> Hermes
>|||In SQL 2000, you can also SELECT * from
master..sysaltfiles to get the locations of all your
data/log files.
Linchi
>--Original Message--
>> - SQL Server version type (enterprise, standard, etc.)
>select @.@.version
>> - SQL Server character set
>> - SQL Server sort order
>sp_helpsort
>> - SQL Server files location
>HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Setup
>> - SQL Server master database location
>> - SQL Server tempdb data location
>> - SQL Server tempdb log location
>sp_helpdb your_database
>
>"hermes" <hermes@.messenger.com> wrote in message
>news:epFYIwqsDHA.2360@.TK2MSFTNGP10.phx.gbl...
>> Hello, I'm looking for info on how/where to find the
following info about
>> our SQL Server:
>> - SQL Server version type (enterprise, standard, etc.)
>> - SQL Server character set
>> - SQL Server sort order
>> - SQL Server files location
>> - SQL Server master database location
>> - SQL Server tempdb data location
>> - SQL Server tempdb log location
>> The reason I am looking for this is that I have adopted
an SQL server for
>> SMS, and I am needing to collect all pertinent info and
settings for
>> disaster recovery. TIA.
>> --
>> Hermes
>>
>
>.
>|||While listening to the voices in their head blather on endlessly about
inspecific gibberish, Linchi Shea typed:
:: In SQL 2000, you can also SELECT * from
:: master..sysaltfiles to get the locations of all your
:: data/log files.
::
:: Linchi
::
::: --Original Message--
:::: - SQL Server version type (enterprise, standard, etc.) select
:::: @.@.version
:::: - SQL Server character set
:::: - SQL Server sort order
::: sp_helpsort
:::: - SQL Server files location
::: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Setup
:::: - SQL Server master database location
:::: - SQL Server tempdb data location
:::: - SQL Server tempdb log location
::: sp_helpdb your_database
:::
:::
::: "hermes" <hermes@.messenger.com> wrote in message
::: news:epFYIwqsDHA.2360@.TK2MSFTNGP10.phx.gbl...
:::: Hello, I'm looking for info on how/where to find the following
:::: info about our SQL Server:
:::: - SQL Server version type (enterprise, standard, etc.)
:::: - SQL Server character set
:::: - SQL Server sort order
:::: - SQL Server files location
:::: - SQL Server master database location
:::: - SQL Server tempdb data location
:::: - SQL Server tempdb log location
::::
:::: The reason I am looking for this is that I have adopted an SQL
:::: server for SMS, and I am needing to collect all pertinent info and
:::: settings for disaster recovery. TIA.
::::
:::: --
:::: Hermes
::::
::::
:::
:::
::: .
Thank you so much for the quick replies! Unfortunately I am pretty much
clueless when it comes to SQL server, and I have no knowledge of running
commands like this ( I am the pc tech and new sms admin). From all of the
info you have posted, I only know how to get the info from the registry.
Could you possibly give me more info on how to run these commands such as
which application would I go into, the SQL Enterprise Manager? Also, in the
registry location, is it the SQLPath entry? Thanks again.
--
Hermes|||Hi Hermes
When someone just suggests a command to run, the tool is usually the Query
Analyzer. This is the tool you use to have your SQL Server directly execute
SQL statements.
So you could enter
SELECT * FROM master..sysaltfiles
<now press the green arrow button on the toolbar to execute this query>
or sp_helpdb 'your database name'
<press green arrow>
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"hermes" <hermes@.messenger.com> wrote in message
news:erhxTzrsDHA.2248@.TK2MSFTNGP09.phx.gbl...
> While listening to the voices in their head blather on endlessly about
> inspecific gibberish, Linchi Shea typed:
> :: In SQL 2000, you can also SELECT * from
> :: master..sysaltfiles to get the locations of all your
> :: data/log files.
> ::
> :: Linchi
> ::
> ::: --Original Message--
> :::: - SQL Server version type (enterprise, standard, etc.) select
> :::: @.@.version
> :::: - SQL Server character set
> :::: - SQL Server sort order
> ::: sp_helpsort
> :::: - SQL Server files location
> ::: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Setup
> :::: - SQL Server master database location
> :::: - SQL Server tempdb data location
> :::: - SQL Server tempdb log location
> ::: sp_helpdb your_database
> :::
> :::
> ::: "hermes" <hermes@.messenger.com> wrote in message
> ::: news:epFYIwqsDHA.2360@.TK2MSFTNGP10.phx.gbl...
> :::: Hello, I'm looking for info on how/where to find the following
> :::: info about our SQL Server:
> :::: - SQL Server version type (enterprise, standard, etc.)
> :::: - SQL Server character set
> :::: - SQL Server sort order
> :::: - SQL Server files location
> :::: - SQL Server master database location
> :::: - SQL Server tempdb data location
> :::: - SQL Server tempdb log location
> ::::
> :::: The reason I am looking for this is that I have adopted an SQL
> :::: server for SMS, and I am needing to collect all pertinent info and
> :::: settings for disaster recovery. TIA.
> ::::
> :::: --
> :::: Hermes
> ::::
> ::::
> :::
> :::
> ::: .
>
> Thank you so much for the quick replies! Unfortunately I am pretty much
> clueless when it comes to SQL server, and I have no knowledge of running
> commands like this ( I am the pc tech and new sms admin). From all of the
> info you have posted, I only know how to get the info from the registry.
> Could you possibly give me more info on how to run these commands such as
> which application would I go into, the SQL Enterprise Manager? Also, in
the
> registry location, is it the SQLPath entry? Thanks again.
> --
> Hermes
>|||While listening to the voices in their head blather on endlessly about
inspecific gibberish, Kalen Delaney typed:
:: Hi Hermes
::
:: When someone just suggests a command to run, the tool is usually the
:: Query Analyzer. This is the tool you use to have your SQL Server
:: directly execute SQL statements.
::
:: So you could enter
::
:: SELECT * FROM master..sysaltfiles
:: <now press the green arrow button on the toolbar to execute this
:: query>
::
:: or sp_helpdb 'your database name'
:: <press green arrow>
::
:: --
:: HTH
:: --
:: Kalen Delaney
:: SQL Server MVP
:: www.SolidQualityLearning.com
::
::
:: "hermes" <hermes@.messenger.com> wrote in message
:: news:erhxTzrsDHA.2248@.TK2MSFTNGP09.phx.gbl...
::: While listening to the voices in their head blather on endlessly
::: about inspecific gibberish, Linchi Shea typed:
::::: In SQL 2000, you can also SELECT * from
::::: master..sysaltfiles to get the locations of all your
::::: data/log files.
:::::
::::: Linchi
:::::
:::::: --Original Message--
::::::: - SQL Server version type (enterprise, standard, etc.) select
::::::: @.@.version
::::::: - SQL Server character set
::::::: - SQL Server sort order
:::::: sp_helpsort
::::::: - SQL Server files location
:::::: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Setup
::::::: - SQL Server master database location
::::::: - SQL Server tempdb data location
::::::: - SQL Server tempdb log location
:::::: sp_helpdb your_database
::::::
::::::
:::::: "hermes" <hermes@.messenger.com> wrote in message
:::::: news:epFYIwqsDHA.2360@.TK2MSFTNGP10.phx.gbl...
::::::: Hello, I'm looking for info on how/where to find the following
::::::: info about our SQL Server:
::::::: - SQL Server version type (enterprise, standard, etc.)
::::::: - SQL Server character set
::::::: - SQL Server sort order
::::::: - SQL Server files location
::::::: - SQL Server master database location
::::::: - SQL Server tempdb data location
::::::: - SQL Server tempdb log location
:::::::
::::::: The reason I am looking for this is that I have adopted an SQL
::::::: server for SMS, and I am needing to collect all pertinent info
::::::: and settings for disaster recovery. TIA.
:::::::
::::::: --
::::::: Hermes
:::::::
:::::::
::::::
::::::
:::::: .
:::
:::
::: Thank you so much for the quick replies! Unfortunately I am pretty
::: much clueless when it comes to SQL server, and I have no knowledge
::: of running commands like this ( I am the pc tech and new sms
::: admin). From all of the info you have posted, I only know how to
::: get the info from the registry. Could you possibly give me more
::: info on how to run these commands such as which application would I
::: go into, the SQL Enterprise Manager? Also, in the registry
::: location, is it the SQLPath entry? Thanks again.
:::
::: --
::: Hermes
Kalen and everyone, you have been incredibly helpful, thanks! :) I have all
of the info I need now except the character set. Anyone out there who knows
how to find out which character set my SQL Server is using? Thanks again.
--
Hermes|||SELECT SERVERPROPERTY('collation')
will give you the default for the SQL Server, but databases can have a
different collation than the server default.
For a particular database:
SELECT DatabasePropertyEx('database_name','Collation')
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"hermes" <hermes@.messenger.com> wrote in message
news:OABB0$ssDHA.1884@.TK2MSFTNGP10.phx.gbl...
> While listening to the voices in their head blather on endlessly about
> inspecific gibberish, Kalen Delaney typed:
> :: Hi Hermes
> ::
> :: When someone just suggests a command to run, the tool is usually the
> :: Query Analyzer. This is the tool you use to have your SQL Server
> :: directly execute SQL statements.
> ::
> :: So you could enter
> ::
> :: SELECT * FROM master..sysaltfiles
> :: <now press the green arrow button on the toolbar to execute this
> :: query>
> ::
> :: or sp_helpdb 'your database name'
> :: <press green arrow>
> ::
> :: --
> :: HTH
> :: --
> :: Kalen Delaney
> :: SQL Server MVP
> :: www.SolidQualityLearning.com
> ::
> ::
> :: "hermes" <hermes@.messenger.com> wrote in message
> :: news:erhxTzrsDHA.2248@.TK2MSFTNGP09.phx.gbl...
> ::: While listening to the voices in their head blather on endlessly
> ::: about inspecific gibberish, Linchi Shea typed:
> ::::: In SQL 2000, you can also SELECT * from
> ::::: master..sysaltfiles to get the locations of all your
> ::::: data/log files.
> :::::
> ::::: Linchi
> :::::
> :::::: --Original Message--
> ::::::: - SQL Server version type (enterprise, standard, etc.) select
> ::::::: @.@.version
> ::::::: - SQL Server character set
> ::::::: - SQL Server sort order
> :::::: sp_helpsort
> ::::::: - SQL Server files location
> :::::: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Setup
> ::::::: - SQL Server master database location
> ::::::: - SQL Server tempdb data location
> ::::::: - SQL Server tempdb log location
> :::::: sp_helpdb your_database
> ::::::
> ::::::
> :::::: "hermes" <hermes@.messenger.com> wrote in message
> :::::: news:epFYIwqsDHA.2360@.TK2MSFTNGP10.phx.gbl...
> ::::::: Hello, I'm looking for info on how/where to find the following
> ::::::: info about our SQL Server:
> ::::::: - SQL Server version type (enterprise, standard, etc.)
> ::::::: - SQL Server character set
> ::::::: - SQL Server sort order
> ::::::: - SQL Server files location
> ::::::: - SQL Server master database location
> ::::::: - SQL Server tempdb data location
> ::::::: - SQL Server tempdb log location
> :::::::
> ::::::: The reason I am looking for this is that I have adopted an SQL
> ::::::: server for SMS, and I am needing to collect all pertinent info
> ::::::: and settings for disaster recovery. TIA.
> :::::::
> ::::::: --
> ::::::: Hermes
> :::::::
> :::::::
> ::::::
> ::::::
> :::::: .
> :::
> :::
> ::: Thank you so much for the quick replies! Unfortunately I am pretty
> ::: much clueless when it comes to SQL server, and I have no knowledge
> ::: of running commands like this ( I am the pc tech and new sms
> ::: admin). From all of the info you have posted, I only know how to
> ::: get the info from the registry. Could you possibly give me more
> ::: info on how to run these commands such as which application would I
> ::: go into, the SQL Enterprise Manager? Also, in the registry
> ::: location, is it the SQLPath entry? Thanks again.
> :::
> ::: --
> ::: Hermes
>
> Kalen and everyone, you have been incredibly helpful, thanks! :) I have
all
> of the info I need now except the character set. Anyone out there who
knows
> how to find out which character set my SQL Server is using? Thanks again.
> --
> Hermes
>

localization and sqlserver

Hi,

I have a simple 'user' table and an ASP form that connects to it. I
want users of all languages to be able to type in their registration
info, in their own language, store it that way, then have another page
that displays it.

Are there any specific settings I need in SQL Server to handle this?

ThanksSorry, I forgot to mention I'm using SQL Server 2000, Windows 2000
server and ASP Classic.

On Tue, 10 Aug 2004 19:25:34 GMT, Justin
<ng@.NO_SPAMmaritimeNO_SPAMsource.ca> wrote:

>Hi,
>I have a simple 'user' table and an ASP form that connects to it. I
>want users of all languages to be able to type in their registration
>info, in their own language, store it that way, then have another page
>that displays it.
>Are there any specific settings I need in SQL Server to handle this?
>Thanks|||Justin (ng@.NO_SPAMmaritimeNO_SPAMsource.ca) writes:
> I have a simple 'user' table and an ASP form that connects to it. I
> want users of all languages to be able to type in their registration
> info, in their own language, store it that way, then have another page
> that displays it.
> Are there any specific settings I need in SQL Server to handle this?

There are ways to set user languages in SQL Server, yes, but I am not
sure that this is what you are after. What happens when you set the language
in SQL Server is that it controls how date literals are interpreted, the
output from the function datename(), and it may also affect the text of
error messages. There are probably a few more things, but nothing terribly
exciting. And the format of dates is best handled client-side anyway.

Maybe you could clarify what you are looking for?

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On Tue, 10 Aug 2004 21:45:58 +0000 (UTC), Erland Sommarskog
<esquel@.sommarskog.se> wrote:

>Justin (ng@.NO_SPAMmaritimeNO_SPAMsource.ca) writes:
>> I have a simple 'user' table and an ASP form that connects to it. I
>> want users of all languages to be able to type in their registration
>> info, in their own language, store it that way, then have another page
>> that displays it.
>>
>> Are there any specific settings I need in SQL Server to handle this?
>There are ways to set user languages in SQL Server, yes, but I am not
>sure that this is what you are after. What happens when you set the language
>in SQL Server is that it controls how date literals are interpreted, the
>output from the function datename(), and it may also affect the text of
>error messages. There are probably a few more things, but nothing terribly
>exciting. And the format of dates is best handled client-side anyway.
>Maybe you could clarify what you are looking for?

I'm investigating strategies for internationalization of an asp-based
website.

So basically as long as the sqlserver supports unicode I can stuff any
kind of characters in there I want to, but the only difference is when
using SQL Servers functions it has to know what locale it's dealing
with?|||Justin (ng@.NO_SPAMmaritimeNO_SPAMsource.ca) writes:
> I'm investigating strategies for internationalization of an asp-based
> website.
> So basically as long as the sqlserver supports unicode I can stuff any
> kind of characters in there I want to, but the only difference is when
> using SQL Servers functions it has to know what locale it's dealing
> with?

Of course the full story is not that simple. Generally, I would to as much
of the localization client-side, but there are of course situations when
the DB engine gets involved. And you may find these too complex to actually
resolve.

All character columns in SQL Server has a collation, but obviously one
collation that fits one user, does not fit another.

The most obvious case is sorting. While you can sort client side, it's
probably more performant to do it on the server. If you are sending down
SQL statements from the server, you can tack on a COLLATE clause:

SELECT *
FROM tbl
ORDER BY textcol COLLATE Finnish_Swedish_CI_AI

If you use stored procedures, you will have to resort to dynamic SQL, in
which case the point with the procedures are lost to a great deal.

When it comes to searching it becomes more delicate. Say that a user
is looking for a person named Wallenberg, and enters Vallenberg. In
most collations you would not get a hit, but in Finnish_Swedish_CI_AI
you would, since W is just a variant of V in Swedish. You can address
this with the COLLATE clause, but this will lead to indexes being
useless, and can have serious performance consequences. I would say
that in this case you will have to settle with Latin1_General_CI_AI
or what you choose.

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

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