Showing posts with label locked. Show all posts

Friday, March 30, 2012

Locking

I have set the Isolation Level to Read Uncommitted but the tables are still
locked after i have run a select in Query Analyzer, can anyone explain why
this is 'Can you provide more details? Hoe did you set the isolation level. Can you reproduce this from two
connection in query analyzer?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Clive Godden" <clive.godden@.peopleworld.co.uk> wrote in message
news:eGpQzej$EHA.2572@.tk2msftngp13.phx.gbl...
>I have set the Isolation Level to Read Uncommitted but the tables are still
> locked after i have run a select in Query Analyzer, can anyone explain why
> this is '
>|||All i did was run this
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
go
begin transaction
select * from gipsilicence
commit transaction
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||That doesn't help us reproduce the problem as this is only one connection, and I assume that the one
connection doesn't block itself. Are you saying that this connection is blocking another connection,
or is it blocked by another connection? NOLOCK should not acquire shared locks and should not honor
exclusive locks, hence I'm asking as much details as possible, preferably a repro.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Clive Godden" <clive.godden@.peopleworld.co.uk> wrote in message
news:eqoQm3j$EHA.2136@.TK2MSFTNGP10.phx.gbl...
> All i did was run this
> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> go
> begin transaction
> select * from gipsilicence
> commit transaction
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!sql

Locked Stored Procedures

I am reasonably new to this so bear with me.

I have a copy of a SQL Server 2000 Database, and would like to see what the stored procedures are doing (as I think it is wrong).

in Server Management Studio the stored procedures have a lock symbol on them and when I try and view them I get the message:

TITLE: Microsoft SQL Server Management Studio

Script failed for StoredProcedure 'dbo.sp_procedure'. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Script+StoredProcedure&LinkId=20476

ADDITIONAL INFORMATION:

Property TextHeader is not available for StoredProcedure '[dbo].[sp_procedureprocedure]'. This property may not exist for this object, or may not be retrievable due to insufficient access rights. The text is encrypted. (Microsoft.SqlServer.Smo)

This was developed by someone else, but I need to see it.

My question is, is there any way i can see the text of this stored procedure?

Thanks

Will

The stored procedure code has been encrypted. Apparently, whoever wrote the code didn't want others to read the code.

I suggest that you contact the person ( or vendor ) that supplied the stored procedure code and request a copy of the code.

locked out of sql server management studio

Hi everyone, I hope someone can help me here!

A friend of mine set up a Windows 2003 Server for me in my home with Sql Server 2005. I'm developing a website for prospective employers to see the kinds of things I can program since I have no field-related work experience. I have a cool site up, but I can't showcase the any features that use sql server (which obviously, are the kinds of things employers want to see) because the login and password my friend remembers produces this error:

Cannot connect to server.

Additional information:

An error has occurred while establishing a connection to the server. When connecting to sql server 2005, this failure may be caused by the fact that under the default settings sql server doesn't allow remote connections,(provider: Named Pipes Provider, error: 40-could not open a connection to sql server)(Microsoft sql server, Error:2)

My friend doesn't know how to fix this. Is there a way to create another login with admin rights in the command prompt so that I can login & get my database functionality going?

Thanks in advance!

Can you connect to sqlserver at all on the server itself?
Do you know which version of sql2k5 you're running? Do "select @.@.version" to find out - this assumes you can connect to the server somehow.
If you're using sqlexpress version, the ability to connect remotely is off by default. You will need to use SQL Surface Area tool to change the setting.|||

Hey OJ, thanks for trying to get me out of this pickle!

No, I don't "think" I can connect to sql server. Don't I need to add the databases my app is using to sql server in order for my app to work? As I said, this is my first time moving an app to a server.

The result I get when running "select @.@.version" is SqlServer 2000! Dang, I don't even have it running on my own laptop in the right instance of sql server! I'm glad you had me check that out. I'll move everything over to 2005. I think I have the developer version. Whatever version of 2005 I have on my laptop, I should have on the server, which I can't reach at all. I just checked the version of 2005 I have, & it's 2005 v.9.00.1399.06.

I have the Surface Area tool, but I've never used it. What do I do?

Thanks, OJ. I appreciate your time.

Kimmy

|||Kimmy,

Before you get yourself in big trouble with licensing, I suggest you take a closer look at your server and make sure it does not run a developer edition - dev edition means for development only and should not be used for any type of production.

To determine whatever version you have on the server, we will have to fix your connection problem. ;-) Let see if I can walk you through this.

1. Logon to server locally - either via remote desktop (mstsc /console) or at the console itself.
2. Execute "net start" to get a list of started services. Your sqlserver would be one if it's started. If it's not started yet, run "services.msc" to bring up services mgr and start it. You should find you sqlserver under "SQL Server (instance_name)".
3. Now startup sql surface area tool. I.e. Start -> Programs -> sql2k5 -> config tools -> sqlSAC (i.e. "C:\Program Files\Microsoft SQL Server\90\Shared\SqlSAC.exe")
4. Select "SAC for services and connections"
5. Pick your sql instance and ensure Remote Connection is set to local & remote.
6. Restart your sqlserver service to take effect.
7. Now you should be able to connect to your sql instance remote from another computer.
8. If your NT login is part of the sql server's local admins, you should be able to run

sqlcmd -S"your_server_name\instance_name" -E -Q"select @.@.version"|||

Hey OJ,

Thanks for your last post...I've been interviewing with recruiters and haven't had time to check what you last said, but I'm on it now.

Mannnnnn, the liscencing issue? What version do I need to use on a production server?

I will respond tomorrow with what I find out about your other comments from your last post.......and THANKS A BUNCH for sticking with me through this!

Kimmy

|||Hi everyone,

I got the same error when I connect to SQL Server 2005 that exists on windows 2003 server from client using windows 2000.
Can you help me with any idea?

Thanks,|||

nyaung wrote:
Hi everyone,
I got the same error when I connect to SQL Server 2005 that exists on windows 2003 server from client using windows 2000.
Can you help me with any idea?
Thanks,

please post the full error here. there can be many causes for not able to connect.

locked out of sql server management studio

Hi everyone, I hope someone can help me here!

Cannot connect to server.

Additional information:

My friend doesn't know how to fix this. Is there a way to create another login with admin rights in the command prompt so that I can login & get my database functionality going?

Thanks in advance!

Hey OJ, thanks for trying to get me out of this pickle!

I have the Surface Area tool, but I've never used it. What do I do?

Thanks, OJ. I appreciate your time.

Kimmy

|||Kimmy,

To determine whatever version you have on the server, we will have to fix your connection problem. ;-) Let see if I can walk you through this.

sqlcmd -S"your_server_name\instance_name" -E -Q"select @.@.version"|||

Hey OJ,

Thanks for your last post...I've been interviewing with recruiters and haven't had time to check what you last said, but I'm on it now.

Mannnnnn, the liscencing issue? What version do I need to use on a production server?

I will respond tomorrow with what I find out about your other comments from your last post.......and THANKS A BUNCH for sticking with me through this!

Kimmy

|||Hi everyone,

I got the same error when I connect to SQL Server 2005 that exists on windows 2003 server from client using windows 2000.
Can you help me with any idea?

Thanks,|||

nyaung wrote:
Hi everyone,
I got the same error when I connect to SQL Server 2005 that exists on windows 2003 server from client using windows 2000.
Can you help me with any idea?
Thanks,

please post the full error here. there can be many causes for not able to connect.

sql

locked out of sql server management studio

Hi everyone, I hope someone can help me here!

Cannot connect to server.

Additional information:

My friend doesn't know how to fix this. Is there a way to create another login with admin rights in the command prompt so that I can login & get my database functionality going?

Thanks in advance,

Monster

Have you enabled remote connections for the SQL2005 instance with SQL Surface Area Configuration? You can also try to remove User Instance="true" attribute from the connection string if you don't need to generate user instnace.|||

No, because I don't know how to do those things. I'm used to just developing on my laptop, where everything always works great! Could you tell me how to do the first suggestion? And regarding the second suggestion, I think there's something there I need to understand when you transfer an application to a server. Does that mean that somehow the database is included in the app & doesn't need to interact with sql server at all? Obviously, I'm very new to this.

Thank you so much for your suggestions! I'll await your reply!

|||OK, for suggestion 1 you can click start->All Programs->Microsoft SQL Server2005->Configuration Tools-> SQL Server Surface Area Configuration->click?"Surface Area Configuration for Services and Connections"->expand the SQL instance in the tree on the left->Database Engine->Remote Connections->Using both TCP/IP and Named Pipes. For details, you can refer to:
How to configure SQL Server 2005 to allow remote connections

For the User Instance attribute,?if?it?is?true?it?indicates to redirect the connection from the default SQL Server Express instance to a runtime-initiated instance running under the account of the caller. you can refer to:

http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstring(VS.80).aspx

You can use database file included in your application, but that also requires an accessible SQL2005/Express instance to host the database file: we accomplish this by using the AttachDBFileName attribute in the connection string.

I recommend you take a look at this article which may help to understand the way VS2005 using .mdf file:

http://weblogs.asp.net/scottgu/archive/2005/08/25/423703.aspx

|||

Iori_Jay,

THANK YOU FOR YOUR RESPONSE...I haven't been able to check out what you've said, but I will do that tomorrow & get back to you.

Thank you for sticking with me through this!

Monster

|||It's my pleasure Smile

What's going on now?|||

Well, I'm finally into mgmt studio and my databases are there. It turned out that in order for sql server to work on the test server it needed more ram, which was why I was locked out (it had nothing to do with the login, apparently).

But now, of course, there are other issues Crying

|||

Whoops...must have pressed something and it posted before I was done...

I have 2 db's there, 1 for a fictitious bookstore, which works absolutely fine. The other is for a fictitious event booking site, which is accessed both traditionally via the db & then the app is rewritten to extract the data access to a web service. Neither of these work. I also have another simple web service that loads Northwind customer info, which doesn't work either.

Here are the connect strings (remember I'm a newbie):

Hey...just had a brainstorm...I'm going to try it & get back...

|||

Ok, brainstorm didn't work.

Here are the relevant sections of web.config:

appSettings> //the brainstorm had to do with changing the localhost below to the name of my website, but that didn't help

addkey="NorthwindCustomers.CustomerService"value="http://localhost:2285/KSMCADNET/WebServicesRemoting/CustomerService.asmx"/>

addkey="EventBookingWebService.EventBookingService"value="http://localhost:2285/KSMCADNET/EventBookingWebService/EventBookingClient/EventBookingService.asmx"/>

appSettings>

connectionStrings>

addname="pubsConnectionString1"connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\pubs.mdf;Integrated Security=True;User Instance=True"providerName="System.Data.SqlClient"/>

addname="KimsBookstoreConnectionString"connectionString="Data Source=notebook\SQL2K5DEV;Initial Catalog=KimsBookstore;Integrated Security=True"providerName="System.Data.SqlClient"/>

addname="KimsBookstoreConnectionString2"connectionString="Data Source=notebook\SQL2K5DEV;Initial Catalog=KimsBookstore;Integrated Security=True"providerName="System.Data.SqlClient"/>

addname="EventBookingConnectionString"connectionString="Data Source=notebook\SQL2K5DEV;Integrated Security=True;Initial Catalog=EventBooking"providerName="System.Data.SqlClient"/>

addname="EventBookingConnectionStringServer"connectionString="Data Source=sqlsvr;Initial Catalog=EventBooking;Integrated Security=True"providerName="System.Data.SqlClient"/>

addname="KimsBookstoreConnectionStringServer"connectionString="Data Source=sqlsvr;Initial Catalog=KimsBookstore;Integrated Security=True"providerName="System.Data.SqlClient"/>

addname="KimsBookstoreConnectionString2Server"connectionString="Data Source=sqlsvr;Initial Catalog=KimsBookstore;Integrated Security=True"providerName="System.Data.SqlClient"/>

addname="NorthwindConnectionString"connectionString="Data Source=notebook\SQL2K5DEV;Initial Catalog=Northwind;Integrated Security=True"providerName="System.Data.SqlClient" />

addname="NorthwindConnectionStringServer"connectionString="Data Source=sqlsvr;Initial Catalog=Northwind;Integrated Security=True"providerName="System.Data.SqlClient" />

connectionStrings> //sqlsvr is my test server, that my friend built, the notebook source is my dev laptop where everything works fine

The error I get when I try to connect to the data driven EventBooking site is this (so odd, because the bookstore connects fine, although 2 images consistently err, where the others work fine):

Server Error in '/' Application.

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 28 - Server doesn't support requested protocol)

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 28 - Server doesn't support requested protocol)

Source Error:

Line 37: da = new SqlDataAdapter();Line 38: da.SelectCommand = cmd;Line 39: da.Fill(ds, "Rooms");Line 40: Line 41: ddlRooms.DataSource = ds;

Source File:e:\1ocmcad\EventBookingWebService\EventBookingClient\EventBookingClient.aspx.cs Line:39

Stack Trace:

[SqlException (0x80131904): An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 28 - Server doesn't support requested protocol)] System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +734867 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188 System.Data.SqlClient.TdsParser.Connect(Boolean& useFailoverPartner, Boolean& failoverDemandDone, String host, String failoverPartner, String protocol, SqlInternalConnectionTds connHandler, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject, Boolean aliasLookup) +820 System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +628 System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +170 System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +359 System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +28 System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +424 System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +66 System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +496 System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +82 System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105 System.Data.SqlClient.SqlConnection.Open() +111 System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +121 System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +137 System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +83 EventBookingWebService_EventBookingClient_EventBookingClient.Page_Load(Object sender, EventArgs e) in e:\1ocmcad\EventBookingWebService\EventBookingClient\EventBookingClient.aspx.cs:39 System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) +15 System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +34 System.Web.UI.Control.OnLoad(EventArgs e) +99 System.Web.UI.Control.LoadRecursive() +47 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1061

Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.210

The error I get when I try to run the same app written with the web service is this:

Server Error in '/' Application.

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 28 - Server doesn't support requested protocol)

Source Error:

Line 29: Calendar1.TodaysDate = DateTime.Now;Line 30: Calendar1.TodayDayStyle.BackColor = Color.Tan;Line 31: DataBind();Line 32: Line 33:

Source File:e:\1ocmcad\EventBookingWebService\EventBookingClient\EventBookingWebServiceClient.aspx.cs Line:31

Stack Trace:

[SqlException (0x80131904): An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 28 - Server doesn't support requested protocol)] System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +734867 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188 System.Data.SqlClient.TdsParser.Connect(Boolean& useFailoverPartner, Boolean& failoverDemandDone, String host, String failoverPartner, String protocol, SqlInternalConnectionTds connHandler, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject, Boolean aliasLookup) +820 System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +628 System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +170 System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +359 System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +28 System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +424 System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +66 System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +496 System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +82 System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105 System.Data.SqlClient.SqlConnection.Open() +111 System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +121 System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +137 System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +83 System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +1770 System.Web.UI.WebControls.ListControl.OnDataBinding(EventArgs e) +92 System.Web.UI.WebControls.ListControl.PerformSelect() +31 System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +70 System.Web.UI.Control.DataBindChildren() +216 System.Web.UI.Control.DataBind(Boolean raiseOnDataBinding) +216 System.Web.UI.Control.DataBind() +12 System.Web.UI.Control.DataBindChildren() +216 System.Web.UI.Control.DataBind(Boolean raiseOnDataBinding) +216 System.Web.UI.Control.DataBind() +12 EventBookingWebService_EventBookingClient_EventBookingWebServiceClient.Page_Load(Object sender, EventArgs e) in e:\1ocmcad\EventBookingWebService\EventBookingClient\EventBookingWebServiceClient.aspx.cs:31 System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) +15 System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +34 System.Web.UI.Control.OnLoad(EventArgs e) +99 System.Web.UI.Control.LoadRecursive() +47 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1061

Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.210

And the error I get when trying to run the Northwind web service is this:

Server Error in '/' Application.

No connection could be made because the target machine actively refused it

Exception Details:System.Net.Sockets.SocketException: No connection could be made because the target machine actively refused it

Source Error:

Line 45: [System.Web.Services.Protocols.SoapDocumentMethodAttribute("http://www.1ocmcad.net/GetCustomers", RequestNamespace="http://www.1ocmcad.net", ResponseNamespace="http://www.1ocmcad.net", Use=System.Web.Services.Description.SoapBindingUse.Literal, ParameterStyle=System.Web.Services.Protocols.SoapParameterStyle.Wrapped)]Line 46: public System.Data.DataSet GetCustomers() {Line 47: object[] results = this.Invoke("GetCustomers", new object[0]);Line 48: return ((System.Data.DataSet)(results[0]));Line 49: }

Source File:c:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files\root\fdd4322a\74322047\App_WebReferences.vmho5c06.1.cs Line:47

Stack Trace:

[SocketException (0x274d): No connection could be made because the target machine actively refused it] System.Net.Sockets.Socket.DoConnect(EndPoint endPointSnapshot, SocketAddress socketAddress) +1002146 System.Net.Sockets.Socket.InternalConnect(EndPoint remoteEP) +33 System.Net.ServicePoint.ConnectSocketInternal(Boolean connectFailure, Socket s4, Socket s6, Socket& socket, IPAddress& address, ConnectSocketState state, IAsyncResult asyncResult, Int32 timeout, Exception& exception) +431[WebException: Unable to connect to the remote server] System.Net.HttpWebRequest.GetRequestStream() +1504525 System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters) +103 NorthwindCustomers.CustomerService.GetCustomers() in c:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files\root\fdd4322a\74322047\App_WebReferences.vmho5c06.1.cs:47 WebServicesRemoting_ConsumeService.btnGetCustomers_Click(Object sender, EventArgs e) in e:\1ocmcad\WebServicesRemoting\ConsumeService.aspx.cs:22 System.Web.UI.WebControls.Button.OnClick(EventArgs e) +105 System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +107 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5102

Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.210

I've enabled remote connections via named pipes & tcp/ip for the dev version of sql server 2005 as you suggested & also enabled sql server browser like the article you referred me to suggested.

I'm going to work on this all weekend, so there won't be any gaps in communication...I hope to hear from you soon!

Kim

|||

Ok, this is for all you guys who are looking for answers. I spent days on this problem (I won't say how many!) and it was all because of my lack of Sql Server knowledge. The initial problem "locked out of sql server mgmt studio" was an issue of my server not having sufficient memory. After that, I kept getting a myriad of login errors, which changed every time I tried another way to connect to the databases.

What the issue was, was that I didn't have permissions & users & logins set up right in management studio. I ended up creating a new user for each database under each databases' Security > Users folder of "BUILTIN\Administrators" and set role memberships so that that account could access the db's.

I hope this helps someone who is as frustrated as I was!

Thanks for all your help, Iori_Jay.

Monster

|||I'm glad to see you've solved the issue, really great job for a person not familiar with SQL Smile

|||

Thank you for the compliment! Hopefully one day in the future I will be answering other people's questions rather than asking a ton of them!!!

And on that issue, could you please take a look at my other question?http://forums.asp.net/thread/1471646.aspx

Thanks so much!

Monster

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

Locked out of Enterprise Manager

Hi all

First post - please be gentle :)

I have had a search of the forum and couldn't find a solution to this one.

On a clean install of SQL Server 2000 on a W2003 server we setup our database as normal, selected appropriate priveldges for everyone and all was well.

Until an adminsitraor accidently turned builtin\administors account to DENY access. We are now locked out of EM and I can't think of a way back in.

We can't access the database via the sa account as it is set to Windows Autentication, and we can't re-register the database for the same reason.

No other user has sufficent priveldges to update the master db or change authenticaion, or even log into EM for that matter.

I think we are snookered, but any thoughts are welcomed.

Cheers

K2Interesting.
There's also one more user that has full admin rights in SQL instance, that is local admin account. i.e., machinename\administrator
Since this user also is part of admin group, try changing it's member group to POWER USERS.
Start-->Run--> control userpasswords2
Select the ADMINISTRATOR user , choose properties and under the Group Membership change it to Power Users.
Now, it won't inherit the BUILTIN/ADMINSTRATOR group , but would still be existing in sqlserver logins as admin user.
Try connecting to sql under this local admin account!

Hopefully, it would work.
AND, please don't forget to create one more ADMIN user on the machine, before changing localadmin user to POWERUSERS :)

If you couldn't connect, you might need to rebuild the master db using rebuild utility, that will leave ur instance intact but rebuild the master db and you will need to reattach ur user dbs after wards.
c:\Program Files\Microsoft SQL Server\80\Tools\Binn\rebuildm.exe|||My first thought would be to restore the master database from before the incident. There is a small chance you can reset the logon mode to mixed authentication, though. Did you ever set a password for sa?|||Interesting.
There's also one more user that has full admin rights in SQL instance, that is local admin account. i.e., machinename\administrator
Since this user also is part of admin group, try changing it's member group to POWER USERS.
Start-->Run--> control userpasswords2
Select the ADMINISTRATOR user , choose properties and under the Group Membership change it to Power Users.
Now, it won't inherit the BUILTIN/ADMINSTRATOR group , but would still be existing in sqlserver logins as admin user.

Hi

I think this would have been a better angle to take than what we did in the end! We didn't think of that one.

In the end we did a rebuild of the master database and this worked just fine.

We did try to change the authentication mode (we had set the sa password) but it wouldn't have it.

Thanks for your help guys!

K

Locked out again?

I have SQL Server 2005 Developer edition installed. I use Windows
Authentication and the user is an admin on this box. For whatever
reason, I was recently locked out of SQL Server. So I uninstalled and
reinstalled then I could get back in. Then once again, I became locked
out. Same procedure and I can get back in. I downloaded and installed
SP1 for SQL Server 2005 last night. Now I'm locked out again. "sa"
never works with a blank or any other password.
Any ideas why this keeps happening and how I can gain access?
Thanks,
BrettI'm running this on Vista. I always turn off account access control.
After installing SP1 for SQL Server and rebooting, access control was
reenabled. This prevented me from logging into SQL Server. I've once
again turned access control. Now I can log in.
Brett|||Make sure that you have configured it for mixed authentication if you want
to use sa. Also, make sure that if you are using Windows authentication
with SSMS that you have that option selected when you login.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
"brett" <account@.cygen.com> wrote in message
news:1167499761.263497.179960@.h40g2000cwb.googlegroups.com...
I have SQL Server 2005 Developer edition installed. I use Windows
Authentication and the user is an admin on this box. For whatever
reason, I was recently locked out of SQL Server. So I uninstalled and
reinstalled then I could get back in. Then once again, I became locked
out. Same procedure and I can get back in. I downloaded and installed
SP1 for SQL Server 2005 last night. Now I'm locked out again. "sa"
never works with a blank or any other password.
Any ideas why this keeps happening and how I can gain access?
Thanks,
Brett|||Well did you give the sa acoount a password?
You sure that youre windows account is a member of the local administrator
group?
Greetz,
HDD
"brett" wrote:

> I have SQL Server 2005 Developer edition installed. I use Windows
> Authentication and the user is an admin on this box. For whatever
> reason, I was recently locked out of SQL Server. So I uninstalled and
> reinstalled then I could get back in. Then once again, I became locked
> out. Same procedure and I can get back in. I downloaded and installed
> SP1 for SQL Server 2005 last night. Now I'm locked out again. "sa"
> never works with a blank or any other password.
> Any ideas why this keeps happening and how I can gain access?
> Thanks,
> Brett
>|||Hate_orphaned_users wrote:
> Well did you give the sa acoount a password?
This was never an option during set.

> You sure that youre windows account is a member of the local administrato
r
> group?
Yep|||Tom Moreau wrote:
> Make sure that you have configured it for mixed authentication if you want
> to use sa.
Can it be set to mixed after installation?
Thanks,
Brett|||In Vista by default you are not an admin and as such not part of the
sysadmins group. You need to make yourself an admin, create an account for
yourself in SQL Server and then you can log in without being an admin. This
wasn't an issue with XP because you were generally always an admin on the
box when you logged in. Vista changed this default because running as an
admin made viruses much more nasty. I think that SP2 is supposed to create
this user for you automatically but I don't know if that part of it works in
the current CTP.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"brett" <account@.cygen.com> wrote in message
news:1167501829.564730.89250@.n51g2000cwc.googlegroups.com...
> I'm running this on Vista. I always turn off account access control.
> After installing SP1 for SQL Server and rebooting, access control was
> reenabled. This prevented me from logging into SQL Server. I've once
> again turned access control. Now I can log in.
> Brett
>|||Yes.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
"brett" <account@.cygen.com> wrote in message
news:1167505428.307381.19420@.i12g2000cwa.googlegroups.com...
Tom Moreau wrote:
> Make sure that you have configured it for mixed authentication if you want
> to use sa.
Can it be set to mixed after installation?
Thanks,
Brett

Locked out

I am using the desktop sqlexpress 2005 and I was experimenting with the account setting and I accidentally disabled my access. I am using windows authentication and there is no password, just the windows login name.

How can I reactivate the login for this connection. Any help is greatly appreciated.Log on as administrator, then try.sql

locked out

after installing sql server 2005, i messed up the login settings.
I know can't login.
i didn't really understand what i was doing
short of uninstalling, reinstalling, do i have any options?
also do you know of a tutorial for installing (setting windows vs sql
authentication) and then setting up user permissions?
thanks, JeffThere is usually a default account in the sa role for the local admin
account. Login with that and you should be able to do what you need to do.
Andrew J. Kelly SQL MVP
"jmoeller18" <jmoeller18@.gmail.com> wrote in message
news:1140392437.685886.73280@.g43g2000cwa.googlegroups.com...
> after installing sql server 2005, i messed up the login settings.
> I know can't login.
> i didn't really understand what i was doing
> short of uninstalling, reinstalling, do i have any options?
> also do you know of a tutorial for installing (setting windows vs sql
> authentication) and then setting up user permissions?
> thanks, Jeff
>

locked out

after installing sql server 2005, i messed up the login settings.
I know can't login.
i didn't really understand what i was doing
short of uninstalling, reinstalling, do i have any options?
also do you know of a tutorial for installing (setting windows vs sql
authentication) and then setting up user permissions?
thanks, Jeff
There is usually a default account in the sa role for the local admin
account. Login with that and you should be able to do what you need to do.
Andrew J. Kelly SQL MVP
"jmoeller18" <jmoeller18@.gmail.com> wrote in message
news:1140392437.685886.73280@.g43g2000cwa.googlegro ups.com...
> after installing sql server 2005, i messed up the login settings.
> I know can't login.
> i didn't really understand what i was doing
> short of uninstalling, reinstalling, do i have any options?
> also do you know of a tutorial for installing (setting windows vs sql
> authentication) and then setting up user permissions?
> thanks, Jeff
>

Locked files during backups on a cluster

Hello,
I am running an active-active cluster on SQL 2000 SP3a over a Windows 2003
Enterprise server. We have two production instances running (SQL1 and SQL2).
Each was setup on a different node originally. When the two instances are
running on the same node, the instance that was not setup on that node
experiences locked files and failed backups.
Here is an example:
SQL1 was setup on Node1 and SQL2 was setup on Node2. When SQL1 and SQL2 are
both running on Node1, our backup job fails because it sees that a couple of
the database backup files are locked. Even after I clear the locks and re-ru
n
the backup script, the job still fails for the same reason. Now when I run
the same job when SQL2 is back on Node2, the job completes successfully. Not
all of the database backups fail, just the ones that are over 2Gb.
Has anyone experienced this or heard about this before? I've checked posting
s
here as well as TechNet, but haven't found anything.
Thanks for your assistance.
Michael
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200609/1> both running on Node1, our backup job fails
By 'our backup job', I assume you meant the SQL Server database backup job.
The problem should not have anything to do with how the two instances were
originally set up. Nor should it necessarily have anything to do with the
fact that the two instances were running on the same node. It was a
coincident that it did happen when the two instances were running on the sam
e
node.
To resolve the problem, you need to first determine what process(es) or
programs are locking the backup files. My hunch is that they are most likely
locked either by an anti-virus program or a network file backup job. In othe
r
words, the anti-virus or the network file backup job happened to collide wit
h
the database backup on this particular node.
One way to be sure about what is locking the backup file is to add another
step immediately before the SQL Server backup to dump out all the NT handles
or just the handles on the backup file. You can use the sysinternals tool
handle.exe for this purpose.
Linchi
"michaelg via droptable.com" wrote:

> Hello,
> I am running an active-active cluster on SQL 2000 SP3a over a Windows 2003
> Enterprise server. We have two production instances running (SQL1 and SQL2
).
> Each was setup on a different node originally. When the two instances are
> running on the same node, the instance that was not setup on that node
> experiences locked files and failed backups.
> Here is an example:
> SQL1 was setup on Node1 and SQL2 was setup on Node2. When SQL1 and SQL2 ar
e
> both running on Node1, our backup job fails because it sees that a couple
of
> the database backup files are locked. Even after I clear the locks and re-
run
> the backup script, the job still fails for the same reason. Now when I run
> the same job when SQL2 is back on Node2, the job completes successfully. N
ot
> all of the database backups fail, just the ones that are over 2Gb.
> Has anyone experienced this or heard about this before? I've checked posti
ngs
> here as well as TechNet, but haven't found anything.
> Thanks for your assistance.
> Michael
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200609/1
>|||Yes, I meant the SQL Server database backup jobs.
This is not a one-time event. No matter what time I run the database backup
job on Node1, the job fails. This makes me think that it is not a tape backu
p
or any scheduled event. When I moved back to Node2 this morning, the job ran
fine multiple times.
I am not familiar with the handle.exe. Is this a 3rd party tool or is it
included in Microsoft?
Thanks!
Linchi Shea wrote:[vbcol=seagreen]
>By 'our backup job', I assume you meant the SQL Server database backup job.
>The problem should not have anything to do with how the two instances were
>originally set up. Nor should it necessarily have anything to do with the
>fact that the two instances were running on the same node. It was a
>coincident that it did happen when the two instances were running on the sa
me
>node.
>To resolve the problem, you need to first determine what process(es) or
>programs are locking the backup files. My hunch is that they are most likel
y
>locked either by an anti-virus program or a network file backup job. In oth
er
>words, the anti-virus or the network file backup job happened to collide wi
th
>the database backup on this particular node.
>One way to be sure about what is locking the backup file is to add another
>step immediately before the SQL Server backup to dump out all the NT handle
s
>or just the handles on the backup file. You can use the sysinternals tool
>handle.exe for this purpose.
>Linchi
>
>[quoted text clipped - 18 lines]
Message posted via http://www.droptable.com|||This is a sysinternals tool available from www.sysinternals.com.
Linchi
"michaelg via droptable.com" wrote:

> Yes, I meant the SQL Server database backup jobs.
> This is not a one-time event. No matter what time I run the database backu
p
> job on Node1, the job fails. This makes me think that it is not a tape bac
kup
> or any scheduled event. When I moved back to Node2 this morning, the job r
an
> fine multiple times.
> I am not familiar with the handle.exe. Is this a 3rd party tool or is it
> included in Microsoft?
> Thanks!
> Linchi Shea wrote:
> --
> Message posted via http://www.droptable.com
>

Locked files during backups on a cluster

Hello,
I am running an active-active cluster on SQL 2000 SP3a over a Windows 2003
Enterprise server. We have two production instances running (SQL1 and SQL2).
Each was setup on a different node originally. When the two instances are
running on the same node, the instance that was not setup on that node
experiences locked files and failed backups.
Here is an example:
SQL1 was setup on Node1 and SQL2 was setup on Node2. When SQL1 and SQL2 are
both running on Node1, our backup job fails because it sees that a couple of
the database backup files are locked. Even after I clear the locks and re-run
the backup script, the job still fails for the same reason. Now when I run
the same job when SQL2 is back on Node2, the job completes successfully. Not
all of the database backups fail, just the ones that are over 2Gb.
Has anyone experienced this or heard about this before? I've checked postings
here as well as TechNet, but haven't found anything.
Thanks for your assistance.
Michael
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200609/1> both running on Node1, our backup job fails
By 'our backup job', I assume you meant the SQL Server database backup job.
The problem should not have anything to do with how the two instances were
originally set up. Nor should it necessarily have anything to do with the
fact that the two instances were running on the same node. It was a
coincident that it did happen when the two instances were running on the same
node.
To resolve the problem, you need to first determine what process(es) or
programs are locking the backup files. My hunch is that they are most likely
locked either by an anti-virus program or a network file backup job. In other
words, the anti-virus or the network file backup job happened to collide with
the database backup on this particular node.
One way to be sure about what is locking the backup file is to add another
step immediately before the SQL Server backup to dump out all the NT handles
or just the handles on the backup file. You can use the sysinternals tool
handle.exe for this purpose.
Linchi
"michaelg via SQLMonster.com" wrote:
> Hello,
> I am running an active-active cluster on SQL 2000 SP3a over a Windows 2003
> Enterprise server. We have two production instances running (SQL1 and SQL2).
> Each was setup on a different node originally. When the two instances are
> running on the same node, the instance that was not setup on that node
> experiences locked files and failed backups.
> Here is an example:
> SQL1 was setup on Node1 and SQL2 was setup on Node2. When SQL1 and SQL2 are
> both running on Node1, our backup job fails because it sees that a couple of
> the database backup files are locked. Even after I clear the locks and re-run
> the backup script, the job still fails for the same reason. Now when I run
> the same job when SQL2 is back on Node2, the job completes successfully. Not
> all of the database backups fail, just the ones that are over 2Gb.
> Has anyone experienced this or heard about this before? I've checked postings
> here as well as TechNet, but haven't found anything.
> Thanks for your assistance.
> Michael
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200609/1
>|||Yes, I meant the SQL Server database backup jobs.
This is not a one-time event. No matter what time I run the database backup
job on Node1, the job fails. This makes me think that it is not a tape backup
or any scheduled event. When I moved back to Node2 this morning, the job ran
fine multiple times.
I am not familiar with the handle.exe. Is this a 3rd party tool or is it
included in Microsoft?
Thanks!
Linchi Shea wrote:
>> both running on Node1, our backup job fails
>By 'our backup job', I assume you meant the SQL Server database backup job.
>The problem should not have anything to do with how the two instances were
>originally set up. Nor should it necessarily have anything to do with the
>fact that the two instances were running on the same node. It was a
>coincident that it did happen when the two instances were running on the same
>node.
>To resolve the problem, you need to first determine what process(es) or
>programs are locking the backup files. My hunch is that they are most likely
>locked either by an anti-virus program or a network file backup job. In other
>words, the anti-virus or the network file backup job happened to collide with
>the database backup on this particular node.
>One way to be sure about what is locking the backup file is to add another
>step immediately before the SQL Server backup to dump out all the NT handles
>or just the handles on the backup file. You can use the sysinternals tool
>handle.exe for this purpose.
>Linchi
>> Hello,
>[quoted text clipped - 18 lines]
>> Thanks for your assistance.
>> Michael
--
Message posted via http://www.sqlmonster.com|||This is a sysinternals tool available from www.sysinternals.com.
Linchi
"michaelg via SQLMonster.com" wrote:
> Yes, I meant the SQL Server database backup jobs.
> This is not a one-time event. No matter what time I run the database backup
> job on Node1, the job fails. This makes me think that it is not a tape backup
> or any scheduled event. When I moved back to Node2 this morning, the job ran
> fine multiple times.
> I am not familiar with the handle.exe. Is this a 3rd party tool or is it
> included in Microsoft?
> Thanks!
> Linchi Shea wrote:
> >> both running on Node1, our backup job fails
> >
> >By 'our backup job', I assume you meant the SQL Server database backup job.
> >
> >The problem should not have anything to do with how the two instances were
> >originally set up. Nor should it necessarily have anything to do with the
> >fact that the two instances were running on the same node. It was a
> >coincident that it did happen when the two instances were running on the same
> >node.
> >
> >To resolve the problem, you need to first determine what process(es) or
> >programs are locking the backup files. My hunch is that they are most likely
> >locked either by an anti-virus program or a network file backup job. In other
> >words, the anti-virus or the network file backup job happened to collide with
> >the database backup on this particular node.
> >
> >One way to be sure about what is locking the backup file is to add another
> >step immediately before the SQL Server backup to dump out all the NT handles
> >or just the handles on the backup file. You can use the sysinternals tool
> >handle.exe for this purpose.
> >
> >Linchi
> >
> >> Hello,
> >>
> >[quoted text clipped - 18 lines]
> >> Thanks for your assistance.
> >> Michael
> --
> Message posted via http://www.sqlmonster.com
>

locked cube

In analysis server 2000 we have a cube that is locked and can not be edited. Does anyone know of a way to unlock a cube?

Also I have restarted the Olap Service on the server.

Hi Chris,

what kind of message do you have exactly from AS ?

Are you trying to edit the cube using Analysis Manager ?

Francesco

|||

Is your repository in the default access database?

If so, try stopping the service and see if there is an msmdrep.ldb file in the \Program Files\Microsoft Analysis Services\bin folder (This is the access "locks" file). If there is, delete (or rename) this file and then restart the Olap service and see if this fixes your issue. It sounds like something happened while someone was editing this particular cube.

sql

Wednesday, March 28, 2012

lock timeouts

I have a procedure running which is locking a table. I want to execute another one, knowing fullwell it wants to use the locked table. Can I go home and expect the 2nd proc. to run when the table becomes unlocked or will it time out while waiting and send an error.
-KThe procedure will wait indefinitely unless there is a deadlock or the LOCK_TIMEOUT setting has a value other than -1.

LOCK REQUEST TIME OUT PERIOD EXCEEDED

It seems the table is locked by one process for a longer
time, and this has forced other process to throw the
error 1222.
The LOCK_TIMEOUT setting allows an application to set a
maximum time that a statement waits on a blocked
resource. When a statement has waited longer than the
LOCK_TIMEOUT setting, the blocked statement is canceled
automatically, and error message 1222 "Lock request time-
out period exceeded" is returned to the application.
I think if after every 1000 inserts, if you commit the
transaction, then the resource will not be locked for a
longer duration.
regds,
Shrikant Patil,
MCDBA

>--Original Message--
>Got a VB6 program creating records in an SQL 2000 DB. I
have 5 client
>machines creating 100,000 records each in the same table
at the same time.
>On a couple of machine I get the error "LOCK REQUEST
TIME OUT PERIOD
>EXCEEDED". This happened maybe 4 times. Would this be
due to a network or
>hardware limitation or is it a SQL DB factor maybe ?
>The SQL server is only a P4 with 512 ram.
>Clients machines vary greatly and run 2000pro and XPpro.
>Thanks for any pointers.
>Scott.
>
>.
>
i see.
so the error was recived because one clinet/process was ready to commit 1000
records while another clinet/process was in the process of commiting - hence
the error.
the chances that a clinet machine would recive this with our software in
practice is very remotei guess as they would probablty never create that
many records at the same time to the same table. even if they did the RETRY
option seems to deal with it well anyway.
Is there other locking methods that can be employed rather than the 1000
batch one ?
Thanks for your time
Scott

Wednesday, March 21, 2012

Lock a row

I wanted to locked to hide a record to other user if the record is viewing
by one user.
Are there any way to do this?
Does lock the record can hide this record to other user?
Any information is great appreciated.
SourisYou can use query hints with a transaction but you're looking at scalability
issues. Why do you want to hide a row while someone is looking at it?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"souris" <soukkris@.viddotron.com> wrote in message
news:eJ3lg3kBFHA.2916@.TK2MSFTNGP10.phx.gbl...
I wanted to locked to hide a record to other user if the record is viewing
by one user.
Are there any way to do this?
Does lock the record can hide this record to other user?
Any information is great appreciated.
Souris|||On Sat, 29 Jan 2005 17:00:05 -0500, souris wrote:

>I wanted to locked to hide a record to other user if the record is viewing
>by one user.
>Are there any way to do this?
>Does lock the record can hide this record to other user?
>Any information is great appreciated.
Hi Souris,
You could use the READPAST locking hint. Keep in mind that this is only
usefull in some very specific circumstances. If you use this locking hint
in a statistical or reporting query, you'll miss some data without having
any indication if and how much data you miss.
A typical situation where the READPAST hint is useful is a queueing
system: each application / connection / agent / whatever reads the first
row from a wrok-queue table and acquire an exclusive lock; other agents
will read past this row and get the next non-locked one. The exclusive
lock ensures that the connection reading the row will be able to change or
even delete is after the data is processed.
BEGIN TRANSACTION
SELECT TOP 1 ....
FROM WorkQueue WITH (READPAST, XLOCK)
ORDER BY Priority
-- Do some stuff
IF (...) -- data changed
UPDATE WorkQueue
SET ... = ...
WHERE ....
IF (...) -- processing completely done
DELETE WorkQueue
WHERE ....
IF (...) -- check for unhandled errors
ROLLBACK TRANSACTION
ELSE
COMMIT TRANSACTION
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks for the message.
This app is for a call center.
Because I do not want the customer get more than one call for the same issue
Souris
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:eZJxXhlBFHA.1296@.TK2MSFTNGP10.phx.gbl...
> You can use query hints with a transaction but you're looking at
> scalability
> issues. Why do you want to hide a row while someone is looking at it?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
> .
> "souris" <soukkris@.viddotron.com> wrote in message
> news:eJ3lg3kBFHA.2916@.TK2MSFTNGP10.phx.gbl...
> I wanted to locked to hide a record to other user if the record is viewing
> by one user.
> Are there any way to do this?
> Does lock the record can hide this record to other user?
> Any information is great appreciated.
> Souris
>|||Thanks for the information,
It is what I am looking for.
Souris
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:4b6ov0dsbu5e3gmhsne0omtr2ul2ac95ae@.
4ax.com...
> On Sat, 29 Jan 2005 17:00:05 -0500, souris wrote:
>
> Hi Souris,
> You could use the READPAST locking hint. Keep in mind that this is only
> usefull in some very specific circumstances. If you use this locking hint
> in a statistical or reporting query, you'll miss some data without having
> any indication if and how much data you miss.
> A typical situation where the READPAST hint is useful is a queueing
> system: each application / connection / agent / whatever reads the first
> row from a wrok-queue table and acquire an exclusive lock; other agents
> will read past this row and get the next non-locked one. The exclusive
> lock ensures that the connection reading the row will be able to change or
> even delete is after the data is processed.
> BEGIN TRANSACTION
> SELECT TOP 1 ....
> FROM WorkQueue WITH (READPAST, XLOCK)
> ORDER BY Priority
> -- Do some stuff
> IF (...) -- data changed
> UPDATE WorkQueue
> SET ... = ...
> WHERE ....
> IF (...) -- processing completely done
> DELETE WorkQueue
> WHERE ....
> IF (...) -- check for unhandled errors
> ROLLBACK TRANSACTION
> ELSE
> COMMIT TRANSACTION
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||I would probably suggest that this is not a great idea. Holding locks from
the client is troublesome, and if the connection dies you are not even going
to see that there was a call in progress. In this case, why not use a
status column (or table, if you want a complete history of status)
Then when the user gets the row, you set the status to in-progress, and set
the user that has the row in use. If they don't complete the call properly,
it will stay in an open state. When the user logs back in, you can check to
see if they have any open sessions and make them deal with them.
All of your other clients will look for rows that are not in-progress or
completed. Use the readpast hint as mentioned by Hugo to keep any clients
from having to wait. This way your management applications can see which
calls are in progress, what their status is, and it will still be "locked"
from the users that it needs to be locked from.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"souris" <soukkris@.viddotron.com> wrote in message
news:%23Fbdm4oBFHA.2540@.TK2MSFTNGP09.phx.gbl...
> Thanks for the information,
> It is what I am looking for.
> Souris
>
> "Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
> news:4b6ov0dsbu5e3gmhsne0omtr2ul2ac95ae@.
4ax.com...
>

Lock /objects in Enterprise manager**

Hi
when I come to lock/objects branch in enterprise manager I find out so many
tables and views name there ,does it mean that all these are locked? or does
it mean all these are using by some users,but we can use it too?
any help would be thankfulYou can get more information with sp_who and sp_who2 system stored procs
Dishan Fernando
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!|||http://msdn.microsoft.com/library/d...y/en-us/adminsq
l/ad_perfmon_6ofn.asp
DishanF
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!

Friday, March 30, 2012

Locking

Locked Stored Procedures

locked out of sql server management studio

locked out of sql server management studio

locked out of sql server management studio

Server Error in '/' Application.

Server Error in '/' Application.

Server Error in '/' Application.

No connection could be made because the target machine actively refused it

Locked out of Report Manager on my own machine!

Locked out of Enterprise Manager

Locked out again?

Locked out

locked out

locked out

Locked files during backups on a cluster

Locked files during backups on a cluster

locked cube

Wednesday, March 28, 2012

lock timeouts

LOCK REQUEST TIME OUT PERIOD EXCEEDED

Wednesday, March 21, 2012

Lock a row

Lock /objects in Enterprise manager**

sql local data

Blog Archive

About Me