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 commandsdetable -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?