SQL Server OLEDB Error 266

[RainerE]

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

[Steve Leighton]

.
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 )
		!
	!

[RainerE]

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

[Steve Leighton]

.
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.


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 ) , 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 .

[FRBhote]

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.