Locking

Hi all,
SQL server doesnt provide any kind of configuration to
control granularity of locking on tables. As per books
online it is managed dynamically.
I have a major problem with my SQL Server, during
execution of one SP, on one table with 400+ rows it
acquires 600,000 locks. My profiler shows
whooping 'Lock:Acquired' on this particular table.
Can somebody please throw some light on this? Why SQL
server doesn't promote it to single table level lock? Is
there any bug in SQL?
thanks very much
Himanshu JaniYou probably want to keep working on why this happens in the first place (although there's not much
to go on for us), but here are a couple of options for immediate fix:
Specify lock hint in the query (TABLOCK, for instance).
Configure the index used to not allow row locks (sp_indexoption).
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Himanshu Jani" <anonymous@.discussions.microsoft.com> wrote in message
news:2c84301c393cd$c746cab0$a601280a@.phx.gbl...
> Hi all,
> SQL server doesnt provide any kind of configuration to
> control granularity of locking on tables. As per books
> online it is managed dynamically.
> I have a major problem with my SQL Server, during
> execution of one SP, on one table with 400+ rows it
> acquires 600,000 locks. My profiler shows
> whooping 'Lock:Acquired' on this particular table.
> Can somebody please throw some light on this? Why SQL
> server doesn't promote it to single table level lock? Is
> there any bug in SQL?
> thanks very much
> Himanshu Jani

Locked out of Report Manager on my own machine!

I must have inadvertently "touched" something in my configuration, but I
don't know what, or how to fix it. Here's what's happening:
1) Running Windows XP Professional, ISS, SQL Server 2000, Reporting Services
2000 SP2. All are installed on my laptop and have been running normally for
over a year.
2) I am logged in to my machine as Administrator.
3) When I try to open Report Manager via http://localhost/Reports, I get the
prompt
"Enter username and password for "" at http://localhost".
I enter "Administrator", then the password as usual.
4) Normally, Report Manager then opens up. However, now, I can't get past
this dialog! It immediately gives me the same prompts again, and again, and
again...
5) I have tried every user and password on this machine. I have reset the
Admin password and tried again. I have rebooted many times.
6) The IIS Security settings are the same everywhere: Integrated Windows
Authentication, NO anonymous access. This is set:
* At the Web Sites level
* At the Default Web Site level
* At the Reports virtual directory
* At the ReportServer virtual directory
===================
Hope you can help! I am completely locked out. (How can IIS not allow THE
ADMINISTRATOR access?)
Thanks,
C17Problem solved -- it's something to do with the HTTP Keep-Alive property.
In IIS, if you go to the Default Web Site, and un-check the "HTTP
Keep-Alive" property, you will experience the bizarre problem of being
locked out of your own machine, even as Administrator.
If you check the property, all is well.
--C17

local database "XXYYZZ" is not defined in configuration

I'm quite addicted to Patterns-&-Practices Enterprise.Library.Data module, but I can't get it to access a SQL Server Express Database. I've tried several different .CONFIG files and a unch of different settings and I keep getting:

The local database "XXYYZZ" is not defined in configuration.

I'm using Visual Studio 2005 C# in a WinForms application.

Can Microsoft.Practices.EnterpriseLibrary.Data access SQL Server Express Database?

<configuration>

<configSections>

<section name="dataConfiguration" type="Microsoft.Practices.EnterpriseLibrary.Data.Configuration.DatabaseSettings, Microsoft.Practices.EnterpriseLibrary.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=null" />

</configSections>

<dataConfiguration defaultDatabase="Connection String" />

<connectionStrings>

<add name="myDb" connectionString="Database=Database;Server=(local)\SQLEXPRESS;AttachDbFilename=C:\myDir\myDBname.mdf;Integrated Security=True;User Instance=True"

providerName="System.Data.SqlClient" />

</connectionStrings>

</configuration>

- and it's use

Database db = DatabaseFactory.CreateDatabase("myDb");
DbCommand dbC = db.GetStoredProcCommand("mySPname");
db.AddInParameter(dbC, "MyParameterName", DbType.Int32, MyIntVal);
DataSet ds = db.ExecuteDataSet(dbC);

Needs this stuff too ...

using System.Data;

using System.Data.SqlTypes;

using System.Data.SqlClient;

using Microsoft.Practices.EnterpriseLibrary.Data;

using Microsoft.Practices.EnterpriseLibrary.Data.Sql;

using System.Data.Common;

|||As the Elib cannot find the "mydb" entry, it seems that the ELIB is requesting the wrong (in terms of your thinking) .config file. See if you are pointing to the right config file.

Jens K. Suessmeyer.

http://www.sqlserver2005.de