Transact-SQL Barcode Font Encoder Formula Tutorial
This Transact-SQL (TSQL) barcode font tutorial explains to database developers and users how to encode data for barcodes in Microsoft SQL Server using TSQL font
formulas.
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
This demonstration uses IDAutomation's TSQL function in MS SQL Server to encode data in a field for Report Builder. To display the barcode in Report Builder, select the barcode font on the data field. 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 the purchase of a 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 IDAutomation Barcode Fonts. The example uses the Code 128 Font Package to generate Code 128 barcodes. Locate the TSQL Font Formulas in the Licensed version of the separate Developer Tools ZIP file included with the 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 then paste the IDAutomation font formula (copy the code from the IDAutomation_Code128B.sql file) into the query file.
- Select Execute. This operation stores 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.
- Create a query; select the New Query button.
- Select the fields to use from the table in the database.
Results:
- Use the formula
format DatabaseName.dbo.FunctionName([Field]) As FieldName to encode the field for the barcode.
- 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.
- Right-click the new table and choose Select Top
1000 Rows to query the results. The BarcodeID field displays the encoded data.
- Export the encoded data as a data source into an application, such as Report Builder. To display the barcode in an application, highlight the encoded data or encoded data field, and then choose the IDAutomationC128M barcode
font.
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.