Does anyone have any experience getting SSRS to work with Teradata such that something like the following code
can be embbeded within SSRS and execute without error?
CREATE VOLATILE TABLE Vtab(n INT)
ON COMMIT PRESERVE ROWS;
INSERT INTO Vtab(n)
SELECT TOPE 50 memberid
FROM member;
SELECT *
FROM member;
Drop TABLE Vtab;
When we try to run this now, we get the error:
[Teradata Database][3932] Only an ET or null statement is legal after a DDL Statement.
This code runs within SQL Assitant but not in SSRS.
We are thinking that this may have something to do with the ODBC attribute SQL_ATTR_AUTOCOMMIT, but have been unsuccessful (so far) in resolving it.
We had originally tried using a stored procedure to do this, and then calling it from SSRS but we could not get the following stored procedure to compile:
REPLACE PROCEDURE My_Proc()
DYNAMIC RESULTS SETS 1
SEC 1: BEGIN
SEC 2: BEGIN
CREATE VOLATILE TABLE Vtab AS (
SELECT TOP 10 memberid
FROM member
) WITH DATA ON COMMIT PRESERVE ROWS;
END SEC2;
SEC 3: BEGIN
DECLARE cur1 CURSOR WITH RETURN ONLY FOR
SELECT * FROM Vtab;
OPEN cur1;
END SEC3;
END SEC1;
Error Msg: Missing/Invalid SQL Statement E(3807): Object Vtab does not exist
When we removed the BEGIN/END blocks for SEC2 and SEC3 that error was replaced with a CURSOR error.
Can anyone provide some guidance on how to set this up correctly?
Thanks
