Create attribute rules to access feature classes in a different database instance

Summary

In the previous article on Creating Attribute Rules to Access Feature Classes in a Different Database, we explored creating and registering a view that accesses a feature class in a different enterprise geodatabase on the same SQL Server instance. The view was subsequently used in authoring an Attribute Rule. This article takes a slightly different approach by creating the view on a separate SQL server instance. This is useful especially when you do not have the required permissions to query the feature class you want to use in authoring your Attribute Rule.

In this example, using Microsoft SQL Server, we are trying to access the FAVORITEFOODANDLOCALE feature class owned by DataLabOwner stored in the DataLab geodatabase on a different instance, on the same network. To achieve this, we must create a database link to access the data on the separate instance.

Procedure

Workflow for creating a database link 

1. Open SQL Server Management Studio (SSMS) and connect as the Database Administrator or Data Owner.
2. Follow the instructions in the article Create linked servers (SQL Server Database Engine) in SQL Server Management Studio to create a link to the targeted SQL Server instance.
   1. In the General tab, set the Server type to SQL Server, and in the Linked server textbox, type the name of the instance (machine name) or IP address of the machine.
   2. In the Security tab, set the connection login option to Be made using this security context and specify the username and password of the Database Administrator or the Data Owner of the feature class you are trying to access.
   3. In the Server Options tab, set RPC and RPC Out to True.
   4. Test the connectivity by right-clicking on the linked server, and select Test Connection.

Create a view to access the feature class from the linked server

1. In SSMS, click New Query
2. Review and type the following query to create a view:

Use sqlDatabase
Go
Create view DataAdmin.vw_FavoriteFoodandLocale_LS1
as
Select *
from
OPENQUERY([10.150.51.50],
    'Select
        OBJECTID,
        Place,
        Item,
        IsBeverage,
        State,
        Shape,
        GlobalID,
        GDB_GEOMATTR_DATA
    From DataLab.DataLabOwner.FAVORITEFOODANDLOCALE')

In this example, sqlDatabase is the active database on my instance and the name of the view is vw_FavoriteFoodandLocale_LS1 with the schema owner as DataAdmin. The OPENQUERY method is being passed to allow us to execute a pass-through query to access tables in the linked server [10.150.51.50].

3. Execute the query to create the database view:
   • The DataAdmin.vw_FavoriteFoodandLocale_LS1 view is created and added to the database.
   • Next, we will access the view in ArcGIS Pro and register it with the geodatabase.
4. Open your ArcGIS Pro project.
5. In the Catalog Pane, if necessary, right-click Databases and connect to the database you are working in by establishing a New Database Connection.
6. Expand the database and confirm that the view you created is available.
7. In the Catalog pane, in the database, right-click the view and choose Register with Geodatabase.
8. In the Register with Geodatabase geoprocessing tool pane, set the required parameters and Run the tool.

The view is registered with the geodatabase and is accessible using Arcade with the $datastore function when setting up an Attribute Rule, as shown in the example and image below.

var fnl = FeatureSetByName
    ($datastore,"DATAADMIN.vw_FavoriteFoodandLocale_LS1, ["Place"])
var ffnl = Intersects(fnl, $feature)
var F = First(ffnl)
//var NPharma = NearestCoordinate(Fpharma, $feature)
if (F == null) return {"errorMessage: "Outside the county"}
return F.Place

This Attribute Rule populates a field (FnL) in a polygon feature class (DATAADMIN.Counties) with the place name of an intersecting (DATAADMIN.vw_FavoriteFoodandLocale_LS1) view layer, as shown in the next image.

In this article, we explored creating a view in SQL Server database that references a feature class from another database in a different SQL Server instance. We created a linked server to the target instance while connecting as a data owner, which gave us the permission to access and query the layer in our active instance.

After registering the view with the database, we were able to refer to the view via an Arcade expression when setting up an attribute rule to generate information based on the attributes in the feature class in the other instance.