Linking Bentley elements properties to SQL database table

Question

Gooday 
 
 How do i link my MS SQL server database tables with my GSA schema. When i update my Database in GSA i have no tables. I have attached my database and my schema file.

Kees van Prooijen [Bentley] · Answer

Fullscreen 
 MScatalog and Feature tables.sql 
 Download 
 
 use master
go
CREATE TABLE "MSCATALOG" 
 (	"TABLENAME" VARCHAR(32 ), 
	"ENTITYNUM" float, 
	"SCREENFORM" VARCHAR(64 ), 
	"REPORTTABLE" VARCHAR(64 ), 
	"SQLREVIEW" VARCHAR(64 ), 
	"FENCEFILTER" VARCHAR(64 ), 
	"DASTABLE" VARCHAR(64 ), 
	"FORMTABLE" VARCHAR(64 )
 );

INSERT INTO mscatalog (tablename,entitynum,screenform,reporttable,dastable,sqlreview,fencefilter,formtable) VALUES ('highway',1,'highway',NULL,NULL,NULL,NULL,NULL);
INSERT INTO mscatalog (tablename,entitynum,screenform,reporttable,dastable,sqlreview,fencefilter,formtable) VALUES ('parcel',18,'parcel','parcel_report','parcel_das','select * from parcel',NULL,NULL);

create table parcel 
 (
 mslink int not null,
 old_map_no char(5) null,
 group_no char(6) null,
 parcel_no char(15) null,
 trailer_no char(4) null,
 clt_no char(30) null,
 owner char(50) null,
 value int null,
 house_num char(10) null,
 street_name char(40) null,
 city char(40) null,
 state char(50) null,
 zip_code int null,
 county char(50) null,
 district char(50) null,
 zone_class char(50) null,
 block_num char(4) null,
 lot_num char(4) null,
 subd_name char(50) null,
 parc_area float null,
 perimeter float null,
 mapid int null
 );

grant all on parcel to public;
create unique index parcel_mslink on parcel (mslink);

use gtaa_gis_spatial
go
create table highway 
 (
 mslink int not null,
 hname char(40) null,
 htype char(10) null,
 classification char(50) null,
 date_created char(8) null,
 date_paved char(8) null,
 surface_type char(50) null,
 surface_rating int null,
 weight_limit int null,
 height_limit int null,
 speed_limit int null,
 width int null,
 row_width int null,
 length float null,
 num_of_lanes int null,
 division_status	char(50) null,
 direction char(50) null,
 hour_traffic_vl int null,
 year_traffic_vl int null,
 city char(40) null,
 county char(50) null,
 state char(50) null,
 mapid int null
 );

grant all on highway to public;
create unique index highway_mslink on highway (mslink);

Configuring the &ldquo;classic&rdquo; database connection with a SQL Server database via the Database node in the XFM schema. It uses the Mscatalog table and mslink column in the property tables. With this option the properties are stored in an external database instead of in the DGN file. 
 In the online help file , go to the following pages to have more information: 
 
 OpenCities Geospatial Administrator > OpenCities Geospatial Administrator Features > Defining Feature Properties > DGN Resident Versus Database Properties. 
 OpenCities Geospatial Administrator > Database.

Some remarks: 
 
 As an example, use the attached SQL script MScatalog and Feature tables.sql to create Mscatalog table and two Feature Property tables in the SQL Server database. 
 Create an ODBC datasource name that makes a connection with an SQL server database e.g. LucaraBotswana . 
 In the Geospatial Administrator, make a connection (note that Windows Authentication is not available here). 
 If the connection is successful, in the Overview tab, tables are listed that can be used for database properties. 
 Next create features, and add database properties.

Linking Bentley elements properties to SQL database table

Top Replies