Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

with (nolock)

Posted on 2006-11-29
2
Medium Priority
?
568 Views
Last Modified: 2006-11-29
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
Comment
Question by:dinesh_bali
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 18037220
(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
 

Author Comment

by:dinesh_bali
ID: 18037662
Many Thanks
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

670 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question