Locking

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

Locking

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

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

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

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

Lock_Timeout - best practice?

Hi,
I'm occosianally having my main shop floor users 'hang'
for 30+ seconds and I can see that my app is timing out
somewhere SQL Server when it fires a new record insert
transaction that, in turn, fires a trigger which calls a
number of procs. This problem only happens two or three
times per day, for no particular reason and some days no
problems at all. All the the sql code in the chain of
events set Lock_Timeout to 200ms.
I would like to ask three questions:
1) Is it generally better to shorten a Lock_Timeout so as
to not hang around too long and restart the trans quickly
or extend it in the hope the trans will eventually fire
and therefore not suffer the workload caused by a Rollback.
This seems like a classic 'it depends' question - but are
there any general rules here?
2) I've always presumed that Lock_Timeouts are used each
time SQLServer wants to aquire a lock, ie if a proc needs
to aquire 10 locks then each attempt has its own
Lock_Timeout - is this correct?
3) Lock_Timeouts in no way set the overall time limit for
executing a complete transaction - is this correct?
TIA - PeterOn Wed, 23 Jul 2003 16:33:43 -0700, "Peter Jones"
<jonespm@.ozemail.com.au> wrote:
>I'm occosianally having my main shop floor users 'hang'
>for 30+ seconds and I can see that my app is timing out
>somewhere SQL Server when it fires a new record insert
>transaction that, in turn, fires a trigger which calls a
>number of procs. This problem only happens two or three
>times per day, for no particular reason and some days no
>problems at all. All the the sql code in the chain of
>events set Lock_Timeout to 200ms.
>I would like to ask three questions:
>1) Is it generally better to shorten a Lock_Timeout so as
>to not hang around too long and restart the trans quickly
>or extend it in the hope the trans will eventually fire
>and therefore not suffer the workload caused by a Rollback.
Shorten it if you want to pop up a message to the user, otherwise you
might even want to lengthen it, if you know that you get these
infrequent 30+ second hangs which are correct functioning.
>This seems like a classic 'it depends' question - but are
>there any general rules here?
>2) I've always presumed that Lock_Timeouts are used each
>time SQLServer wants to aquire a lock, ie if a proc needs
>to aquire 10 locks then each attempt has its own
>Lock_Timeout - is this correct?
AFAIK.
>3) Lock_Timeouts in no way set the overall time limit for
>executing a complete transaction - is this correct?
Yes that is correct. The only limit I know on transaction times is
connection timeout, and I'm not even certain how those interact.
Joshua Stern|||<snip>
> 2) I've always presumed that Lock_Timeouts are used each
> time SQLServer wants to aquire a lock, ie if a proc needs
> to aquire 10 locks then each attempt has its own
> Lock_Timeout - is this correct?
Yes, but the lock_timeout of the 10th lock is not relevant, because the
1st lock is always the first to time out.
> 3) Lock_Timeouts in no way set the overall time limit for
> executing a complete transaction - is this correct?
It depends on the lock type. In default isolation transaction level,
Shared locks can be released immediately after the Select statement is
finished. However, exclusive locks are essential for the transaction,
and will be held until the end of the transaction.
For example, if you have the following transaction:
BEGIN TRANSACTION
UPDATE MyTable1 SET Col1 = 1
UPDATE MyTable2 SET Col2 = 2
COMMIT TRANSACTION
Then the exclusive locks on MyTable1 will be held until the transaction
is committed. If the lock_timeout is set to 10 seconds, then the
transaction will fail if the total time of the two Updates exceeds these
10 seconds.
Hope this helps,
Gert-Jan|||Hi Gert-Jan,
Yes - this and Joshua's reply are very helpful. But they
raise a couple of issues I would like to clarrify:
2) Using your example in point 3 I would have expected the
Lock_Timeout value to be used when updating T1 then
another, independent Lock_Timeout, to be used it tries to
update T2. Your response to point 2 indicates that this is
not true - am I understanding you correctly?
3) Regarding point 3, this relates to point 2 I guess in
that it is completely contary to what I understood about
Lock_Timeouts. Your response seems to say that a
Lock_Timeout is the time SQL Server will 'hold' a lock
whereas it was my understanding it is how long it
will 'wait' for a blocked resourse to become unblocked.
Please clarrify that I have understood your response
correctly.
Cheers, Peter
>--Original Message--
><snip>
>> 2) I've always presumed that Lock_Timeouts are used each
>> time SQLServer wants to aquire a lock, ie if a proc
needs
>> to aquire 10 locks then each attempt has its own
>> Lock_Timeout - is this correct?
>Yes, but the lock_timeout of the 10th lock is not
relevant, because the
>1st lock is always the first to time out.
>> 3) Lock_Timeouts in no way set the overall time limit
for
>> executing a complete transaction - is this correct?
>It depends on the lock type. In default isolation
transaction level,
>Shared locks can be released immediately after the Select
statement is
>finished. However, exclusive locks are essential for the
transaction,
>and will be held until the end of the transaction.
>For example, if you have the following transaction:
>BEGIN TRANSACTION
>UPDATE MyTable1 SET Col1 = 1
>UPDATE MyTable2 SET Col2 = 2
>COMMIT TRANSACTION
>Then the exclusive locks on MyTable1 will be held until
the transaction
>is committed. If the lock_timeout is set to 10 seconds,
then the
>transaction will fail if the total time of the two
Updates exceeds these
>10 seconds.
>Hope this helps,
>Gert-Jan
>.
>|||Peter,
I have to appologize. It seems you are correct. The lock_timeout value
is only used when acquiring locks, and not for holding the locks. I
verified this with a simple test.
So if we go back to your original question 2:
>> 2) I've always presumed that Lock_Timeouts are used each
>> time SQLServer wants to aquire a lock, ie if a proc needs
>> to aquire 10 locks then each attempt has its own
>> Lock_Timeout - is this correct?
I tested this. I made a transaction that runs for approximately 25
seconds if there are no lock waits. Then - with another connection - I
locked a relevant row for 50 seconds. When I ran the transaction again
with a lock_timeout ot 51000 it completed successfully in 56 seconds.
IMO this proves that the lock_timeout is set for each individual lock
acquisition. (Otherwise, the transaction could not have finished in > 51
seconds).
Gert-Jan

Lock SQL table using asp.net

Hi,all:
This problem almost drives me crazy, hope I can get some hints from you guyz!!!
Ok, here is the situation:
I wanna only one users 2 modify the data(update) from my page each time, and if at the same time, there are some other users connecting my database through .aspx page, they can only browse the data until the first users finish updating.
It seems I need to implement locking the database, but I am not sure how I am gonna do that using asp.net!!!
Thanx in advance!This sort of locking is not a great idea. What happens if the first editing user goes to lunch, or looses his connection, or just looses interest?

Better, create a system that will test to ensure that an impending update is in fact updating the same data as it originally got to do the editing on.|||Hi,douglas:
Thanx a lot for ur reply! But I still have some question about this,
would you mind telling me that if the sql server has this kind of functionality?
And if it has, how about the time-out for this function?
Thanx in advance!|||the problem is, you will not have an open connection to the database between page posts. Locking is not viable for an asp.net app.|||Hi, Douglas:
Thanx for the reply, I have one more question :)
Is it possible that I can excecute the "Lock Table..." sql statement in asp.net?
if it's possible, is there any limit time for that "lock"?
Thanx a lot!
Cheers|||Have a look at "isolation levels" and, in you specific case "serializable". But you really need to understand the ramifications otherwise you'll bring the database and/or your application to its knees.

Lock row.

Hello everyone,
I have a web project where users access a aspx page to view information stored in an SQL database.
My client want that one user can access a row of information and see it, allother users shouldn't be able to view or update thesame row?
it means whenever a row of data is displayed by some user, this row should be locked even for beeing viewed by all other users, when this user close this page, this row will be available. ?I should do this in code behind or something in sql...
How can I do that?

It is not easy to do but the link below will take you in the right direction. Hope this helps.

http://www.sql-server-performance.com/lock_contention_tamed_article.asp

|||

Thanks for your reply,
All I want to do is: if a userA access a row, all other users can't access this row even for reading.
I read the article but I didn't understand how can I do this.
Any help...

|||

No, not really. You'd have to roll your own method of locking out views when someone is viewing the same record.

Really, it sounds like a bad design.

|||

Goodway:

Thanks for your reply,
All I want to do is: if a userA access a row, all other users can't access this row even for reading.
I read the article but I didn't understand how can I do this.
Any help...

(UPDATE Users WITH (ROWLOCK)
SET Username = 'fred' WHERE Username = 'foobar'

By specifically requesting row-level locks, these problems are avoided.)

I got this from that link but I have told you it is not easy to lock access because as relational algebra expert Chris Date puts it a SELECT returns a table so you could be locking a table instead of one row through lock escalation managed by SQL Server. Hope this helps.

|||

That Update doesn't do what he asked for.

A) The WITH ROWLOCK really doesn't help. Sure, it (might) help with reducing contention by holding less granular locks, but... It doesn't hold the lock for any longer than the update statement takes to complete. That is unless you wrap it in a highly isolated transaction.

B) Transactions complete when the execution of the transaction variable is destroyed or the connection object is closed. This normally happens when the execution of the page completes. In order to avoid that (If it's even possible), you would need to stuff the transaction into either a session or application object so it doesn't go out of scope with the page finishes executing.

The workaround for B causes problems C,D and E.

C) Because the transaction (and locks) are now being held for LONG periods of time, you'll start having all kinds of performance and timeout problems within the database.

D) Memory usage within the webserver will skyrocket because of all the transaction/connection objects being held across postbacks. This will lead to additional scaling issues. Possibly consuming enough memory to trigger the .NET framework to recycle the application. Make sure to add plenty of memory to the webserver, and set the recycling threshold very high to avoid the locks being lost randomly when the system recycles the process.

E) Abandoned sessions will cause the record to be locked indefinately. What if the user loses power, or closes the browser? The server will continue to lock that record forever (Or until the session dies after a very long period of activity if you've stored the information in session, or until you recycle the application if you stored them in application). Sure the user can then log back in to the website, and he'll have to wait for the record to unlock itself before even he can do anything with the record.

The idea is flawed. Don't lock the record. Remember the original values, and when you go to update the record make sure the record still looks exactly the same prior to actually doing the update. The sql wizard will do this for you if you tell it to compare all values.

|||

(My client want that one user can access a row of information and see it, all other users shouldn't be able to view or update the same row?)

I was replying his original post and I understand he is trying to lock the viewing of scalar value well that is not something you could do without problems with RDBMS(relational database management systems).

Lock requests/sec very high....

Monitoring our system during one of its busiest times (only several
"hundred" users) we noticed the lock requests/sec on our SQL server was
hitting as high as 60,000!!! Is this normal? Or is something in the
developers code gone really wrong? Even the average sustained is up in the
5-10000 range. The CPU is hitting the ceiling and we are getting timeouts.
I just don't believe that 500 users is too many for a system like this.
4 web servers hitting 2 asmx WebServices servers against a dual-Xeon SQL box
which is quite loaded. We are adding another SQL box just for fail-over but
this should be able to process this load I think."Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:%23JckWBtVGHA.4900@.TK2MSFTNGP12.phx.gbl...
> Monitoring our system during one of its busiest times (only several
> "hundred" users) we noticed the lock requests/sec on our SQL server was
> hitting as high as 60,000!!! Is this normal? Or is something in the
> developers code gone really wrong? Even the average sustained is up in
> the 5-10000 range. The CPU is hitting the ceiling and we are getting
> timeouts. I just don't believe that 500 users is too many for a system
> like this.
> 4 web servers hitting 2 asmx WebServices servers against a dual-Xeon SQL
> box which is quite loaded. We are adding another SQL box just for
> fail-over but this should be able to process this load I think.
>
"Lock requests/sec" is a general measure of SQL Server load. Almost every
request requires locks, so if the system is busy, you will have a lot of
locks.
You should probably analyze the database server workload to see what's
happening, and what queries are causing the most Logical IO and using the
most CPU. In SQL 2005 you can just look at the server summary reports. In
SQL 2000 you need to run profiler to capture the workload and then analyze
it.
David|||Hi Tim,
One important point is to know if non-technical user have access to lauch
queries or something like that.
--
Please post DDL, DCL and DML statements as well as any error message in
order to understand better your request. It''s hard to provide information
without seeing the code. location: Alicante (ES)
"David Browne" wrote:

> "Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
> news:%23JckWBtVGHA.4900@.TK2MSFTNGP12.phx.gbl...
> "Lock requests/sec" is a general measure of SQL Server load. Almost every
> request requires locks, so if the system is busy, you will have a lot of
> locks.
> You should probably analyze the database server workload to see what's
> happening, and what queries are causing the most Logical IO and using the
> most CPU. In SQL 2005 you can just look at the server summary reports. I
n
> SQL 2000 you need to run profiler to capture the workload and then analyze
> it.
> David
>
>|||It would be very hard to provide any definitive code. There isn't anything
pointing at one specific stored proc or anything...I'd have to dump all our
procedures out to you! This is the busiest time of the w for us. There
is alot of orders being placed so lots of inserts/updates are happening. I
guess I just figured our server could stand up to more users.
I did also notice that there were over 1500 processes running in SQL server,
most running the same thing. That sounds like a bit much since our
connection pools collectively max at 250.
"Enric" <vtam13@.terra.es.(donotspam)> wrote in message
news:D635498D-23EB-4D93-9A51-FB62D16088FE@.microsoft.com...
> Hi Tim,
> One important point is to know if non-technical user have access to lauch
> queries or something like that.
> --
> Please post DDL, DCL and DML statements as well as any error message in
> order to understand better your request. It''s hard to provide information
> without seeing the code. location: Alicante (ES)
>
> "David Browne" wrote:
>|||I don't have the link handy but yesterday I was told by an MVP that select
statements do not generate locks unless they are in a transaction. Is this
not so? Looking at the results of a profiler run I'd have to believe that
is false. I see reference to certain procedures afterwhich there are MANY
locks aquired/released. Seems maybe some of these select statements should
have NOLOCK added to them. Would this even make a difference?
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:OonWDXtVGHA.5332@.tk2msftngp13.phx.gbl...
> "Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
> news:%23JckWBtVGHA.4900@.TK2MSFTNGP12.phx.gbl...
> "Lock requests/sec" is a general measure of SQL Server load. Almost every
> request requires locks, so if the system is busy, you will have a lot of
> locks.
> You should probably analyze the database server workload to see what's
> happening, and what queries are causing the most Logical IO and using the
> most CPU. In SQL 2005 you can just look at the server summary reports.
> In SQL 2000 you need to run profiler to capture the workload and then
> analyze it.
> David
>|||"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:ul%23ve6yVGHA.4976@.TK2MSFTNGP11.phx.gbl...
>I don't have the link handy but yesterday I was told by an MVP that select
>statements do not generate locks unless they are in a transaction. Is this
>not so?
Under the default isolation level READ COMMITED, all SELECT's generate
shared locks. Those locks are not held across multiple statements without a
transaction, but they are acquired and released just the same.

> Looking at the results of a profiler run I'd have to believe that is
> false. I see reference to certain procedures afterwhich there are MANY
> locks aquired/released. Seems maybe some of these select statements
> should have NOLOCK added to them. Would this even make a difference?
> , , ,
Could, but there is no reason to think that you have a problem with locks.
The number of lock requests is probably a just a sign of a large workload.
David

lock out users and shrink database

I need a script or way to lock out users from a database
long enough to shrink/trucated the database....You can set the database to single user or restricted user using ALTER DATAB
ASE and use the ROLLBACK
option to kick out existing users.
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=...ls
erver
"jelliott@.alexlee.com" <anonymous@.discussions.microsoft.com> wrote in messag
e
news:9c2801c3eb21$4a0f7140$a001280a@.phx.gbl...

quote:

---

> I need a script or way to lock out users from a database
> long enough to shrink/trucated the database....

---

lock out users and shrink database

I need a script or way to lock out users from a database
long enough to shrink/trucated the database....You can set the database to single user or restricted user using ALTER DATABASE and use the ROLLBACK
option to kick out existing users.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"jelliott@.alexlee.com" <anonymous@.discussions.microsoft.com> wrote in message
news:9c2801c3eb21$4a0f7140$a001280a@.phx.gbl...
> I need a script or way to lock out users from a database
> long enough to shrink/trucated the database....

Lock out user for database structure update

Is there any way to disconnect active users from a live database and lock them out till the database strucure has been updated?

Something like this should work for you.

use <db>

go

--remove all active users from the database

Alter database <db> set SINGLE_USER with ROLLBACK IMMEDIATE

go

--make changes to the database structure on this connection

go

--let users back in

Alter database <db> set MULTI_USER

|||Thank you

LOCK DB WHEN INSERTING

Hello.

I need to insert some records to an accounting table and calculate the balance after that. Thus, other users can be trying to do the same. How to lock the db and make the other users wait until the right moment? I'm using SqlDataSource to do that.

Thanks.

You don't need to lock the db, just lock the database objects (accounting table in this case). You can use SqlTransaction to control the locks in your code, with setting the IsolationLevel according to your requirement. Keep in mind that you should commit or rollback the opened transaction after you finish/cancel using the database resource, otherwise it may give rise to database blocking/deadlock issues. For example:

connection.Open();

SqlCommand command = connection.CreateCommand();
SqlTransaction transaction;

transaction = connection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted,"SampleTransaction");

Here are some useful links:

SqlTransaction Class:

http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqltransaction.aspx

Isolation levels:

http://msdn2.microsoft.com/en-us/library/system.data.isolationlevel.aspx

|||

Good.

I'm doing some research about that.

Assuming I'm using sqldatasource, the code below should work?

sqlReceiver.InsertCommand =

"SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; " & insertCommand &"SET TRANSACTION ISOLATION LEVEL READ COMMITTED; "
sqlReceiver.Insert()

I couldn't find any property to use with sqldatasource for that purpose.

Thanks!

|||Good Your sql command should work. Unfortunately it seems there is no property to set the isolation level in SqlDataSource.|||

I believe you can have the sqldatasouce's insert command join a transaction in _Inserting, and commit it in _Inserted.

Or maybe not. You should be able to catch the insert attempt in _Inserting, create your own connection, start a transaction, steal the inserting's command object (With the parameters already set up for you), then execute it, commit the transaction, and set "e.cancel=true" to cancel the original inserting request.

Or, you can put the whole thing in a stored procedure, and call it from the sqldatasource, that works too.

Or you can just wrap your original Insert statement like you did (Just put it in the sqldatasource's insertcommand property along with your insert statement). That works as well.

|||

Good, thanks for the answers.

I was monitoring the commands via Sql Server Profiler. I thought I would be able to see theSET TRANSACTION ISOLATION LEVEL SERIALIZABLEcommand being executed there, but I couldn't. That made me think that the command was not being executed.

Now, I'm using something like that to set the isolation level:

(...)
transaction = connection.BeginTransaction(System.Data.IsolationLevel.Serializable)

Any additional comments?

Thanks.

|||

Hey,

about my problem, I still have it. Let me explain.

I'm doing several SELECTS, to get the balances from different accounts. So, until I finish that and insert the new records, the table would be really locked. I couldn't do that using a transaction level isolation. Well, I guess I can do, but that would make me to rewrite lots of code.

So, it's possible to lock the entire table with one command, and unlock only after another command?

Thanks.

|||

sqldatasource1.selectcommand="BEGIN TRANSACTION SELECT TOP 0 * FROM Table1 WITH (TABLOCKX,HOLDLOCK) More SQL Statements COMMIT TRANSACTION"

With that said, you are probably coding your T-SQL poorly, and could do what you want to do in one or two statements. There are also a lot better ways of handling the locking than this as well. This will cause concurrency issues, and you will probably start hitting deadlock issues if you use this technique very much.

Lock database to update

Hi,

I need to lock a database (prevent users from connecting) in order to
update it. I already know how to kick everyone out with their spid but
i can't figure how to prevent them from reconnecting.

Thanks !Set the datbase in single user mode, so no one can reconnect.

ALTER DATABASE myDB SET SINGLE_USER WITH ROLLBACK Immediate
Markus|||It works, thanks a lot !|||It works, thanks a lot !|||Kittikun (kittikun@.gmail.com) writes:
> It works, thanks a lot !

And when you are done

ALTER DATABASE db SET MULTI_USER

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thats good point Erland

Madhivanansql

lock database and shrink database log

I need a script or method to lock users out of a database,
shrink it/truncated it and then let them back in....You can try:
ALTER DATABASE SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE
This will set the database to dbo_useonly and roll back all current
transactions.
--
Carlos E. Rojas
SQL Server MVP
Co-Author SQL Server 2000 Programming by Example
"Jamie Elliott" <jelliott@.alexlee.com> wrote in message
news:966501c3eb21$82ec2cb0$a101280a@.phx.gbl...
> I need a script or method to lock users out of a database,
> shrink it/truncated it and then let them back in....|||Hi,
use the below steps in script
1. ALTER DATABASE PUBS SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE
2. If it full recovery model Execute , Backup Log command (To backup
transaction log)
3. Execute DBCC SHRINKFILE on Transaction log file
4. ALTER DATABASE PUBS SET MULTI_USER
Thanks
Hari
MCDBA
"Jamie Elliott" <jelliott@.alexlee.com> wrote in message
news:966501c3eb21$82ec2cb0$a101280a@.phx.gbl...
> I need a script or method to lock users out of a database,
> shrink it/truncated it and then let them back in....

lock database and shrink database log

I need a script or method to lock users out of a database,
shrink it/truncated it and then let them back in....You can try:
ALTER DATABASE SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE
This will set the database to dbo_useonly and roll back all current
transactions.
Carlos E. Rojas
SQL Server MVP
Co-Author SQL Server 2000 programming by Example
"Jamie Elliott" <jelliott@.alexlee.com> wrote in message
news:966501c3eb21$82ec2cb0$a101280a@.phx.gbl...

quote:

---

> I need a script or method to lock users out of a database,
> shrink it/truncated it and then let them back in....

---

|||Hi,
use the below steps in script
1. ALTER DATABASE PUBS SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE
2. If it full recovery model Execute , Backup Log command (To backup
transaction log)
3. Execute DBCC SHRINKFILE on Transaction log file
4. ALTER DATABASE PUBS SET MULTI_USER
Thanks
Hari
MCDBA
"Jamie Elliott" <jelliott@.alexlee.com> wrote in message
news:966501c3eb21$82ec2cb0$a101280a@.phx.gbl...

quote:

---

> I need a script or method to lock users out of a database,
> shrink it/truncated it and then let them back in....

---

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

Localization and BUILTIN groups

Is there a synonym for the group BUILTIN\Users which can be used for GRANT ... TO and sp_grantlogin/sp_grantdbaccess, but which will work on localized computers?

I have a number of automated unit tests I wish to run on two different computers. The process involves recreating a database if it does not exist and then granting access and privileges to the BUILTIN\Users group.

The problem is that one computer is installed with a Swedish Windows XP Professional (the users group is called BUILTIN\Anv?ndare) and the other is an English WinXP MCE (the group is called BUILTIN\Users) so I cannot easily script this.

An alternative is to be able to retrieve the respective name through a .NET class or the Windows API.

Is any of this possible?

Thanks,
Johan

synonyms can be used only to securables residing inside a schema not principals or users.

you can however rename a login to a more friendly name inside a datbase using sp_grant dbaccess

Examples

This example adds an account for the Windows NT user Corporate\GeorgeW to the current database and gives it the name Georgie.

EXEC sp_grantdbaccess 'Corporate\GeorgeW', 'Georgie'

|||So there's no way to refer to BUILTIN\Users without knowing what language OS the machine has installed?

This works on an English OS only:
execute sp_grantlogin [BUILTIN\Users];
execute sp_grantdbaccess [BUILTIN\Users];.. and this on a Swedish OS:
execute sp_grantlogin [BUILTIN\Anv?ndare];
execute sp_grantdbaccess [BUILTIN\Anv?ndare];|||

run both statement and

put it in a try... catch.. block...

i mean use error handling

|||Good thinking!
Thanks|||

Actually you don't need to use try..catch at all. All Windows operating systems have certain number of well-known groups / users. These are universal and the SIDs for those are also the same across machines/windows OSes. So in your case, just do the following:

declare @.builtin_admins nvarchar(128)

set @.builtin_admins = suser_sname(0x01020000000000052000000020020000)

exec sp_grantdbaccess @.builtin_admins

declare @.builtin_users nvarchar(128)

set @.builtin_users = suser_name(0x01020000000000052000000021020000)

exec sp_grantdbaccess @.builtin_users

The SID values are the well-known values that will not change and you can use that to lookup the name. This will not work for user-defined groups.

|||That does it for me.
Thanks a million.

local sql server not visible for 'Users' on the same PC

Users on the same PC who do not have admin rights cannot see or logon to the
sql server/msde that exists on the PC. Any user with admin rights can see
it, of course. I have waded thru lots of hits in my searches but none of
them describe this behavior and whether I can surmount this or not. Does
anyone have any suggestions?
Thanks,
Steve
I'm not sure what tool they are using to "see" SQL Server
but in terms of logging into the instance, they would need a
login set up in order to do this. By default, the windows
local admin group is added as a login on SQL Server. That's
how the admins are able to log into the instance.
-Sue
On Mon, 6 Dec 2004 11:09:04 -0800, "Steven.Dahlin"
<StevenDahlin@.discussions.microsoft.com> wrote:

>Users on the same PC who do not have admin rights cannot see or logon to the
>sql server/msde that exists on the PC. Any user with admin rights can see
>it, of course. I have waded thru lots of hits in my searches but none of
>them describe this behavior and whether I can surmount this or not. Does
>anyone have any suggestions?
>Thanks,
>Steve
|||The tool is Query Analyzer. As a another user I can run the tool and execute
the connect function. This will pull a list of all servers that can be
seen. The (local) server is not visible at all even though others on the
network are and the local is running.
Presumedly this accessing a broadcast feature of the servers. I do not know
why the local cannot be seen.
Steve
"Sue Hoegemeier" wrote:

> I'm not sure what tool they are using to "see" SQL Server
> but in terms of logging into the instance, they would need a
> login set up in order to do this. By default, the windows
> local admin group is added as a login on SQL Server. That's
> how the admins are able to log into the instance.
> -Sue
> On Mon, 6 Dec 2004 11:09:04 -0800, "Steven.Dahlin"
> <StevenDahlin@.discussions.microsoft.com> wrote:
>
>
|||The drop down is populated with instances you have connected
to - it's not based upon servers that are broadcasting.
The users should be able to type the server name or put in a
period (just "." without the quotes) or type in (local) to
connect to the local instance.
-Sue
On Fri, 10 Dec 2004 12:07:02 -0800, "Steven.Dahlin"
<StevenDahlin@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>The tool is Query Analyzer. As a another user I can run the tool and execute
>the connect function. This will pull a list of all servers that can be
>seen. The (local) server is not visible at all even though others on the
>network are and the local is running.
>Presumedly this accessing a broadcast feature of the servers. I do not know
>why the local cannot be seen.
>Steve
>"Sue Hoegemeier" wrote:

Local Groups and Access.

I'm trying to prototype some reports, and having difficulties granting access
to users, connecting to my LocalHost.
I work for a company that uses LDAP.
I have RS running on my LocalHost, and would like to grant access to users
to Report Manager so they can render reports.
In the Computer management > Local Users & Groups > Groups: I see Users,
Guests. Can I add these group(s) in the Report Manager using 'New Role
Assignment', and assign a Role, like Browser, then will a unknown user be
able to visit the site and render some reports.
Thanks,
rwiethornI'm getting closer, the user can see the reportmanger but cannot run a report.
Here is what I did:
I created a user on my local machine, call him Bob. I then created a group
on my local machine, called ReportViewers, and added Bob to the group. I then
added the group to ReportManager, gave it a Content Manager role, and for
'Configure site-wide security', System Role Assignments, assigned the group
as System Users.
However, they can not view the report. Ther error points to the Datasource.
The Datasource is supposed to pull a recordset from the AdventureWorks
catalog. It uses the SA account. When I look at the datasource in the
ReportManager, the credentials are saved (user name: sa and its password).
So why would that not return the results from the AdventureWorks database?
Do I neeed to add the local group also to the Database and catalog?
Do I need to add the IUSR_machine account to the database?
Do I need to add the ASPNET account to the group?
All help is appreciated,
thanks, confused,
rwiethorn
"rwiethorn" wrote:
> I'm trying to prototype some reports, and having difficulties granting access
> to users, connecting to my LocalHost.
> I work for a company that uses LDAP.
> I have RS running on my LocalHost, and would like to grant access to users
> to Report Manager so they can render reports.
> In the Computer management > Local Users & Groups > Groups: I see Users,
> Guests. Can I add these group(s) in the Report Manager using 'New Role
> Assignment', and assign a Role, like Browser, then will a unknown user be
> able to visit the site and render some reports.
> Thanks,
> rwiethorn

Local Application Launch

Hi,

Does anyone know if there is a way to launch a local application on a users machine from a report?

We have a bespoke thick client system running which accepts a command line execution i.e. c:\program\programx.exe ref=xyz. I would like to lauch this from a report if possible passing in the ref - has anyone done anything like this before as I presume we may need to add code to do this.

We could use client side script and wshost however IE doesnt like this unless its a trusted site so dont want to go down this route if possible although this may be the answer.

Thanks in advance for your help

Dan.

ok i've found a solution :-

As this is a local intranet scenario I have full access to the clients hence the registry. To make this work I can add the following to the registry on each pc :-

Add to a new key to HKEY_CLASSES_ROOT called 'myprog'
Add an empty string to this key called 'URL Protocol'
Add the sub-keys: \shell\open\command
Set the value of the default string inside the command key to: " c:\my prog\prog.exe %1"

on your report set your link to be
="myprog:" & Fields!fieldvalue

Thanks

Dan.

LoadRunner + SqlTrace Problem

Hi ,
I am having Evaluation version of LoadRunner. I create a
sceneiro for 10 virtual users. My requirement is i want to know the
database hits. (ie i need to see the Database hits for 10 Virual
users).Because for me in some cases dead lock are occured. So i want to
see the transactions. I open the Sql Profiler to see the transactions.
When i run the sceneiro and i open the SQL profiler nothing
will happend in the SQL Profiler. Any one can please explain how to
attach the profiler for the Virual users.
I want to generate a hit for my local server with 10 virtual
users. And also i want to see the Hits with the help of Profiler.
Any can help me regarding this..
Thanks in Advance..
Thanks & Regards
VeluHi
If you are running connection pooling or the same user login, then I don't
think it will be possible to distinguish which client is running the code
unless you do something like adding a comment to the command string. This
would mean a change in the application.
If you do differentiate connections to different logins then you would need
to change the loadrunner script to read the user details on start up.
HTH
John
"velus123@.yahoo.com" wrote:
>
> Hi ,
> I am having Evaluation version of LoadRunner. I create a
> sceneiro for 10 virtual users. My requirement is i want to know the
> database hits. (ie i need to see the Database hits for 10 Virual
> users).Because for me in some cases dead lock are occured. So i want to
> see the transactions. I open the Sql Profiler to see the transactions.
>
> When i run the sceneiro and i open the SQL profiler nothing
> will happend in the SQL Profiler. Any one can please explain how to
> attach the profiler for the Virual users.
> I want to generate a hit for my local server with 10 virtual
> users. And also i want to see the Hits with the help of Profiler.
> Any can help me regarding this..
>
> Thanks in Advance..
>
> Thanks & Regards
> Velu
>

LoadRunner + SqlTrace Problem

Hi ,
I am having Evaluation version of LoadRunner. I create a
sceneiro for 10 virtual users. My requirement is i want to know the
database hits. (ie i need to see the Database hits for 10 Virual
users).Because for me in some cases dead lock are occured. So i want to
see the transactions. I open the Sql Profiler to see the transactions.
When i run the sceneiro and i open the SQL profiler nothing
will happend in the SQL Profiler. Any one can please explain how to
attach the profiler for the Virual users.
I want to generate a hit for my local server with 10 virtual
users. And also i want to see the Hits with the help of Profiler.
Any can help me regarding this..
Thanks in Advance..
Thanks & Regards
VeluHi
If you are running connection pooling or the same user login, then I don't
think it will be possible to distinguish which client is running the code
unless you do something like adding a comment to the command string. This
would mean a change in the application.
If you do differentiate connections to different logins then you would need
to change the loadrunner script to read the user details on start up.
HTH
John
"velus123@.yahoo.com" wrote:

>
> Hi ,
> I am having Evaluation version of LoadRunner. I create a
> sceneiro for 10 virtual users. My requirement is i want to know the
> database hits. (ie i need to see the Database hits for 10 Virual
> users).Because for me in some cases dead lock are occured. So i want to
> see the transactions. I open the Sql Profiler to see the transactions.
>
> When i run the sceneiro and i open the SQL profiler nothing
> will happend in the SQL Profiler. Any one can please explain how to
> attach the profiler for the Virual users.
> I want to generate a hit for my local server with 10 virtual
> users. And also i want to see the Hits with the help of Profiler.
> Any can help me regarding this..
>
> Thanks in Advance..
>
> Thanks & Regards
> Velu
>