What does the community do with monthly backup files and SQL queries?

Mal Colley

Hello everyone, we request a monthly data backup file from Blackbaud. We need to run some SQL checks on our data for reporting because some of the things that we need to look at in more detail cannot be done within RE Query. Our current solution is to export into Excel and use array formulas, but we would like to improve that process.

However, in interrogating our monthly backup file, we are now encountering some difficulties with many fields having a numeric code instead of something in plain English, where we've since learned that many tables have foreign keys as well as primary keys. We have acquired a schema, but that seems to hinder more than help, in terms of converting those numeric codes to either short descriptions or long descriptions.

I'm hoping that we cannot surely be the only client encountering these challenges, hence what does the Blackbaud community do instead? Is there a shared resource of standardised T-SQL queries for use with Microsoft SQL Server Management Studio, or do you just contact Blackbaud Project Support for a quote and delivery date?

Alex Wong

@Mal Colley
Blackbaud support for SQL is very minimal, KB has some article if you search “SQL”.

But will not be enough depending on your need. RE Schema help file is all you have to reference.

The tableentriesid you are seeing a lot is referencing TABLEENTRIES table, tableentriesid field.

For example:

Code from CONSTITUENT_CODES table reference TABLEENTRIES.tableentriesid

SELECT r.constituent_id AS ‘Constituent ID’,cc_te.longdescription AS ‘Constituent Code’

FROM records AS r

LEFT JOIN constituent_codes AS cc ON cc.constit_id=r.id

LEFT JOIN tableentries AS cc_te ON cc_te.tableentriesid=cc.code

You need to do your own due diligent to understand all the tables in the schema and how each relates. All the One-to-Many relationship. There is really no other “resource”

Venky Immadisetty

@Mal Colley
You can download the schema from here

And follow the below instructions to view the complete tables information in Schema