topic Re: arcpy.ArcSDESQLExecute(sde) problem in Data Management Questions

arcpy.ArcSDESQLExecute(sde) problem

ChadMarch1 — Thu, 10 Oct 2013 16:18:51 GMT

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

Re: arcpy.ArcSDESQLExecute(sde) problem

VinceAngelo — Thu, 10 Oct 2013 16:44:59 GMT

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

Re: arcpy.ArcSDESQLExecute(sde) problem

ChadMarch1 — Sat, 11 Dec 2021 12:50:36 GMT

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)+"'"

Re: arcpy.ArcSDESQLExecute(sde) problem

VinceAngelo — Thu, 10 Oct 2013 16:58:24 GMT

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

Re: arcpy.ArcSDESQLExecute(sde) problem

ChadMarch1 — Thu, 10 Oct 2013 17:25:44 GMT

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.

Re: arcpy.ArcSDESQLExecute(sde) problem

VinceAngelo — Thu, 10 Oct 2013 18:35:57 GMT

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

Re: arcpy.ArcSDESQLExecute(sde) problem

ChadMarch1 — Thu, 10 Oct 2013 18:54:08 GMT

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.

Re: arcpy.ArcSDESQLExecute(sde) problem

TimDine — Mon, 17 Jun 2019 15:08:21 GMT

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.