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
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:
If you need to do this for multiple procedures, the best way to change the Pre SQL Statement dynamically is by using a macro:
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.
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 )