Automation: Database Connection -> Execute Script -> Export to local access table

Toad for SQL Server
1

Hello,

I am trying to create an automation script that connects to a database, runs a query, and then exports the results to a local MS Access table. I have no issues with the database connection and executing the script. My issue comes when I try and export.

I am still relatively new at this so I might be going about this the wrong way, but here is how I am attempting (and failing) to export:

  1. Add Export Wizard action after the execution script
  2. Script query result is placed into ##DEM_RawOutput
  3. Create a new export template
  4. Export objects list: Add -> Query - > Code = Select * from ##DEM_RawOutput
  5. Receive an error: "Schema read error: Invalid object name '##DEM_RawOutput'.
    Export1.png

Export2.png
Export2.png880×560 16.1 KB

Any ideas what I'm doing wrong?

Thanks!

-Max

2

Hello Max1616,

I believe this is due to the fact that the temp tables (even if double hash ##) are not preserved when it arrives to the next export activity. You have to do create table if not exists in the beginning, export it and at the end run some truncate table or something to clear it. The thing with temp tables in automation is that temp tables are dropped when last connection that used it is closed. And in case of automation it is closed every time the activity finishes its job and the next activity is about to be executed.

Let me know if this helps.

Martin

3

Hi Martin,

That makes sense, but I need to be able to export a query result to a local access table without creating a new table on the SQL server. Would something like this work:

I have only the export wizard. Within the export template I have the select object as the results of a query:

4478.Export2.png
4478.Export2.png1182×1068 43.2 KB

The query has a few temp tables that it creates (#Table) and in the end it selects * from the final temp table. Yet when I run the automation script, it doesn't export anything. Any ideas why this isn't working? How can I get this export to a access database?

Thanks,

Max

4

By isn’t working you mean it is giving you error? Or is it completing successfully but exported rows is 0?

I am using TDP v4.0.0.624 and the following example works for me:

create table #temp (adr varchar(max));
insert into #temp select top 10 address from dev…newaddress;
select * from #temp;

Could you please try this with some of your tables? Also please note that i have to use fully qualified table name (dev.dbo.newaddress) because in my Connection properties i didn’t specify a default database/schema on connect (so its assuming ‘master’ otherwise). Once you try and it works then try to use more statements until you reach point of error / 0 rows exported.

Also please enable Verbose logging in the settings activity so we can see details about what SQL is actually being executed.

thanks, Martin