Alteryx Pre/Post SQL Statements

[DiganP]

Alteryx has the ability to create and query Pre and Post SQL statements. Using this feature is good if you need to create a temporary table, delete or update the table before running the select. With this, you also have the ability to run stored procedures (via exec stored_procedure;) before and after the actual query as well.

Order of operations:

The Pre-SQL statement would run first, then the Table or Query and finally the Post-SQL statement. One thing to note is that Alteryx will not output the results from the Pre/Post SQL statements into the workflow.

Let’s look at an example below:

Pre-SQL:

Drop table [dbo].[Alot_of_data_1]
CREATE TABLE [dbo].[Alot_of_data_1](
[Store Num] [varchar](5) NULL,
[Address] [varchar](25) NULL,
[City] [varchar](25) NULL,
[State] [varchar](2) NULL,
[ZIP] [varchar](5) NULL,
[Lat] [float] NULL,
[Lon] [float] NULL,
[County ] [varchar](64) NULL,
[Region] [char](5) NULL,
[Type] [char](4) NULL,
[Date] Datetime
) ON [PRIMARY];

WAITFOR DELAY '00:00:05';

Table or SQL:

Select * From Alot_of_data_1;
WAITFOR DELAY '00:00:05';

Post-SQL:

INSERT INTO [dbo].[Alot_of_data_1]
([Store Num],[Address],[City],[State],[ZIP],[Lat],[Lon],[County ],[Region],[Type],[Date])
VALUES('1234','1234 irvine drive','Irvine','CA','92627',-85.3,105,'Orange','CA','XY',CURRENT_TIMESTAMP);

What this configuration does:

1. Table Alot_of_data_1 is dropped then created with the specified columns. Delay for 5 seconds.
2. Empty table (Alot_of_data_1) is read into Alteryx Designer. Delay for 5 seconds.
3. Row is inserted into table (Alot_of_data_1) as specified with the timestamp.

Looking at the logs:

 

The Workflow Started at 11:04:10

Pre-SQL Timestamp: 11:04:10

SQL Query Timestamp: 11:04:15

Post-SQL Timestamp: 11:04:20

Since there’s a 5 second delay, the timestamps are 5 seconds off to the next SQL statement.

Note:

Pre-SQL statements will run when the workflow is opened, when a user clicks on the canvas or new tools are added. This can cause your Pre-SQL statements to run which may cause workflows to fail or unnecessary queries to run.

To prevent Pre-SQL from running when opening the workflow or clicking on the canvas:

Within Alteryx Designer > Under Options > User Settings > Edit User Settings there is an option in the "Advanced" tab called "Disable Auto Configure".  If you check that off it will prevent your Pre-SQL from running. However, it also prevents metadata from passing automatically through the workflow, so some tools will get harder to use (for example the Summarize Tool).

Another fix is to add precautions in your SQL Statement. For example, if you have a DROP statement in the Pre-SQL query then you may want to configure your SQL with something along the lines of :

IF EXISTS(SELECT TOP 1 * FROM TABLENAME) DROP TABLE TABLENAME;

Or something like:

IF OBJECT_ID('dbo.Tablename', 'U') IS NOT NULL 
DROP TABLE dbo.Tablename; 

This would check if the table exists before dropping it, which should remove any unnecessary queries to run.

[TommyB]

DiganP - to claryify, in order to not execute the Pre-SQL when the workflow is opened, should the "Disable Auto Configure" box be checked or not-checked?

It appears that this "Disable Auto Configure" option is by default not-checked for me, and I don't want Pre-SQL executed automatically. So I think I'm good to go, right?

On a side note, I think that the automatic execution of Pre-SQL upon opening workflow is undesired behavior. Nothing about the workflow should run until I hit "Run Workflow". This should be fixed in my opinion.

[TommyB]

Is a semi-colon needed when invoking a stored procedure in SQLServer?

When I don't include a semi-colon, there is no error returned in Alteryx Designer so it gives the impression that the procedure ran to completion.

Is there a best-practice recommendation for returning the success status or result of the stored procedure back to Alteryx?

[DiganP]

@TommyB 

1. In order to not execute the Pre-SQL when the workflow is opened, should the "Disable Auto Configure" box be checked or not-checked?

• The "Disable Auto Configure" should be checked off. This will prevent you from running the Pre-SQL executed automatically.

2. As a best practice, Stored Procedures should have a semi-colon. Also, stored procedures should show up in the Stored Procedure tab in the Visual Query Builder. The results in the results tab should have the results from the stored procedure after it is ran. 

[matrongone]

Im having an issue running preSQL statement

Im trying to delete the last 5 records in a Post gres table

Delete Call_Coding_Summary_MT
From Call_Coding_Summary_MT
WHERE REPORT_DATE between (CURRENT_DATE-5) and CURRENT_DATE

I get an error syntax error at or near "Call_Coding_Summary_MT"

[MuralidharAreti]

Is it possible to pull a field from the incoming data in the pre-sql statement?

e.g. 

UPDATE dbo.Table
SET column = NULL
WHERE FieldId IN (1,2,3)

Where I would replace the 1,2,3 with a value from the dataset. 

Also I agree fully with this statement from @TommyB: 

On a side note, I think that the automatic execution of Pre-SQL upon opening workflow is undesired behavior. Nothing about the workflow should run until I hit "Run Workflow". This should be fixed in my opinion.

Edit/Follow-up question:

Will disabling tools that write to output help prevent the auto run pre-sql thing?

[HiralMavani]

How do we write Post Create SQL Statement in Write In-DB tools? I cant see that option.