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

Autocomplete - when nolock is used?

jj14jj14 Posts: 7
edited March 23, 2007 12:39PM in SQL Prompt Previous Versions
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!

Comments

  • Options
    Bart ReadBart Read Posts: 997 Silver 1
    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
    Principal Consultant
    bartread.com Ltd
  • Options
    Thanks for the quick response Bart.
    Is this something that will be fixed in the near future?

    Thanks
    - Joseph
  • Options
    Bart ReadBart Read Posts: 997 Silver 1
    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
    Principal Consultant
    bartread.com Ltd
Sign In or Register to comment.