Spatial Data from SQL Server shows up as "None", "Null", or other characters in the Input Data tool

[gtorres8]

Environment Details

Data stored in a SQL Server database that contains spatial object data do not show up in an Input Data tool in Alteryx Designer. The field only displays the message, "None - View Browse Tool Map Tab" or "[Null]" or other characters like an apostrophe in the column as it seems the data is stripped away.

• Alteryx Designer
  • 2020.1 +
• SQL Server
  • 2008R2+
• SQL Server ODBC
  • ODBC 13
  • ODBC 17
  • Native Client 11
• SQL Server OLE DB.
• SQL Server Geography Data Type.
• Alteryx SpatialObj Data Type.

 

Cause

An incompatible SQL Server ODBC or OLE DB driver is used. At this time, OLE DB 18.3 or lower is fully supported in Alteryx.

Resolution

1. Click the Input Data Tool and click the drop-down for Connect a Database.



2. Click on Data sources on the right > click on the OleDB link for SQL Server.



3. Select SQL Server Native Client 11.0 as the OLE DB provider and click next.  



4. Fill in the Server Name and credentials and appropriate database.

5. Test the connection and click OK.

NOTE: The connection string should start as such: odb:Provider=SQLNCLI11.1
 

The column will now show up as such:



 

Additional Information

• Troubleshooting: "Login failed for user" when connecting to an OleDB data source
• Previous releases of MS OLEDB

[icisneroz]

I have this exact same issue. It started with the newest update to 2020.1

Except when following these steps, I can login to my SQL server through all the other drivers but when I select the SQL Server 11 Driver it doesn't let me login even after the Test Connection works successfully.

[Patrick007]

I have the same issue. My DB is SQL server 2014, both native client 10 and 11 are working for me. I found a fast way to connect to the database by entering the connection string directly in the box as shown below, then

• press Enter key on your keyboard
• Click the refresh button on the configuration panel
• Click the every right button on options 4 to select the table which has spatial fields from your database

odb:Provider=SQLNCLI11.1;User ID=xxxxxx;Password=xxxxxx;Initial Catalog=xxxxxx;Data Source=xxxxxx;