arcpy.ArcSDESQLExecute(sde) problem

[ChadMarch1]

So I've written up this script to print out any alias names of feature classes in all the schemas of our DEV database. However, there's something wrong with my query statement I'm guessing because I get an encoding error back.

This is the error message: ArcSDESQLExecute: SreamBindOutputColumn ArcSDE Error -65 ???

import arcpy,sys try:     arcpy.env.workspace = r'C:\staging\database connections\dev'     sdes = arcpy.ListWorkspaces()     for sde in sdes:         arcpy.env.workspace = sde         first = raw_input("Do you want to update aliases in "+str(sde)+"...\n(Yes/No)\n")         if first.upper() == 'YES':             fcs = arcpy.ListFeatureClasses()             for fc in fcs:                 sde_conn = arcpy.ArcSDESQLExecute(sde)                 sql_stmt = "select Definition.value('(/DEFeatureClassInfo/Name/node())[1]', 'nvarchar(max)') AS Name, Definition.value('(/DEFeatureClassInfo/AliasName/node())[1]', 'nvarchar(max)') AS Alias from agdc_sde.sde.gdb_items where Name = '"+str(fc)+"'"                 sde_return = sde_conn.execute(sql_stmt)                 if sde_return == True:                     print "SQL statement: {0} ran successfully.".format(sql)                 else:                     print "SQL statement: {0} FAILED.".format(sql)                 print "+++++++++++++++++++++++++++++++++++++++++++++\n" except Exception as err:     print unicode(err)     sde_return = False

[VinceAngelo]

The -65 error is SE_INVALID_POINTER, which indicates a coding error.

Can you provide the text of your SQL statement?  Have you tried running
is in a different SQL client?

- V

[ChadMarch1]

It's in the python in the OP.

sql_stmt = "select Definition.value('(/DEFeatureClassInfo/Name/node())[1]', 'nvarchar(max)') AS Name, Definition.value('(/DEFeatureClassInfo/AliasName/node())[1]', 'nvarchar(max)') AS Alias from agdc_sde.sde.gdb_items where Name = '"+str(fc)+"'"

But here is just the statement:

"
select Definition.value('(/DEFeatureClassInfo/Name/node())[1]', 'nvarchar(max)') AS Name, 
Definition.value('(/DEFeatureClassInfo/AliasName/node())[1]', 'nvarchar(max)') AS Alias 
from agdc_sde.sde.gdb_items 
where Name = '"+str(fc)+"'"

[VinceAngelo]

I wanted one of the strings to which the *evaluated* expression resolved.

It's possible that the ArcSDE API doesn't support the return type of the
"Definition" function.  What RDBMS are you using?  (Web searches with
"Definition" in them return a billion hits.)

- V

[ChadMarch1]

In my original post I put in the whole error message that was returned. I'm using SQL Server 2008 R2. I can execute the statement successfully in a query window in SSMS, so I know the query is correct for SQL.

I feel like there is a problem with how the arcpy function is passing it to the DB.

[VinceAngelo · Accepted solution]

Not in passing to the database, but in how it is returned (SE_stream_bind_output_column
is invoked after SE_stream_execute, before SE_stream_fetch).  The problem is encountered
when binding to one of the nvarchar(max) result columns.  I'm trying to determine if this is
an ArcObjects issue or an ArcSDE API one.

What version of ArcGIS are you using?  Which service pack?  Are any other patches installed?

Have you tried casting to 'nvarchar(255)'?

- V

[TimDine]

Encountered this same issue and found this post.  

Using ArcGIS Desktop 10.5.1.7333 (Python 2.7.13)

SQL Server 13.0.5026.0

I was able to go all the way up to nvarchar(4000).  The context is I am returning some results in a python toolbox / geoprocessing service to be passed back to a WAB site so the result is being returned using the "FOR JSON AUTO" options in the SQL query to be easily parsed in JavaScript using a custom widget.  There was a specific desire to stay within the ArcGIS stack rather than create a separate web service to return these results.

[ChadMarch1]

Product Name: ArcGIS 10.1 for Desktop
Release Version: 10.1
Product Version: 10.1.1.3143

I don't believe any other patches have been installed.

I changed max to 255 and I am no longer getting the same error.

That did the trick, thanks!

I did however change how to check the result. Rather than checking if sde_return == true:
I just check to see if results[1] == None: because we'll always have feature classes.