How can we help you today? How can we help you today?

Autocomplete - when nolock is used?

Hi!
I'm using SSMS (when connected to SQL server 2000 database) on Win XP Pro SP 2.
SQL Prompt 3.0.0.868

When I start typing in
SELECT column1, column2, column3 FROM Mytable1 WHERE
as soon as I type the space after the WHERE, I get the list of the columns from Mytable1 for me to select from.

But, if I type
SELECT column1, column2, column3 FROM Mytable1 (nolock) WHERE
I don't get the list of columns to select from. Since our DBAs recommend using the nolock hint when getting data from production environment (and I am not too worried about dirty reads for this table), this is an inconvenience.

Is there an option that can be turned on to allow the autocomplete to work even with nolocks? Any other workaround?

Thanks!
jj14
0

Comments

3 comments

Sort by
  • Bart Read
    Hi there jj,


    That looks like a bug to me. Here's an example from AdventureWorks 2005.

    The following doesn't work:

    SELECT AddressID,
    AddressLine1,
    AddressLine2,
    City,
    StateProvinceID,
    PostalCode,
    rowguid,
    ModifiedDate FROM Person.Address (NOLOCK) WHERE

    whereas the following does:

    SELECT AddressID,
    AddressLine1,
    AddressLine2,
    City,
    StateProvinceID,
    PostalCode,
    rowguid,
    ModifiedDate FROM Person.Address WITH (NOLOCK) WHERE

    so my suggestion for now is that when working with table hints you use the WITH keyword. This is obviously slightly irritating when using a single hint where it's optional but it does mean that you get intellisense in the where clause. You could work around this by created a hint called "nl" or something that automatically inserted "WITH (NOLOCK) WHERE" for you.

    Hope that helps.


    Thanks,
    Bart
    Bart Read
    0
  • jj14
    Thanks for the quick response Bart.
    Is this something that will be fixed in the near future?

    Thanks
    - Joseph
    jj14
    0
  • Bart Read
    Hmm... possibly. It depends on whether it's a parser problem or not, which I have a nasty suspicion it may be. We aren't going to be touching the parser for a while since it's embedded in another component. That said, if I can work around it easily enough we might put a fix in.
    Thanks,
    Bart
    Bart Read
    0

Add comment

Please sign in to leave a comment.