TSQL syntax error in Input tool in the latest version of Designer (2023.2)

[Paul_Holden]

Hi,

We have a number of workflows that us local variables as part of the TSQL in their Input tools.

This works without any issues in our current production environment which is Version: 2022.1.1.42604 Patch: 4

We are intending to upgrade to version 2023.2 but we have encountered an issue during testing where the tool now produces a SQL syntax error.

Error opening table: Microsoft OLE DB Driver for SQL server: incorrect syntax near the keyword 'DECLARE'. \42000 = 156

I've been through the release notes a number of times but I can't identify anything that might be causing this change in behaviour. We are unable to upgrade until this issue is resolved as it impacts a number of business critical workflows.

Any suggestions as to the cause/resolution of this issue gratefully received.

Additional Information

We get an equivalent error using the MS ODBC driver.

We get the same error for both local SQL server and Azure DB connections.

We get the error when running 2023.2 side by side with 2022.1 on the same machine, so we can be pretty sure that nothing is different with the drivers.

The DECLARE(d) variables are local to the SQL, we are not capturing any user parameters we simply want to restrict the dataset returned to records which have various relationships to the date on which the workflow is run e.g. just give me the last 6 months data.

Some example TSQL would be as follows:

DECLARE @RunDate    AS DATE
DECLARE @CurrentYear  AS BIGINT
DECLARE @CurrentMonth  AS BIGINT
DECLARE @CurrentDay    AS BIGINT
DECLARE @Month2      AS BIGINT
DECLARE @Month3      AS BIGINT
DECLARE @Month4      AS BIGINT
DECLARE @Month5      AS BIGINT
DECLARE @Month12    AS BIGINT
DECLARE @ExtractDate  AS BIGINT

SET @RunDate =  GETDATE()  -- For the scheduled runs this will be GETDATE(), for the manual runs this will be a date the user inputs when running workflow
SET @CurrentYear = YEAR(@RunDate) 
SET @CurrentMonth = MONTH(@RunDate)
SET @CurrentDay = DAY(@RunDate)
SET @Month3 = CONCAT(@CurrentYear,  CASE WHEN @CurrentMonth < 10 THEN '0' ELSE '' END, @CurrentMonth)
SET @Month2 = @Month3 - 1
SET @Month4 = CASE WHEN RIGHT(@Month3,2) = 12 THEN CONCAT(@CurrentYear+1,'01') ELSE @Month3+1 END
SET @Month5 = CASE WHEN RIGHT(@Month4,2) = 12 THEN CONCAT(@CurrentYear+1,'01') ELSE @Month4+1 END
SET @Month12 = CONCAT(@CurrentYear,'12')
SET @ExtractDate = CONCAT(@Month3,  CASE WHEN @CurrentDay < 10 THEN '0' ELSE '' END, @CurrentDay)

...followed by a complex SELECT statement that uses the above variable.

[Paul_Holden · Accepted solution]

Ah, so it seems I should have searched the KB as well as the Discussions:

https://knowledge.alteryx.com/index/s/article/SQL-Queries-Statements-such-as-SET-or-DECLARE-var-for-...