No Target OLEDB available when using Stored Procedure with Dynamic Input

[klturi421]

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

[Felipe_Ribeir0]

Hi @klturi421 

You can use this procedure inside of the Pre SQL Statement, and then do the select inside of the Table or Query field. I did this recently using this post as reference:

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/No-target-OLEDB-object-available/m-p/3...

If you need to do this for multiple procedures, the best way to change the Pre SQL Statement dynamically is by using a macro:

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Use-Dynamic-Input-with-Pre-SQL-Stateme...

[klturi421]

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.

[Namshad]

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.

[Guilherme-d]

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.