SqlNumResultCols returns count as 0 for CURSOR queries while using FreeTDS driver, works in Microsoft's ODBC driver

[kowsikbabu]

We have used the native SqlNumResultCols() method to run a query that uses CURSOR but the result is returned wrongly as 0 for FreeTDS driver, but works correctly in Microsoft's ODBC Driver.

This is the query used:

DECLARE db_cursor CURSOR FOR SELECT name FROM sys.databases WHERE state_desc = 'ONLINE' AND name NOT IN ('master','tempdb','model','msdb'); DECLARE @DatabaseName NVARCHAR(128); DECLARE @outset TABLE([DATABASENAME] varchar(100),[TABLENAME] varchar(100));OPEN db_cursor;FETCH NEXT FROM db_cursor INTO @DatabaseName;WHILE @@FETCH_STATUS = 0 BEGIN DECLARE @command nvarchar(1000) = ' USE ' + QUOTENAME(@DatabaseName) + ';SELECT DB_NAME() AS databasename,ISNULL(''['' + SCHEMA_NAME(schema_id) + ''].['' + name + '']'', ''No Tables'') AS tablename FROM sys.tables UNION ALL SELECT DB_NAME() AS databasename, ''No Tables'' AS tablename WHERE NOT EXISTS (SELECT 1 FROM sys.tables)';INSERT INTO @outset EXEC (@command);FETCH NEXT FROM db_cursor INTO @DatabaseName; END CLOSE db_cursor; DEALLOCATE db_cursor; SELECT DISTINCT databasename, tablename FROM @outset ORDER BY databasename,tablename;

Here's a screenshot that highlights the issue:

When ODBC driver is used:

When FreeTDS driver is used:

Kindly help us with this issue.

FreeTDS Version: Current Master branch (I think 1.04.12 in registry) SQL server Version: 2019 OS: Windows 10

[kowsikbabu]

@freddy77 we also saved the query as a stored procedure and tried executing it, we faced the same issue again, works in Microsoft ODBC driver and doesn't work in FreeTDS.

[kowsikbabu]

@freddy77 to add more information:

We also rewrote the query to avoid using cursor and ran this query:

SET NOCOUNT ON; CREATE TABLE #AllTables (DatabaseName NVARCHAR(255),TableName NVARCHAR(255));DECLARE @SQL NVARCHAR(MAX) = ''; SELECT @SQL = @SQL + 'INSERT INTO #AllTables (DatabaseName, TableName) SELECT ''' + name + ''', t.name FROM [' + name + '].sys.tables t UNION ALL SELECT ''' + name + ''', ''No Tables'' WHERE NOT EXISTS (SELECT 1 FROM [' + name + '].sys.tables);' FROM sys.databases WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb'); EXEC sp_executesql @SQL; SELECT * FROM #AllTables ORDER BY DatabaseName, TableName; DROP TABLE #AllTables;

This one doesn't use the CURSOR, but still this doesn't return value in SqlNumResultCols. SET NOCOUNT ON in the query is optionally used to make it work when Microsoft's ODBC Driver is used.

[0x00000oak]

Hello, I am having a similar problem. The application is getting odbc error code 100 SQL_NO_DATA when executing stored procedures that contains SET NOCOUNT ON using FreeTDS. The same procedures work just fine with Microsoft ODBC driver 17. If I change the procedure to NOCOUNT OFF as a workaround it works with FreeTDS as well however I would like to keep the NOCOUNT option set.

[freddy77]

I probably could use this to reproduce the issue.

[0x00000oak]

I do not have source code available for the app but I can share the part of the procedure that causes the behavior. Anyway the app is calling it like this: {?=CALL _Procedure (%d)} The procedure that seems to not return anything looks something like this:

ALTER PROCEDURE [dbo].[_Procedure]
    @Param INT
AS

SET NOCOUNT ON

SET @NewID = 0

BEGIN TRANSACTION
    -- Bunch of inserts here
    SET @NewID = @@IDENTITY
    -- Some more inserts here also
COMMIT TRANSACTION

RETURN @NewID
GO

The above procedure works as expected and performs all the tasks it needs to but when using FreeTDS the app does not get any results but instead complains about ODBC error code 100. Using the Microsoft driver the app gets the return value without any errors. If I unset nocount in the procedure just before it needs to return something like below it works as expected and the app gets the return value and behaves the same using FreeTDS as well as Microsoft driver.

ALTER PROCEDURE [dbo].[_Procedure]
    @Param INT
AS

SET NOCOUNT ON

SET @NewID = 0

BEGIN TRANSACTION
    -- Bunch of inserts here
    SET @NewID = @@IDENTITY
    -- Some more inserts here also
COMMIT TRANSACTION

SET NOCOUNT OFF

RETURN @NewID
GO

Could this be related to Microsoft handling SQL_NO_DATA differently? Just a wild guess I have little knowledge on how ODBC drivers work.