Managing connections and memory

SQL Prompt - 4.0

Versions available: 5.3, 5.2, 5.1, 5.0, 4.0, 3.9, 3.8, 3.6

Learning SQL Prompt - 4.0

Managing connections and memory

SQL Prompt will automatically connect to other databases specified in your query. For example:

CrossDatabaseQuery

In this example, SQL Prompt loads objects from the Northwind database as soon as you type the database name and then continue with your query. For large databases, you may have to wait a few seconds while SQL Prompt loads the objects.

If you do not have permissions to connect to that database, then a message is displayed:

PermissionsDialog

Click Details to see more information about the connection error:

MoreDetailsConnectionMessage

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

Note: this message 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.

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 4 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.

Managing the SQL Prompt memory

SQL Prompt loads database objects into its memory whenever you connect to a database in SQL Server 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:

CacheProgressDialog

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 will be displayed.

If this message is displayed, you can clear the SQL Prompt memory by clicking Unload All Databases. 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, close your editor application. This unloads all objects from all databases from memory.

Connecting to a linked server

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

Linkedserverssuggestions

When you select a linked server, SQL Prompt 4 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: