Linking Bentley elements properties to SQL database table

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.

Parents
  • 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 “classic” 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.

Reply
  • 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 “classic” 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.

Children
No Data