• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 574
  • Last Modified:

with (nolock)

Hi,

I am using SQL-2005

But If I use with (nolock)

in the query it gives me error:

Msg 319, Level 15, State 1, Line 12
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.


But, I want to add with (nolock) in my query


SELECT TOP 10 CUSDETAIL.DCOP_CULTURE ,CUSDETAIL.DCOP_ID, CUSDETAIL.CTCT_ID, CUSDETAIL.CTCT_CREATIONDATE, CUSDETAIL.CTCT_LASTACCESSDATE,
                                            CUSDETAIL.CTCT_TITLE, CUSDETAIL.CTCT_FIRSTNAME,
                                    CUSDETAIL.CTCT_LASTNAME, CUSDETAIL.CTCT_DECRYPT_PASSWORD,
                                            CUSDETAIL.CTCT_ADDRESSLINE1, CUSDETAIL.CTCT_ADDRESSLINE2, CUSDETAIL.CTCT_ADDRESSLINE3,
                                            CUSDETAIL.CTCT_BUILDINGNUMBER, CUSDETAIL.CTCT_CITY, CUSDETAIL.CTCT_POSTCODE, CUSDETAIL.CTCT_STATE_COUNTY,
                                            CUSDETAIL.COCU_ID, CUSDETAIL.CTCT_EMAIL, CUSDETAIL.CTCT_NATIONALITY, CUSDETAIL.CTPL_ID,CUSDETAIL.DCOP_MARKETINGCONTACTEMAIL,
                                            CUSDETAIL.DCOP_MARKETINGCONTACTEPOSTAL, CUSDETAIL.DCOP_MARKETINGCONTACTETHIRDPARTY,
                                            CUSDETAIL.COCU_CODE, CUSDETAIL.CTPL_CODE, CUSDETAIL.DCOP_MIGRATE_STATUS
                                     FROM B025765.ENET_20NOV_NEW.DBO.VW_CUSTOMER_PERSONAL_DETAIL  CUSDETAIL
                                                            LEFT OUTER JOIN B025765.VB_MIGRATED_CUSTOMER.DBO.MIGRATED_CUSTOMER MIG_CUS
                                                                  ON CUSDETAIL.CTCT_ID <> MIG_CUS.CTCT_ID AND CUSDETAIL.CTCT_EMAIL <> MIG_CUS.CTCT_EMAIL
                                                            with (nolock)
                                    WHERE CUSDETAIL.DCOP_CULTURE ='en-US'
                                     AND CUSDETAIL.DCOP_MIGRATE_STATUS IS NULL
                                     AND CUSDETAIL.CTCT_DECRYPT_PASSWORD <> '!!!!' AND CUSDETAIL.CTCT_DECRYPT_PASSWORD IS NOT  NULL
                                     

                                                      

Thanks
0
dinesh_bali
Asked:
dinesh_bali
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
(NOLOCK) comes either behind the tablename (without "WITH"), or at the very end of the query:

SELECT TOP 10 CUSDETAIL.DCOP_CULTURE ,CUSDETAIL.DCOP_ID, CUSDETAIL.CTCT_ID, CUSDETAIL.CTCT_CREATIONDATE, CUSDETAIL.CTCT_LASTACCESSDATE,
                                            CUSDETAIL.CTCT_TITLE, CUSDETAIL.CTCT_FIRSTNAME,
                              CUSDETAIL.CTCT_LASTNAME, CUSDETAIL.CTCT_DECRYPT_PASSWORD,
                                            CUSDETAIL.CTCT_ADDRESSLINE1, CUSDETAIL.CTCT_ADDRESSLINE2, CUSDETAIL.CTCT_ADDRESSLINE3,
                                            CUSDETAIL.CTCT_BUILDINGNUMBER, CUSDETAIL.CTCT_CITY, CUSDETAIL.CTCT_POSTCODE, CUSDETAIL.CTCT_STATE_COUNTY,
                                            CUSDETAIL.COCU_ID, CUSDETAIL.CTCT_EMAIL, CUSDETAIL.CTCT_NATIONALITY, CUSDETAIL.CTPL_ID,CUSDETAIL.DCOP_MARKETINGCONTACTEMAIL,
                                            CUSDETAIL.DCOP_MARKETINGCONTACTEPOSTAL, CUSDETAIL.DCOP_MARKETINGCONTACTETHIRDPARTY,
                                            CUSDETAIL.COCU_CODE, CUSDETAIL.CTPL_CODE, CUSDETAIL.DCOP_MIGRATE_STATUS
                                     FROM B025765.ENET_20NOV_NEW.DBO.VW_CUSTOMER_PERSONAL_DETAIL  CUSDETAIL
                                                  LEFT OUTER JOIN B025765.VB_MIGRATED_CUSTOMER.DBO.MIGRATED_CUSTOMER MIG_CUS
                                                       ON CUSDETAIL.CTCT_ID <> MIG_CUS.CTCT_ID AND CUSDETAIL.CTCT_EMAIL <> MIG_CUS.CTCT_EMAIL
                                    WHERE CUSDETAIL.DCOP_CULTURE ='en-US'
                                     AND CUSDETAIL.DCOP_MIGRATE_STATUS IS NULL
                                     AND CUSDETAIL.CTCT_DECRYPT_PASSWORD <> '!!!!' AND CUSDETAIL.CTCT_DECRYPT_PASSWORD IS NOT  NULL
with (nolock)


or


SELECT TOP 10 CUSDETAIL.DCOP_CULTURE ,CUSDETAIL.DCOP_ID, CUSDETAIL.CTCT_ID, CUSDETAIL.CTCT_CREATIONDATE, CUSDETAIL.CTCT_LASTACCESSDATE,
                                            CUSDETAIL.CTCT_TITLE, CUSDETAIL.CTCT_FIRSTNAME,
                              CUSDETAIL.CTCT_LASTNAME, CUSDETAIL.CTCT_DECRYPT_PASSWORD,
                                            CUSDETAIL.CTCT_ADDRESSLINE1, CUSDETAIL.CTCT_ADDRESSLINE2, CUSDETAIL.CTCT_ADDRESSLINE3,
                                            CUSDETAIL.CTCT_BUILDINGNUMBER, CUSDETAIL.CTCT_CITY, CUSDETAIL.CTCT_POSTCODE, CUSDETAIL.CTCT_STATE_COUNTY,
                                            CUSDETAIL.COCU_ID, CUSDETAIL.CTCT_EMAIL, CUSDETAIL.CTCT_NATIONALITY, CUSDETAIL.CTPL_ID,CUSDETAIL.DCOP_MARKETINGCONTACTEMAIL,
                                            CUSDETAIL.DCOP_MARKETINGCONTACTEPOSTAL, CUSDETAIL.DCOP_MARKETINGCONTACTETHIRDPARTY,
                                            CUSDETAIL.COCU_CODE, CUSDETAIL.CTPL_CODE, CUSDETAIL.DCOP_MIGRATE_STATUS
                                     FROM B025765.ENET_20NOV_NEW.DBO.VW_CUSTOMER_PERSONAL_DETAIL (nolock)  CUSDETAIL
                                                  LEFT OUTER JOIN B025765.VB_MIGRATED_CUSTOMER.DBO.MIGRATED_CUSTOMER (nolock) MIG_CUS
                                                       ON CUSDETAIL.CTCT_ID <> MIG_CUS.CTCT_ID AND CUSDETAIL.CTCT_EMAIL <> MIG_CUS.CTCT_EMAIL
                                    WHERE CUSDETAIL.DCOP_CULTURE ='en-US'
                                     AND CUSDETAIL.DCOP_MIGRATE_STATUS IS NULL
                                     AND CUSDETAIL.CTCT_DECRYPT_PASSWORD <> '!!!!' AND CUSDETAIL.CTCT_DECRYPT_PASSWORD IS NOT  NULL


0
 
dinesh_baliAuthor Commented:
Many Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now