topic SQL Server and Float values in ArcGIS Enterprise Questions

SQL Server and Float values

RobMcCulley — Thu, 04 Sep 2014 16:59:52 GMT

I'm working on creating an Enterprise GeoDatabase in SQL Server 2012, but I'm having a problem with float values.

The GeoDatabase was created using the Create Enterprise GeoDatabase tool. The user being used to connect is the "sde" user.

I've got a table with the following fields:

LINC - Text

OwnerName - Text

TenancyGroup - Text

TenancyInterest - Float, Precision 4, Scale 3

OwnerAddress - Text

GlobalID

The TenancyInterest column stores a percentage value ranging between 0 and 1, with three decimal places. When I view the table in ArcGIS, the float value always displays as 0. Further, the OwnerAddress and GlobalID columns both display as empty. I'm populating the table using an arcpy script. If I don't populate the TenancyInterest column, then the OwnerAddress and GlobalID column both display the correct values.

If I edit the table and manually enter new values, there is no error message, but the value still displays 0. I tried creating the table in a File GeoDatabase, where everything worked fine. I then imported the File GeoDatabase table into my SQL Server GeoDatabase, and ended up with the same result: float values all showing as 0, remaining columns showing as empty.

I tried creating the empty table, and inserting one record in ArcGIS. I've tried using Float and Double for the data type. I've tried not setting the Precision and Scale (apparently it defaults to 38 and 8). Same result.

If I view the table in SQL Server Management Studio, all of the values are in the table correctly. I can see the proper percentage value in the TenancyInterest column, and I can see the proper Text in the OwnerAddress column. When I edited the data manually in ArcGIS, the changes show up in the table in Management Studio, even though they aren't displayed in ArcGIS.

Any ideas on what I'm doing wrong?

Re: SQL Server and Float values

VinceAngelo — Fri, 05 Sep 2014 20:49:48 GMT

What version of ArcGIS are you using? What service packs and patch(es) do you have applied? Can you try installing the ArcSDE command-line client applications, and report what 'sdetable -o describe' reports on the table?

It is against best practice to ever create or access spatial data as the SDE user. The SDE login should be reserved for geodatabase instance administration (using it for mundane purposes increases the risk of accidentally destroying your instance). Instead, you should create one or more "data ownership" login(s) to own and manage spatial data, and one or more "data user" login(s) to access the data through "read mostly" applications (with permissions granted through roles).

- V

Re: SQL Server and Float values

RobMcCulley — Mon, 08 Sep 2014 14:43:47 GMT

In looking at it further, I was wrong. I'm not connecting with the sde user, I'm connecting with the operating system authentication. My user is mapped to dbo.

The output of sdetable -o describe_long is:

ArcSDE 10.2

Attribute Administration Utility

-----------------------------------------------------

----------------------------------------------------------------

Column Database : ARCGIS2

Column Owner : DBO

Column Table : TITLEOWNERS

Column Name : OBJECTID

Row ID Column Type : SDE Maintained

SDE Column Type : SE_INT32

Column Size : 10

Decimal Digits : 0

Null Allowed? : False

----------------------------------------------------------------

Column Database : ARCGIS2

Column Owner : DBO

Column Table : TITLEOWNERS

Column Name : LINC

SDE Column Type : SE_NSTRING

Column Size : 12

Decimal Digits : 0

Null Allowed? : False

----------------------------------------------------------------

Column Database : ARCGIS2

Column Owner : DBO

Column Table : TITLEOWNERS

Column Name : OwnerName

SDE Column Type : SE_NSTRING

Column Size : 150

Decimal Digits : 0

Null Allowed? : True

----------------------------------------------------------------

Column Database : ARCGIS2

Column Owner : DBO

Column Table : TITLEOWNERS

Column Name : Address

SDE Column Type : SE_NSTRING

Column Size : 200

Decimal Digits : 0

Null Allowed? : True

----------------------------------------------------------------

Column Database : ARCGIS2

Column Owner : DBO

Column Table : TITLEOWNERS

Column Name : TenancyGroup

SDE Column Type : SE_NSTRING

Column Size : 4

Decimal Digits : 0

Null Allowed? : False

----------------------------------------------------------------

Column Database : ARCGIS2

Column Owner : DBO

Column Table : TITLEOWNERS

Column Name : TenancyInterest

SDE Column Type : SE_FLOAT32

Column Size : 4

Decimal Digits : 3

Null Allowed? : False

Re: SQL Server and Float values

VinceAngelo — Mon, 08 Sep 2014 16:24:10 GMT

So it looks like the ArcSDE subsystem is accessing the data correctly, and identifying the column as containing 32-bit float values. Can you try using 'sde2shp' to verify the column value is exported to dBase correctly?

It seems like Desktop may be trying to read some other type. Has the column changed type due to SQL commands since the time the feature class was orignially registered? It probably wouldn't hurt to work this through with Tech Support.

Re: SQL Server and Float values

RobMcCulley — Mon, 08 Sep 2014 17:29:36 GMT

I've checked two things:

sde2tbl exports the proper values to dBase.
Two other systems in the office exhibit the same behavior.

I guess I'll take it up with Tech Support.

Thanks Vince.