Creating an SQL Server Workspace

[MitchWolberg]

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 · Accepted solution]

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();         }      }

[MitchWolberg]

Hooray! I tried once more and it worked without using the IP of the server and using your code as a template and it worked.

Thank you for your time and help,
Mitch Wolberg,
RockWare, Inc.