Useful SQL statements and ArcSDE commands

ArcSDE

A lot of work during administration of ArcSDE includes managing sde instances. I just wanted to write down some of the most useful and often used ArcSDE administration commands I have been using for a while. Parameters embraced in <> are defined by user; so, if we have “<servicename>” that would result to “esri_sde”.

To show all SDE instances:

Sdeservice -o list

To show status of a specific sde instance including the number of accepting connections:

Sdemon -o status -i <servicename>

To show who is connected to an ArcSDE service:

Sdemon -o info -I users -i <servicename> -p <sdepassword>

To stop an sde service:

Sdemon -o shutdown -i <servicename> -p <sdepassword>

To start an sde service:

Sdemon -o start -i <servicename> -p <sdepassword>

To start an sde service remotely:

sdemon -o start -p <sdepassword> -s <servername> -i <servicename>

NOTE: ArcSDE administrator should be in the Windows admins group/power users group on the remote server.

To kill all connections to an sde service:

Sdemon -o kill -t all -i <servicename>

To kill a direct connect to an sde database:

Ths can be done either by restarting SQL Server database engine or by using SQL Server Activity Monitor, tracing the locked processes, and killing those processes. These things are being done on the SQL Server level, because direct connection has nothing to do with sde services – all users connect directly to the SQL Server database engine bypassing sde.

To shut down a stalled giomgr process (go to %sdehome%\tools and then run):

Killp <giomgr_process_ID>

NOTE: Process ID (PID) can be seen in the Windows Task Manager. Click Select Columns button on the Processes tab from the View menu. Then select properties you want to appear as column headings and then click OK.

To get list of layers and their properties from a specific sde service:

Sdelayer -o describe -i <servicename>

To get the list of current locks for a certain sde service:

sdemon -o info -I locks -i <servicename>

SQL Server

While working with Microsoft SQL Server, I have found extremely useful having some T-SQL commands I was using very often close at hand. No doubt, the best way to found a way to solve a certain issue with data management in SQL Server is to search on Google, but it is good to list some commands here as well.

Remember to set a database you will work with either in the SQL Editor toolbar in SQL Server Management Studio or in USE statement.

To copy a view from one database to another:

Right-click a view > Script View as > Create to > New Query Editor Window. In the script code, one should change the name of a database to a target one in the USE statement and define explicitly the database in the FROM statement (i.e., <database>.<owner>.<tablename>). Execute a query.

To edit dbo tables or feature classes in a database from ArcCatalog:

One should add privileges to sde user for a specific feature classes. In ArcCatalog, right-click a feature class stored in a database and go to Privileges.

To create and populate a column in a SQL Server table with the default value:

Alter table <tablename> add <columnname> <datatype> not null default (<defaultvalue>)

Ex. Alter table Customers add CustomerCode smallint not null default (5)

To create a default constraint on a table column (i.e., when creating new rows in a table, a default value will be added to a column automatically):

Alter table <tablename> add constraint <constraintname> default (<value>) for <column name>.

Ex. Alter table Customers add constraint CustomersCode default (11) for CustCodeNum

To get info about objects you have in a database:

Select * from information_schema.tables use <databasename>

To obtain a list of all databases that contain data registered with ArcSDE:

Select distinct (<database_name>) from <sdedatabase name>.<sde schema user>.sde_table_registry

go

Ex. Select distinct (sde) from sde.sde.sde_table_registry

go

To create a table with unique values as an identifier (if one does not exist yet):

Alter table <tablename> add <columnname> int IDENTITY(1,1)

Talking about identifiers, some users may need sometimes to add a sequential unique ID field to the attribute table of a feature class. If this is the case, ESRI Knowledge Base article #27427 will help.

To create a copy of a table:

Select * into <databasename>.<tableowner>.<tablename> from <databasename>.<tableowner>.<tablename>

Ex. Select * into Enterprise.john.CustomersNew from EnterpriseOld.david.CustomersOld

To set identity_insert for a table on:

Set identity_insert <tablename> ON

NOTE: identity_insert can be set to ON only for one table per session. After stopping working with a table, execute such a query: set identity_insert <tablename> off

To copy one column values to another column in the same table:

Update <tablename> set <column2name> = <column1name>

Ex. Update Customers set <NewCustomerID> = <OldCustomerID>

How to sort a table or view in SQL Server by a certain column?

Select * from <tablename> order by <columnname>

A lot of work during administration of ArcSDE includes managing sde instances. I just wanted to write down some of the most useful and often used ArcSDE administration commands I have been using for a while.

To show all SDE instances:

Sdeservice –o list

To show status of a specific sde instance including the number of accepting connections:

Sdemon –o status –i <servicename>

To show who is connected to an ArcSDE service:

Sdemon –o info –I users –i <servicename> -p <sdepassword>

To stop an sde service:

Sdemon –o shutdown –i <servicename> -p <sdepassword>

To start an sde service:

Sdemon –o start –i <servicename> -p <sdepassword>

To kill all connections to an sde service:

Sdemon –o kill –t all –i <servicename>

To get list of layers and their properties from a specific sde service:

Sdelayer –o describe –i <servicename>

How to see the users connected via direct connection to a database? Nicklas Hjarne

While working with Microsoft SQL Server, I have found extremely useful having some T-SQL commands I was using very often close at hand. No doubt, the best way to found a way to solve a certain issue with data management in SQL Server is to search on Google, but it is good to list some commands here as well.

Remember to set a database you will work with either in the SQL Editor toolbar in SQL Server Management Studio or in USE statement.

To copy a view from one database to another:

Right-click a view > Script View as > Create to > New Query Editor Window. In the script code, one should change the name of a database to a target one in the USE statement and define explicitly the database in the FROM statement (i.e., <database>.<owner>.<tablename>). Execute a query.

To edit dbo tables or feature classes in a database from ArcCatalog:

One should add privileges to sde user for a specific feature classes. In ArcCatalog, right-click a feature class stored in a database and go to Privileges.

To create and populate a column in a SQL Server table with the default value:

Alter table <tablename> add <columnname> <datatype> not null default (<defaultvalue>)

Ex. Alter table Customers add CustomerCode smallint not null default (5)

To create a default constraint on a table column (i.e., when creating new rows in a table, a default value will be added to a column automatically):

Alter table <tablename> add constraint <constraintname> default (<value>) for <column name>.

Ex. Alter table Customers add constraint CustomersCode default (11) for CustCodeNum

To get info about objects you have in a database:

Select * from information_schema.tables use <databasename>

To obtain a list of all databases that contain data registered with ArcSDE:

Select distinct (<database_name>) from <sdedatabase name>.<sde schema user>.sde_table_registry

go

Ex. Select distinct (sde) from sde.sde.sde_table_registry

go

To create a table with unique values as an identifier (if one does not exist yet):

Alter table <tablename> add <columnname> int IDENTITY(1,1)

Talking about identifiers, some users may need sometimes to add a sequential unique ID field to the attribute table of a feature class. If this is the case, ESRI Knowledge Base article #27427 will help.

To create a copy of a table:

Select * into <databasename>.<tableowner>.<tablename> from <databasename>.<tableowner>.<tablename>

Ex. Select * into Enterprise.john.CustomersNew from EnterpriseOld.david.CustomersOld

To set identity_insert for a table on:

Set identity_insert <tablename> ON

NOTE: identity_insert can be set to ON only for one table per session. After stopping working with a table, execute such a query: set identity_insert <tablename> off

To copy one column values to another column in the same table:

Update <tablename> set <column2name> = <column1name>

Ex. Update Customers set <NewCustomerID> = <OldCustomerID>

How to sort a table or view in SQL Server by a certain column?

Select * from <tablename> order by <columnname>

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s