Troubleshooting

This topic provides information that may help you if you are experiencing problems with SQL Prompt.

Candidate list displays incorrect candidates

When SQL Prompt parses SQL code to populate the candidate list, the number of lines of code parsed is defined by the number of lines specified in Search a fixed number of lines from the caret. This setting is on the Listed Candidates, Variables and Parameters options page.

If you decrease this value, performance is improved because SQL Prompt parses less code. However, if you have a statement that spans more lines than are parsed, SQL Prompt cannot populate the candidate list correctly. You may also see unexpected results if SQL Prompt parses some, but not all, of a BEGIN... END block.

In general, it is good practice to refactor very large stored procedures or functions into smaller blocks. If you do this, SQL Prompt is less likely to encounter parsing problems.

If you work only with small scripts (less than 100 lines of SQL code), or with large scripts that have batch markers every 50 or 100 lines, you can select Search entire batch/GO block on the Listed Candidates, Variables and Parameters options page. SQL Prompt can then display an accurate list of candidates without slowing performance.

The candidate list may also display unexpected results when you type SQL statements that contain invalid syntax, because SQL Prompt may be unable to parse the statement.

Candidate list incorrect for JOIN clause

The following SQL code snippets may cause SQL code to display incorrect candidates in the candidate list when you press CTRL+SPACEBAR at the insertion point:

and:

This is because you have not followed the SELECT keyword with * or a list of columns, therefore the SELECT statement is invalid.

You may have omitted the list because you intend to add it later using SQL Prompt. However, you must always type * after the SELECT keyword, even when you want use SQL Prompt to insert the column list later. You can then go back to the SELECT keyword, delete the *, and press CTRL+SPACEBAR to display the candidates.

For examples, see Worked Examples.

Alias not assigned to table following unterminated BEGIN...END block

If you do not terminate a BEGIN... END clause in a WHILE loop, SQL Prompt does not recognize the WHILE loop and therefore may not generate aliases even though Enable alias assignment is selected on the Auto Insert, Aliases options page.

The following example uses the pubs database on SQL Server 2000. In this SQL code, the BEGIN clause is not terminated:

When the table is selected, SQL Prompt inserts the table name without assigning the alias:

If the END keyword is included in the SQL code, SQL Prompt assigns an alias to the table.

SQL Prompt is showing an old version of a stored procedure

When displaying the creation SQL for an object in the schema panel, or inserting SQL code into the query editor window, SQL Prompt uses the version of the SQL in its cache file; it does not retrieve the latest version from the database. This means that the SQL code may be out of date.

Because of this, if you auto insert an object definition after the ALTER statement and you have not updated your cache, it is possible to accidentally overwrite the new version of a stored procedure with the old version stored in the cache file. Therefore, when you are editing SQL code on a database for which the schema may have changed, ensure you refresh the cache before you use the auto insertion features (you can press CTRL+SHIFT+D to refresh the cache).

For more information about caching databases, see Managing the SQL Prompt Cache.

SQL Prompt menu needs to be restored

If you are using SQL Server Management Studio, and you need to restore the SQL Prompt menu, run sqlwb.exe /setup at the command prompt. Note that this restores all the SQL Server Management Studio menus.

Common table expressions

SQL Prompt does not support common table expressions.

Quoted identifiers

SQL Prompt does not support quoted identifiers. However, if you have quoted identifiers switched on, this does not usually cause a problem with SQL Prompt.

String literals

SQL Prompt does not provide completion assistance inside string literals.

Contacting SQL Prompt Technical Support

You can contact the Red Gate support team at support@red-gate.com.

The support team may ask you to send a log of SQL Prompt's activities; to do this, on the SQL Prompt menu, click Show Log. The log file is opened in your default text editor and you can save or copy the text as required.