SQL stored procedure is not working as intended in alteryx

[DanielKoh]

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]

When you click on "Test Query" in the connection window, does it run successfully?

[DanielKoh]

no it says error : no columns returned 😞

[seinchyiwoo]

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

[DanielKoh]

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]

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-... 

Cheers,

Seinchyi