When PASS dissolved in 2021, we also lost the SQLSaturday website and administrative tools to manage SQL Saturday. It left SQL Saturday Organizers without an easy way to provide SpeedPasses to the attendees.
Here is what we did for SQL Saturday Jacksonville, 2022, building on what Andy Warren did for SQL Saturday Orlando, 2021.
What you'll need:
Avery Business Card Paper (Amazon is the cheapest. If your group is a non-profit and has a sales tax exemption from your state, file for Amazon Tax Exemption here.)
Stapler & Staples - Only if you require more than one 10 card sheet per attendee.
Microsoft Word - Download the Avery Template 5870, or download our QR Code/vCard template here.
Azure Data Studio - For importing files from Eventbrite.
A few SQL tables on your local SQL Server instance.
Laserjet printer - With spare ink ready to go.
Summary
The paper we used was perforated business cards - Avery Printable Business Cards, Laser Printers, 2,000 Cards, 2 x 3.5, Clean Edge (5870), White ($69.10 each)
Each sheet has 10 business cards, and with our Registration, Lunch, Ice Cream, ID Badge, and sponsors, we had to print 2 sheets (20 cards) per person. That one pack of cards worked for 100 attendees. We had to purchase 4 packs ($276.40), plus a small pack, as we had 422 registrations.
We stapled the top tear-away margin of the paper together since we used two sheets. This also made it easy to grab it out of the file boxes at the registration table.
We started by using the Avery Template 5870. We had quite a few issues with ensuring the vCard QR code fit based on the character length of the data for each attendee and the sponsor's image on each business card.
We were going to put only the email address as a QR code, but then there was no real way to save it or export it. Then we decided to just create a vCard and have that be the QR code. This way the sponsors could save the contacts to their phones and then, with a filter, export the data to a spreadsheet or list.
We exported a vCard from outlook for comparison. After a few iterations, we got the file to work as a string saved to a text file from SQL Server populated with data from Eventbrite.
Scanning Process
Explain the vCard QR SCANNING, SAVING, and EXPORTING process to your sponsors, so they understand how to secure their contacts/leads from the event.
Below, see the QR code highlighted on a phone which contains the vCard.
Then after selecting the QR code, here is what the vCard looks like. We placed a note in the 'Notes' text to distinguish this contact from other existing contacts. 'SQL Saturday Jacksonville 2022 Contact'. Now just click 'create new contact'.
In Outlook, you can search and find all contacts matching those notes. Here are some instructions on exporting contacts from Outlook and Outlook Web.
Eventbrite Data
Now to obtain the data for the SpeedPass, go to Eventbrite, select 'Reports', then 'Custom Question Responses'.
Select your event, and 'then export 'CSV'.
Importing Data
Open Azure Data Studio and install the extension 'SQL Server Import' if you haven't already. (So far we have found that this is the fastest way to import random .csv files into SQL Server. If you have a better way, please let us know!)
Connect to your local SQL Server, right-mouse, click on your database, and select 'Import Wizard'.
Select the database and the file you just downloaded from Eventbrite. Then rename the table to 'Attendees'. Before continuing, if you have gone through this process previously, rename the old 'Attendees' table to 'Attendees1' or 'Attendees2' etc., so you can reference it if need be. This import process won't work if the table name already exists.
Click 'Next' on the Preview Data screen.
Depending on your data and custom questions, you may need to adjust some data types (i.e. bit to varchar(3) for 'yes/no') before importing. If it fails, you may have to come back and adjust it a few times. (Also, for the sake of time, we set all columns to allow nulls.)
Click Import Data, and then if all goes well, you should see a message like this below.
We performed this import and printed new SpeedPasses every few days leading up to the event as new registrations came in, the night before the event, and also Saturday during the morning sessions for the walk-in registrations.
vCard and Schema
Here is the 'vCard' column in the query.
CAST('BEGIN:VCARD
VERSION:3.0
N:' + a.Last_Name + ';' + a.First_Name + ';;;
FN:' + a.First_Name + ' ' + a.Last_Name + '
NICKNAME:' + ISNULL( CASE
WHEN LEFT(a.Twitter_Handle, 1) <> '@'
AND LEFT(a.Twitter_Handle, 2) <> '''@' THEN
'@' + REPLACE(a.Twitter_Handle, 'https://twitter.com/', '')
WHEN LEFT(a.Twitter_Handle, 2) = '''@' THEN
REPLACE(a.Twitter_Handle, '''@', '@')
ELSE
a.Twitter_Handle
END, ''
) + '
ORG:' + REPLACE(REPLACE(a.Company, ',', '\,'), '’', '''') + ';
TITLE:' + a.Job_Title + '
EMAIL;TYPE=WORK,INTERNET,pref:' + ISNULL(a.Email, '') + '
ADR;TYPE=WORK:;;' + ISNULL(a.Zip_Code, '') + ';
URL;TYPE=WORK:' + ISNULL(a.Website, '') + '
URL:' + ISNULL(a.Blog, '') + '
NOTE;ENCODING=QUOTED-PRINTABLE:SQL Saturday Jacksonville 2022 Contact
END:VCARD
' AS VARCHAR(MAX)) AS vCardHere is the text output.
BEGIN:VCARD VERSION:3.0 N:Taylor;Jeff;;; FN:Jeff Taylor NICKNAME:@ReviewMyDB ORG:Fulton Analytics; TITLE:Principal Data Architect EMAIL;TYPE=WORK,INTERNET,pref:jeff@reviewmydb.com ADR;TYPE=WORK:;;32246; URL;TYPE=WORK:https://jefftaylor.io URL:https://jefftaylor.io/blog NOTE;ENCODING=QUOTED-PRINTABLE:SQL Saturday Jacksonville 2022 Contact END:VCARDHere is the full schema for you to download. *Do not forget to change the 'Note' text to your event!
Mail Merge and Printing
If you would like, you can start by using our Word template as we have tested with various sizes of data inside the vCard, which changes the size of the QR code on each card. If you'd like to start from scratch, you can use the Avery 5870 template for the business cards.
If you use our Word template, after opening it up, click on 'Mailings', then 'Select Recipients', and 'Use an Existing List'
Then click on 'New Source'.
Select 'Microsoft SQL Server', and then click 'Next'.
Next, type in the SQL Server where the data is and click 'Next'.
Select the view 'AttendeesGetUnPrintedOrders' and click 'Next'.
Enter your connection file name and click 'Finish'.
Next, click on 'Preview Results' to view the data from your query.
Two things we want to note as key items.
ALWAYS sort. Click on 'Edit Recipient List', then click 'Filter', then sort by 'Last_Name'.
2. When opening the saved mail merge file in the future, ALWAYS select 'NO' when you see this pop-up. If you don't, it may keep your previous run data in Word/Mail Merge and create duplicates of previously printed SpeedPasses along with the new registrations. Always reload your data fresh each time you want to print a new round.
Once you feel confident that the vCard QR codes do not go beyond the edge of the business card margins, print several on regular printer paper and hold them over the business card paper with a backlight to double-check your margins.
Another thing we did was manually feed the printer two sheets of paper at a time. We didn't put the paper in the drawer, as thicker paper leads to paper jams.
Here are the printer settings. Note, we changed the Paper source to 'Manual Feed' and the Paper type to 'Cardstock'.
Print Cleanup - Important
After each print run, we executed a stored procedure (dbo.AttendeesPrintedInsert) to mark all printed SpeedPasses so they wouldn't be selected for printing in the next run.
The stored procedure takes all 'order ids' and places them into the table dbo.AttendeesPrinted.
Final Product
Here is what our printed SpeedPass looked like.
We hope this helps your SQL Saturday event run more smoothly. If you use this process, please let us know. If you have any comments or suggestions to improve the SpeedPass, please comment below or feel free to reach out to us at any time!
Enjoy!
Comments