There are a couple of gotchas when attempting to automate BarTender using XMLScript against an SQL database. Here’s the example from the documentation:
<?xml version="1.0" encoding="utf-8"?> <XMLScript Version="2.0"> <Command Name="Job1"> <Print> <Format>c:\labels\Label1.btw</Format> <RecordSet Name="SQL_OLEDB" Type="btOLEDB"> <UserID>Admin</UserID> <Password>seagull</Password> <SQLStatement>SELECT ProductName FROM Products WHERE Supplier = 'Exotic Liquids'</SQLStatement> </RecordSet> </Print> </Command> </XMLScript>
- The sql you use in XMLScript needs to match the sql that BarTender uses in the database setup of the label format. For instance, if BarTender wants to write the sql like this “dbo”.”TableName”.”FieldName” then the SQLStatement node of your XMLScript has to follow the same format. FieldName won’t work and neither will TableName.FieldName or even dbo.TableName.FieldName. The quotes are aparently important for BarTender to match the fields it has in the label format with the fields that come back from sql. The example will lead you astray here.
- The Name attribute of the RecordSet node needs to be the name of the sql database you are connecting to. The docs state that Name is the “Name of the record set.” which is not exactly clear and again, the sample is not much help.
- The UserID and Password are not necessary if you set the label format to Use Windows NT Integrated security.
- As far as I can tell there is no way to set the Server you want BarTender to query using XMLScript.
- You want to set the Use Client Cursor option in Database Setup Options of the label format to true. If not, the sql sent to the server is a series of unreadable prepared statements which as far as I can tell don’t actually evaluate to the query you set up in SQLStatement node.
- You have to make sure the print dialog Selected Records field reads 1… If you do a print preview while designing the label format, the program will helpfully populate this field with 1-8 if you have 8 records you are previewing. The problem is that this is then saved with the label format! When you issue an XMLScript SQLStatement that returns 20 records, only records 1-8 will print!