Configuring SQL Server Reporting Services with Teradata to Allow Use of DDL for Volatile Temp Tables as Embedded SQL in SSRS

Third Party Software

Configuring SQL Server Reporting Services with Teradata to Allow Use of DDL for Volatile Temp Tables as Embedded SQL in SSRS

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

4 REPLIES

Re: Configuring SQL Server Reporting Services with Teradata to Allow Use of DDL for Volatile Temp Tables as Embedded SQL in SSRS

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!!

Re: Configuring SQL Server Reporting Services with Teradata to Allow Use of DDL for Volatile Temp Tables as Embedded SQL in SSRS

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.

N/A

Re: Configuring SQL Server Reporting Services with Teradata to Allow Use of DDL for Volatile Temp Tables as Embedded SQL in SSRS

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.

Teradata Employee

Re: Configuring SQL Server Reporting Services with Teradata to Allow Use of DDL for Volatile Temp Tables as Embedded SQL in 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