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
Hello Everyone,
I'm posting this question here because I did not found 'New Thread' option here. Actually I'm looking for a third party software tool which having functionality to import data from file and could some modification in it. After that I could export this data in a file (file can be txt, csv, xls etc). Please resolve my problem as soon as possible.
I hope you will have understood my question. Thanks in advance!!
Hi Chandimal,
Yes! There are lots of famous tool in the market which having functionality to perform import and export data from file. Some famous tool like MindStick DataConverter, AutoConverter etc. in the market which perfoming this action. MindStick DataConverter is a free charge tool so you could easily download it.
MindStick DataConverter Downloads Link: http://www.mindstick.com/Product/ProductDC.aspx
I hope it will be useful for you.
As for the original thread, I have the same issue, same error message. Only difference I guess is that my SS destination is SSAS Tabular, not SSRS.
Not sure if VT works with SSRS, but I know a MACRO with a Global Temp (GT) works with SSRS report server projects .. if thats an option