Incorrect syntax near the keyword 'with'

Question

0.00/5 (No votes)

See more:

SQL-Server-2008

my sql query is :

SQL

INSERT INTO #TempUsersAudt 
SELECT  p.IPersonID, p.sFirstName, p.sLastName, p.sLoginName, p.sEmail, REPLACE(o.sOrgPath, '>', '> ') AS sOrgPath,p.INodeID,  p.sPersonality, fAccountType = CASE p.fAccountType WHEN 'S' THEN 'Full Access' ELSE 'Regular'   END, fRights = CASE p.fRights WHEN 'A' THEN 'Administer' WHEN 'W' THEN 'Write'WHEN 'R' THEN 'Read' END 
From auditor.People p with (nolock) 
INNER JOIN auditor.OrgTree o with (nolock) ON o.INodeID = p.INodeID 
INNER JOIN @tbl_TYPE_LOOKUP TL ON TL.Type = p.fAccountType
WHERE p.fAccountStatus <> 'D'   AND p.fAccountType IN('R', 'R') AND o.sOrgPath LIKE 'Acme Corporation>%'    AND p.sLoginName LIKE '%j%' AND p.sEmail LIKE '%j%' AND TL.TypeName  LIKE '%j%'

i am getting error is :

Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

please help me to find where i am doing wrong

Posted 31-Jul-13 1:55am

Susheel Kumar Verma

Updated 31-Jul-13 2:00am

ZurdoDev

v2

Add a Solution

Comments

ZurdoDev 31-Jul-13 8:02am

Are you sure that is the error you are getting with this exact sql? It parses for me.

[no name] 31-Jul-13 8:15am

yes i am getting the same error which i have mentioned in question.

ZurdoDev 31-Jul-13 8:22am

Not sure what to tell you then. It parses for me. Perhaps in your actual code you are missing a space or something. Is it really SQL2008?

[no name] 31-Jul-13 8:27am

is it possible this error can come because of extra or less space
in my query there are some extra spaces should i remove that

ZurdoDev 31-Jul-13 8:49am

It's hard to believe an extra space would do it.

Adarsh chauhan 31-Jul-13 8:30am

I agree with ryanb31.. nothing seems wrong in your query...

CHill60 31-Jul-13 8:36am

Try taking all of the "with (nolock)" clauses out and check that query runs/parses correctly - I have seen errors reported in sql apparently on one particular line when in fact the real error was elsewhere

[no name] 31-Jul-13 9:13am

after removing "with(nolock)" my query runs successfully
but i want "with(nolock)"
is there any way.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Answer 1 · 2013-07-31T06:26:00

Solution 1

Ok - I was hoping that it would throw out another problem :-)

Try putting SET TRANSACTION ISOLATION LEVEL READUNCOMMITTED at the top of this transaction and leaving the with(nolock) clauses off - it's a (very) similar effect.

Also have a look at this link re whether you really do need the with(nolock)
http://www.sqlservercentral.com/Forums/Topic696705-360-1.aspx[^]

Posted 31-Jul-13 6:26am

CHill60

Comments

Maciej Los 31-Jul-13 16:57pm

A4!
It might help!
Who vote 1?

CHill60 31-Jul-13 17:06pm

Thank you! No idea who voted the 1 ... there's been a lot of that lately

Adarsh chauhan 1-Aug-13 1:37am

Nice to know about "SET TRANSACTION ISOLATION LEVEL READUNCOMMITTED "
was not having any idea about it.. now will Google a little more on this.. +5 :)

Maciej Los · Answer 2 · 2013-07-31T09:41:00

Frist hint:
Try to replace this part of query:

SQL

SELECT  p.IPersonID, p.sFirstName, p.sLastName, p.sLoginName, p.sEmail, REPLACE(o.sOrgPath, '>', '> ') AS sOrgPath,p.INodeID,  p.sPersonality, fAccountType = CASE p.fAccountType WHEN 'S' THEN 'Full Access' ELSE 'Regular'   END, fRights = CASE p.fRights WHEN 'A' THEN 'Administer' WHEN 'W' THEN 'Write'WHEN 'R' THEN 'Read' END

with:

SQL

SELECT  p.IPersonID, p.sFirstName, p.sLastName, p.sLoginName, p.sEmail, REPLACE(o.sOrgPath, '>', '> ') AS sOrgPath, p.INodeID,  p.sPersonality,
    CASE WHEN p.fAccountType='S' THEN 'Full Access'
         ELSE 'Regular' END AS fAccountType,
    CASE WHEN p.fRights='A' THEN 'Administer'
         WHEN p.fRights='W' THEN 'Write'
         WHEN p.fRights='R' THEN 'Read'
    END AS fRights

Second hint:
Remove WITH[^] near (nolock) statement.

Quote:
The following table hints are allowed with and without the WITH keyword: NOLOCK, READUNCOMMITTED, UPDLOCK, REPEATABLEREAD, SERIALIZABLE, READCOMMITTED, FASTFIRSTROW, TABLOCK, TABLOCKX, PAGLOCK, ROWLOCK, NOWAIT, READPAST, XLOCK, and NOEXPAND. When these table hints are specified without the WITH keyword, the hints should be specified alone. For example:

SQL
FROM t (TABLOCK)

I hope it helps ;)