Passing Parameters from Alteryx to SQL Server Stored Procedures

[ACharChar]

Hello, Alteryx Community! I'm developing workflows between Alteryx and SQL Server and am stumped when attempting to pass parameters to SQL Server. In the provided screenshot, the following message appears - "Stored Procedures are not supported for this type of connection". Prior posts indicated that an OleDB driver is recommended over ODBC for a richer environment. SQL Server is a great SQL editor and we're hoping to leverage the strengths of both tools. Any guidance would be appreciated.

Driver: Microsoft OLE DB Driver for SQL Server (MSOLEDBSQL)

[rfoster7 · Accepted solution]

We use odbc:DRIVER={SQL Server Native Client 11.0}; for our SQL Server connections and it lets us access the stored procedures and the variables within. 

[danilang · Accepted solution]

Hi @ACharChar 

In order to call stored proc using OLE DB, you'll need to configure your connection using the SQL Server Native Client as opposed to Microsoft OLE DB Driver for SQL Server

Dan

[smysnbrg]

Per this article, the SQL Server Native Client is deprecated and not recommended for new development.  Has Alteryx support provided any guidance on this? https://docs.microsoft.com/en-us/sql/relational-databases/native-client/sql-server-native-client?vie...

[danilang]

hi @smysnbrg 

I did some tests after seeing your message and it looks like you can use Microsoft OLE DB Provider for SQL Server and call Stored Procs 

Dan

[ACharChar]

Hi, @danilang, does the install/configuration for the OLE DB driver follow the standard setup, or are there specific configuration options that need to be accounted for?

[danilang]

I just selected that option when configuring a new data source.  No special setup was required.

Dan