SQL Server OLEDB Error 266

General discussion forum about all databases other than SqlBase.
RainerE
Germany
Posts: 2053
Joined: 27 Apr 2021, 12:40
Location: Germany

SQL Server OLEDB Error 266

Post by RainerE » 08 Dec 2021, 10:28

Hi,

our TD application connects to a MS SQL Server database using OLEDB and SqlUDL.
In the past, we used the OLEDB Provider 'SQLOLEDB' (which is part of Windows itself).
But MS has depreciated this old driver years ago (which uses TLS 1.0).
The new one is 'MSOLEDBSQL' (which uses TLS 1.2).

Due to security reasons, our customer does not allow to use the old driver any more.

I have installed the new driver and changed the connection string.
I am able to connect and I can select and update data.

But if I execute 'commit', I get the following error:
Err No: 266 :Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0.;
This happens with TD 4.2 and with TD 7.4.1.

Here is my sourcecode:

Code: Select all

Set SqlUDL = 'MSOLEDBSQL;Data Source=SRVSQL01;Initial Catalog=SO_MAIN_Dev;Integrated Security=SSPI;Persist Security Info=False;'
Call SqlConnect( hSql )
Call SqlSetParameter( hSql, DBP_AUTOCOMMIT, FALSE, '' )
Call SqlPrepareAndExecute( hSql, "update dbo.Ini set Val = 2 where Id = 1" )
Call SqlPrepareAndExecute( hSql, 'Commit' )
There is no stored procedure and not trigger involved.

What do I have to change not to receive this error?

Regards,
Rainer

User avatar
Steve Leighton
Site Admin
Site Admin
New Zealand
Posts: 289
Joined: 05 Mar 2017, 20:57
Location: Tauranga, New Zealand <--> Stroud, England

Re: SQL Server OLEDB Error 266

Post by Steve Leighton » 08 Dec 2021, 22:53

.
Here a simplified version of what I have for SS using MSOLEDBSQL, with no problems ( but only using on TD6.2 and TD7.3.4 ).
Note the isolation level, autcommit, cursor context preservation , resultset mode might make a difference for you.
Also, FYI with MSOLEDBSQL, I found you can tweak the Packet Size and Client Cursor settings in your connection string , to make a HUGE difference to performance.

Code: Select all

!
Set SqlUDL = '
		Provider=MSOLEDBSQL.1;Integrated Security=SSPI;Persist Security Info=False;User ID="";
		Initial Catalog=MYDATABASE;Data Source=MYSERVER;
		Extended Properties="CLIENTCURSOR=TRUE;SSPROP_INIT_PACKETSIZE=8192";
		Packet Size=8192;Application Name=MYAPP;Initial File Name="";Trust Server Certificate=True;
		Server SPN="";Authentication="";Access Token="" '
! 
! Create a Session
Set bOk = SqlCreateSession ( hcSession, SqlUDL )
!
! Connect new handle for this session
If bOk and hcSession
	Set bOk = SqlCreateStatement ( hcSession, hSql )
	!
	If bOk
		!
		! If first handle to be connected , set isolation level . If OLE DB use 'RL'
		If ncPoolCount = 0
			Call SqlSetIsolationLevel( hSql, 'RL' )
		!
		! SQL Server: Result set must be on. Defaults off. DBP_PRESERVE must be on.
		Call SqlSetResultSet( hSql, TRUE )
		Call SqlSetParameter( hSql, DBP_PRESERVE, TRUE, STRING_Null )
		Call SqlSetLockTimeout( hSql, 30 )
		!
		If SqlPrepareAndExecute( hSql, "update dbo.Ini set Val = 2 where Id = 1"  )
			Call SqlCommit( hSql )
		!
	!
Greetings from New Zealand
Steve Leighton

Bankside Systems Ltd.
UK ♦ Australia ♦ New Zealand

www.banksidesystems.co.uk

Image

RainerE
Germany
Posts: 2053
Joined: 27 Apr 2021, 12:40
Location: Germany

Re: SQL Server OLEDB Error 266

Post by RainerE » 09 Dec 2021, 08:52

Hi Steve,

thx for your answer.

I've found out that the error does not occur, if we use SqlCommit( hSql ) instead of SqlPrepareAndExecute( hSql, 'Commit' ).
This using SqlUDL and SqlConnect() as we do or using SqlCreateSession() and SqlCreateStatement() as you do.

Changing this will be a very big effort on our side, because we have to change this for every commit in all parts of our application(s) which we have implemented the last 25 years (250 sourcecode files).
I remember, that SqlCommit( hSql ) did have some memory leaks (at least >= 15/20 years ago). Therefor we did not use it.

And the problem still remains for Rollback, which we execute by calling SqlPrepareAndExecute( hSql, 'Rollback' ). I did not found something like SqlCommit( hSql ) for Rollback :-(

At the moment we have changed our global error handler to suppress error 266, but I don't think that this is a good idea because the error occurs on every commit.
Seems that we have to change SqlPrepareAndExecute( hSql, 'Commit' ) by SqlCommit( hSql ).

Kind regards,
Rainer

User avatar
Steve Leighton
Site Admin
Site Admin
New Zealand
Posts: 289
Joined: 05 Mar 2017, 20:57
Location: Tauranga, New Zealand <--> Stroud, England

Re: SQL Server OLEDB Error 266

Post by Steve Leighton » 09 Dec 2021, 23:52

.
Hello Rainer

I tried with your 'SqlPrepareAndExecute( hSql, 'Commit' )' method and it seems to work A-Ok (TD6.2.2 and TD7.1.1) with MSOLEDBSQL ( v18.3.0.0 ) on SS 2017 .
No evidence of any SQL error.
I also tried 'ROLLBACK' in the same way, no issue.
I put Dave's SQL monitor on it to make sure and it looks ok.
Image

But each time, I do make sure the UPDATE statement returns before issuing the COMMIT.
e.g.

Code: Select all


If not SqlPrepareAndExecute( hSqliDML,sSql )
	Call SqlPrepareAndExecute( hSqliDML, Constant_ROLLBACK)
	Call SystemMsg(ERR_APP_MIN+26, scApplicationName,'Update Failed', MB_Ok)
	Return FALSE
Else
	If not SqlPrepareAndExecute( hSqliDML, Constant_COMMIT)
		Call SystemMsg(ERR_APP_MIN+26, scApplicationName,'COMMIT Failed', MB_Ok)

p.s. we have 1500 users all using SQLCommit() hundreds of times per session, with no noticeable evidence of memory leak - albeit on SQLBase using native router.
Much less I/O on SQLServer with OLEDB ( don't want to hurt it too much :roll: ) , but still no issues -

Have you tried a different ( newer / older ) MSOLEDBSQL driver ? We are running v18.3.0.0

Sorry not more use, as switching to OLEDB is well worth it for performance .
You do not have the required permissions to view the files attached to this post.
Greetings from New Zealand
Steve Leighton

Bankside Systems Ltd.
UK ♦ Australia ♦ New Zealand

www.banksidesystems.co.uk

Image

FRBhote
India
Posts: 2177
Joined: 09 Mar 2017, 05:32
Location: Hyderabad, India

Re: SQL Server OLEDB Error 266

Post by FRBhote » 10 Dec 2021, 05:41

Don't know about a memory leak, but there were many times that a SqlCommit did not commit.

We too always use SqlPrepareAndExecute( hSql, 'Commit' ) instead.

Return to “General Discussion”

Who is online

Users browsing this forum: [Ccbot] and 0 guests