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
dinesh_baliAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Guy Hengel [angelIII / a3]Connect With a Mentor 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
All Courses

From novice to tech pro — start learning today.