mSQL - Deprecated Stored Procedures

msp_CreateKeysTable

WARNING: This stored procedure has been deprecated and will be removed in a future release of mSQL.  The stored procedure should no longer be used – except for configurations that have compatibility setting less than 2.0.0 – and existing processes should be modified accordingly.

 

Input Parameters:

  • Configuration file – the file path of the configuration file to be used when this procedure is run.
  • Datasource ID – specifies the data source to be used within the configuration file, which contains the table and column mapping specifications.

 

Setting

Description

datasources

Specifies the database connection, table and column mappings used to define the dataset being processed.

The keys table that will be created when this procedure runs will have the same name as the first mapped table with a ‘__keys__’ suffix.

generalSettings->deleteKeysTableOnGenerate

When this setting is switched on, the keys table (if it already exists) will be deleted and recreated by this procedure, prior to being re-populated with keys by the BulkGenerateKeys stored procedure.

outputSettings->keyColumns

Specifies the columns that the keys table is created with (and populated by BulkGenerateKeys).

Note that all key fields used in the match keys (such as the fuzzy and exact match keys if running the relevant stored procedures) must be included in the keys table.

 

msp_AddKeyFieldsToTable

WARNING: This stored procedure has been deprecated and will be removed in a future release of mSQL.  The stored procedure should no longer be used, and existing processes modified accordingly.

 

Input Parameters:

  • Configuration file – the file path of the configuration file to be used when this procedure is run.
  • Datasource ID – specifies the data source to be used within the configuration file, which contains the table and column mapping specifications.
  • Table name – specifies the named table that the key columns will be added to.

 

Simply appends all necessary matching and key fields to the specified table in the database (using the connection string specified in the datasource).

Note that to use this procedure, your XML must specify only one table in the datasources section, and this must also be marked as the keys table. 

e.g.

<tables>

  <table name="contacts" isKeysTable="true" />

</tables>


msp_GenerateKeys

WARNING: This stored procedure has been deprecated and will be removed in a future release of mSQL.  The stored procedure should no longer be used, and existing processes modified accordingly.

 

Input Parameters:

  • Configuration file – the file path of the configuration file to be used when this procedure is run.
  • Datasource ID – specifies the data source to be used within the configuration file, which contains the table and column mapping specifications

 

This procedure generates the match key field values for all records in the current database (using the connection string specified in the datasource).

We highly recommended using msp_BulkGenerateKeys, with a separate keys table, as it provides the best key generation performance.                                                                                   

The settings are the same as msp_BulkGenerateKeys, except that the matching and key field data is not bulk loaded into an empty keys table. Significantly increasing the time taken for key generation, particularly when used with large databases.

 

msp_CreateCustomMatchesTable

WARNING: This stored procedure has been deprecated and will be removed in a future release of mSQL.  The stored procedure should no longer be used, and existing processes modified accordingly.

 

Input Parameters:

  • Configuration file – the file path of the configuration file to be used when this procedure is run.
  • Datasource ID – specifies the data source to be used within the configuration file, which contains database connection string and table/column mappings.
  • Table name suffix – table in which to create the primary key column.

 

Creates a custom schema of a matches table from the standard matches table as defined in the configuration object for the specified data source.  Relies on a matches table existing in the specified data source.  The table name defined in the configuration can be given a suffix as the third parameter of the procedure if creating multiple instances to distinguish between them.

 

msp_CreateCustomGroupedMatchesTable

WARNING: This stored procedure has been deprecated and will be removed in a future release of mSQL.  The stored procedure should no longer be used, and existing processes modified accordingly.

 

Input Parameters:

  • Configuration file – the file path of the configuration file to be used when this procedure is run.
  • Datasource ID – specifies the data source to be used within the configuration file, which contains database connection string and table/column mappings.
  • Table name suffix – table in which to create the primary key column.

 

Creates a custom schema of a grouped matches table from the standard matches_grouped table as defined in the configuration file for the specified data source.  Relies on a matches_grouped table existing in the specified data source.  The table name defined in the configuration can be given a suffix as the third parameter of the procedure if creating multiple instances to distinguish between them.

 

msp_GroupOverlap

 

Input Parameters:

  • Configuration file – the file path of the configuration file to be used when this procedure is run.
  • Datasource ID – specifies the data source to be used within the configuration file, which contains database connection string.
  • Level – Matching Level at which to group the records (Individual, Family, Household, Business, Custom).

 

After running msp_FindOverlap, this will group all matching record pairs into sets of matching records.  If you have previously run FindExactOverlap, then we recommend that you copy the matching pairs data from your exact_matches table into you matches table prior to running this grouping step, unless Merge Exact Matches is enabled.

 

Setting

Description

generalSettings->preventBridgedMatches

When enabled mSQL will attempt to stop match groups containing bridged records such as:

J Smith

John Smith

Julian Smith

In the scenario above, both John Smith and Julian Smith match with J Smith, but not with each other.

generalSettings->masterRecordIdentification

When this setting is active, mSQL will use the MasterPriority matrix to determine which record in a matching group should be marked as the master record (i.e. the best record). When this setting is off, the record with the lowest unique_reference will be chosen as the master record.

dataSources->ConnectionString

Connection string used to connect to the database during processing.

outputSettings->groupedMatchesTable

Name of the group matches output table that will be produced during the processing of this procedure.

Note that if the overlap attribute is empty, then the name in the name attribute will be used.

outputSettings->matchesTable

Name of the matches table containing the matching pairs that will be used as the input source for this procedure.

Note that if the overlap attribute is empty, then the name in the name attribute will be used.

  

Overlap matches_grouped table structure

During processing the stored procedure will output the results to the matches_grouped table (this name can be configured – see above). The structure of the output table is as follows:

 

Column

Description

ID

Record ID for each matching group.

Record1

Reference ID of the first record in the matching pair (from the Main database)

Record2

Reference ID of the record that is deemed to match Record1 From the Overlap datasource.

Score

The Score column is copied from the relevant level’s total score (grouping can only take place on one matching level; to group using multiple levels requires multiple runs of GroupMatches/GroupOverlap).

MatchRef

Indicates the unique reference of the master record in the group.

In the case of an overlap, the MatchRef column indicates the unique reference of the record from the overlap table; in effect, it’s a simple copy of the Record2 column.

BaseScore

Indicates the lowest score of all the matches in the group, normally not relevant to an overlap.

 

msp_GenerateNCOAAddresses

NCOA Address

Input Parameters:

  • Configuration file – the file path of the configuration file to be used when this procedure is run.
  • Datasource ID – specifies the data source to be used within the configuration file, which contains the table and column mapping specifications.

 

GenerateNCOAAddresses can be used to keep your database up-to-date as your customers move or their addresses are corrected.  Note that this is available to licensed users only, and can only process US data.

 

 

GenerateNCOAAddresses is available as both a stored procedure and an SSIS task.  The process involves sending data from the input table to an online service, and writing the received processed data into an output table for subsequent processing and use. 

Stored Procedure

When running as a stored procedure, the data source is used to configure the input table and field mappings that are passed to the NCOA service.  If this will follow GenerateCorrectedAddresses, be sure to use the corrected addresses table and its columns as inputs to the NCOA service.

To use the output NCOA tables and columns in following stored procedures (i.e. GenerateKeys), no further configuration is necessary because the stored procedure will be able to determine the existence of the NCOA table and automatically make use of it.

 

SSIS Task

When running as an SSIS task, the user has a choice of how the input table and mappings are obtained: from a preceding GenerateKeys task (in which case no extra configuration is necessary), from a preceding GenerateCorrectedAddresses task (in which case only the required name fields need mapping, while the corrected addresses are automatically used), or via manual configuration of the connection string, tables, and columns.

To use the output NCOA tables and columns in following tasks (i.e. GenerateKeys), it is necessary to map the table and its columns in the task.

 

 

Setting

Description

dataSources

Specifies the database connection, table and column mappings used to define the dataset being processed.

ncoa->customerInfo->listProcessor->name

List processor full name.

ncoa->customerInfo->listProcessor->street

List processor street address.

ncoa->customerInfo->listProcessor->lastLine

List processor city, state, ZIP.

ncoa->customerInfo->listProcessor->phone

List processor telephone number.

ncoa->customerInfo->mailer->name

Mailer full name.

ncoa->customerInfo->mailer->street

Mailer street address.

ncoa->customerInfo->mailer->lastLine

Mailer city, state, ZIP.

ncoa->customerInfo->mailer->phone

Mailer telephone number.

ncoa->output->table->name

Can be used to specify the name of the table that will be created.  If this is left blank, then the table’s name will be automatically generated.

ncoa->output->optionalFields

Any of these fields can be written to the output table. 

ncoa->options->useMixed

If this is set to "true", data is returned in mixed-case format. If this is set to "false", result data is returned in all upper case characters.

ncoa->options->useAlias

If this is set to "true", alias street names are returned when they are used in the input. If this is set to "false", 'official' street names are returned even when alias street names are used in the input.

(An alias street name is an alternative name for a street that is acceptable to the USPS. It may be a name by which a street was formerly known, a commonly-used nickname for the street, or one the community prefers to use.)

ncoa->options->validAddressesOnly

If this is set to “true”, and GenerateCorrectedAddresses has been run beforehand, then only valid addresses (with a score of 0) are processed by the NCOA service.

ncoa->options->blockSize

Data is uploaded to the NCOA service in ‘blocks’.  This setting specifies the maximum number of records per block.

ncoa->options->timeout

The maximum time to allow for every 100,000 records processed, in minutes (the default is 15).

This should be increased if you have a slow internet connection or are experiencing timeouts.

Timeouts can be disabled by specifying 0.

ncoa->options->retries

The maximum number of retry attempts when processing has failed (the default is 1).  Specify 0 to disable retry attempts.

Input Fields

 

The following fields must be input to the NCOA process, and will be written to the output table:

 

Name

Description

FullName or

Fullname field. Optional if an organization is specified

FirstNames andLastName

If FullName is not used, then both these fields are required.

Organization

Optional. Can be used to specify the company name.

Address1

Sets the “Street” field of the lookup record. Use this field type to pass all information relating to the primary street address, including the street name, house number, directional’s, and street suffix. You can also use this field type to pass all other acceptable forms of primary address information, such as PO Box numbers, rural route numbers, and highway contract numbers. In addition, suite and apartment information can be passed with primary address information through the “Street” field. For example, “123 Main St” and “123 Main St, Apt A” are both acceptable.

Address2

Optional. Can be used to specify any suite and apartment information, if these are in a separate column from the street part of the lookup address (see Address1 above).

Town

Specifies the city.

Region

Specifies the state.

Postcode or

Specifies the 9-digit ZIP code.

PostOut and PostIn

Specifies the 5-digit ZIP code and the ZIP+4, if these are in separate columns.

 

Output Fields

 

The following fields are written to the output table:

 

Name

Description

ncoaFullName or

Only if the fullname was specified on input.

ncoaFirstName andncoaLastName

Only if the fullname was not specified on input.

ncoaCompany

Optional. Retrieves the company name. This is only output if Organization was passed in via the input fields, or if this optional field is enabled 

ncoaStreet

Retrieves the street part of the output address.

ncoaSuite

Optional. Retrieves the suite or apartment information of the output address. This is only output if Address2 was passed in via the input fields, or if this optional field is enabled 

ncoaCity

Retrieves the city from the output address.

ncoaState

Retrieves the output state abbreviation.

ncoaZip

Retrieves the output ZIP Code.

 

Optional Output Fields

 

Any of the following fields can be written to the output table:

 

Name

Description

ncoaCOACode

This field will be populated with the return codes, see NCOA Return Code section for the description of codes returned.

ncoaCoaFound

This field is a True/False flag that tells whether an address change was found.

ncoaMoveEffectiveDate

Date of move. mm/yyyy

ncoaCompany

Retrieves the company name that was entered through the input “ncoaCompany” field, if any. AccuMail Move™ will also verify company moves based off this the company field.

ncoaSuite

Retrieves output secondary street information, such as suite and apartment information, if such information was entered through the input “ncoaSuite” field.

ncoaPlus4

Retrieves the output ZIP+4 Code. The ZIP+4 Code is the 4-digit extension only. You can retrieve the 5-digit ZIP Code using the ncoaZIP field type described earlier in this table.

ncoaUrbanization

Retrieves output urbanization information for Puerto Rican addresses.

ncoaDP

This string consists of a 2-byte delivery point code

ncoaCRRT

Retrieves the output carrier route code. This is a 4-digit code assigned to each address on a mail carrier’s route.

ncoaLotCode

Retrieves the output Line of Travel identifier consisting of a 4-digit number, plus a 1-character sequence code (either "A" for Ascending or "D" for Descending). The 4-digit number indicates the order in which delivery will be made within a given ZIP+4. The 1-character sequence code indicates whether delivery will be made in ascending or descending order. Once a LOT code is appended to your data file records, you can use it to presort your mailings so that they qualify for Enhanced Carrier Route rates.

ncoaLotDir

The order in which the mail carrier delivers mail within a given carrier route. When you include the Line of Travel information, your mail may be eligible for the USPS Standard Mail Non-Automation Basic Enhanced Carrier Route Presort Rate.

ncoaCountyNumber

Retrieves the output county number. This is the 3-digit USPS code for the county in which the address resides.

ncoaCountyName

Retrieves the output county name.

ncoaCongDistrict

Retrieves the output congressional district code. This is a 2-digit identifier for the United States congressional district to which the input address belongs.

ncoaLACS

A 1-character Locatable Address Conversion Service (LACS) code to identify records that have been converted to the LACS system. The LACS system is being used for many rural route addresses and city addresses that are being modified to city style addresses so that emergency vehicles, such as police cars and ambulances, can more easily find these locations.

ncoaAcsKeyline

Retrieves the output Address Change Service (ACS) keyline. The ACS keyline is a code the USPS uses to uniquely identify any address in the United States. You can use the ACS keyline to match the records in your mailing list with the list of ACS notifications in the USPS’s ACS fulfillment files.

ncoaHouseNumber

Retrieves the house number for the output street address. For example, if the output street address is “123 Main St,” the house number is “123.”

ncoaPreDirectional

Retrieves the pre-directional for the output street address. For example, if the output street address is “123 E Main St,” the predirectional designator is “E.”

ncoaStreetName

Retrieves the street name for the output street address. For example, if the output street address is “123 Main St,” the street name is “Main.”

ncoaStreetSuffix

Retrieves the street suffix for the output street address. For example, if the output street address is “123 Main St,” the street suffix is “St.”

ncoaPostDir

Retrieves the post-directional for the output street address. For example, if the output street address is “123 Main St N,” the postdirectional is “N.”

ncoaSUD

Retrieves the secondary unit designator (SUD) for the output street address. For example, if the output street address is “123 Main St Apt 12,” the SUD is “Apt.”

ncoaUnitNum

Retrieves the unit number for the output street address. For example, if the output street address is “123 Main St Apt 12,” the unit number is “12.”

ncoaLeftovers

Retrieves any leftover information that was part of the input street address string, but was not used for obtaining a match. Leftover information is input data that Accumail had to discard to correct the address. For example, if the input street address is “123 Main St Rubbish Here,” the output leftover information is “Rubbish Here.”

ncoaPMB

Retrieves the output Public Mailbox address

ncoaDPV

Retrieves the 3-byte DVP (Delivery Point Verification) match codes. The match codes indicate whether or not the address is valid (and if not, why not), whether or not the address is within a Commercial Mail Receiving Agency (CMRA), and whether or not the address was flagged as a False Positive. The “DPV” field returns a separate code in each position of the 3-byte result string, as follows:

BYTE 1

blank – The address was not coded by AccuMail and therefore no DPV processing was performed.

Y – All delivery point components of the address were DPV validated.

D – The address’s building number was DPV validated, but required unit-level information is missing.

S – The address’s building number was DPV validated, but the unit number is invalid.

N – The address’s building number is invalid.

BYTE 2

blank – The address was not coded by AccuMail and therefore no DPV processing was performed.

Y – The address was found in the CMRA (Commercial Mail Receiving Agency) table.

N – The address was not found in the CMRA (Commercial Mail Receiving Agency) table.

BYTE 3

blank – The address was not coded by AccuMail and therefore no DPV processing was performed.

Y – The address was found in the False Positive table.

N – The address was not found in the False Positive table.

ncoaDPVAnswer

blank – The address was not coded by AccuMail and therefore no DPV processing was performed.

Y – All delivery point components of the address were DPV validated.

D – The address’s building number was DPV validated, but required unit-level information is missing.

S – The address’s building number was DPV validated, but the unit number is invalid.

N – The address’s building number is invalid.

ncoaDpvCMRA

Commercial Mail Receiving Agency

ncoaDpvFalsePositive

This is a seed table used by the Delivery Point Verification Service (DPV). It is used by the service to guard against the possibility of mailers manufacturing artificial mailing lists from the data in the DPV database of every valid delivery point in the U.S.

ncoaDpvFootnotes

Retrieves the 8-byte DPV (Delivery Point Verification) Footnotes string. This field returns up to four 2-character codes that supplement the DVP field codes (described above) providing additional information about the DPV match/mismatch. Up to four of the following 2-character codes will be returned:

AA – The address was successfully coded by AccuMail.

A1 – The address was not successfully coded by AccuMail.

BB – All components of the address were DPV validated.

CC – The address’s building number was DPV validated, but the unit number is invalid.

N1 – The address’s building number was DPV validated, but required unit -level information is missing.

M1 – A building number is missing for the input address.

M3 – The address’s building number is invalid.

P1 – The input address is missing a required PO, RR, or HC Box number.

RR – The input address was identified by DPV as a Commercial Mail Receiving Agency (CMRA).

R1 – The input address was identified by DPV as a Commercial Mail Receiving Agency (CMRA), but required unit-level information is missing.

ncoaLastLine

Retrieves the output ‘last line’ string. This output ‘last line’ string is a formatted city/state/ZIP string that includes the correct ZIP+4 Code. For example, the ‘last line’ information for Datatech SmartSoft is “Agoura Hills, CA 91301-4301.”

ncoaMoveType

Indicates the move as Family, Business, or Residential.

ncoaResult

Retrieves the output error code. AccuMail assigns an error code if the input record could not be found in the USPS National Database. This is a 2-byte code that identifies what was wrong with the input address and why AccuMail could not match it. If AccuMail matched the input address successfully, then this field returns a blank string.

ncoaCorrections

Retrieves the output correction codes string. This string consists of single character codes that indicate the corrections AccuMail had to make to the input record.

ncoaErrorMessage

Retrieves the message text associated with the error code that AccuMail assigned if the input address could not be matched. This is a descriptive sentence or paragraph that describes the reason AccuMail could not correct the input record. If AccuMail matched the input address successfully, then this field returns a blank string.

ncoaWasDigitCoded

Returns a 1 for addresses that successfully coded with a plus4. Returns a 0 for uncoded addresses.

ncoaWasCRRTCoded

Returns a 1 for addresses that has returned with a Carrier Route or a 0 if no Carrier Route was returned.

ncoaWasPlusCoded

Returns a 1 for addresses that successfully coded with a plus4. Returns a 0 for uncoded addresses.

ncoaWasDPBCoded

Retrieves the output delivery point barcode string. This string consists of a 2-byte delivery point code plus a 1-byte checksum digit. These constitute the values required for producing a Delivery Point Barcode.

ncoaWasDPVCoded

Returns a 1 for addresses that were successfully DPV coded or a 0 for addresses that did not pass DPV validation.

 

Reports

 

Three reports are created after NCOA processing:

  • CASS Certificate (PDF);
  • NCOA Details Report - this is a text file that will list all matches found;
  • NCOA Processing Summary Report (PDF).

 

When processing has completed, the reports are moved to the reports folder (as specified by the path attribute of the outputSettings/reports node in the configuration file).

 

 

 

Was this article helpful?
0 out of 0 found this helpful

have a question or not finding what you're looking for?

Submit a ticket to get some help