This article explains several BarTender problems that cause invalid SQL Queries and provides a solution to them.
- Various database related error messages
- Query Prompt fails
Custom SQL Queries entered into the Database Connection Setup can pose problems if they are or become invalid. Conditions that lead to invalid queries include:
- Changes to Database – Names of the database, tables, or fields change or the structure (e.g. linkages or JOINS)
- Invalid SQL Script – The script itself is incorrect. Using SQL Management Studio is a good way to test the script
- Query Too Complex or Large for BarTender – The script is correct but it is too large or complex to be executed by BarTender (e.g. more than 3 JOINS)
The solution depends on the specific condition. Different conditions:
Changes to Database – The script needs to be updated to match the new database names and structure. If field names are changed, problems maybe found throughout the label wherever the changed Data Sources that use the database is referenced. Each of these settings need to be updated to new names/structure. If the change is in the database names, table names, or database structure (links between tables), then the database itself may need to be removed and added back again.
Invalid SQL Script – The script would need to be rewritten. Microsoft SQL Management Studio is a good application to test scripts. Bartender gives off different error messages for different script problems. Use the EBI Articles to see the different error codes and their meaning.
Query Too Complex or Large for BarTender – We have encountered issues where the valid query does not work in BarTender. It seems to happen when the query is too large or complex. One example is using more than three JOIN statements. This causes BarTender to lockup. Solving problems with a valid script requires creativity in finding a different way to write the script. In the case of the multiple JOINs we created a View in SQL and then connected BarTender to the newly created SQL View.
- BarTender Instruction Article: KBA-01176-L3N4B7 – Reestablish A Database Connection With a Custom SQL Query in BarTender
- SQL Server Instruction Article: KBA-01177-P6C6Z9 – Create A SQL View Using SQL Management Studio
- Problem Article: KBA-01170-Z4H1H4 – Multiple SQL JOIN Statements in the Database Setup Causes Problems in BarTender