Transact-SQL Barcode Font Encoder Formula Tutorial
This Transact-SQL (TSQL) barcode font tutorial provides a walk-through of steps for formatting data for barcodes in Microsoft SQL Server using TSQL font formulas. Once the font formula formats the data in MS SQL Server, barcode fonts can be applied to the formatted data source in applications such as Report Builder for SSRS.
Compatibility
- MS SQL Server 2012 or greater.
- A Developer License to IDAutomation Code 128, Code 39 or Interleaved 2 of 5 font packages.
Download Demo The demo version includes only the Code 39 symbology for proof of concept.
Transact-SQL Barcode Font Formulas Tutorial Overview
Microsoft SQL Server database fields can be formatted for barcode fonts using IDAutomation Transact-SQL barcode font formulas. This tutorial will demonstrate how to use the barcode font formula in Microsoft SQL Server by calling the function in a query and storing the formatted data in a table. The example uses the IDAutomation Code 128B font formula to convert an Employee ID field into a field prepared for the barcode font.
Applications and Components used for this Tutorial
- MS SQL Server 2012
- Code 128 Font Package
- Transact SQL Font Formulas
Note: Use of this SQL Font Formula, requires a Developer License or above. This font encoder is supplied with Purchase of Developer's License for the Code 128, Code 39, or Interleaved 2 of 5 Font Packages.
Transact-SQL Barcode Font Formulas Tutorial
- Download and install the purchased IDAutomation Barcode Fonts. This example uses the Code 128 Font Package to generate Code 128 barcodes. The TSQL Font Formulas are located in the Licensed version of the separate Developer Tools Zip file included with purchase for Code 128, Code 39, and Interleaved 2 of 5.
- Run MS SQL Server and connect to the database.
- Navigate to Programmability - Functions - Scalar-valued Function and
right-click the folder. Select New Scalar-valued Function.
- Erase the contents of the SQLQuery.sql file and paste the IDAutomation font formula (copy the code from the IDAutomation_Code128B.sql file) into the query file.
- Select Execute. This will store the function in the Scalar-valued
Functions list.
NOTE: To view the function in the Scalar-valued Functions folder, right-click the folder and choose Refresh.
- To create a query, select the New Query button.
- Select the fields to use from the table in the database. This tutorial will
convert [Employee ID] from the
[Employee] table
to a field formatted for the barcode by applying
the font formula.
Results:
- To encode the field for the barcode, use the formula
format DatabaseName.dbo.FunctionName([Field]) As FieldName where:
- DatabaseName is the name of the database.
- FunctionName is the name of the IDAutomation function.
- Field is the DataToEncode.
- FieldName is the name of the column.
- To save the
results into a new table, use INTO and the name of the new table, such as
INTO NewTable.
- Select Execute.
- The new table with the encoded field is added to the database. If the table
is not visible, right-click the Tables folder, and select Refresh.
- To query the results, right-click the new table and choose Select Top
1000 Rows. The BarcodeID field displays the encoded data.
- The encoded data stored in the new table can be used as a data source in Report Builder
and other applications. To create the barcode, apply the IDAutomationC128M barcode
font to the encoded data.
Transact SQL Barcode Font Formulas
TSQL Functions for Barcode Fonts | ||
Barcode Type | Barcode Function Methods and Notes | Required Font |
Code-128 (Auto Mode) |
dbo.Code128
(DataToEncode,
ApplyTilde) Example: dbo.Code128('123456789012',0) |
IDAutomationC128 |
Code-128 (Manual Mode) |
IDAutomation recommends using
Auto Mode for most situations. dbo.Code128A (DataToEncode) dbo.Code128B (DataToEncode) dbo.Code128C (DataToEncode) Example: dbo.Code128b('12345678') |
IDAutomationC128 |
Code-39 | dbo.Code39
(DataToEncode) dbo.Code39Mod43 (DataToEncode) Example: dbo.Code39Mod43('12345678') |
IDAutomationC39 |
GS1-128 | dbo.Code128
(DataToEncode,
1) GS1-128 is enabled in Code 128 Auto by setting ApplyTilde to True. Example: dbo.Code128('(12)3456789012',1) |
IDAutomationC128 |
Interleaved 2 of 5 | dbo.I2of5
(DataToEncode) dbo.I2of5Mod10 (DataToEncode) Example: dbo.I2of5Mod10('123456789') |
IDAutomationI25 |
Support Methods
- Search the IDAutomation Public Forum for similar issues that have been resolved.
- Speak with a Sales or Support representative via online chat.
- Contact IDAutomation via email or telephone.