Synapse Serverless SQL Pool in Alteryx Designer

[LizRogers]

Hello!

I'm trying to connect to Synapse Serverless SQL Pool (NOT a dedicated SQL Pool, I have successfully connected to that).  I have successfully authenticated and pulled a list of tables, but when I run the workflow, I get the following error:

Error: Input Data (1): Error SQLExecute: [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]DECLARE CURSOR is not supported.[Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Statement(s) could not be prepared.

I have tested this with both ODBC 17 and 18 drivers.  

Has anyone successfully loaded data from a Serverless SQL Pool in Alteryx?  Do I need to connect differently than an ODCB driver or use a ODBC driver?

[LizRogers · Accepted solution]

I finally got it.  I had to use the OLE DB Driver for SQL Server v18.  Version 19 gives a popup box error in Alteryx after setting up all the connections.  

Also, you need to have your permissions for your Azure AD user on both the Serverless Database and the Storage Account used by the Serverless SQL.  

[LizRogers]

Also, this topic was useful: https://knowledge.alteryx.com/index/s/article/Error-Cursors-are-not-supported-or-Cursor-support-is-n...

Basically add SET NOCOUNT ON to all your SQL Statements.