Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 570
  • 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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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