This is one of those questions that will seem so easy, but it’s not working.
I have a SQL with a couple of insert statements and finally a select statement like the following
INSERT INTO A select 1 from dual;
INSERT INTO B select 1 from dual;
select * from A where A = B
When I run the query it executes and returns results. When I use any of the export options to automate and schedule the query it always returns 0 results. I am using the “To File” option in the report automation and I can’t figure out why it will not work. If I only use a select statement in the report automation it works just fine, but it’s either the multiple statements or inserts that are causing it to not produce results.
If anyone could provide any insight I would appreciate it. I’ve been trying to figure this out the past week and I’ve had no success. Thank you in advance.
I believe the select to file just grabs and executes the first statement.
Try putting all of your inserts into an ExecuteSQL activity followed by commit. Then put the select in a select to file all by itself.
I wish that would have worked. It made sense and I thought it would. When the automation execute script runs all of the queries return 0 records per the log. I would expect it to be reading “47 rows added”, etc.
This e-mail, including attachments, may include confidential and/or
proprietary information, and may be used only by the person or entity
to which it is addressed. If the reader of this e-mail is not the intended
recipient or his or her authorized agent, the reader is hereby notified
that any dissemination, distribution or copying of this e-mail is
prohibited. If you have received this e-mail in error, please notify the
sender by replying to this message and delete this e-mail immediately.
is this table a temp table? what is the database type.
Yes, they are Global Temporary Tables in Oracle.
This e-mail, including attachments, may include confidential and/or
proprietary information, and may be used only by the person or entity
to which it is addressed. If the reader of this e-mail is not the intended
recipient or his or her authorized agent, the reader is hereby notified
that any dissemination, distribution or copying of this e-mail is
prohibited. If you have received this e-mail in error, please notify the
sender by replying to this message and delete this e-mail immediately.
Does the temporary table persist beyond the session? We close the session after every activity. Can you post example of how your are creating the table? Do you use preserve rows?
Yes, the GTT exists after every session. The table itself doesn’t go anywhere unless I drop it. I’ve got it set to preserve rows as well. It’s just odd because it works as expected when you execute the script manually, just not in the report automation.
If it wasn’t working when you manually execute I would totally understand it not working with the report automation, but that is not the case. I am stumped and it’s been very frustrating.
Some background, we were using Sybase up until a couple of weeks ago and it worked fine on Sybase. Once we migrated to Oracle then it stopped working.
Did you add a Commit after all of your inserts? The Oracle provider is the only one that does not have auto commit on as default,.
Every insert has a COMMIT as well. The log just keeps saying “Query returned 0 rows” on all of the inserts, so it seems like it’s ignoring them.
Just for reference, here is the basic outline of my execute query:
insert into Global Temporary Table
select * from internal_table;
COMMIT;
insert into Global Temporary Table
select * from internal_table;
COMMIT;
DELETE from Global Temporary Table;
Then my To File query is a simple SELECT * from Global Temporary Table.
My report automation has an Execute SQL and a To File SQL, both populated with scripts above as needed.
Can you send me your automation script so I can take a look? debbie.peabody@quest.com
I spent some time on this today. I found out two things to make this work.
-
For Oracle you need to make a Global temp table with preserve rows option. I am not sure what connection type you are using. But this was one of the things that was holding me up on this.
-
When using the Execute SQL activity it does execute the whole script but if you also want to export to Local Storage or publish it only takes the first statement.
-
If you separate your Execute SQL activity and Select to file you need to turn on the option to use "Connection on Startup". This option will use the connection opened on start up of TDP and continue to use this for all activities. If you don't have this option on the connection gets closed for each session and you will not have any rows.
I do think that the Execute SQL and Select to File should use the last statement and will enter a task for this
So I am able to do what you want with both of these items. i attached screen shot of this option and sample script.
Script_MultiR.tas (18.1 KB)
We entered QAt-8316 and have done some work to support your usecase. It will now execute a script and only use the last statement to export the data. You can try this fix in the next TDP 4.1 Beta. (ETA Oct 30)
