No target OLEDB object available?

[SHAHI01]

I'm running the attached query in the Input  but getting an error with No target OLEDB object available... Can anyone point me to some leads?

[HannahSinclair]

Although from your query, you are not creating a stored procedure there is a blog post on them written by @AlexKothat mentions your error (https://community.alteryx.com/t5/Engine-Works-Blog/Troubleshooting-Stored-Procedures/ba-p/13355). @awilkey responded (https://community.alteryx.com/t5/Engine-Works-Blog/Troubleshooting-Stored-Procedures/bc-p/20807#M419) with a similar issue in the Input Data tool and said explicitly creating the temp tables in the query fixed their issue.

Hopefully that helps!

Hannah Keller

Software Developer

Alteryx, Inc.

[PEliason]

I'm getting the same error as the original poster.  It would be nice if Alteryx was able to execute SQL Scripts with temp tables. I don't see a need to have a stored procedure, nor do I have permissions to create stored procedures in my environment, so the suggestion won't work for me.  In the example below, I am trying to output the last sql statement (the select of the top 50 rows).  Here's my SQL:

if OBJECT_ID('tempdb..#mytemp') is not null
drop table #mytemp;

CREATE TABLE #mytemp
(LeadId uniqueidentifier,
leadstate VARCHAR(40),
PRIMARY KEY (LeadId)
);

insert into #mytemp
select top 100 leadid, 'a' from CRMReporting..Lead;

select top 50 * from #mytemp;

[RodL]

I was able to have an Input Data tool bring in data from a temp table I created.

I basically used the same code you used...

In the Pre SQL Statement dialog...

if OBJECT_ID ('#mytemp') is not null
drop table #mytemp;

CREATE TABLE #mytemp
(Store varchar(5),
Item smallint,
Required smallint);

insert into #mytemp
select top 10 * from "Demand"

In the SQL Editor...

SELECT top 15 * FROM dbo."#mytemp"

And then for good measure, I included...

drop table #mytemp

...in the Post SQL Statement dialog...although technically the system should take care of this.

I'm wondering if the issue is that there is no #mytemp table to include in the Table Select? What I ended up doing was manually typing in the Select statement in the SQL Query tab of the editor. (BTW...if you test the query from there it will fail, but runs just fine during run time.)