TADOConnection.Close does not release sql lock immediately?

Hi!

Using MS SQL Server 2005.

I have an application that connects to a database catalog named MYDB, it can call another application which task is to re-create the database MYDB. 

The problem is that if the main-application is connected to MYDB, the sub-application won't be able execute "DROP DATABASE MYDB", giving an exception that the database is in use.

So before I call the sub-application I call adoConnection.Close; The adoConnection object indicates the connection is closed, but in the Activity Monitor in SQL Management Studio I can see that there's still a "lock" on the database created by the main-application. This "Lock" is released after a rather long time-out it seems. It is also release directly if the main-application is terminated.

How can I close an ado connection totally, making sure the server has released any locks?

 / Peter
0
Utf
12/5/2008 4:02:33 PM
embarcadero.delphi.ado 597 articles. 1 followers. Follow

3 Replies
734 Views

Similar Articles

[PageSpeed] 3

Close all open tables, querys and stored procs before you close the
connection.


Peter Hartlén wrote:

> So before I call the sub-application I call adoConnection.Close; The
> adoConnection object indicates the connection is closed, but in the
> Activity Monitor in SQL Management Studio I can see that there's
> still a "lock" on the database created by the main-application. This
> "Lock" is released after a rather long time-out it seems. It is also
> release directly if the main-application is terminated.
> 
> How can I close an ado connection totally, making sure the server has
> released any locks?
0
Joachim
12/5/2008 4:29:10 PM
Peter Hartlén wrote:

> How can I close an ado connection totally, making sure the server has
> released any locks?

Try freeing the object.

-- 
Iman
0
Iman
12/5/2008 5:57:03 PM
My guess is that your problem is that SQL server defaults to using connection Pooling so the connection last used waits around for a while to be reused.
Try adding "OLE DB Services = -2;"  to your connection string.
That enables all services except connection pooling.



> 
> Using MS SQL Server 2005.
> 
> I have an application that connects to a database catalog named MYDB, it can call another application which task is to re-create the database MYDB. 
> 
> The problem is that if the main-application is connected to MYDB, the sub-application won't be able execute "DROP DATABASE MYDB", giving an exception that the database is in use.
> 
> So before I call the sub-application I call adoConnection.Close; The adoConnection object indicates the connection is closed, but in the Activity Monitor in SQL Management Studio I can see that there's still a "lock" on the database created by the main-application. This "Lock" is released after a rather long time-out it seems. It is also release directly if the main-application is terminated.
> 
> How can I close an ado connection totally, making sure the server has released any locks?
> 
>  / Peter
0
Brian
12/6/2008 2:27:46 AM
Reply:

Similar Artilces:

ADO.NET ASA 8.0.1
Hello All, I've been having a problem with a table row being locked after updating a row. This generally occurs only after I have performed this update 4 times consecutively, on the fifth attempt I get a record locked error. I am not wrapping this update in a transaction, all I do is open a connection, perform the update and close the connection. I did notice that in Sybase Central the last request for my connection reads "CURSOR_OPEN @ 2002-09-26 02:04:43000". Apparently other people have encountered a record locking problem, but they claim it was fixed in the 8....

delphi 2010 memory not released when closing delphi project
each time im runing delphi 2010 the memory that was used was not release after closing a project and the memory don't stop to grow and the browsing for file becoming slow any idea ? Thanks Pierre Auger wrote: > each time im runing delphi 2010 the memory that was used was not > release after closing a project and the memory don't stop to grow and > the browsing for file becoming slow > > any idea ? You are using some 3rd-party components that do not properly release memory in their design-time packages would be my guess. A design-time package stays l...

Delphi, ADO, SQL Anywhere
This combination seem not to work together. I'm using D5 Enterprise, ASA 6.03 w/latest EBF On some tables it works. But most of the tables produce a message "An Error occured". As soon as data bound contols are used, the connection just gives errors. It seem to work fine, if I use client based cursors. But this option copys the server table to the local client. This is not acceptable for my large tables. It lasts several minutes on my pc. Are there plans to release a native OLE DB provider for ASA or at least to fix the ODBC driver so, that ADO can be used? I am very...

Convert Delphi 5 ADO App to Delphi 2007 ADO.NET
I have an appliction tha that is writen in Delphi 5 with ADOConnetion, ADOTable, and ADOQuery components. I need to upgrade to Delphi 2007 and use ADO.NET. I have the program building in with Delphi 2007. Is there anything I need to do to convert the ADO to ADO.NET? > I need to upgrade to Delphi 2007 and use ADO.NET. Why ? -- With best regards, Dmitry Arefiev AnyDAC (www.anydac.com) - Oracle, MS SQL Server, IBM DB2, MySQL, Firebird, Interbase, PostgreSQL, Sybase ASA, SQLite, MS Access, DBX, ODBC, InMem - universal high-speed native data access engine Reasons for ...

Record Locking in ADO.net/Sql Server
Currently I am working on asp.net Intranet system.I need some suggestion on records locking for Sqlserver & ASP.net. I want to prevent two user to open the same record for modify.Means if some one has open one record (id=xxxxx) for modify then others should not be able to open same same record (id=xxxxx) for modify .otherwise if both user will open/update same record then information in database will not be updated correctly. Any idea how we can do the record locking so that not two users can open same record(id=xxxxx) for modify. Thanks in Advance Arvind The concept you ar...

Has anyone tried Delphi + ADO + SQL Anywhere?
Good morning, Has anyone tried using Delphi 5 with the ADO components hitting SQL Anywhere? What was your experience? TIA -- Mr. John A. Jackson President & CEO LWE Research, Inc. www.lweresearch.com John, I am currently using ADO with C++ Builder 5 enterprise hitting SQL Anywhere Studio 6.0.3 with no problems whatsoever. Neo Mr. John A. Jackson <info@lweresearch.com> wrote in message news:OKnZFeihAHA.87@forums.sybase.com... > Good morning, > > Has anyone tried using Delphi 5 with the ADO components hitting SQL > Anywhere? What was you...

Connecting to MS-SQL-Express with ADO and Delphi 6
Hi, I have installed MS-SQL Express and created a database - Test1 - and a table within that database called Equipment with a few columns. I have created a new delphi 6 application with a TADOConnection component. When creating the connection string I have chosen the "Microsoft OLE DB Provider for SQL Server", then entered the server name as "mtpcname\SQLExpress1" and selected Windows NT Integrated security. Clicking the Test Connection button says that the connection was successful. However the drop down list to select the database on the server only list ...

Delphi 2.0 and Sybase 11: shared intent table locks not released?
We are using Delphi 2.0 (Win95, 32-bit) to develop against a Sybase System 11 server (11.0.3, Windows NT 4.0). We have made heavy use of Delphi's data-aware controls, linking them to TQuery components. We have just discovered the following: When a query retrieves a result set that is larger (at least one more network packet's worth) than what the client form can initially display, the query remains open, waiting to retrieve the unsent results, should the user decide to scroll down. (The Sybase process has a status of "send sleep".) Even though the query has released...

Dynamic SQL and Locking (lock sleep)
I have an embedded sql (c) program that builds dynamic sql statements and executes them. If I try to run 20 copies of this program concurrently most of the processes will report a lock sleep condition. I presume that this is locking on the system tables because of the dynamic sql. It looks like tempdb is taking Ex locks on some of the system tables. Is this true and is there a way around it? (commit after prepare maybe?) -- Sincerely, Jeff H. Jacobi This isn't because the SQL is genarated dynamically: as fas as the server is concerned it's just T-SQL statements comi...

Delphi Paradox to Delphi SQL Anywhere
I need to convert a Delphi 3 app from Paradox tables to something more flexible/powerful. My app creates a limited number of complex transactions ( i.e. 200 to 300 transactions per month - each transaction has 300 to 400 fields in the primary file and links to about a dozen support files. What programming issues should I watch for in this conversion ( i.e. is using quicken style incremental search combo boxes a problem due to network traffic, etc. ) ??? What would be the conversion steps/Issues involved with this process ??? Obviously tables/relationships need to be created a...

[Delphi 2010] ADO - MS SQL Server 2000 - Tranasction
Hello! For the first time I try to use Transaction when connecting via ADO to an MS SQL 2000 Server. I open the Connection, I open the ADOTable, I start the transaction, I insert some records to the ADOTable, I committ the transaction and ... nothing is saved in the SQL Server. Any hints what I'm missing? Thanks in advance and regards, Klaus "Klaus Edelmann" <fritzfranz24@hotmail.com> ha scritto nel messaggio news:246769@forums.embarcadero.com... > Hello! > > For the first time I try to use Transaction when connecting via ADO to an > ...

Delphi 2009 upd2 + ADO + Sybase ASA 10.0.1 = weird table locks?
Hi there, I'm facing a strange behaviour of Delphi/ADO/Sybase ASA: Application has a main form, where I place one TADOConnection (AC) and one TADOQuery (AQ). I set up connection string (Provider=SAOLEDB.10;Persist Security Info=True;OLE DB Services=-2;User ID=dba;Password=sql;Data Source=MyTestODBC), the rest of the parameters remains default. In AQ I hook up the connection to my AC, SQL is a simple "Select User_Name From User_List Where User_Id=1", rest of the parameters remains default. As soon as I open the AQ,the table "User_List" remains locked, I can see it i...

How to create a Delphi 7 ADO "LIVE" (auto update) query to SQL Server
How to create a Delphi 7 ADO "LIVE" query (one SQL Server table only) that once a table field is edited - the underlying query results are updated. Kind of like the "LIVE" query concept from the BDE etc. Below is the ADO query that I am using and it generally *seems* to work well where once a field is edited (via TADOQuery.Edit and .Post), the query result are also changed. I think it is true that after an edit the change is always visible in the same program using the same query object. {code}procedure ExecuteQryTable( Connection: TADOConnection; Qry: TADOQuery...

How to create a Delphi 7 ADO "LIVE" (auto update) query to SQL Server [Edit]
How to create a Delphi 7 ADO "LIVE" query (one SQL Server table only) that once a table field is edited - the underlying query results are updated. Kind of like the "LIVE" query concept from the BDE etc. Below is the ADO query that I am using and it generally *seems* to work well where once a field is edited (via TADOQuery.Edit, .Post etc), the query result are also changed. I think it is true that after an edit the change is always visible in the same program using the same query object. {code}procedure ExecuteQryTable( Connection: TADOConnection; Qry: TADOQuer...

Web resources about - TADOConnection.Close does not release sql lock immediately? - embarcadero.delphi.ado

Resources last updated: 1/14/2016 12:11:18 PM