Temp Tables in Stored Proc cause error in Creating DataObject

[ousoonerjoe]

I have a stored Procedure that uses 2 temp tables to clean and generate the return result set. However, SSRS (Using Server-Side) generates the error:

[COLOR=red]There is an error in the query. Invalid object name 'BAN010_DeleteMe'.
Invalid object name 'CleanBan10'.[/color]

Is there some way around this, or have I just hit a limitation of SSRS? The following is the procedure:

--ALTER PROCEDURE cst_INSERT_BAN010L
--AS

DECLARE @Sql		VARCHAR(1000);
DECLARE @Ban010Path	VARCHAR(255);
SET @Ban010Path = 'C:\Data\TestData\Ban010\cleared.csv';

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE [ID] = OBJECT_ID(N'BAN010_DeleteMe') AND OBJECTPROPERTY([id], N'IsTable') = 1)
DROP TABLE BAN010_DeleteMe;

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE [ID] = OBJECT_ID(N'CleanBan10') AND OBJECTPROPERTY([id], N'IsTable') = 1)
DROP TABLE CleanBan10;

CREATE TABLE BAN010_DeleteMe (
	AsOfDate		DATETIME NOT NULL,
	BankID			VARCHAR(20) COLLATE SQL_EBCDIC037_CP1_CS_AS NOT NULL DEFAULT '0',
	AcctNbr			VARCHAR(35) COLLATE SQL_EBCDIC037_CP1_CS_AS NOT NULL DEFAULT '0',
	AcctName		VARCHAR(50) COLLATE SQL_EBCDIC037_CP1_CS_AS NOT NULL DEFAULT '',
	BAICode			INT NOT NULL,
	TransDesc		VARCHAR(50) COLLATE SQL_EBCDIC037_CP1_CS_AS NOT NULL DEFAULT '',
	Currency		VARCHAR(10),
	ValueDate		DATETIME NOT NULL,
	DebitAmt		DECIMAL(10,2) NOT NULL DEFAULT 0,
	CreditAmt		DECIMAL(10,2) NOT NULL DEFAULT 0,
	AVAILImmed		DECIMAL(10,2) NOT NULL DEFAULT 0,
	AVAIL1Day		DECIMAL(10,2) NOT NULL DEFAULT 0,
	AVAIL2PlusDay	DECIMAL(10,2) DEFAULT 0,
	BankRef			VARCHAR(30) COLLATE SQL_EBCDIC037_CP1_CS_AS DEFAULT '0',
	CustomerRef		VARCHAR(30) COLLATE SQL_EBCDIC037_CP1_CS_AS DEFAULT '0',
	Comments		VARCHAR(1000) COLLATE SQL_EBCDIC037_CP1_CS_AS DEFAULT '');

SET @Sql = 'BULK INSERT BAN010_DeleteMe
	FROM ''' + @Ban010Path + '''
	WITH (
		FIRSTROW = 2, 
        MAXERRORS = 0, 
		FIELDTERMINATOR = '','', 
        ROWTERMINATOR = ''\n'' )';

EXEC (@Sql);

SET @Sql = ''

UPDATE BAN010_DeleteMe
SET BankID = .dbo.TRIM(BankID),
	AcctNbr = dbo.TRIM(AcctNbr),
	AcctName = dbo.TRIM(AcctName),
	TransDesc = dbo.TRIM(TransDesc),
	Currency = dbo.TRIM(Currency),
	BankRef = dbo.TRIM(BankRef),
	CustomerRef = dbo.TRIM(CustomerRef),
	Comments = dbo.TRIM(Comments)

UPDATE BAN010_DeleteMe
SET CustomerRef = 0 
WHERE CustomerRef = ''

SELECT *, [CheckNum] = 0
INTO CleanBan10
FROM BAN010_DeleteMe;

DROP TABLE BAN010_DeleteMe;

UPDATE CleanBan10
SET CheckNum = CASE ISNUMERIC(CustomerRef)
				WHEN 1 THEN CAST(CustomerRef AS INT)
				WHEN 0 THEN 0 END;


SELECT  bd.AsOfDate, bd.BankID, bd.AcctNbr, bd.AcctName, bd.BaiCode, bd.TransDesc, bd.ValueDate, 
		bd.DebitAmt, bd.CreditAmt, bd.AvailImmed, bd.Avail1Day, bd.Avail2PlusDay, bd.BankRef,
		bd.CustomerRef, bd.CheckNum, ac.apchk_check_no, pc.prchk_ck_no
FROM CleanBan10 bd
	LEFT JOIN TmpApchk ac ON ac.apchk_net_amt = bd.DebitAmt 
			AND ac.apchk_account_no = bd.AcctNbr 
			AND ac.apchk_check_no IN (SELECT CheckNum FROM CleanBan10)
	LEFT JOIN TmpPrChk pc ON pc.prchk_ck_no = bd.CheckNum
WHERE BAICode = 475
ORDER BY CheckNum

DROP TABLE CleanBan10;

"If I were to wake up with my head sewn to the carpet, I wouldn't be more surprised than I am right now.

 

[ousoonerjoe]

Found the work around.

Allow the tables to stay persistent while you create the DataSet. It will then see the tables and ignore the "invalid object" issues. The temp tables can now be DROPped, and all Refreshes after that seem to have no issues. Report runs as expected now--creating 2 tables for importing and cleaning and then dropping them after kicking out the recordset.

"If I were to wake up with my head sewn to the carpet, I wouldn't be more surprised than I am right now.