USE Database quirk

ChrisRickard

This applies to both SQL Prompt 3.1 and 3.5. I don't know why I waited until now to report this; It's pretty minor though.

If I start out with a new query window and I type in a use database statement SQL Prompt has a limited candidate list in subsequent lines.

For Instance:

USE Northwind
<--(start typing here)

I found that only "other candidates" are listed in the suggested list, no snippets, tables, views, columns, procs, or functions. The Snippets, Suggested, and Other Candidates are the only options available, the others aren't.

If I manually invoke it via CTRL+Space it behaves normally. Also if I put a go immediately after the use statement it behaves normally.

Bart Read

Hi Chris,


You should really enter a GO after the USE statement so we don't display terribly much at that point. What you'll find is the following:

USE Northwind
GO

<- caret is here

If you force the candidate list at this point you'll find most objects on the list, however as soon as you start typing SQL Prompt will parse what you've typed as a stored procedure invocation. E.g.

USE Northwind
GO

se<-- caret is here

looks like you're trying to invoke a SP called "se" so what you'll see is any SPs starting with "se" (which probably won't be any) and then further down the list things like keywords (so SELECT will be there). The reason this happens is that in tSQL it's valid to execute a SP at this point without using EXEC/EXECUTE.

For anything else, you need to start typing the statement, e.g. SELECT, to get up the relevant object types.

Hope that clears it up a bit.


Thanks,
Bart

ChrisRickard

It does, thanks.

Bart Read

I did also notice on mine that before entering the GO I also got a list of all candidates on the line after the USE.

If you take a look in your "C:\Documents and settings\{username}\Local Settings\Application Data\Red Gate\SQL Prompt 3" directory you'll find a file called CompletionConfiguration.xml. If you take a look in there you'll find you can configure what appears, when it appears, and in what order. Although we don't document this functionality it should be relatively self-explanatory.

A disclaimer:

- If you screw up the file SQL Prompt might just overwrite your changes with the default settings, or (in extreme cases) it might just barf, although it should be fairly fault tolerant so I doubt that would happen.

If you get into a real mess you can always just delete the file and SQL Prompt will recreate it the next time you start it up.

Enjoy!


Thanks,
Bart