ArcPy Export SQL Server to Shapefile gives RuntimeError: Object: Error in executing tool?

Question

I have executed a stored procedure and want to export this layer to a shapefile. I feel like this should be an easy solution. I tried using arcpy.CopyFeatures_management and it threw an error. I am not sure if CopyFeature is meant for sql server tables.

I am running ArcGIS 10.1. I am using SQL server 2012 with geography data type. The table is not registered with the geodatabase. The error is below:

RuntimeError: Object: Error in executing tool
<<< Process finished. (Exit code 1)

Below is my code:

import pyodbc
import arcpy
sqlserver = pyodbc.connect('DRIVER={SQL Server};SERVER=sqlserver;DATABASE=AndrewSandbox;Integrated Security=True')
cursor = sqlserver.cursor()
table = cursor.execute("SELECT * FROM TEMPLINE")
arcpy.CopyFeatures_management(table, "D:\Python\WaterSage\Viability\TestExport.shp")

• nickpeihl: Please give more information. What error did it throw? CopyFeatures_management should work fine for SQL Server.
• Vince: Please update the question to include the version of ArcGIS in use, the geometry storage type, the SQL-Server version, whether the table is registered with the geodatabase, and the exact error message.

Answer 1

I do not have an SQL Server table available to test this, but I was surprised to see you using pyodbc in this workflow.

I recommend that you try to use a Query Layer:

A query layer is a layer or stand-alone table that is defined by a SQL query.

...

You can persist a query layer by exporting it to a feature class.