First, a little background…
The current guidance from ESRI is to use “Direct Connect” to ArcSDE databases so that you don’t have to license all the cores on your DBMS box. This is great, and can be a cost savings if you’ve got a dual quad box, but only a single ArcGIS Server license (4 cores). If you are in a huge organization and run Oracle on some monster Sun box with 32 CPU’s you’re looking at a HUGE savings. So, a lot of people are happily running with Direct Connect. Great.
Now the Usability bit…
Last week I threw 9.3 ArcEditor on my workstation, and went to open some data…
Based on the error message, and a little digging around I concluded that I needed to setup ArcSDE Services in order to connect. This is a non-trivial pain because I have a lot of spatially enabled databases. I ended up writing a bunch of SQL code that writes out the SDESERVICE command-line code to set this up (lower down in this post for those interested).
Then, on a flight over to Florida last Wednesday I was installing Desktop 9.3 on my notebook, and what did I see…
Yep – the Pre-9.3 Geodatabase Direct Connect is an option on the installer splash screen. When I installed desktop on my Workstation I browsed directly to the Desktop installer and launched that (we keep the installers on a network drive). Thus I never saw this screen, and wasted whole wack of time writing the SQL Script etc. So this is great news, but why is this stuck 5 items below the ArcEditor install link? And ever better, why is it not an option INSIDE the Desktop installer package? It’s great that this is available, but burying it in a long list of stuff I typically ignore is pretty “meh” in my book. When I ran the installer it told me that the drivers for 9.0, 9.1 and 9.2 would take up a whole 91Mb – when you are looking at close to 2 GB for ArcGIS Desktop, WHY NOT JUST INSTALL THIS?
And the error message shown above fully deserves a “fail” from a usability standpoint. How does that message help the average user? And repeating it 4 times does not help. Would it be too difficult to change this to something useful like:
You are trying to create a “direct connection” to a previous version of ArcSDE. At this time, the pre-9.3 Geodatabase Direct Connect option has not been installed on this computer. Use the ArcGIS Desktop installer to add this component.
Anyhow – once installed, it works like a charm. Thanks.
For those interested in the more brute force option, and a little run down on using SQL to write SQL or command line scripts, read on…
We’ll use SQL to write out a bunch of scripts. Specifically:
1) Add SDE user to each database that contains the SDE metadata tables
2) Grant the SDE user correct permissions in the database
3) Create sdeservice command line(s)
4) Create command line scripts that will stuff service/port mappings into the services.sde and windows services files
Note: Before running this you will need to setup an SDE login to your SQL 2005 instance.
Here’s the SQL:
-- Make sure we are running in master...
use master
go
---make sure temp table is not present
IF object_id('tempdb..#SDEDatabases') IS NOT NULL
BEGIN
DROP TABLE #SDEDatabases;
END
-- Create the tempory table
CREATE TABLE #SDEDatabases
(
DatabaseName VARCHAR(255) NULL,
Port int
)
GO
set nocount on
DECLARE @DBNAME varchar(50)
DECLARE @CMD AS VARCHAR(500)
DECLARE @PORT as Int
--Create a cursor on the sys.databases table
DECLARE dbCur CURSOR FOR
select [name] from sys.databases
-- open the cursor
OPEN dbCur
set @PORT = 5151
FETCH NEXT FROM dbCur INTO @DBNAME
WHILE @@FETCH_STATUS = 0
BEGIN
Set @CMD = 'select ''' + @DBNAME + ''', ' + CAST(@PORT as varchar(4)) + ' from ' + @DBNAME +'.dbo.sysobjects where type=''U'' and name = ''SDE_Version'''
set @PORT = @PORT + 1
INSERT INTO #SDEDatabases EXEC(@CMD)
FETCH NEXT FROM dbCur INTO @DBNAME
END
CLOSE dbCur;
DEALLOCATE dbCur;
--Create the SQL & commandline scripts
select 'use ' + DatabaseName + ' CREATE USER [sde] FOR LOGIN [sde] WITH DEFAULT_SCHEMA=[dbo]' from #SDEDatabases
select 'use ' + DatabaseName + ' grant CREATE PROCEDURE, create table, create view, create function, delete to [sde]' from #SDEDatabases
select 'sdeservice -o create -d SQLSERVER -p YOURPWD -i sde' + DatabaseName + ' -H C:\arcgis\ArcSDE\sqlexe ' from #SDEDatabases
select 'sdeservice -o register -d SQLSERVER -r ADMIN_DATABASE -v ' + DatabaseName + ' -i sde' + DatabaseName + ' -p dTs1dEv' from #SDEDatabases
select 'sdeservice -o delete -i sde' + DatabaseName + ' -d SQLSERVER' from #SDEDatabases
select 'echo sde' + DatabaseName + ' ' +CAST(PORT as varchar(4)) + '/tcp #ArcSDE for SQL Server >> C:\arcgis\ArcSDE\sqlexe\etc\services.sde' from #SDEDatabases
select 'echo sde' + DatabaseName + ' ' +CAST(PORT as varchar(4)) + '/tcp #ArcSDE for SQL Server >> %windir%\system32\drivers\etc\services' from #SDEDatabases
-- clean up the temp table
drop table #SDEDatabases
Run this and specify results to text in your SQL tool. This will output something like this:
use CDOTSDE CREATE USER [sde] FOR LOGIN [sde] WITH DEFAULT_SCHEMA=[dbo]
use TIMSSDE CREATE USER [sde] FOR LOGIN [sde] WITH DEFAULT_SCHEMA=[dbo]
use ArcData CREATE USER [sde] FOR LOGIN [sde] WITH DEFAULT_SCHEMA=[dbo]
use Gains_Spatial CREATE USER [sde] FOR LOGIN [sde] WITH DEFAULT_SCHEMA=[dbo]
use sdeblank CREATE USER [sde] FOR LOGIN [sde] WITH DEFAULT_SCHEMA=[dbo]
use TexasFire CREATE USER [sde] FOR LOGIN [sde] WITH DEFAULT_SCHEMA=[dbo]
use WindSpatial CREATE USER [sde] FOR LOGIN [sde] WITH DEFAULT_SCHEMA=[dbo]
use CDOTSDE grant CREATE PROCEDURE, create table, create view, create function, delete to [sde]
use TIMSSDE grant CREATE PROCEDURE, create table, create view, create function, delete to [sde]
use ArcData grant CREATE PROCEDURE, create table, create view, create function, delete to [sde]
use Gains_Spatial grant CREATE PROCEDURE, create table, create view, create function, delete to [sde]
use sdeblank grant CREATE PROCEDURE, create table, create view, create function, delete to [sde]
use TexasFire grant CREATE PROCEDURE, create table, create view, create function, delete to [sde]
use WindSpatial grant CREATE PROCEDURE, create table, create view, create function, delete to [sde]
sdeservice -o
create -d SQLSERVER -p YOURPWD -i sdeCDOTSDE -H C:\arcgis\ArcSDE\sqlexe
sdes
ervice -o create -d SQLSERVER -p YOURPWD -i sdeTIMSSDE -H C:\arcgis\ArcSDE\sqlexe
sdeservice -o create -d SQLSERVER -p YOURPWD -i sdeArcData -H C:\arcgis\ArcSDE\sqlexe
sdeservice -o create -d SQLSERVER -p YOURPWD -i sdeGains_Spatial -H C:\arcgis\ArcSDE\sqlexe
sdeservice -o create -d SQLSERVER -p YOURPWD -i sdesdeblank -H C:\arcgis\ArcSDE\sqlexe
sdeservice -o create -d SQLSERVER -p YOURPWD -i sdeTexasFire -H C:\arcgis\ArcSDE\sqlexe
sdeservice -o create -d SQLSERVER -p YOURPWD -i sdeWindSpatial -H C:\arcgis\ArcSDE\sqlexe
sdeservice -o register -d SQLSERVER -r ADMIN_DATABASE -v CDOTSDE -i sdeCDOTSDE -p YOURPWD
sdeservice -o register -d SQLSERVER -r ADMIN_DATABASE -v TIMSSDE -i sdeTIMSSDE -p YOURPWD
sdeservice -o register -d SQLSERVER -r ADMIN_DATABASE -v ArcData -i sdeArcData -p YOURPWD
sdeservice -o register -d SQLSERVER -r ADMIN_DATABASE -v Gains_Spatial -i sdeGains_Spatial -p YOURPWD
sdeservice -o register -d SQLSERVER -r ADMIN_DATABASE -v sdeblank -i sdesdeblank -p YOURPWD
sdeservice -o register -d SQLSERVER -r ADMIN_DATABASE -v TexasFire -i sdeTexasFire -p YOURPWD
sdeservice -o register -d SQLSERVER -r ADMIN_DATABASE -v WindSpatial -i sdeWindSpatial -p YOURPWD
sdeservice -o delete -i sdeCDOTSDE -d SQLSERVER
sdeservice -o delete -i sdeTIMSSDE -d SQLSERVER
sdeservice -o delete -i sdeArcData -d SQLSERVER
sdeservice -o delete -i sdeGains_Spatial -d SQLSERVER
sdeservice -o delete -i sdesdeblank -d SQLSERVER
sdeservice -o delete -i sdeTexasFire -d SQLSERVER
sdeservice -o delete -i sdeWindSpatial -d SQLSERVER
echo sdeCDOTSDE 5155/tcp #ArcSDE for SQL Server >> C:\arcgis\ArcSDE\sqlexe\etc\services.sde
echo sdeTIMSSDE 5156/tcp #ArcSDE for SQL Server >> C:\arcgis\ArcSDE\sqlexe\etc\services.sde
echo sdeArcData 5159/tcp #ArcSDE for SQL Server >> C:\arcgis\ArcSDE\sqlexe\etc\services.sde
echo sdeGains_Spatial 5163/tcp #ArcSDE for SQL Server >> C:\arcgis\ArcSDE\sqlexe\etc\services.sde
echo sdesdeblank 5166/tcp #ArcSDE for SQL Server >> C:\arcgis\ArcSDE\sqlexe\etc\services.sde
echo sdeTexasFire 5170/tcp #ArcSDE for SQL Server >> C:\arcgis\ArcSDE\sqlexe\etc\services.sde
echo sdeWindSpatial 5171/tcp #ArcSDE for SQL Server >> C:\arcgis\ArcSDE\sqlexe\etc\services.sde
echo sdeCDOTSDE 5155/tcp #ArcSDE for SQL Server >> %windir%\system32\drivers\etc\services
echo sdeTIMSSDE 5156/tcp #ArcSDE for SQL Server >> %windir%\system32\drivers\etc\services
echo sdeArcData 5159/tcp #ArcSDE for SQL Server >> %windir%\system32\drivers\etc\services
echo sdeGains_Spatial 5163/tcp #ArcSDE for SQL Server >> %windir%\system32\drivers\etc\services
echo sdesdeblank 5166/tcp #ArcSDE for SQL Server >> %windir%\system32\drivers\etc\services
echo sdeTexasFire 5170/tcp #ArcSDE for SQL Server >> %windir%\system32\drivers\etc\services
echo sdeWindSpatial 5171/tcp #ArcSDE for SQL Server >> %windir%\system32\drivers\etc\services
From here, we need to do some copy pasting – the SQL code needs to go into another query window and be executed.
The sdeservice -o create, -o register and echo stuff needs to go into a batch file called “create.bat”. I included -o delete so you can easily remove all this crap when you get everything up to 9.3, so put those lines into a “drop.bat” file.
Then just run your create bat file, and start the services. You’ll need to add entries into everyone’s services file or just send out the port numbers, which is my preference… as shown below, the connection is good to go.
as “fun” as this was, unfortunately it was a total waste of time.
