What are the challenges you face when working across database platforms? Take the survey
Options

USE Database quirk

ChrisRickardChrisRickard Posts: 37 Bronze 2
edited July 18, 2007 1:13PM in SQL Prompt Previous Versions
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.

Comments

  • Options
    Bart ReadBart Read Posts: 997 Silver 1
    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
    
    &lt;- 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&lt;-- 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
    Bart Read
    Principal Consultant
    bartread.com Ltd
  • Options
    ChrisRickardChrisRickard Posts: 37 Bronze 2
    It does, thanks.
  • Options
    Bart ReadBart Read Posts: 997 Silver 1
    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
    Bart Read
    Principal Consultant
    bartread.com Ltd
Sign In or Register to comment.