SQL Prompt 7

SQL Prompt will automatically connect to other databases specified in your query. If you don't have permissions to connect to that database, a notification is shown:

Click the notification to see more information about the error:

Once you close this message, you can continue to write your query, but no suggestions from that database will be shown in the suggestions box.

Error notifications may also be displayed for reasons other than permissions issues, for example, when SQL Prompt doesn't have enough memory to store the objects for this database.

Managing database connections

By default, SQL Prompt loads suggestions for all databases specified in a query. However, to improve the performance of SQL Prompt you might want to exclude certain databases, or only load suggestions for particular databases.

Example: excluding a large database

In this example, we want to stop SQL Prompt from loading suggestions for the Jumbo database on all servers.

On the SQL Prompt menu, select Options.
Go to the Suggestions > Connections page.
Under Databases, select Don't load suggestions for certain databases.
Enter the following values:
Server Database
* Jumbo
You can use the * wildcard to specify all servers, or all databases on a server.

Connecting to different databases in the same session

If you connect to a number of different databases during the same session, SQL Prompt will load all the objects for each database as required, without unloading any of the previously stored objects. This means that you can go back to previous queries and continue to see suggestions for any database to which you have previously connected.

Refreshing suggestions when a database schema changes

If you are writing a query on a database while someone else is making changes to the structure of that database, the suggestions offered by SQL Prompt may become out of date.

For example, if someone has added several tables to the database while you have been connected to that database, these tables will not appear in the suggestions box.

To reload the database objects, on the SQL Prompt menu, select Refresh Suggestions or press Ctrl + Shift + D. Any changes to the database structure will now be reflected in the suggestions box.

You should also refresh suggestions when you have created a new object in the current query, and executed that block of code. For example, if you create a table NewObject and execute the statement, then continue to write the query, the NewObject table will not appear in the suggestions box until you refresh the suggestions.

Notes

Refreshing suggestions applies only to the database to which you are currently connected.
SQL Prompt automatically refreshes a database's objects when you close down your query editor application, then re-open it and re-connect to that database.

Experimental feature: Automatically refresh suggestions

If the SQL Prompt Labs feature Automatically refresh suggestions is enabled, SQL Prompt automatically refreshes the suggestion list when a change to a connected database is detected.

SQL Prompt Labs features are still in development, so might run slowly or have bugs.

To turn it on:

In SQL Server Management Studio or Visual Studio, click the SQL Prompt menu, and then click Experimental Features.
On the Experimental Features page, select Automatically refresh suggestions:

Managing the SQL Prompt memory

SQL Prompt loads database objects into its memory whenever you connect to a database in Management Studio or Visual Studio, in order to display suggestions from that database. For databases with large schemas, a progress dialog may be displayed while SQL Prompt loads the objects into memory:

If you connect to several databases with large schemas, this may eventually result in SQL Prompt running out of memory. In this case, a warning message is shown.

If this message is shown, you can clear the SQL Prompt memory by clicking Clear cache. SQL Prompt will load each database's objects as required.

If you close the message, you can continue to write your query, but no objects from that database will appear in the suggestions box.

Clearing the SQL Prompt memory

To clear the SQL Prompt memory at any time, you can disable suggestions. To do this, go to the SQL Prompt menu and clear Enable Suggestions (or press Ctrl + Shift + P):

This unloads all database objects from memory.

Connecting to a linked server

Linked server objects for the current instance are shown in the suggestions box:

When you select a linked server, SQL Prompt attempts to connect automatically to that server, using the mapped credentials for that linked server object.

If you have permissions to connect to the server, suggestions from that server will be loaded into SQL Prompt's memory.
If you do not have permissions to connect to that server, a message is displayed. Click Close to continue writing your query; no suggestions from the linked server will appear in the suggestions box.

To stop SQL Prompt attempting to connect to linked servers:

On the SQL Prompt menu, select Options.
Go to the Suggestions > Connections page.
Clear the Load suggestions for linked servers check box.

Synonyms and referenced databases

By default, SQL Prompt suggests synonyms, but it doesn't load databases referenced by synonyms into its memory. You can choose to allow SQL Prompt to load referenced databases.

If you have a large number of synonyms, loading referenced databases can affect the performance of SQL Prompt.

If you allow SQL Prompt to load referenced databases, more suggestions are provided using synonym base objects. For example:

Columns in referenced tables are suggested.
Parameters in referenced stored procedures are suggested.
You can expand wildcards (*) for synonyms.

To allow SQL Prompt to load referenced databases:

On the SQL Prompt menu, select Options.
Go to the Suggestions > Connections page.
Select the Load referenced databases check box.