sdetable -o create_mv_view not working

ShaunMcCloud · ‎03-15-2012

I have a PostgreSQL + PostGIS ArcSDE (PostgreSQL 8.4 & ArcSDE 10.0) that I need to create mv_views in. I'm able to create 16 of the 18 I need just fine. For the first feature class I use the following command

sdetable -o create_mv_view -T address_location_mv -t address_location -i sde:postgresql:192.168.7.24 -D staging -u sde -p ******* -s 192.168.7.24

and I get the following as CLI output

ArcSDE 10.0  for PostgreSQL Build 1343 Thu Feb 17 11:45:42  2011
Attribute        Administration Utility
-----------------------------------------------------
        Error: Invalid SQL syntax (-42).
        Error: Unable to create MV view address_location_mv for table address_location.

For the second table I use the following command

sdetable -o create_mv_view -T fire_hydrants_mv -t fire_hydrants -i sde:postgresql:192.168.7.24 -D staging -u sde -p ******* -s 192.168.7.24

and I get the following as CLI output

ArcSDE 10.0  for PostgreSQL Build 1343 Thu Feb 17 11:45:42  2011
Attribute        Administration Utility
-----------------------------------------------------
        Error: Invalid SQL syntax (-42).
        Error: Unable to create MV view fire_hydrants_mv for table fire_hydrants.

My C:\Program Files (x86)\ArcGIS\ArcSDE\pgexe\etc\sdedc_PostgreSQL.log file contains the following for the two commands above

SHOW SEARCH_PATH = '"$user", public, sde' 
Instance initialized . . .
[Thu Mar 15 10:49:07 2012] [56] [PROV-MASTER] ev.SQLstate = 42601
SDE DBMS Error: -42  ERROR:  syntax error at or near "$1"
LINE 1: ...de_level, parcel_uid, segment_uid, c1_exception,  $1 , globa...
                                                             ^
QUERY:  INSERT INTO staging.sde.a20(objectid, source, user_id, date_modified, versionname, site_unique_id, add_number, add_pre, add_name, add_type, add_suf, add_fullname, add_building, add_unit, add_floor, add_room, add_seat, add_loc_notes, supp_info, lastname, firstname, areacode, phone, p_sub, p_block, p_lot, mail_add, mail_city, mail_state, mail_zip, add_hist_a, rr_add, alias_add, address_class, stru_type, stru_mat1, stru_mat2, stru_color1, stru_color2, stru_height, stru_fence, stru_notes1, stru_notes2, date_create, zip5, exchange, telco, msag_comm, comm_name, postal_comm, esn, mapgrid, prec, vote, collect_method, geocode_level, parcel_uid, segment_uid, c1_exception,  $1 , globalid, sde_state_id, shape) VALUES ((Select (Base_ID) From staging.sde.i20),  $2 ,  $3 ,  $4 ,  $5 ,  $6 ,  $7 ,  $8 ,  $9 ,  $10 ,  $11 ,  $12 ,  $13 ,  $14 ,  $15 ,  $16 ,  $17 ,  $18 ,  $19 ,  $20 ,  $21 ,  $22 ,  $23 ,  $24 ,  $25 ,  $26 ,  $27 ,  $28 ,  $29 ,  $30 ,  $31 ,  $32 ,  $33 ,  $34 ,  $35 ,  $36 ,  $37 ,  $38 ,  $39 ,  $40 ,  $41 ,  $42 ,  $43 ,  $44 ,  $45 ,  $46 ,  $47 ,  $48 ,  $49 ,  $50 ,  $51 ,  $52 ,  $53 ,  $54 ,  $55 ,  $56 ,  $57 ,  $58 ,  $59 ,  $60 ,  $61 , staging.sde.SDE_get_current_state(),  $62 )
CONTEXT:  SQL statement in PL/PgSQL function "v20_insert" near line 8

[Thu Mar 15 10:49:07 2012] [56] [PROV-MASTER] Unable to create IMV insert rule function for view address_location_mv for table sde.address_location[Thu Mar 15 10:49:08 2012] [56] [PROV-MASTER] In DB_instance_close...
[Thu Mar 15 10:49:08 2012] [56] [PROV-MASTER] 
Describe Cache...
[Thu Mar 15 10:49:08 2012] [56] [PROV-MASTER] 
 Cache Size............................. 200
[Thu Mar 15 10:49:08 2012] [56] [PROV-MASTER] 
 Number of Table Objects Cached          (0)
[Thu Mar 15 10:49:08 2012] [56] [PROV-MASTER] 
 Number of Selects Cached                (0)
[Thu Mar 15 10:49:08 2012] [56] [PROV-MASTER] 
 Number of Joins Cached (DBMS-described) (0)
[Thu Mar 15 10:49:08 2012] [56] [PROV-MASTER] 
    Cached     Cached
     Hits    Statements
          ---------------------
[Thu Mar 15 10:49:08 2012] [56] [PROV-MASTER] 
[Thu Mar 15 10:49:08 2012] [56] [PROV-MASTER]     SELECT's DBMS Described: [0][0]

SHOW SEARCH_PATH = '"$user", public, sde' 
Instance initialized . . .
[Thu Mar 15 10:50:40 2012] [57] [PROV-MASTER] ev.SQLstate = 42601
SDE DBMS Error: -42  ERROR:  syntax error at or near "$1"
LINE 1: ...hydrant_unique_id, notes, psi, maintenance_date,  $1 , globa...
                                                             ^
QUERY:  INSERT INTO staging.sde.a11(objectid, source, user_id, date_modified, versionname, hydrant_unique_id, notes, psi, maintenance_date,  $1 , globalid, sde_state_id, shape) VALUES ((Select (Base_ID) From staging.sde.i11),  $2 ,  $3 ,  $4 ,  $5 ,  $6 ,  $7 ,  $8 ,  $9 ,  $10 ,  $11 , staging.sde.SDE_get_current_state(),  $12 )
CONTEXT:  SQL statement in PL/PgSQL function "v11_insert" near line 8

[Thu Mar 15 10:50:40 2012] [57] [PROV-MASTER] Unable to create IMV insert rule function for view fire_hydrants_mv for table sde.fire_hydrants[Thu Mar 15 10:50:40 2012] [57] [PROV-MASTER] In DB_instance_close...
[Thu Mar 15 10:50:40 2012] [57] [PROV-MASTER] 
Describe Cache...
[Thu Mar 15 10:50:40 2012] [57] [PROV-MASTER] 
 Cache Size............................. 200
[Thu Mar 15 10:50:40 2012] [57] [PROV-MASTER] 
 Number of Table Objects Cached          (0)
[Thu Mar 15 10:50:40 2012] [57] [PROV-MASTER] 
 Number of Selects Cached                (0)
[Thu Mar 15 10:50:40 2012] [57] [PROV-MASTER] 
 Number of Joins Cached (DBMS-described) (0)
[Thu Mar 15 10:50:40 2012] [57] [PROV-MASTER] 
    Cached     Cached
     Hits    Statements
          ---------------------
[Thu Mar 15 10:50:40 2012] [57] [PROV-MASTER] 
[Thu Mar 15 10:50:40 2012] [57] [PROV-MASTER]     SELECT's DBMS Described: [0][0]

Does anyone have some suggestions on what I can try to get these views created?

ShaunMcCloud · ‎04-17-2012

Just a quick update on this. It also appears that STATUS as a field name causes an issue. Removing the STATUS field from address_location & fire_hydrants fixes the following "Error: Invalid SQL syntax (-42)."