Run Pass-Through Query to SQL Server
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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,
Solved! Go to Solution.
- Labels:
- Database Connection
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thanks for the suggestion, I created a stored procedure on the server side and call it in Alteryx , it works !
ArtShen
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi, I'm also trying. I'd appreciate it if you could show me how you did it
dont work
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Can you show me an example of how I can run such queries?
