Run Pass-Through Query to SQL Server

[ArtShen]

Hi, I am trying to run a SQL script on the SQL Server through Alteryx . I use input data tool to connect to SQL server database and add query to the tool,  For a normal select or join etc. it works fine. However, it seems not working if my script includes assigning parameter or inset into a  #temp table(sample code below), it won't pass the 'Test Query' even.  Does Alteryx has the feature of 'Pass-Through', means pass whatever you input to the server side to run without checking anything?   I have put the sample script below, it works when it runs on SQL Server directly.    

DECLARE @M_Date date;

SELECT @M_DATE = MAX(CLOSING_DT) FROM TABLE1;   <----- NOT WORKING

SELECT COL1, COL2

INTO #TEMP                                          <-------------NOT WORKING

FROM TABLE2

WHERE SALES_DT = @M_DATE

Thanks, 

[JagdeeshN · Accepted solution]

Hi @ArtShen ,

Have you looked into the Dynamic Input tool? (https://help.alteryx.com/current/designer/dynamic-input-tool)

With this tool you can dynamically change parts of the query, by using a value from the incoming datastream.

Alternatively if calling a Stored Proc/Script is what you are looking to do, you can do that through the Output tool.

The script you have has multiple queries and thus does not work. If you encapsulate this into a Stored Proc and call that from alteryx, it will work.

Hope that helps.

Best,

Jagdeesh

[ArtShen]

Thanks for the suggestion, I  created a stored procedure on the server side and call it in Alteryx , it works !

ArtShen

[amir0606]

Hi, I'm also trying. I'd appreciate it if you could show me how you did it   

dont work 

select CustID,FamHeadID,Gender,BTL_Join_Date,Birth_Date

into #DirectInsertJoin

from(

select Customer_Full_ID CustID,

       From_Date,

   Gender_Desc Gender,

   Family_Head_Full_ID FamHeadID,

   BTL_Join_Date,

   Birth_Date,

   row_number() over(partition by Customer_Full_ID order by from_date) CustRow

from [Dims].[Dim_Customer_Details] with (nolock)

where Customer_Full_ID not in (select distinct CustID from #KupaEvents where TransType='הצטרפות')

  and year(BTL_Join_Date) between 2011 and 2024

  and Status_Code=2

    )T

where CustRow=1

  and year(From_Date) between 2011 and 2024

[amir0606]

Can you show me an example of how I can run such queries?

select CustID,FamHeadID,Gender,BTL_Join_Date,Birth_Date

into #DirectInsertJoin

from(

select Customer_Full_ID CustID,

       From_Date,

   Gender_Desc Gender,

   Family_Head_Full_ID FamHeadID,

   BTL_Join_Date,

   Birth_Date,

   row_number() over(partition by Customer_Full_ID order by from_date) CustRow

from [Dims].[Dim_Customer_Details] with (nolock)

where Customer_Full_ID not in (select distinct CustID from #KupaEvents where TransType='הצטרפות')

  and year(BTL_Join_Date) between 2011 and 2024

  and Status_Code=2

    )T

where CustRow=1

  and year(From_Date) between 2011 and 2024