TMSTable.Open hangs

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Kire
Posts: 19
Joined: Thu 26 Jul 2012 09:07

TMSTable.Open hangs

Post by Kire » Thu 26 Jul 2012 09:35

Hi,

I'm evaluating the use of SDAC for our Delphi products. First tests seem positive, however I have one issue that is quite a showstopper.

I create temporary (duplicate) tables often, mainly for lookup of data. Most of the times this works without problems but sometimes the application keeps hanging on the Open method.

The code for creating the temp table:

function TBasicDM.CreateTempTable( tbl: TMSTable; ReadOnly: Boolean ): TMSTable;
begin
Result := TMSTable.Create( nil );
Result.TableName := tbl.TableName;
Result.Connection := tbl.Connection;
Result.ReadOnly := ReadOnly;
Result.Open;
end;

ConnectionString of Connection is like this:

"Provider=SQLOLEDB.1;User ID=xxx;Password=xxxx;Data Source=developos;Initial Catalog=FusionAnimalsArchive;Persist Security Info=True;MultipleActiveResultSets=True"

In what circumstances can this behaviour occur?

Hope someone can give me a clue to get me into the right direction with this.

TIA,

Erik

AndreyZ

Re: TMSTable.Open hangs

Post by AndreyZ » Fri 27 Jul 2012 09:27

Hello,

This problem can be caused by using the FetchAll=False mode. When FetchAll=False, execution of such queries blocks the current session. In order to avoid blocking, OLE DB creates additional session that causes several problems, including the problem with the table hanging on opening. To prevent the TMSConnection component from creating additional connections for datasets that work in the FetchAll=False mode, you should enable the MultipleActiveResultSets option.
You enabled MultipleActiveResultSets but you are using OLEDB provider that doesn't support MultipleActiveResultSets. This option is only supported by SQL Native Client provider. Please try using SQL Native Client provider and check if the problem persists. To use SQL Native Client provider, you should set the TMSConnection.Options.Provider property to prNativeClient.

Kire
Posts: 19
Joined: Thu 26 Jul 2012 09:07

Re: TMSTable.Open hangs

Post by Kire » Mon 30 Jul 2012 10:03

Hi,

Switched to prNativeClient but the problem still persists.

Any other items I can check?

Regards,
Erik

AndreyZ

Re: TMSTable.Open hangs

Post by AndreyZ » Mon 30 Jul 2012 10:39

Please try creating a small sample to demonstrate the problem and send it to andreyz*devart*com , including a script to create and fill a table. Also please specify the following:
- the exact version of SDAC. You can learn it from the About sheet of TMSConnection Editor;
- the exact version of your IDE;
- the exact version of SQL Server and client. You can learn it from the Info sheet of TMSConnection Editor.

Kire
Posts: 19
Joined: Thu 26 Jul 2012 09:07

Re: TMSTable.Open hangs

Post by Kire » Wed 01 Aug 2012 09:59

Tried to create a small project with the same part of code but that is a bit difficult.

Getting also some other problems now.

Situation with updating data is like this.

* Using TMSConnection with the above connection string and a lot of TMSTable using that connection. Basicly one main (Base) MSTable with a lot of underlying tables as a 1-to-many relation. Primary key is a simple Integer field.
* Position Base table to the correct record and also the underlying tables that need an update. This was done by SetRange originally and now done by FilterSQL on the primary key on all tables.
* Using MSConnection.StartTransaction to open a transaction.
* Update affected tables. Also create temporary tables as lookupdata. Sometimes here occurs the original problem that the application hangs on the opening of the table.
* Some tables need a new record. Then first a default new record is created and then updated right after that. With the update I'm getting 'Update failed. found 0 records'. Strange thing I'm not getting these errors when I set a breakpoint and step through the code.
* MSConnection.Commit

Although I'm pretty convinced that the problems are related to the way we use the TTable/TMSTable but it looks like a rough path to upgrade our application from BDE to SDAC at the moment. Also because I'm getting random access violations that occur when using the SDAC components.

Most tables/data we have is working alright with SDAC, only the tables where we use TBytesField columns seems to give problems.

Any suggestion is much appreciated! I'll keep trying to reproduce the problem in small separate project.

Regards,
Erik

AndreyZ

Re: TMSTable.Open hangs

Post by AndreyZ » Wed 01 Aug 2012 11:41

Sometimes, SQL Server can lock a table if it is used in a transaction. You can find more information about that at http://msdn.microsoft.com/en-us/library/ms173763.aspx . Here is a qoute from this article:
The transaction isolation levels define the type of locks acquired on read operations. Shared locks acquired for READ COMMITTED or REPEATABLE READ are generally row locks, although the row locks can be escalated to page or table locks if a significant number of the rows in a page or table are referenced by the read.
If SQL Server locks a table, you will not be able to work with it until a transaction, in which the table is used, is committed or rolled back. You can try setting the TMSTable.CommandTimeout property to the amount of time that expires before an attempt to execute a command is considered unsuccessful. By default, TMSTable.CommandTimeout = 0, that means to wait infinite time.

The 'Update failed. N records found' error occurs if the number of updated or deleted records is not equal to 1. This problem usually occurs when a table has no Primary Key. Please check that your table has Primary Key. Also, this error can occur if you have a trigger that influences the number of updated or deleted records. To solve the problem in this case, you should add the 'SET NOCOUNT ON;' line to the beginning of the trigger code.

Kire
Posts: 19
Joined: Thu 26 Jul 2012 09:07

Re: TMSTable.Open hangs

Post by Kire » Wed 01 Aug 2012 11:53

Thanks for the SQL server info! It indeed seems initiated by SQL server because I can't access the table from within SQL Management Studio too when this situation occurs. I'll look into that although not sure how to work around that without rewriting much of the code.

Edit 1: default isolation level of TMSConnection is ReadCommited, so ReadUncommited should fix this problem?

Edit 2: the temp tables are connected to the same connection/transaction.

Edit 3: problem with opening temp tables also occurs when reading and not being in a transaction. Still it seems initiated somehow by the SQL server.

Regarding the 'Update failed': Tables do have a primary key, in fact primary key consists of two fields: Int and a datetime. I'm not using triggers. I see it happening even when the table is initially empty: Create first record with defaults then update and the message occurs. Altough I can ignore the exception with StrictUpdate = false, but then the update is just lost.

Erik

AndreyZ

Re: TMSTable.Open hangs

Post by AndreyZ » Thu 02 Aug 2012 09:39

Unfortunately I cannot give you a more valid answer about the problem with the table locking without the test project that demonstrates it. Please try creating it and send it to andreyz*devart*com . Also, please don't forget to specify the following:
- the exact version of SDAC. You can learn it from the About sheet of TMSConnection Editor;
- the exact version of your IDE;
- the exact version of SQL Server and client. You can learn it from the Info sheet of TMSConnection Editor.

Concerning the problem with the "Update failed" error. The point is that SDAC doesn't get information about default values from the server by default. That's why, when you use default values for your primary key fields, client application doesn't know these values when you insert a new record. Therefore, the refresh operation returns zero number of updated records. To avoid this problem, you should set the TMSTable.Options.DefaultValues property to True.

Kire
Posts: 19
Joined: Thu 26 Jul 2012 09:07

Re: TMSTable.Open hangs

Post by Kire » Mon 06 Aug 2012 12:30

changed our code a little so that "Update failed" doesn't appear any more.

My main concern at the moment are random access violations that occur as soon as I start using SDAC components and in particular tables with TBytesFields. When using them most of the time I get access violations when shutting down the application and I can't get any more information on where and what. No clue yet of where to search for. :cry:

AndreyZ

Re: TMSTable.Open hangs

Post by AndreyZ » Mon 06 Aug 2012 14:12

Please provide the following information:
- the exact version of SDAC. You can learn it from the About sheet of TMSConnection Editor;
- the exact version of your IDE;
- the exact version of SQL Server and client. You can learn it from the Info sheet of TMSConnection Editor.
Without this information I will not be able to help you.

Kire
Posts: 19
Joined: Thu 26 Jul 2012 09:07

Re: TMSTable.Open hangs

Post by Kire » Mon 06 Aug 2012 14:29

SDAC: 6.2.7
IDE: RAD Studio V 11.0.2902.10471
Microsoft SQL Server: 10.50.1600
Microsoft SQL Server Native Client 10.0: 10.0.1600.22

Problems seem to arise when I add more than one row to the table with TBytesFields...

TIA,
Erik

AndreyZ

Re: TMSTable.Open hangs

Post by AndreyZ » Tue 07 Aug 2012 11:01

I tried to reproduce this problem in different ways, but with no success. I still cannot reproduce the problem with access violations when shutting down the application. As I can see, you are using SQL Native Client 10.0 that comes with SQL Server 2008, but you are working with SQL Server 2008 R2. Please try installing SQL Native Client 10.5 (10.50.1600.1) that comes with SQL Server 2008 R2 and check if this solves the problem. If it doesn't solve the problem, please try creating a small sample that demonstrates the problem. Without this sample I will not be able to investigate this problem and give you a valid answer.

Kire
Posts: 19
Joined: Thu 26 Jul 2012 09:07

Re: TMSTable.Open hangs

Post by Kire » Wed 08 Aug 2012 14:00

I understand.

Bit hard to isolate the problem in a small project, but I'll keep tryin'.

When it crashes at shutdown of the application it breaks in CPU window in NTDLL.DbgBreakPoint (Int 3)....

AndreyZ

Re: TMSTable.Open hangs

Post by AndreyZ » Wed 08 Aug 2012 14:40

Please specify the OS version you are using (Windows 2000, XP, Vista, 7, etc.).

Kire
Posts: 19
Joined: Thu 26 Jul 2012 09:07

Re: TMSTable.Open hangs

Post by Kire » Mon 20 Aug 2012 13:10

Back from a week off.

I'm running it on a XP as a virtual machine.

I'm also still using the trial version of SDAC. Could that be the problem? I now remember I did read a thread some time ago which suggested that was causing the problem, but can't find the thread any more.

Post Reply