Hi Debbie,
Like Chad, I am having some similar issues on my end. When using 3.8 to execute automations, I had MS SQL queries that would store items in temp tables and then produce a final SQL statement comprised of those temp tables. I would use Select To File because these results needed to be exported and distributed. Leveraging temp tables was a housekeeping measure for myself as these wouldn’t leave a nasty footprint of physical tables on the server. I’ve since moved over to 4.1 on my reporting machine and this has killed all of my automations containing those queries. I now have to either move a portion of this query to an execute script command, while adding an additional steps of inserting my final SQL statement in to a physical table and then dropping after export, or manually convert all of my temp table logic to CTE’s which can slow my run times down substantially. Either way, this just seems to produce a lot of unnecessary work on my end. If I’m missing something, please let me know as I really don’t want to change all of my queries for all of my automations.
An example of the queries that functioned without issue in 3.8 automations:
--------------------------------------------------------------------------------------------------------------------------------------------------------
Select To File:
if object_id(‘tempdb…#tmp_date’) is not null drop table #tmp_prepdate
select distinct getdate() as current_date into #tmp_date
if object_id(‘tempdb…#tmp_results1’) is not null drop table #tmp_results1
select * into #results1 from table1 where table1.action_date in (select current_date from #tmp_date)
if object_id(‘tempdb…#tmp_results2’) is not null drop table #tmp_results2
select * into #results2 from table2 where table2.action_date in (select current_date from #tmp_date)
select r1., r2.
from #results1 r1
inner join #results2 r2 on r1.pk = r2.pk
drop table #tmp_date
drop table #tmp_results1
drop table #tmp_results2
--------------------------------------------------------------------------------------------------------------------------------------------------------
It appears I now have to do this for automations to work in 4.1
Execute Script:
if object_id(‘tempdb…#tmp_date’) is not null drop table #tmp_prepdate
select distinct getdate() as current_date into #tmp_date
if object_id(‘tempdb…#tmp_results1’) is not null drop table #tmp_results1
select * into #results1 from table1 where table1.action_date in (select current_date from #tmp_date)
if object_id(‘tempdb…#tmp_results2’) is not null drop table #tmp_results2
select * into #results2 from table2 where table2.action_date in (select current_date from #tmp_date)
if object_id(‘database1.dbo.tmp_final_results’, ‘u’) is not null drop table database1.dbo.tmp_final_results;
select r1., r2.
into tmp_final_results
from #results1 r1
inner join #results2 r2 on r1.pk = r2.pk
drop table #tmp_date
drop table #tmp_results1
drop table #tmp_results2
Select To File:
select * from database1.dbo.tmp_final_results
Execute Script:
drop table database1.dbo.tmp_final_results
--------------------------------------------------------------------------------------------------------------------------------