About UsEdit in Gitbook

Database Tables

This page contains information on the database tables in assessingsearch on VSQL01. (NOTE: assessingupdates is a clone of assessingsearch for dev)

Views

This view does not appear to be used by default.asp or any other procedures.

This view could be deleted from the DB.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create view [dbo].[value_tax]
as 
SELECT TOP 1 dbo.taxbill.*, dbo.Res_exempt.personal_exemption, dbo.Landuse_Described.Description, dbo.propertycodes_described.*,
dbo.Res_exempt.residential_exemption, dbo.parcel.condo_main condo__main, tax_preliminary.[Bill Year], tax_preliminary.[Bill Number],
tax_preliminary.[RE Tax Amt], tax_preliminary.[CPA Amt], tax_preliminary.[Downtown BID Amt], tax_preliminary.[Greenway BID Amt], tax_preliminary.[Total Billed Amt]
FROM dbo.taxbill  
LEFT OUTER JOIN dbo.parcel ON dbo.taxbill.parcel_id = dbo.parcel.parcel_id  
LEFT OUTER JOIN dbo.Res_exempt ON dbo.taxbill.parcel_id = dbo.Res_exempt.parcel_id  
LEFT OUTER JOIN dbo.propertycodes_described ON dbo.taxbill.property_type = dbo.propertycodes_described.[property-code]  
LEFT OUTER JOIN dbo.Landuse_Described ON dbo.taxbill.land_use = dbo.Landuse_Described.Short_Description  
LEFT OUTER JOIN dbo.tax_preliminary ON dbo.taxbill.parcel_id = dbo.tax_preliminary.parcel_id  

GO

Stored Procedures

Active

This stored procedure is run to complete the processing of temporary import tables into the main data tables, after the MSAccess tables have been extracted see Updating Database Tables below.

This SP is ready to be used. For each maintenance cycle, the only alterations required are: 1. [best practice] clone this SP and give it a name relevant to this Financial Year and quarter 2. [required] change the @quarter to be "1" in December and "3" in July. 3. [required] search the script and update the names of dated import tables with the names from the tables created during the MSAccess Import.

Inactive

None of these stored procedures appear to be used by default.asp or any view or triggers.

These procedures could be deleted from the DB

Database Table Structure

additional_data

This table is accessed from default.asp.

Column
Source
Notes

parcel_id nvarchar(10)

MS Access

PK The unique parcel ID

living_area int

MS Access

gross_area int

MS Access

year_built smallint

MS Access

year_remodeled smallint

MS Access

condo_units_residential smallint

MS Access

condo_units_commercial smallint

MS Access

condo_units_mixed smallint

MS Access

stories decimal(4, 1)

MS Access

bid

This table is accessed from default.asp.

Potentially, there is some adjustment of the bid amount depending on billing schedule.

Column
Source
Notes

parcel_id nvarchar(10)

PK The unique parcel ID

bid_greenway money

MS Access

This is extracted and compiled from the greenway_bid table in the MSAccess database.

bid_downtown money

MS Access

This is extracted and compiled from the greenway_bid table in the MSAccess database.

condo_attributes

This table is accessed from default.asp.

Column
Source
Notes

parcel_id nchar(10)

Style nvarchar(20)

Exterior Condition nvarchar(14)

Exterior Wall nvarchar(14)

Grade nvarchar(14)

Stories nvarchar(4)

'#Units' decimal(9, 0)

Street Type nvarchar(50)

Square Feet of Living area decimal(9, 0)

Base Floor nvarchar(4)

Fireplaces nvarchar(4)

'# Floors' nvarchar(4)

Total Rooms nvarchar(4)

Bedrooms nvarchar(4)

Bathrooms nvarchar(4)

Half Bathrooms nvarchar(4)

Bathroom Style1 nvarchar(14)

Bathroom Style2 nvarchar(14)

Bathroom Style3 nvarchar(14)

Kitchen Style nvarchar(14)

Kitchen Type nvarchar(14)

Heat Type nvarchar(14)

Interior Condition nvarchar(14)

Interior Finish nvarchar(14)

Orientation nvarchar(14)

Corner Unit nvarchar(14)

View nvarchar(14)

condo_main nchar(10)

CNS_BID decimal(9, 0)

current_owners

This table is accessed from default.asp.

Column
Source
Notes

Parcel_id nchar(10)

PK

seqno tinyint

PK

owner_name nvarchar(255)

landuse_described

This table is accessed from default.asp.

Column
Source
Notes

id int

Short_Description nvarchar(10)

Description nvarchar(50)

outbuildings

This table is accessed from default.asp.

Column
Source
Notes

parcel_id nchar(10)

PK

line_number tinyint

PK

Code nvarchar(50)

Tot Units decimal(9, 2)

Quantity decimal(9, 2)

Quality nvarchar(50)

Condition nvarchar(50)

Overval_application_numbers

This table does not appear to be used.

parcel

This table is accessed from default.asp.

Column
Source
Notes

parcel_id nchar(10)

PK

street_number nvarchar(10)

street_name nvarchar(50)

apartment_no nvarchar(20)

suffix nvarchar(2)

landuse nvarchar(2)

owner nvarchar(255)

condo_main nchar(10)

parcel_all_owners

This table is accessed from default.asp.

Column
Source
Notes

parcel_id nchar(10)

street_number nvarchar(10)

street_name nvarchar(50)

apartment_no nvarchar(20)

suffix nvarchar(2)

landuse nvarchar(2)

owner nvarchar(255)

condo_main nchar(10)

propertycodes_described

This table is accessed from default.asp.

Column
Source
Notes

property-code smallint

property-class tinyint

property-class-description nvarchar(255)

property-code-description nvarchar(255)

property-code-state bit

property-code-city bit

Res_exempt

This table is accessed from default.asp.

Column
Source
Notes

parcel_id nvarchar(10)

MSAccess:Tyler Real Estate Export File

residential_exemption nvarchar(255)

MSAccess:Tyler Real Estate Export File

personal_exemption bit

MSAccess:Taxes

Defaults to 0 but is 1 if either of Personal Ex Type 1 or Personal Ex Type 2 is not null.

RESIDENTIAL PROPERTY ATTRIBUTES

This table is accessed from default.asp.

Column
Source
Notes

parcel_id nvarchar(255)

Building Seq float

Composite Land Use nvarchar(255)

Building Style nvarchar(255)

Rooms nvarchar(255)

Bedrooms nvarchar(255)

Full Bath float

Half Bath float

Other Fixtures float

Bath Style 1 nvarchar(255)

Bath Style 2 nvarchar(255)

Bath Style 3 nvarchar(255)

Kitchens float

Kitchen Type nvarchar(255)

Kitchen Style 1 nvarchar(255)

Kitchen Style 2 nvarchar(255)

Kitchen Style 3 nvarchar(255)

Fireplaces float

AC Type nvarchar(255)

Heat Type nvarchar(255)

Interior Condition nvarchar(255)

Interior Finish nvarchar(255)

View nvarchar(255)

Grade nvarchar(255)

"# of Parking Spots" nvarchar(255)

Year Built float

Story Height nvarchar(255)

Roof Cover nvarchar(255)

Roof Structure nvarchar(255)

Exterior Finish nvarchar(255)

Exterior Condition nvarchar(255)

Foundation nvarchar(255)

tax_preliminary

This table is accessed from default.asp.

Column
Source
Notes

parcel_id nchar(10)

Bill Year smallint

Bill Number int

RE Tax Amt decimal(12, 2)

CPA Amt decimal(12, 2)

Downtown BID Amt decimal(12, 2)

Greenway BID Amt decimal(12, 2)

Total Billed Amt decimal(12, 2)

taxbill

This table is accessed from default.asp.

Column
Source
Notes

parcel_id nchar(10)

condo_main nchar(10)

ward_precinct_block nvarchar(7)

street_number nvarchar(10)

street_number_suffix nvarchar(10)

street_name nvarchar(50)

street_name_only nvarchar(50)

street_name_suffix nvarchar(2)

apt_unit nvarchar(20)

city nvarchar(50)

location_zip_code nvarchar(5)

location_zip_code_plus_four nvarchar(4)

owner nvarchar(50)

mail_addressee nvarchar(50)

mail_street_address1 nvarchar(50)

mail_street_address2 nvarchar(50)

mail_city_and_state nvarchar(50)

mail_zip_code nvarchar(5)

mail_zip_code_plus_four nvarchar(4)

country nvarchar(50)

future_owner nvarchar(50)

future_mail_addressee nvarchar(50)

future_owner_mail_street_address1 nvarchar(50)

future_owner_mail_street_address2 nvarchar(50)

future_owner_mail_city_and_state nvarchar(50)

future_owner_mail_zip_code nvarchar(5)

future_owner_mail_zip_code_plus_four nvarchar(4)

future_owner_country nvarchar(50)

land_area int

land_use nvarchar(2)

exempt_code nvarchar(4)

property_type nvarchar(4)

state_class_code nvarchar(1)

residential_building_value decimal(12, 2) residential_land_value decimal(12, 2) residential_air_rights_value decimal(12, 2)

commercial_building_value decimal(12, 2) commercial_land_value decimal(12, 2) commercial_air_rights_value decimal(12, 2)

industrial_building_value decimal(12, 2) industrial_land_value decimal(12, 2) industrial_air_rights_value decimal(12, 2)

open_space_land_value decimal(12, 2)

exempt_building_value decimal(12, 2) exempt_land_value decimal(12, 2)

total_building_value decimal(12, 2) total_land_value decimal(12, 2) total_value decimal(12, 2)

latest_sale_date date

latest_bkpgcert nvarchar(10)

residential_exemption_flag bit

coop_value decimal(12, 2)

clause_abatement_type_1 nvarchar(6) clause_abt_1_pct_ownership nvarchar(5) clause_abt_1_pct_occupancy nvarchar(5) clause_abatement_type_2 nvarchar(6) clause_abt_2_pct_ownership nvarchar(5) clause_abt_2_pct_occupancy nvarchar(5)

paraplegic nvarchar(6)

workoff_type nvarchar(6) workoff_credit_amt decimal(12, 2)

sewer_betterment_amt decimal(12, 2) street_betterment_amt decimal(12, 2) sidewalk_betterment_amt decimal(12, 2)

penalty1_type nvarchar(6) penalty1_amt decimal(12, 2) penalty2_type nvarchar(6) penalty2_amt decimal(12, 2) penalty3_type nvarchar(6) penalty3_amt decimal(12, 2)

fine1_type nvarchar(6) fine1_amt decimal(12, 2) fine2_type nvarchar(6) fine2_amt decimal(12, 2) fine3_type nvarchar(6) fine3_amt decimal(12, 2)

BID_type nvarchar(6) BID_amt decimal(12, 2)

nontax_collection_type nvarchar(6) nontax_collection_amt decimal(12, 2)

personal_exemption_flag bit

condo_main_value decimal(12, 2)

taxes

This table is accessed from default.asp.

Column
Source
Notes

parcel_id nchar(10)

PK

gross_tax decimal(12, 2)

net_tax decimal(12, 2)

persexempt_1 decimal(12, 2)

persexempt_2 decimal(12, 2)

persexempt_total decimal(12, 2)

resexempt decimal(12, 2)

cpa decimal(12, 2)

code_enforcement_tax decimal(12, 2)

38D_fine decimal(12, 2)

sidewalk_betterment decimal(12, 2)

street_betterment decimal(12, 2)

bill_number int

value_history

This table is accessed from default.asp.

Column
Source
Notes

Parcel_id nchar(10)

Fiscal_Year smallint

Assessed_value bigint

Land_use nvarchar(4)

Updating Database Tables

THIS FULLY EXPLAINS DEC STEP 6 AND JUNE STEP 4 FROM THIS CHECKLIST.

As noted elsewhere, data for the MS SQL databases are provided by Assessing in MS Access database/s.

The number of rows in the database tables is high (e.g. 150k - 400k records) and the data size of the tables can be high (30 - 500 MB).

This means a strategy for updating the SQL database with the source data needs to accommodate the size of the import, and the time it will take to import.

OVERVIEW

It is assumed that a new assessingupdates20YYQX database has been created:

  • in Q1 this should be a copy of the previous Q3 database, and

  • in Q3 this should be a copy of the previous Q1 database.

  1. Extract the schema from MS Access database and use to create new import tables in the new MS SQL database (aka the target database).

  2. Export the data from each of the source tables in MS Access as a sequence of INSERT statements in a series of files.

  3. Import the raw data from the files into "temporary" import tables in the target database.

  4. Manipulate the data from the "temporary" import tables into the "permanent" data tables in the target database.

STEP BY STEP

An extraction script exists (intended to run in a bash session) which extracts data from the MS Access file and uploads to the target MS SQL database.

A stored procedure exists which updates or replaces the new data with existing data in the target database.

Script Errors: (unless they've been fixed)

  1. The script will note an error when trying to drop a table that does not exist: Cannot drop the table xxxx, because it does not exist or you do not have permission This can safely be ignored.

  2. The script will likely throw a single error per file imported: Msg 102, Level 15, State 1, Server zPDMZSQL01, Line 1 Incorrect syntax near ')'. This is because there is a blank line at the end of each file and can safely be ignored.

This script will take some time to complete. - as much as 6 hours. There is lots of console output, so you can pipe the stdout to a file so you have a record: . extract.sh > out.txt

Once extract.sh has completed, all of the tables (and their data) from the MS Access database will have been copied into "temporary" import tables in the target MS SQL database.

If you do not have access to an SQL editor:

  1. Copy the SP text from here, and paste into any basic text editor.

  2. In the editor, change the sp name as per check item #2 above,

  3. Set the @quarter variable as per check item #3 above,

  4. Change the table names as per chec item #4 above,

  5. Save the file locally, (e.g. updatesp.sql),

  6. In a bash session, create the SP in the target database, run: >export SQLCMDPASSWORD=[password] >sqlcmd -S "[host]" -U "[username]" -d "[target]" -i "updatesp.sql" -M where - [host] is the network location of the MSSQL server (IPAddress or DNS entry) - [username] is username -see extract.sh -[password] is the username's password -see extract.sh - [target] is the database name, the name of the DB created for this quarter.

  7. In a bash session, execute the stored procedure: >export SQLCMDPASSWORD=[password] >sqlcmd -S "[host]" -U "[username]" -d "[target]" --query "exec sp_20YYQ1_update_01;" -M

After the SP completes (it will take <10 mins to run), the data update is complete and the database is ready for use by the asp pages.

PreviousAnnual PDF InitializationNextNo-Tow Maintenance

Last updated

Was this helpful?