Tips on working with SQL queries

Symptom

Database actions through an error, but I am not able to identify the issue by looking at the error message. 

Troubleshooting tips

1. Quotes in the SQL statement 

Replace any single quote (') characters with double quotes (") or vice versa.  

For example: 

            %var% = my value’ 

            SELECT * FROM logs WHERE message = '%var%'; 

  

The above query would produce an error due to the single quote at the end of the variable’s value. 

To resolve that, replace the single quotes in the SQL statement with double quotes: 

            %var% = my value’ 

            SELECT * FROM logs WHERE message = “%var%”; 

  

2. Percentage sign (%) in the SQL statement 

Percentage signs in PAD indicate the beginning of a variable. To use them as a character, they should be escaped by a an additional percentage sign (%%). Find more info here

 

3. Error communicating with the database 

Verify that the firewall or any other network security system is not blocking the connection between PAD and the database. 

 

4. Connection to Microsoft Excel 

An SQL connection can be established with Microsoft Excel, as soon as the appropriate driver is installed (Microsoft.ACE.OLEDB). 

Things to note: 

  • The database name is considered the name of the Excel worksheet followed by a dollar sign ($), for example [Sheet1$]. 

  • The database columns are the headers of the Excel data table. 

Need more help?

Expand your skills
Explore Training
Get new features first
Join Microsoft Insiders

Was this information helpful?

What affected your experience?

Any additional feedback? (Optional)

Thank you for your feedback!

×