3.5 not a smooth transition for me

TomT

E.g. I type in CAST(@var as varchar(25)), and varchar does not show up in the list of candidates at all. However when I declare a parameter varchar, it does.

Also, declared parameters don't always appear in the list. Things don't work, for me anyway, as well as before....

tom

Bart Read

Hi Tom,


What are you actually trying to do here? If you send me the SQL statement you're trying to get help for I think I can probably give you some changes to make to the CompletionConfiguration.xml file (in C:\Documents and Settings\{username}\Local Settings\Application Data\Red Gate\SQL Prompt 3) that will fix this.


Thanks,
Bart

TomT

Hi Bart, thanks for the reply.

Here's an example:

DECLARE
@test1 INT,
@test2 VARCHAR(25)

SET @test1 = 12345

SET @test2 = CAST(@test1 AS VARCHAR(25))

When typing the cast statement, the suggestions when I type var, the suggestions are VAR and VARP...

This is different from how the previous version worked....

Thanks

Bart Read

Hi Tom,


Thanks for getting back to me. Here's what you need to do:

(1) First make sure that any editors SQL Prompt supports are closed.

(2) Open the CompletionConfiguration.xml file in your favourite editor (don't use TextPad though). You'll find this file in "C:\Documents and settings\{username}\Local Settings\Application Data\Red Gate\SQL Prompt 3".

(3) Find the following section:

<CompletionContext Name="SetVariableExpression" EnableColumnMultiSelect="False">
      <CompletionItem>Variables_Scalar</CompletionItem>
      <CompletionItem>Variables_Cursor</CompletionItem>
      <CompletionItem>UDFs_Scalar</CompletionItem>
      <CompletionItem>AllBuiltInFunctions</CompletionItem>
      <CustomItemRef>SetVariableExpression_ReservedWords</CustomItemRef>
      <CustomItemRef>AnyStatement_BasicKeywords</CustomItemRef>
    </CompletionContext>

(4) Now you need to add a couple more <CompletionItem> elements for user-defined and built-in data types to give you the following:

<CompletionContext Name="SetVariableExpression" EnableColumnMultiSelect="False">
      <CompletionItem>Variables_Scalar</CompletionItem>
      <CompletionItem>Variables_Cursor</CompletionItem>
      <CompletionItem>UDFs_Scalar</CompletionItem>
      <CompletionItem>AllBuiltInFunctions</CompletionItem>
      <CompletionItem>UDTs</CompletionItem>
      <CompletionItem>BuiltInDataTypes</CompletionItem>
      <CustomItemRef>SetVariableExpression_ReservedWords</CustomItemRef>
      <CustomItemRef>AnyStatement_BasicKeywords</CustomItemRef>
    </CompletionContext>

(5) Save your changes to CompletionConfiguration.xml.

(6) Open up an editor and you should find that the example you've described will now work as you'd like.

You can similarly add data types to other statements and expressions by editing the right section in the above file. Don't be afraid to experiment because if you screw things up completely you can always delete the file and SQL Prompt will recreate the default version.

Hope that helps.


Thanks,
Bart

TomT

Thanks Bart, that did the trick.

Out of curiosity, why is this necessary? As I recall previous versions did this without this modification....

Also, is there anyway to speed up completion on remote servers? There's quite a hesitation in completing an entry once it's been chosen from the list of candidates....

Thanks

Bart Read

Hi Tom,


As far as the remote servers go I'm not sure what the problem is. Once the meta-data is loaded I don't see any problems with code completion here, however you might want to this:

(1) Go to SQL Prompt > Cache Management.
(2) Increase the value of "Maximum number of databases to hold in memory". The default for 3.5 is 5, but if you upgraded from 3.0/3.1 it's likely that this would be set to 3. If you're not experiencing memory problems and the databases don't have really large schemas (tens to hundreds of thousands of objects) you should be fine to push this up a bit further. The delay might be caused by SQL Prompt flushing some meta-data from physical memory and having to pull it back from disk.

That might or might not have an effect, but it's probably worth a try.

With regard to your question about the CompletionConfiguration.xml file, previous versions of SQL Prompt 3.0/3.1 used to just display everything in the database quite a lot of the time, although often with the most relevant objects towards the top of the list. SQL Prompt 3.5 on the other hand generally tries to display only those objects that are relevant. This file defines the objects you'd expect to see in any given context.

So, why a configuration file? There are a number of reasons. Firstly it meant that I could write the code for populating the lists in a generic manner and just farm out the custom stuff to a set of appropriate delegates (e.g. all the columns in all the tables in the FROM clause). The code for doing this puts all the objects in the right shortcut lists etc. It makes it much easier to test the effect of changes by just modifying this file, and then updating the default configuration if necessary, without having to make changes to the code.

It also means that if somebody (like you) comes to us and says, "hey, I'd like to see this in the list and it's not there," or, "I don't like ordering of the objects when I do blah," then you can fix it without having to wait for the next point release or whatever.

This does mean though that sometimes if we forget something, as we did in this situation, it doesn't appear whereas in Prompt 3.0/3.1 because it was probably listing everything, or at least most of the objects, in the database it would have been there.

Hopefully that helps.


Thanks,
Bart