topic Create Spatial view using sdetable -o create_view...error in Data Management Questions

Create Spatial view using sdetable -o create_view...error

AprilYang — Fri, 25 Jan 2013 21:06:22 GMT

Hi,
I working with 10.1 SDE and 2008 R2 SQL server. When I created a spatial view using: sdetable -o create_view -T my_view -t mytable1, mytable2 -c table1.shape, table2.c1 -w mytable1.c3=mytable2.c4 -i sde:sqlserver -D SDE -u username -p mypassword

I got two errors:
Error: Failure to access the DBMS server <-409>.
Error: Could not create a connection on server ..., for user...

Could someone tell me what went wrong? and how could i sovle this problerm?

Thank you very much.
April.

Re: Create Spatial view using sdetable -o create_view...error

AsrujitSengupta — Fri, 25 Jan 2013 21:40:57 GMT

April,

the connection string is incorrect

-i sde:sqlserver:"instance name" E.G: sde:sqlserver:server2 (here server2 is the SQL Server instance name)

Regards,

Re: Create Spatial view using sdetable -o create_view...error

AprilYang — Fri, 25 Jan 2013 21:53:01 GMT

April,

the connection string is incorrect

-i sde:sqlserver:"instance name" E.G: sde:sqlserver:server2 (here server2 is the SQL Server instance name)

Regards,

Hi Asrujit,

It worked out very well, thanks for your solution:)

April.

Re: Create Spatial view using sdetable -o create_view...error

AprilYang — Sat, 26 Jan 2013 00:43:51 GMT

A related question: What's the difference between the view (including spatial information, like "shape") created using SQL query and the spatial view created by "sdetable -o create_view..." ? since the view created by the second approach could be refined inside SQL after generated.

Thanks for any thoughts.

Re: Create Spatial view using sdetable -o create_view...error

VinceAngelo — Sat, 26 Jan 2013 15:25:35 GMT

Could you rephrase the question?

'sdetable -o create_view' was designed to be used with SDEBINARY or SDELOB storage,
where it's not possible to use SQL to make a view. Given spatial types, you should always
use SQL to create views (at a minimum, they allow the full syntax of the SQL language, not
a stripped-down subset). It's up to the RDBMS' optimizer to dictate differences from there.

- V

Re: Create Spatial view using sdetable -o create_view...error

AprilYang — Mon, 28 Jan 2013 20:46:18 GMT

Could you rephrase the question?

'sdetable -o create_view' was designed to be used with SDEBINARY or SDELOB storage,
where it's not possible to use SQL to make a view. Given spatial types, you should always
use SQL to create views (at a minimum, they allow the full syntax of the SQL language, not
a stripped-down subset). It's up to the RDBMS' optimizer to dictate differences from there.

- V

Hi Vince,

Thank you for your explanations.

If I understood you correctly, one of the purposes of using 'sdetable -o create_view' is to utilize the SDEBINARY or SDELOB storage, right? What are the benefits of using these storages?

Let me rephrase my question here:

The "sdetable -o create_view -T my_view -t mytable1, mytable2 -c table1.shape, table2.c1 -w mytable1.c3=mytable2.c4 -i sde:sqlserver:local -D SDE -u username -p mypassword " would create a inner join between mytable1 and myatable2. If would like to edit this view, e.g.change "inner join" to a "left outer join" , I would open "my_view" in SQL and edit it. Could you do the edition through command line? Will the changes of the view put into the SDELOB storage, if it's done within SQL ? if so how would I check it?

Under what kind of situation, the spatial types won't be given? I am confused here, since I only met data given spatial types.

Thanks.

Re: Create Spatial view using sdetable -o create_view...error

VinceAngelo — Tue, 29 Jan 2013 11:13:44 GMT

This isn't a "benefits" issue, it's just a "what's possible" one. With SQL-Server, you have
a choice of SDEBINARY or native GEOMETRY or GEOGRAPHY (the latter only applies to
coordinates stored in decimal degrees).

Altering views after construction is not supported. It's probably done all the time, but
there's little way to prevent something bad from happening (bad things include returning
NJULL or duplicate registered rowid (objectid) values, unsupported column types, and
various other issues that will prevent ArcGIS from accessing the table).

Using native geometry is probably best in this situation, but it has quirks, too (most
notably, accessor functions which are case-sensitive). As long as the resulting views
contain the types supported by ArcGIS, with distinct unique rowids, you can register
the with with ArcGIS.

- V