error

Question

Post reply

error

riya_dave

Hall of Fame

Points: 3106
More actions
July 16, 2013 at 1:13 pm

#277434

hi
select * from tableA with(nolock) a
join tableB with(nolock) b
on a.id = b.id
where a.date > getdate()
i am getting error incorrect syntax near a

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

To correct the error, put the alias befor the WITH

select * from tableA a with(nolock)

join tableB b with(nolock)

on a.id = b.id

where a.date > getdate()

You should as well be aware that NOLOCK might give you wrong results.

Check these links

SQL Server NOLOCK Hint & other poor ideas.

Understanding the SQL Server NOLOCK hint[/url]

More Info

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 2

Try the following:

select * from tableA a with(nolock)

join tableB b with(nolock)

on a.id = b.id

where a.date > getdate()

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Or you could just remove the nolock hints entirely and remove any chance of their nasty side effects causing you problems.

See - http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Voide Mr or Mrs. 500 Points: 548 More actions · Answer 4

riya_dave (7/16/2013)
hi
select * from tableA with(nolock) a
join tableB with(nolock) b
on a.id = b.id
where a.date > getdate()
i am getting error incorrect syntax near a

As Gail said, why nolocks?

Also alias names for tables either follow the table name itself or the word 'as', nothing else.

kapil_kk SSC-Insane Points: 21316 More actions · Answer 5

Voide (7/16/2013)
riya_dave (7/16/2013)
hi
select * from tableA with(nolock) a
join tableB with(nolock) b
on a.id = b.id
where a.date > getdate()
i am getting error incorrect syntax near a
As Gail said, why nolocks?
Also alias names for tables either follow the table name itself or the word 'as', nothing else.

Because, if you use NOLOCK then there is a chance of get inappropriate data...

Suppose you have opened a transaction and made some changes to the table and didn't Commit the transaction... After that you are using SELECT statement using NOLOCK option then you will get the modified data as the transaction was not committed... Then you ROLLBACK the transaction and again run the SELECT statement with NOLOCK then you will get your previous data...

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

kapil_kk SSC-Insane Points: 21316 More actions · Answer 6

GilaMonster (7/16/2013)
Or you could just remove the nolock hints entirely and remove any chance of their nasty side effects causing you problems.
See - http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx

Gail,

Can you please tell me that when we have to use NOLOCK option as it gives us inappropriate data in result?

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Can you please tell me that when we have to use NOLOCK option as it gives us inappropriate data in result?

You don't have to use it. There's no situation ever where SQL is going to force you to use the nolock hint. Using it is your choice, as with just about anything else.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 8

kapil_kk (7/17/2013)
GilaMonster (7/16/2013)
Or you could just remove the nolock hints entirely and remove any chance of their nasty side effects causing you problems.
See - http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx
Gail,
Can you please tell me that when we have to use NOLOCK option as it gives us inappropriate data in result?

It doesn't necessarily give you inappropriate data each time. But there is a chance, which is worse enough.

You can use it when you don't want to have an impact on the source table, e.g. you don't want to have any locking. For example in a reporting scenario. But only if , and only if, your manager, the boss of your manager and everyone else looking at the report agrees that the data they are viewing could be wrong.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Voide Mr or Mrs. 500 Points: 548 More actions · Answer 9

kapil_kk (7/17/2013)
Voide (7/16/2013)
riya_dave (7/16/2013)
hi
select * from tableA with(nolock) a
join tableB with(nolock) b
on a.id = b.id
where a.date > getdate()
i am getting error incorrect syntax near a
As Gail said, why nolocks?
Also alias names for tables either follow the table name itself or the word 'as', nothing else.
Because, if you use NOLOCK then there is a chance of get inappropriate data...
Suppose you have opened a transaction and made some changes to the table and didn't Commit the transaction... After that you are using SELECT statement using NOLOCK option then you will get the modified data as the transaction was not committed... Then you ROLLBACK the transaction and again run the SELECT statement with NOLOCK then you will get your previous data...

We may have misunderstood each other 🙂 I wasn't advising him to use NOLOCK.

kapil_kk SSC-Insane Points: 21316 More actions · Answer 10

Voide (7/17/2013)
kapil_kk (7/17/2013)
Voide (7/16/2013)
riya_dave (7/16/2013)
hi
select * from tableA with(nolock) a
join tableB with(nolock) b
on a.id = b.id
where a.date > getdate()
i am getting error incorrect syntax near a
As Gail said, why nolocks?
Also alias names for tables either follow the table name itself or the word 'as', nothing else.
Because, if you use NOLOCK then there is a chance of get inappropriate data...
Suppose you have opened a transaction and made some changes to the table and didn't Commit the transaction... After that you are using SELECT statement using NOLOCK option then you will get the modified data as the transaction was not committed... Then you ROLLBACK the transaction and again run the SELECT statement with NOLOCK then you will get your previous data...
We may have misunderstood each other 🙂 I wasn't advising him to use NOLOCK.

:-P:-P

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/