Error: Cannot find table 0. SmartConnect – OLEDB/SQL Data Source

You encountered an error “Cannot find table 0” while executing sql script through the OLEDB/SQL connection after clicking ‘Validate’ and/or ‘Preview’.

The “Cannot find table 0” error usually occurs when using OLEDB with ADO.NET or similar data access layers, and it typically means that the query didn’t return any result sets (EMPTY SELECT), but SmartConnect code is trying to access the first table in the result set collection and could not fill it via data table.

Alternatively, using a SQL connection (as shown below), there’s NO ISSUE, because OLEDB with ADO.NET behaves differently from a standard SQL connection, similar to how it runs manually in SQL Server Management Studio (SSMS).

Root Cause: The issue is primarily caused by the use of a #temp table 
For Example, consider the simple code snippet below, which retrieves account data and inserts it into a #temp table—this will result in the same error.

select ACTNUMST into #LIMSGL from GL00105 where ACTNUMST = '000-7040-01'
select * from #LIMSGL
drop table #LIMSGL

Solution:  If you choose to use an OLEDB connection, you would consider modifying sql script slightly to use a table variable @temp instead of a temporary table #temp, among several other alternatives such as CTEs, derived tables, a permanent staging table, or a global temporary table (##temp).

Pros For Table Variable:

• No Need for Drop Table Statement
• Session-local, scoped to the batch/stored procedure.
• Avoids many issues with temp tables.

For Example: same example with revised format.

DECLARE @LIMSGL TABLE 
(
    ACTNUMST CHAR(201) NULL
);
INSERT INTO @LIMSGL select ACTNUMST from GL00105 where ACTNUMST = '000-7040-01';
select * from @LIMSGL;

In a few occurrences, if you encountered the same error when using a @temp table in complex logic involving cursors, UNION ALLand dynamic SQL execution with sp_executesql. This issue occurred specifically with SQL Data Source, OLEDB Data Source, and GP Data Source Query connections.

For Example:

DECLARE @Databases TABLE (DatabaseName NVARCHAR(255));
DECLARE @SQL NVARCHAR(MAX);
DECLARE @DatabaseName NVARCHAR(255);
DECLARE @UnionSQL NVARCHAR(MAX);

-- Populate the table with the names of the databases you want to query
INSERT INTO @Databases (DatabaseName)
VALUES ('ONE'), ('TWO');

-- Initialize the UNION ALL SQL
SET @UnionSQL = '';

-- Cursor to iterate through each database
DECLARE db_cursor CURSOR FOR
SELECT DatabaseName
FROM @Databases;
OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @DatabaseName;

WHILE @@FETCH_STATUS = 0
BEGIN
	-- Construct the SQL query for the current database
	SET @SQL = '
		SELECT 
		CASE 
			WHEN ''' + @DatabaseName + ''' = ''ONE'' THEN ''ONE''
			WHEN ''' + @DatabaseName + ''' = ''TWO'' THEN ''TWO''
			ELSE ''' + @DatabaseName + '''
		END as ''DBCode'', *
		FROM [' + @DatabaseName + '].dbo.PM00200
		'; -- Fully qualify the table name'' 

IF @UnionSQL = ''
	BEGIN
		SET @UnionSQL = @SQL;
	END
	ELSE
	BEGIN
		SET @UnionSQL = @UnionSQL + ' UNION ALL ' + @SQL;
	END
	FETCH NEXT FROM db_cursor INTO @DatabaseName;
END;

CLOSE db_cursor;
DEALLOCATE db_cursor;

-- Execute the final UNION ALL SQL statement
EXEC sp_executesql @UnionSQL;

Have a question? Please reach out to us at support@eonesolutions.com