Search results matching tag 'SQL'

SQL Queries for Patron Edge, Part III

06C0951D-0F3F-44D1-AA8F-3C9415303DC1 — Wed, 18 Nov 2009 20:08:00 GMT

This is my final entry in the series on SQL queries for Patron Edge. Today let’s take a look at the access control tables, so we can do some cool stuff with barcode scanning results:

select *
from Barcodes_Log
inner join Barcodes on Barcodes.brtSourceCode = Barcodes_Log.brtlCrossRefID and Barcodes.brtSourceTable = 2
inner join Barcodes_Scanners on Barcodes_Scanners.brtscCode = Barcodes_Log.brtlScannerID
inner join SubsStaticReport on SubsStaticReport.ssrSubsProgNum = Barcodes.brtSourceCode

Scanning tables are pretty complex, so let’s break down some of the fields:

brtlCrossRefID – Corresponds to Barcodes.brtSourceCode
brtlScanningResult – Corresponds to the lookup table Barcodes_ResultMessages
brtSourceCode – Corresponds to ReportStatic.rpsTickCode for tickets, SubsStaticReport.ssrSubsProgNum for membership cards and series
brtSourceTable – 1 for ticket, 2 for membership, 3 for series ticket, 4 for series event ticket
brtEntityType – only used for tickets, 1 for Show, 2 for Event
brtEntityCode – Corresponds to Shows.shCode or Events.evCode, based on the brtEntityType
brtStatus – 0 for active barcodes, 9 for canceled/reprinted

Now you can report on barcode scanning to do things like plan staffing at entrances, figure out traffic flow through your venue, etc. How are you using scanning data? Leave a note in the comments.

SQL Queries for Patron Edge, Part II

06C0951D-0F3F-44D1-AA8F-3C9415303DC1 — Fri, 13 Nov 2009 18:19:00 GMT

Continuing on yesterday’s theme, here are a few more tables and joins to do with what you will. We’ll start with the SubsStaticReport table this time, which is a data warehouse table that contains all of your subscriptions and memberships.

select *
from SubsStaticReport
inner join Clients on Clients.cltCode = ssrSubsNum
inner join StatusType on StatusType.stsStatus = ssrRenewalStatus

ssrSubsNum - Corresponds to Clients.cltCode
ssrCurrentStatus and ssrRenewalStatus – Correspond to the StatusType lookup table
ssrLastTransact – for series/memberships that are edited or canceled, this field points to ssrTransactNum of the original series/membership

Now let’s add on commissions and discounts:

select *
from SubsStaticReport
inner join Clients on Clients.cltCode = ssrSubsNum
inner join StatusType on StatusType.stsStatus = ssrRenewalStatus
inner join CalculatedCommission on CalculatedCommission.clcTicketNum = rpsTickCode and clcType = 4

You can refer to SQL Queries for Patron Edge, Part I to see what all the commission fields mean. Leave a comment below with your questions!

SQL Queries for Patron Edge, Part I

06C0951D-0F3F-44D1-AA8F-3C9415303DC1 — Thu, 12 Nov 2009 00:12:00 GMT

While giving a session at the Museum Computer Network conference on extracting data from a Patron Edge database, one of the attendees suggested creating a handful of sample queries. The idea is that with some samples showing the important table joins, users can run with them, expand on them, or even just push them directly into Excel to do pivot tables.

With that in mind, here are a couple of samples to get started:

select *
from ReportStatic
inner join Clients on Clients.cltCode = rpsMailingList
inner join Orders on Orders.ordCode = rpsOrder

The ReportStatic table is one of the major data warehouse tables in the system. It has a row for every ticket sale and return, including event tickets that are part of a subscription. Below are some useful fields that aren’t intuitive without having worked in the back end for a while. Check them out:

rpsTickCode – For single tickets, this is the Tickets.tiCode field. For subscription event tickets, it is the TicketsSubscription.tisCode field
rpsMailingList – Corresponds to Clients.cltCode
rpsOrder – Corresponds to Orders.ordCode
rpsSaleStatus – Active tickets have a status of 1, canceled tickets are a 9
rpsTickType – Corresponds to Colors.clrStatus
rpsPriceLevel – Corresponds to Tariff.taCode

Now, let’s add a little more to the query:

select *
from ReportStatic
inner join Clients on Clients.cltCode = rpsMailingList
inner join Orders on Orders.ordCode = rpsOrder
inner join CalculatedCommission on CalculatedCommission.clcTicketNum = rpsTickCode

This gives us some new fields to look at:

clcCommission – Corresponds to Commission.cmsCode
clcCachInOut – 0 means a positive amount (i.e. commission), 1 is a negative amount (i.e. discount)
clcType – 1 for single tickets, 4 for memberships/subscriptions
clcStatus – 0 for active, 9 for returned

Hopefully this will help you get started writing your own queries and reports. Be sure to leave a comment here, or start a new thread on the Patron Edge forum, with questions and to share your own queries.

Unable to connect Native Error: 17

EC919830-3CD1-4982-A17A-F868ACBF7C79 — Thu, 22 Oct 2009 12:43:00 GMT

Windows Server 2008 has been out for a while and it’s easier to move your Blackbaud product(s) to a new server than to continue maintaining the old server. After moving to a new server some of you may have trouble connecting your Blackbaud application to the new SQL Server. Workstations may get the error, “SQL Server does not exist or access denied. Native error: 17.” This is much more common with the release of Windows Server 2008.

The single most common reason can be summed up in one word – “Firewall.” Windows Server 2008 comes with the Firewall enabled by default and Windows Update has a habit of turning on Firewalls that have been turned off.

When moving servers, check the Firewall settings and the ports being used by SQL Server on the new server, sometimes they match up fine, other times they will be different. To determine if the Firewall is your issue, disable the firewall on your server and try to connect through your Blackbaud application on the workstation. If you can log in then the Firewall is your issue and you need to add an exception for the SQL Server port. If you still can’t log in then you have another issue.

The second most common issue is with DNS. The DNS server may not yet reflect the new server Name and IP Address. Try to PING the server by Name to see if it resolves to the correct IP Address. For example, when you open a command prompt and type PING Servername, it should result in a response of “Pinging Servername [192.168.0.1] (where Servername is the name of your server and 192.168.0.1 is the IP Address of your Server). If the results return a different IP address then you need to check your DNS settings. If you receive the correct IP address but failed ping results then you need to check Network Connectivity to the server.

How to Troubleshoot login issues is a useful solution if we are still having trouble logging in after moving to a new server. If you are familiar with working in the registry check the InstallSource (The Raiser’s Edge or Financial Edge/Education Edge/Student Information System) for your product to see if it has updated to point to the new Deploy kit on the new server. A simple repair by running the Setup.exe located in the Deploy folder will typically point the workstation to the new server.

Problems connecting to the database can be a common issue when working with SQL Server applications such as Blackbaud Products. Many of these issues are caused by Firewalls or incorrect DNS settings. Checking these two areas first will help resolve a majority of database connectivity issues related to the error “SQL Server does not exist or access denied. Native error: 17.”