SQL stored procedure is not working as intended in alteryx

Question

Hi all,

I have a store procedure that is coded to help update an archive table known as "archive_test"

but when i run the stored procedure in the input tool it doesnt seem to be updating the archive "Archive_test"

do any of you guys know of the solution ? is there something im missing out ?

i was told that to place a dummy select infront for the stored procedure to work but yet its not working

seinchyiwoo · Answer

Hey Daniel,

The Input Tool is always expecting a record to be return and hence, if you don't have data return from the SP execution, it will give you that error.

A workaround can be found in this article:

https://community.alteryx.com/t5/Engine-Works/Troubleshooting-Stored-Procedures/ba-p/13355/tab/html

Alternatively, for what you want to do in your stored proc, you can easily create a workflow do exactly the same thing:

1. Input data - connect to [server].[dbo].[tbl_logfile_errors]

2. Ingest to [server].[dbo].[archive_logfile_errors] using Output tool

3. Create a PostSQL statement in the Output data tool to delete [server].[dbo].[tbl_logfile_errors]: https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Alteryx-Pre-Post-SQL-Statements/ta-p/151965

Cheers,

Seinchyi

DanielKoh · Answer

the stored procedure code as below :

INSERT INTO [server].[dbo].[archive_logfile_errors]
SELECT * ,cast(getdate() AS DATE) as ARCHIVE_DATE

FROM [server].[dbo].[tbl_logfile_errors]

DELETE FROM [server].[dbo].[tbl_logfile_errors]

if i run only the EXEC SP they say no columns returned

seinchyiwoo · Answer

Did you run it with just EXEC [Stored Proc]? or still with the 1st line of [dbo].xxx in the SQL editor?

By right it should run successfully with just EXEC.

I guess we will need to look into what your stored procedure is actually to troubleshoot further.

Can you copy and paste your query here or a screenshot of your script to give us an idea on what you have in your stored proc?

Also, you might want to take a look at this Stored Proc FAQ first to see if you are connecting correctly: https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Stored-Procedures-FAQ/ta-p/376886

Best,

Seinchyi