Creating an SQL Server Workspace

MitchWolberg · ‎07-24-2013

I'm having some problems creating a connection to an MS SQL Server DB.

      pWrkspcFact := CoSqlWorkspaceFactory.Create as IWorkspaceFactory ;
      pProps := CoPropertySet.Create as IPropertySet ;
      pProps.SetProperty ( 'DATABASE' , 'SamplesSQL' ) ;
      pProps.SetProperty ( 'DBCLIENT' , 'SQLServer' ) ;
      pProps.SetProperty ( 'SERVERINSTANCE' , 'Quandry' ) ;
      pProps.SetProperty ( 'AUTHENTICATION_MODE' , 'OSA' ) ;
      pWrkspcFact.Open( pProps , 0 ) ;

On calling Open I get along wait and the following error: "Failure to access the DBMS server"

This is coded in Delphi but it should work the same as .NET or C++. I've played with connection strings and different property names since the documentation is a bit inconsistent but without any success. Any idea what I'm missing or doing wrong?

Mitch Wolberg,
RockWare, Inc.

LeoDonahue · ‎07-25-2013

Mitch,

Not sure why I can get away with just the server name. Could be differences in our network setups.

I've re-capped the difference in connecting to a SqlWorkspaceFactory vs a SdeWorkspaceFactory, for the Java users. (if there are any...)

For the SqlWorkspaceFactory, anything less than the properties listed below will throw an AutomationException.

Ex:
AutomationException: 0x80040268 - The connection property set was missing a required property or the property value was unrecognized. in 'ESRI GeoDatabase' at com.esri.arcgis.datasourcesGDB.SqlWorkspaceFactory.open(Unknown Source)

And.. how would I know what property I'm missing if "SERVERINSTANCE" is not documented?

    private static void connectToSQLServer(){          try {              IPropertySet propertySet = new PropertySet();             propertySet.setProperty("SERVER", "server_name");             propertySet.setProperty("SERVERINSTANCE", "server_name");  // Or server_name\\server_instance_name if SQL named instance             propertySet.setProperty("DATABASE", "database_name");             propertySet.setProperty("DBCLIENT", "SQLServer");             propertySet.setProperty("USER", "user_name");             propertySet.setProperty("PASSWORD", "user_password");             //propertySet.setProperty("AUTHENTICATION_MODE", "DBMS");  // Optional.  DBMS is default             //propertySet.setProperty("AUTHENTICATION_MODE", "OSA");   // Required if using Operating Sytem Authentication              IWorkspaceFactory workspaceFactory = new SqlWorkspaceFactory();  // Note: using SqlWorkspaceFactory             IWorkspace workspace = new Workspace(workspaceFactory.open(propertySet, 0));              IEnumDatasetName dsFeatureClassNames = workspace.getDatasetNames(esriDatasetType.esriDTAny);              IDatasetName fcName = dsFeatureClassNames.next();             while (fcName != null){                 System.out.println(fcName.getName());                 fcName = dsFeatureClassNames.next();             }          } catch (AutomationException e) {             e.printStackTrace();         } catch (IOException e) {             e.printStackTrace();         }      }

     private static void connectToSDE(){          try {              IPropertySet propertySet = new PropertySet();             propertySet.setProperty("SERVER", "server_name");             propertySet.setProperty("INSTANCE", "sde:sqlserver:SERVER_NAME");             propertySet.setProperty("DATABASE", "database_name");             propertySet.setProperty("USER", "user_name");             propertySet.setProperty("PASSWORD", "user_password");             propertySet.setProperty("VERSION", "sde.DEFAULT");              // Create a SdeWorkspaceFactory and open it             IWorkspaceFactory sdeworkspaceFactory = new SdeWorkspaceFactory();  // Note: using SdeWorkspaceFactory             IWorkspace workspace = new Workspace(sdeworkspaceFactory.open(propertySet, 0));              IEnumDatasetName dsFeatureClassNames = workspace.getDatasetNames(esriDatasetType.esriDTAny);              IDatasetName fcName = dsFeatureClassNames.next();             while (fcName != null){                 System.out.println(fcName.getName());                 fcName = dsFeatureClassNames.next();             }          } catch (AutomationException e) {             e.printStackTrace();         } catch (IOException e) {             e.printStackTrace();         }      }

View solution in original post

LeoDonahue · ‎07-24-2013

Here is what I have in Java...

this is basically from one of the ESRI samples..

            // Create a propertySet that contains the connection information to ArcSDE
            //  In ArcCatalog, the first three values are: server, service, database in the connection properties of the sde connection.
            IPropertySet propertySet = new PropertySet();
            propertySet.setProperty("SERVER", "servername");
            propertySet.setProperty("INSTANCE", "sde:sqlserver:SERVERNAME");
            propertySet.setProperty("DATABASE", "databasename");
            propertySet.setProperty("USER", "sde");
            propertySet.setProperty("PASSWORD", "sdepassword");
            propertySet.setProperty("VERSION", "sde.DEFAULT");

            // Create a SdeWorkspaceFactory and open it
            IWorkspaceFactory sdeworkspaceFactory = new SdeWorkspaceFactory();
            IWorkspace workspace = new Workspace(sdeworkspaceFactory.open(propertySet, 0));

MitchWolberg · ‎07-24-2013

Thanks but it didn't work and since it isn't an SDE DB (though it does have geospatial data) I'm not sure it would have helped anyway.

SachinKanaujia · ‎07-24-2013

@Mitch

You are trying to connect using Operating System Authentication, so I hope you already have your database configured to use Operating system authentication else you need to configure it.

1) Try to see if normal SQL Server Authentication is working as per the code provided above by Leo Donahue. This essentially means you are able to make connections using arcobjects code.

            IPropertySet propertySet = new PropertySet();
            propertySet.setProperty("SERVER", "servername");
            propertySet.setProperty("INSTANCE", "sde:sqlserver:SERVERNAME");
            propertySet.setProperty("DATABASE", "databasename");
            propertySet.setProperty("USER", "databaseUser");
            propertySet.setProperty("PASSWORD", "databaseUserpassword");

            // Create a SdeWorkspaceFactory and open it
            IWorkspaceFactory sdeworkspaceFactory = new SdeWorkspaceFactory();
            IWorkspace workspace = new Workspace(sdeworkspaceFactory.open(propertySet, 0));

2) A good way to test OSA is to first use some kind of a client or sql command window to connect in OS Authentication mode. Then try using the code you have.

MitchWolberg · ‎07-24-2013

Got it too work with ISqlWorkspaceFactory by supplying the IP of the server!

      pProps.SetProperty ( 'SERVER' , 'servername' ) ;
      pProps.SetProperty ( 'INSTANCE' , 'sde:sqlserver:servername' ) ;
      pProps.SetProperty ( 'DATABASE' , 'SamplesSQL' ) ;
      pProps.SetProperty ( 'DBCLIENT' , 'SQLServer' ) ;
      pProps.SetProperty ( 'SERVERINSTANCE' , 'IP Address of server' ) ; //***Key***
      pProps.SetProperty ( 'AUTHENTICATION_MODE' , 'OSA' ) ;

Even better, I was able to create the IFeatureWorkspace, load the table and plot a point map.

Thanks for your help.
Mitch Wolberg
RockWare, Inc.

LeoDonahue · ‎07-24-2013

Well, I can't find the Java equivalent of class Type.. hmmm.

But to answer your question try this: http://help.arcgis.com/en/sdk/10.0/arcobjects_net/conceptualhelp/0001/0001000003z8000000.htm

Connecting to a SQL workspace
Connecting to a SQL workspace involves creating a SqlWorkspaceFactory and using one of the IWorkspaceFactory or IWorkspaceFactory2 Open methods to provide the workspace factory with connection properties.
       The following code sample shows how to connect to an Oracle 11g instance using the IWorkspaceFactory.Open method:

// Create the workspace factory. Type factoryType = Type.GetTypeFromProgID("esriDataSourcesGDB.SqlWorkspaceFactory");
IWorkspaceFactory workspaceFactory = (IWorkspaceFactory)Activator.CreateInstance     (factoryType);

// Create the connection properties. IPropertySet connectionProps = new PropertySetClass();
connectionProps.SetProperty("dbclient", "Oracle11g");
connectionProps.SetProperty("serverinstance", "MyServer");
connectionProps.SetProperty("authentication_mode", "DBMS");
connectionProps.SetProperty("user", "MyLogin");
connectionProps.SetProperty("password", "MyPassword");

// Open the workspace. IWorkspace workspace = workspaceFactory.Open(connectionProps, 0);

oh.. I see you already figured it out.

LeoDonahue · ‎07-24-2013

And I will add "Boo!" to the documentation that doesn't list the PropertyType: SERVERINSTANCE as a required property.

It lists "INSTANCE", but if you just use that value, it won't work, if you are using a SqlWorkspaceFactory.

MitchWolberg · ‎07-24-2013

Thanks Leo, there seems to be a lot of inconsistency in the help:

AUTHORIZATION_MODE vs AUTHENTICATION_MODE,
DBCLIENT vs DATABASE_CLIENT,
etc.

Maybe either one would work but it's just one more thing that makes it difficult to work things out. Of course it would also be nice if the components returned a more helpful error message that didn't leave you guessing as to the missing or incorrect parameters. One last gripe, why require both a Server and a Server IP address, one or the other should be sufficient.

There's a lot of power in ArcObjects but after I've spent a day dealing with an uncooperative black box I get frustrated.

Mitch Wolberg,
RockWare, Inc.

LeoDonahue · ‎07-24-2013

I was able to use your sample with server name where you supplied IP address. Maybe you need to supply the fully qualified name for your SERVERINSTANCE ?

server.domain_name maybe?

MitchWolberg · ‎07-25-2013

I was able to use your sample with server name where you supplied IP address. Maybe you need to supply the fully qualified name for your SERVERINSTANCE ?

server.domain_name maybe?

Just gave that a shot and got: Failure to access the DBMS server

Mitch