Insert/Update Query Not Working in Select to File Automation 4.1

Team,

I’m wondering if you have seen an issue in 4.1 where update/insert queries are not working in 4.1 Select to File Automation?

I do see on the release notes the mention of “In compatibility mode, the ODBC driver does not return enough information for the CommandBuilder to correctly generate update or insert statements.” as being an issue.

BUT, I wanted to see if this applied to my specific issue.

We use an I-Series ODBC connection to DB2. We run daily update queries. In previous versions to automate, we’ve been able to accomplish this using the Select to File activity just fine.

Now, when converting to 4.1, the select to file activity errors and I get the message back saying “Unable to parse any SELECT statements from the SQL query.”

Just so you know though, on 4.1 when running the same exact update query in the edit window, the query works just fine.

I had to revert my update automation tasks at this point back to 4.0 to get them back to working for the time being.

I’m wondering if there’s a known issue and if not, if I should submit this as a support case?

Also, if it is a known issue, is there any type of workaround on 4.1?

Please let me know if you need anything else from myself.

Thanks!

There is a change we made for another user. You can execute multiple statements in the Select to File and Execute SQL automation activities but if you want to export to Excel or local storage the LAST SQL needs to be a select command. That is what it is complaining about. if you do not have any selects, only inserts, updates or deletes they should be done in the Execute SQL activity.

Can you provide more information on how you are using the select to file with just an update statement? There is no result set from an update statement. So using Select to File doesn’t make sense to me.

Debbie,

I’ll be honest. Until just now, I didn’t even know was the Execute SQL script was. Therefore up to this point, I didn’t think there was any other option for update queries outside of a select to file activity. Therefore, I was just using that and sending no results to a dummy file out on the network in the previous versions. With that said, I just converted the query over on 4.1 over to this Execute Script activity and it runs perfectly. I’m with you, using this is much more efficient than select to file, I just didn’t know how it worked and that there was another option. Thank you so much for the heads up and I’ll use Execute SQL activities going forward for automated update/insert queries.

Thanks again!

I clicked on the Unsubscribe from Toad Data Point Forum notifications altogether, but I still
continuously receive emails.

Please unsubscribe me.

Thanks.

Hongxia

From: chad.king [mailto:bounce-chadking@toadworld.com]

Sent: Thursday, January 19, 2017 10:33 AM

To: toaddatapoint@toadworld.com

Subject: RE: [Toad Data Point - Discussion Forum] Insert/Update Query Not Working in Select to File Automation 4.1

RE: Insert/Update Query Not Working in Select to File Automation 4.1

Reply by chad.king

Debbie,

I’ll be honest. Until just now, I didn’t even know was the Execute SQL script was. Therefore up to this point, I didn’t think there was any other option for update queries outside of a select to file activity. Therefore, I was just using that and sending no results to a dummy file out on the network in the previous versions. With that said, I just converted the query over on 4.1 over to this Execute Script activity and it runs perfectly. I’m with you, using this is much more efficient than select to file, I just didn’t know how it worked and that there was another option. Thank you so much for the heads up and I’ll use Execute SQL activities going forward for automated update/insert queries.

Thanks again!

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad Data Point Forum
notifications altogether.

Toad Data Point - Discussion Forum

Flag
this post as spam/abuse.

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

--------------------------------------------------------------------------------------------------------------------------------

i tweaked your sql a bit and used in a Select to file in 3.8, 4.1, 4.2, and TDP 4.3 beta. They all work for me. I do remember a time when we only executed the last statement but this looks like it is fixed. Can you download TDP 4.2.1 or the latest 4.3 Beta and confirm this works as expected?

Thanks Debbie!

I will reach out to our IT dept to see if we can get 4.2 packaged to test. I appreciate you looking in to this and your feedback!

-Chris

Hi Debbie,

Chad was able to get 4.2 packaged and IT just installed it on my machine. Did a quick test in both 4.1 & 4.2 using a query similar to:

if object_id('tempdb..#tmp') is not null drop table #tmp;

select top 100

id

into #tmp

from table

where field1 = 'A'

select * from #tmp

Like before, this query failed to process when inserted in to a Select To File command in 4.1's Automation. Ran the same thing in 4.2 and the result was successful. I provided two quick screen grabs for both 4.1 & 4.2's Automation Log Results. You can see the failure on the left (4.1) and success on the right (4.2). Thanks again for your help! - Chris