OLEDB MSSQL2005 and Transactions

Hi Guys

Anyone using Transactions with MS SQL via OLEDB?

I have got a few issues with attempting to implement this.

To start with, when using a single persistent global connection, I get the error

"Cannot create new transaction because capacity was exceeded"

Googling appears to indicate this has been an issue with other products, but I could find no definitive solution. One suggestion was to create a new connection for the transaction, which means I cannot use the data sources already opened with the default connection inside the transaction.
A colleague has tried using a window specific connection, but he gets the following error

Description = Cannot start more transactions on this session. Source = Microsoft OLE DB Provider for SQL Server
Error Number = -2147168237

Given that this occurred on a single record update inside a transaction, quite why it thought it was nested is a mystery.

This might be because of an earlier crash inside the transaction code which leads me onto another issue. How can you determine you are inside a Transaction?
The HTransactionXXX functions are unavailable in OLEDB.


Interestingly enough, another google came up with information indicating the OLEDB provider does not support nested transactions. Not insurmountable but irritating.

Any thoughts anyone before I pass it on to PC Soft?


Cheers

Malc

Hi Malc

Had/have the same problem - only for some transactions. Sometimes it works fine. I've no idea exactly why. The only solution I found (suggested on several Delphi sites) was to close and reopen the connection to the database before starting the transaction. This works but is obviously very unsatisfactory - restoring the currency on all the open tables etc.

Although this does not seem to be any fault of PC-Soft it is a significant problem. The MS SQL Native driver is not an option for us (would make us uncompetitive) and we would prefer to use the HConmmands so ODBC is not an option.

Sorry I've no silver bullet for you.

Cheers

Justin

Hi Justin

Thanks for the reply, anyway.

Cheers

Malc

Hi Malc
We use transactions a fair bit and have never struck a problem, apart from some performance issues where it has really impacted heavily. I don't know if the access method has much/anything to do with it but we use SQLManagerX drivers (free of charge at www.sqlmanagerx.com) for most of our SQL database access and have found it works really well. It works a little differently to H Commands but once you get used to the syntax (in French but I have modified the class so it speaks English!) it is great; very fast, fully supports stored procedures and the support from the guys who wrote it is first class.

HTH

David

David:

could you share your english version of SQLManagerX ?

Thanks
Andres Sanchez

Hi Andres
Yes, not a problem. Give me a day or 2 to document it a bit better and I'll make it available. I should stress that all we are working with is the MSSQL drivers. They also have drivers for various other databases and a separate application SQLManagerX Control Centre. We haven't done anything with any of those.

Cheers

David

Hi David

Yes!, I want an English Version of SQLmanagerX too, ...
It would be nice to put the download link in the Windev Solutions so
available for everyone as a christmas gift in this holiday season...:-)

Thank in advance

Merry Christmas
Toddy

Hi David

I did actually download SQLManagerX some time ago, but have not had time to look at it in any detail. (My dismal foreign language skills have been putting me off!)

I will perhaps check it out again in the new year


Cheers

Malc

Hi David

Quote
David Egan
Hi Andres
Yes, not a problem. Give me a day or 2 to document it a bit better and I'll make it available. I should stress that all we are working with is the MSSQL drivers. They also have drivers for various other databases and a separate application SQLManagerX Control Centre. We haven't done anything with any of those.

Cheers

David

I would be very interested as well

Cheers

Malc

Hi

Many thanks in advance to David Egan. I'm very interested also.

Justin

Hi All
I've just made the file/s available via the Windev Solutions page.

Merry Christmas

David

Thanks David

Merry Christmas

Andres Sanchez

Hi David

Quote
David Egan
Hi All
I've just made the file/s available via the Windev Solutions page.

Merry Christmas

David

I shall enjoy unwrapping (unzipping?) this prezzie

Hope you are having a good Xmas, :cheers:

Malc

CAN U PLEASE HELP ME FIND WINDEV SOLUTIONS PAGE!!!!

IM NEW AROUND HERE......

Hi Rahul

Just Click the "Quicknavigation" combo box, on the top of this page....

Toddy

Hi toddy,
Thanks a lot

Hi,

Thanks David Egan for refreshing my memory about SQLManagerX and their set of (alter)native access drivers. Your post got me to look into it once more, and I am really excited about it. In our apps we never make use of HyperFile and/or H* commands, so the step from WD Native Access (which is very expensive) to SQLManagerX Access (which is free!) is really small for us.

There was an issue with performance though. I will not get into it, since you can read about it in detail on de www.sqlmanagerx.com forum. You were right when you said "the support from the guys who wrote it is first class". Together with one their technical guys we looked into the performance issue, and eventually solved it. I am very pleased.

So just to let everyone know: As of today you can download a new release of MSSQL4WD (OLEDB access to SQL Server) with significant improvements in speed.

Cheers,
/sohan

Hi Sohan
I'm glad you found it useful and I found your issues/comments very interesting. We have never struck the performance problem because we are just running locally. I never tried Native Access so can't comment on its speed but a couple of years ago I did some comparisons using MSSQL4WD and using OLEDB and found MSSQL4WD to be substantially faster. That you've come up with a problem/solution that makes it even faster is terrific.

Cheers

David

Hello sohan

show wrote

Quote

begin transaction;
select col1 from mydb..mytable with (updlock,holdlock)

Meaning that all rows in mydb..mytable are now locked. Next I let MSSQL4WD try to select the rows:

try with :

Quote

WITH(UPDLOCK, HOLDLOCK, ROWLOCK)

you lock the row and not the table. there is two buttons in the example project that give ou and example

i tested with the news version. i generated 2 exe and use them
one click on the button 1 and lock the entry 9 the second button 2 lock the entry 10

then the first exe click on the button 1
.
the second exe if i click on the first button 1 too i 'm blockerd while the 1 exe release the lock

if i click on the button 2 : i can do my lock then 2 lines are blocked (entry 9 and 10 with two exe)

see the methode mySQLbloque for use lock with select or update on line and not on table
because you can lock line in two modes
- lock other on select
- lock other on update
then in the first case you lock and other can't do a select
in the second case you lock and select are permitted for other but when other do update the server answer that row is locked

mSQL4WD give you the 2 solutions. with a boolean in the mySQLBloque method


Regards
Firetox

Thanks for your remarks FireTox. My study of MSSQL4WD is just beginning, so all pointers are helpfull. I'm looking forward to incorporating SQLManagerX into out apps.

/sohan