how to create row locks

Hello,
We would like to use the following concept :
There are two processes.
First one creates the transacton, makes update on one row via the primary
key
(by the command UPDATE not by UPDATE CURSOR).
We need to achieve
that in the duration this transaction
there is not possible to execute UPDATE for this row by the second process,
but we must have the possibility to make UPDATE on the another rows.
Our problem is
that by using the above construction
the whole table is locked for the second process.
Thanks
misoMiso
By deafult SQL Server creates a lock on row level.
BEGIN TRAN
UPDATE Table SET col1 =@.par1,col2=@.par2 WHERE PK=something
COMMIT TRAN
"Miso" <valent@.spirit.sk> wrote in message
news:eQl5Hw08GHA.2128@.TK2MSFTNGP05.phx.gbl...
> Hello,
> We would like to use the following concept :
> There are two processes.
> First one creates the transacton, makes update on one row via the primary
> key
> (by the command UPDATE not by UPDATE CURSOR).
> We need to achieve
> that in the duration this transaction
> there is not possible to execute UPDATE for this row by the second
> process,
> but we must have the possibility to make UPDATE on the another rows.
> Our problem is
> that by using the above construction
> the whole table is locked for the second process.
>
> Thanks
> miso
>|||Miso,
the update on the single row within the transaction will have an exclusive
lock on that row, but as it's a single row, I don't see how lock escalation
has increased it to a table lock (an intent exclusive is expected). How many
rows are in the table? Are there other transactions operating here? What is
returned if you run sp_lock (for the relevant objid)? Also, what happens if
you use the ROWLOCK hint?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

how to create row locks

Hello,
We would like to use the following concept :
There are two processes.
First one creates the transacton, makes update on one row via the primary
key
(by the command UPDATE not by UPDATE CURSOR).
We need to achieve
that in the duration this transaction
there is not possible to execute UPDATE for this row by the second process,
but we must have the possibility to make UPDATE on the another rows.
Our problem is
that by using the above construction
the whole table is locked for the second process.
Thanks
miso
Miso
By deafult SQL Server creates a lock on row level.
BEGIN TRAN
UPDATE Table SET col1 =@.par1,col2=@.par2 WHERE PK=something
COMMIT TRAN
"Miso" <valent@.spirit.sk> wrote in message
news:eQl5Hw08GHA.2128@.TK2MSFTNGP05.phx.gbl...
> Hello,
> We would like to use the following concept :
> There are two processes.
> First one creates the transacton, makes update on one row via the primary
> key
> (by the command UPDATE not by UPDATE CURSOR).
> We need to achieve
> that in the duration this transaction
> there is not possible to execute UPDATE for this row by the second
> process,
> but we must have the possibility to make UPDATE on the another rows.
> Our problem is
> that by using the above construction
> the whole table is locked for the second process.
>
> Thanks
> miso
>
|||Miso,
the update on the single row within the transaction will have an exclusive
lock on that row, but as it's a single row, I don't see how lock escalation
has increased it to a table lock (an intent exclusive is expected). How many
rows are in the table? Are there other transactions operating here? What is
returned if you run sp_lock (for the relevant objid)? Also, what happens if
you use the ROWLOCK hint?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

how to create row locks

Hello,
We would like to use the following concept :
There are two processes.
First one creates the transacton, makes update on one row via the primary
key
(by the command UPDATE not by UPDATE CURSOR).
We need to achieve
that in the duration this transaction
there is not possible to execute UPDATE for this row by the second process,
but we must have the possibility to make UPDATE on the another rows.
Our problem is
that by using the above construction
the whole table is locked for the second process.
Thanks
misoMiso
By deafult SQL Server creates a lock on row level.
BEGIN TRAN
UPDATE Table SET col1 =@.par1,col2=@.par2 WHERE PK=something
COMMIT TRAN
"Miso" <valent@.spirit.sk> wrote in message
news:eQl5Hw08GHA.2128@.TK2MSFTNGP05.phx.gbl...
> Hello,
> We would like to use the following concept :
> There are two processes.
> First one creates the transacton, makes update on one row via the primary
> key
> (by the command UPDATE not by UPDATE CURSOR).
> We need to achieve
> that in the duration this transaction
> there is not possible to execute UPDATE for this row by the second
> process,
> but we must have the possibility to make UPDATE on the another rows.
> Our problem is
> that by using the above construction
> the whole table is locked for the second process.
>
> Thanks
> miso
>|||Miso,
the update on the single row within the transaction will have an exclusive
lock on that row, but as it's a single row, I don't see how lock escalation
has increased it to a table lock (an intent exclusive is expected). How many
rows are in the table? Are there other transactions operating here? What is
returned if you run sp_lock (for the relevant objid)? Also, what happens if
you use the ROWLOCK hint?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

how to create array column and how to retrive in sqlserver

hi

i am using database sqlserver,
i am searching for varray concept like in oracle to store multiple values in a single column(as array column) like that shell i do in sql server

1.how to create array column in a table using sqlserver

if possible how can i use select query for that

there is no array concept in sql server. you can however store the values as a concatenated string with a delimiter and use some custom function to parse through the string to split them up.|||

Try the link below for samples using the string functions in SQL Server and Oracle PL/SQL is closer to C++ than T-SQL. I would also look at Ken Henderson books at my local bookstore run a search for String functions in the BOL(books online). Hope this helps.

http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm

|||Why do you want to store multiple values in a single column? Thishas some pretty huge implications from a relational modelingstandpoint, not to mention the fact that it's going to kill performanceif you ever need to query the thing. If you can post someinformation about what business problem you're trying to solve, I'mcertian we can help you find a better solution.

|||

Sorry I forgot I have a link with SQL Server Arrays. Try the link below. Hope this helps.

http://www.sommarskog.se/arrays-in-sql.html

How to create a new schema

How can I create a new schema inside a database from Managment studio express.

Also can someone explain me the concept of schema clearly. If database is like a house, then is schema like a room in it ?

if you use the metaphor House, you could say that the house is the database, the appartments are the schemas, though they can be owned by someone on have kinds of objects in them (tables, views --> rooms). If the owner of the appartment or the actual person who rent the appartment wants to quit the contract, you will first have to find another owner / contract partner vefore moving out of the appartment.

http://msdn2.microsoft.com/en-us/library/ms190387.aspx

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

Thanks. Have another question. When we create a new schema is some part of database memory allocated to it and hence having too many schemas... leads to memory fragmentation problems and hence not efficient ?

HOw to create a CUBE

Hi,

I am quite familiar with Pivot Tables and understand the concept of dimensional analysis. I want to take my understanding to the next level and want to learn how to make cubes (and analyze them) using SQL server 2000.

I'll appreciate if you can suggest a URL and ideally a book or two which will help me in this regard.

Many thankxs u in advance.

-JAnalysis Services step-by-step by Reed Jacobson is real easy to follow. If you are wanting more on DW theory, data warehouse lifecycle toolkit by Ralph Kimball.

Also, don't forget that BOL has a lot of informaiton on it about analysis services.

HTH|||Originally posted by rhigdon
Analysis Services step-by-step by Reed Jacobson is real easy to follow. If you are wanting more on DW theory, data warehouse lifecycle toolkit by Ralph Kimball.

Also, don't forget that BOL has a lot of informaiton on it about analysis services.

HTH

Many thanks. I'll try to get these books. Can you in the meanwhile recommend and URL also?|||For DW theory and design this is good

http://intelligententerprise.com/ports/search_dw_fund.shtml

To download BOL, go here

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp?