Managing connections and memory
Published 11 February 2013
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 include/exclude certain servers, databases or schemas.
To configure this:
- On the SQL Prompt menu, select Options.
- Go to the Suggestions > Connections page.
Under Databases, choose an option from the drop down to either include or exclude.
Option
Behavior
Load suggestions for all databases and schemas The contents of all databases and schemas on all servers will be loaded and used for suggestions. Only load suggestions for certain databases/schemas Only items in the specified database/schemas will be used for suggestions.
Don't load suggestions for certain databases/schemas Items in the specified database/schemas will be not used for suggestions. - Enter the names of the server, database and schema that you want the option to apply to.
You can use * and ? to match wildcards.
* will match multiple letters
? will match single letters
e.g. You can use the * wildcard to match all servers, all databases, or all schemas.
Example: excluding a database
To stop SQL Prompt from loading suggestions for the AdventureWorks database on all servers, enter the following values in the settings:
Server | Database | Schema |
|---|---|---|
| * | AdventureWorks | * |
Example: excluding a schema
To stop SQL Prompt from loading suggestions for the Sales schema of the AdventureWorks database on all servers, enter the following values in the settings:
Server | Database | Schema |
|---|---|---|
| * | AdventureWorks | Sales |
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.
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.
