No Target OLEDB available when using Stored Procedure with Dynamic Input

Question

I am attempting to have several Stored Procedures run using a Dynamic Input tool When running the workflow I get an error stating "Error: No target OLEDB object available. in Query EXEC DATA_LOCK_IN_QUALITY." From what I have gathered from other posts it seems like a temporary table is needed however, as shown with the Stored Procedure below, it does not utilize temp tables. How can I work to get this stored procedure to work within Alteryx? I feel like there is something that I'm missing but cannot put my finger on it. One thing I will add is that I know that I can use inputs/outputs to replicate, however, I have other stored procedures that are very similar and do not want to have to re-create each procedure within Alteryx. Please let me know if there is anything else I can provide to assist in resolving. USE Incentives GO /****** Object: StoredProcedure [dbo].[DATA_LOCK_IN_QUALITY] ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[DATA_LOCK_IN_QUALITY] -- Add the parameters for the stored procedure here @EDVAR DATE --ONLY END DATE IS NEEDED AS BEGIN --SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; --DECLARE PAY PERIOD DATES: DECLARE @ed DATE --END DATE SET @ed = @Edvar -- END DATE --FIRST, DELETE FROM THE TABLE ANY EXISTING DATA FOR THE PERIOD BEING LOCKED-IN: DELETE FROM Incentives.[dbo].[RiAvgMtd] WHERE [DATE] = @ed --COPY ALL 30-DAY ROWS WITHIN DATE RANGE FROM THE VIEW: INSERT INTO Incentives.[dbo].[RiAvgMtd] SELECT * FROM [ScarboroughSANAQuality].[dbo].[vw_RiAvgMtd] WHERE [DATE] = @ed AND DAYS = 30 END GO

Guilherme-d · Answer

What worked for me was: In the "Run Pre-Sql" option I wrote the code "SET NOCOUNT ON". But if you are the owner of the procedure, you can code it directly there.

Namshad · Answer

Hi @Felipe_Ribeir0 ,

I have a stored procedure that is using SP_EXECUTESQL at the end to get data from a select query as in below example. While I try to run this SP in Alteryx input tool or in db tool I'm getting errors like No columns retured  ( for ODBC connection ) or No oledb target available (for oeldb connection )

SET @Query = 'Select * Form Table1 WHERE Period=' + @TimePeriod + ' ;EXEC SP_EXECUTESQL @Query;

For some reasons I can't make the stored procedure to load the results into a table and then run the SP in pre sql  and then take data from that table in table query.

Do you know any other solution to this.

klturi421 · Answer

I believe I have figured out the issue.  Per #2 from this post, the procedures are not returning any data so I am "tricking" the procedures into loading data by adding a select statement after the stored procedure.