Introduction

PromptSQL is a utility which provides Intellisense for the following editors:

• Microsoft Query Analyzer (SQL Server 2000)
• Microsoft Enterprise Manager (SQL

  Server 2000)

• Microsoft SQL Server Management Studio (SQL Server 2005)
• Visual

  Studio .NET 2003

• Visual Studio .NET 2005
• UltraEdit.

This utility does not

install any hooks into any of these editors. Rather, it runs in a user's

interactive session and is visible as a tray icon. When a supported editor

starts up and PromptSQL is active, it will provide Intellisense based on

context.

Environment

For my evaluation of the product, I looked at PromptSQL version 1.2, the

latest version at the time of this review. My testing involved the following

applications across both Windows 2000 and Windows XP systems: Microsoft Query

Analyzer, Microsoft SQL Server Management Studio (RTM), and Visual Studio.NET

2005 (RTM). The .NET Framework version 1.1 was installed on the Windows

2000 system and the .NET Framework versions 1.1 and 2.0 were installed

on the Windows XP platform. PromptSQL requires the .NET Framework

version 1.1 as a minimum but also runs with the .NET Framework version 2.0.

Installation

The installation process is straight-forward. A standard installer is used

which prompts for the directory to install the files. Once PromptSQL is

installed, the first time the application is started the user is prompted for

the licensing information. If the user has saved off the license file (one can

be requested prior to download), the user simply points PromptSQL to the file and it'll automatically

import the licensing information. This is stored in an XML file within the user

profile. As an important note, since the settings are stored within the user

profile, if it's a shared system, each user logging in will have to set the

licensing information.

PromptSQL installs by placing itself in the Startup folder under Start |

Programs. This ensures it

starts up whenever the user (or all users, depending on the option selected

during installation) logs on to the system. PromptSQL does not run as a service

and if removed from the Startup folder it'll have to be started manually.

Using PromptSQL

Once PromptSQL is installed, the only frequent user interaction is to define a

database connection if PromptSQL cannot determine how to connect to the server.

If you're using Windows authentication, generally PromptSQL doesn't have an

issue. If, however, you're using a SQL Server login, be prepared to have to

specify the username and password in the connection dialog window, as shown in

Figure 1.

Figure 1: PromptSQL Connection Dialog Window

Other than that, there is almost no further user interaction to get

Intellisense working. When one of the editors PromptSQL supports is started (and

used for a database project), PromptSQL will bring up Intellisense prompts

according to the context. Figure 2 shows PromptSQL's Intellisense within SQL

Server Query Analyzer. Note that it not only provides a list of matching objects

based on dbo.C, but it also provides the columns and their data types (in the

case of a table or view) or parameters (in the case of a stored procedure). Icons

to the left of the object name (such as to the left of Categories) mark

what type of object it is. For instance, tables have a different icon than views

do. In Figure 1 Categories is a table and [Category Sales for 1997] is a view.

Do note that if aliased an object, for instance, if I specified FROM

dbo.Categories c, I could use that alias in the WHERE clause and PromptSQL will

provide Intellisense properly.

Figure 2: PromptSQL within Query Analyzer

PromptSQL's Intellisense within SQL Server Management Studio works

essentially the same way. Figure 3 shows Intellisense against an AdventureWorks

object: Production.BillofMaterials.

Figure 3: PromptSQL within SQL Server Management Studio

Within Visual Studio .NET thinks are a bit different, but only slightly so.

For instance, if you open up a C# project and just type "SELECT * FROM " and

expect PromptSQL's Intellisense to pop up, it won't (nor will Microsoft's since

it doesn't understand the context). However, as shown in Figure 4, if the

context justifies Intellisense, PromptSQL provides it.

Figure 4: PromptSQL within Visual Studio .NET 2005

Again note the icons marking tables and views and the display of the column

definition for the HumanResources.Department table. I assume the

functionality is similar in UltraEdit, however, UltraEdit isn't an editor I use.

But as you can see, there's no real difference between how PromptSQL interacts

with the supported editors.

PromptSQL is highly customizable with respect to how quickly to display

Intellisense, how the lists are formated (font size and type), whether or not to use owners/schema, etc.

Figure 5 shows one of several settings tabs which can be brought up by

right-clicking in the PromptSQL tray icon and selecting Options.

You can configure PromptSQL to be as quick as you need with respect

to Intellisense and have a good amount of control over how the Intellisense is

displayed.

Figure 5: PromptSQL Settings

While I've not shown it here, PromptSQL is  capable of

popping up lists for variables whether they be user-defined (@myVariable) and

pre-defined (such as @@SERVERNAME). Also, PromptSQL allows a user to specify

certain shortcuts which can be entered into an editor which PromptSQL will

replace with the longhand command(s). For instance, "w2" is shorthand for

"sp_who2" and if a user types w2 into the editor, PromptSQL will expand it to

"sp_who2" and execute the command if the user hits the TAB key immediately after

the "2" of "w2."

Support

In my testing of the product, I didn't need support for any bug resolution

issues. However, when I had a question about the product (which versions of the

.NET Framework were supported), Atadore responded in less than 24 hours.

Conclusions

PromptSQL is a tool I now won't do without. After having installed it, I have

gotten so used to having Intellisense that I find myself hitting the magical

CTRL-SPACE to bring it up even before my specified wait times (which are set for

a mere 100 ms). The same reasons Intellisense improve developer productivity in

the various incarnations of the Visual Studio suite also hold for PromptSQL:

less errors and less time searching for proper objects and parameters. There

have been a couple of cases since installation where I've had to dive into

databases where I wasn't intimately familiar with the objects. Having the column

definitions display as I cycle through tables ensured that I could verify at a

glance whether or not the table was the one I really needed (especially in one

particular application where the table names are rather cryptic).

One of the things about PromptSQL you must be aware of, though, is to keep

code for a query together. PromptSQL uses the carriage return/linefeed

characters as a delimiter of what should be grouped together. For instance, this

is fine:

  SELECT *

  FROM sys.sql_logins s

  WHERE s.name = 'sa'

while this is not:

  SELECT *

  FROM sys.sql_logins s

  WHERE s.name = 'sa'

The separation between the line beginning with FROM and the line beginning

with WHERE causes PromptSQL to consider this as two different code blocks. This

means that if you're looking for PromptSQL to pop-up Intellisense in the WHERE

clause after s. (s is the alias for sys.sql_logins), don't throw in the

extra line. PromptSQL will not recognize the alias if you do. While this isn't a

big issue for me, if you like adding extra spaces in your queries and object

definitions, be aware of how this might affect PromptSQL's ability to provide

Intellisense.

Even with that minor issue, given my own satisfaction with the product, I have recommended it to other

developers and DBAs where I work. It's one of the more inexpensive database

tools available yet one of the most useful. This is especially true if you're

like me and are so used to the standard Microsoft provided tools like Query

Analyzer and SQL Server Management Studio that you typically start those up out

of habit. Having Intellisense in these utilities increases my efficiency.

Ratings

I will rate each of the following using a scale from 1 to 5. 5 being the best and 1 being the worst. Comments are in the last column.

Product Information

Web Site: http://www.promptsql.com/

Developer: Atadore SARL

Pricing:

Single Machine - US$25

10 license pack - US$240

25 license pack - US$575