Mobilink - Executing SQL directly; no cursor.

[Former Member]

Hello again,

We've been seeing a lot of warnings in the LogML.txt file, containing the message Executing SQL directly; no cursor., as show below:

W. 2017-04-10 15:36:34. <8> [10050] ODBC: [Microsoft][SQL Server Native Client 11.0][SQL Server]Executing SQL directly; no cursor. (ODBC State = 01000, Native error code = 16954)

After some analysis it seems to be related to download cursor of tables which contains columns of long-length types, like long varchar and long binary. Using a SQL profiler I concluded that for these tables (and only these tables) the following SQL command is executed:

declare @p1 int
set @p1=NULL
declare @p2 int
set @p2=0
declare @p5 int
set @p5=4104
declare @p6 int
set @p6=8193
declare @p7 int
set @p7=0
exec sp_cursorprepexec @p1 output,@p2 output,N'@P1 datetime2,@P2 varchar(128)',N' EXEC MySyncProc @LastDownload = @P1, @idMobileDB = @P2',@p5 output,@p6 output,@p7 output,'2017-04-07 15:16:26.4200000','3147'
select @p1, @p2, @p5, @p6, @p7

However, for other tables the command sp_prepexec is called instead, as follow:

declare @p1 int
set @p1=NULL
exec sp_prepexec @p1 output,N'@P1 datetime2,@P2 varchar(128)',N' EXEC MySyncProd2 @LastDownload = @P1, @idMobileDB = @P2','2017-04-07 15:16:26.4200000','3147'
select @p1

Questions:

1. Why sp_cursorprepexec is called instead of sp_prepexec?
2. Is this warning an error or Mobilink's fault? If not, can I get rid of it?

[Former Member]

That entry is being classified as a warning 10050 so you could consider disabling the reporting of it with the -zwe switch but it is such a generic warning that I would be a little shy to do that just in case other warnings that may be important also get masked out.

In another customer I am seeing that also with and I don't believe it makes much of a difference whichever MSS procedure the MSSQL ODBC driver is picking here. Both are returning the result set; which is what really matters here.

As you have already noted, it very well could have something to do with blob/clob types. I haven't analyzed this much deeper yet myself but if your are concerned about this, an ODBC trace could identify if the MobiLink server is doing anything much different here. One possibility here, could be the use unbound columns ... and SQLGetData calls; however that gets mapped to SQL Server ODBC procedures would need to be determined from the 2 traces (ODBC and SQL Server). That would be something you would naturally need to do with such long types anyway.

I unfortunately don't yet know of any way to quiet this down or avoid it on the Microsoft RDBMS side. To me it feels very much like an implicit cursor type changed warning but it does seemed to have come up just in recent version of MSS.

Hope that helps some.

[VolkerBarth]

Very wild guess: May this also have to do with the fact that the MS SQL Native Client Driver does not support fetching long data in random order:

The SQL Server Native Client ODBC driver does not support using SQLGetData to retrieve data in random column order. All unbound columns processed with SQLGetData must have higher column ordinals than the bound columns in the result set.

(IIRC, that has also been the case with the older SQL Server ODBC driver.)

[Breck_Carter]

Clearly Microsoft believes it is sometimes (often?) easier to document a software bug than fix it... imagine living in a world where you have to worry about column ordering! 🙂

[VolkerBarth]

Wellll, you might do a very wild guess about why I was aware of that feature/bug... - a very old workaround in some ancient ODBC fetch wrapper code:)

[Former Member]

Thanks so much for your explanation @Nick Elson, very clear for me! I don't feel like changing the implementation just to remove the warning. But please, let me know if you guys discovery something else.