what is problem with this query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

what is problem with this query

Author

Topic

rammohan
Posting Yak Master

212 Posts

Posted - 2009-12-15 : 07:42:34

Select top 1 @SeqNo = ResultsID from Results WITH (TABLOCKX)

when i am excuting this inside a procedure i am getting following error :

Incorrect syntax near '1'

please help me out

One can never consent to creep,when one feels an impulse to soar
RAMMOHAN

vikky
Yak Posting Veteran

54 Posts

Posted - 2009-12-15 : 07:51:43

hi,

which server ur using.(2000 or 2005 or 2008.

Thanks,
vikky.

rammohan
Posting Yak Master

212 Posts

Posted - 2009-12-15 : 08:01:48

i am using sqlserver 2000

One can never consent to creep,when one feels an impulse to soar
RAMMOHAN

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-15 : 08:08:54

Post the full code you used

Madhivanan

Failing to plan is Planning to fail

saran_d28
Starting Member

36 Posts

Posted - 2009-12-15 : 08:28:24

Try this,

Select @SeqNo = top 1 ResultsID from Results WITH (TABLOCKX)

saran_d28
Starting Member

36 Posts

Posted - 2009-12-15 : 08:37:41

Try this,

declare @a1 int
select @a1 = (select top 1 c1 from test1)
print @a1

rammohan
Posting Yak Master

212 Posts

Posted - 2009-12-15 : 08:43:55

hi madhivanan,

create procedure usp_getvalues
as
@SqqNo int
begin
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRANSACTION
Select top 1 @SeqNo = ResultsID from Results WITH (TABLOCKX)
COMMIT
end

One can never consent to creep,when one feels an impulse to soar
RAMMOHAN

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-15 : 09:01:41

It should be

create procedure usp_getvalues
as
declare @SeqNo int
begin
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRANSACTION
Select top 1 @SeqNo = ResultsID from Results WITH (TABLOCKX)
COMMIT
end

Madhivanan

Failing to plan is Planning to fail

rammohan
Posting Yak Master

212 Posts

Posted - 2009-12-15 : 09:24:51

sorry the procedure is same as u written above,i forgot to type declare here.

create procedure usp_getvalues
as
declare @SeqNo int
begin
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRANSACTION
Select top 1 @SeqNo = ResultsID from Results WITH (TABLOCKX)
COMMIT
end

One can never consent to creep,when one feels an impulse to soar
RAMMOHAN

rammohan
Posting Yak Master

212 Posts

Posted - 2009-12-15 : 09:25:15

still i am getting same error

One can never consent to creep,when one feels an impulse to soar
RAMMOHAN

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-15 : 09:29:33

Are you getting the error at the time of creation or execution?

Madhivanan

Failing to plan is Planning to fail

rammohan
Posting Yak Master

212 Posts

Posted - 2009-12-15 : 09:32:13

hi madhivanan,
i am getting error at the time of creation

One can never consent to creep,when one feels an impulse to soar
RAMMOHAN

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-15 : 09:35:44

Are you sure?
It wont throw error
Check again

Madhivanan

Failing to plan is Planning to fail

rammohan
Posting Yak Master

212 Posts

Posted - 2009-12-15 : 09:52:57

still i am getting same error :
pls check this code once:

CREATE PROCEDURE [dbo].[usp_GetTriumphDataForProcess] AS
BEGIN
Declare @SeqNo bigint
Declare @internalError int
Declare @RowAffected int
Declare @ErrorMessage varchar(3000)
Begin
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRANSACTION
Select TOP 1 @SeqNo = TriumphResultsID from TriumphResults WITH (TABLOCKX)
where CampStatus = 1 order by TriumphResultsID
SELECT @internalError = @@ERROR
IF (@internalError <> 0) GOTO ErrorOccured
-- Update the status "IsUnderProgress" for the Top 1 record
Update TriumphResults
Set CampStatus = 2,
ProcessStartTime= getdate()
Where TriumphResultsID = @SeqNo
And CampStatus = 1
Select @RowAffected = @@ROWCOUNT,@internalError = @@ERROR
IF (@internalError <> 0) GOTO ErrorOccured
If (@RowAffected > 0)
Begin
--TODO
-- Get Top 1 Record to process
-- Select t.SeqNo,t.DC_RFRN_REF_NBR,ltrim(rtrim(t.DT_NBR_PLST)) as DT_NBR_PLST,t.FcbaAge,t.DC_AMT_CASE,t.FeBeStatus,t.ScriptID,t.DC_CODE_DISPT_TYPE, t.DC_CODE_DISPT_TYPE2, s.scriptName
-- From TriumphResults t ,TriumphApplicationDetails s
SELECT * FROM TriumphResults WHERE TriumphResultsID = @SeqNo

SELECT @internalError = @@ERROR
IF (@internalError <> 0) GOTO ErrorOccured
End
COMMIT TRANSACTION
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
ErrorOccured:
If (@internalError <> 0)
Begin
Rollback Transaction
Select @ErrorMessage = 'SP usp_GetTriumphDataForProcess' + [description] from master.dbo.sysmessages where error = @internalError
exec usp_UpdateTrace null,null,'Error',@ErrorMessage
End
End
End

One can never consent to creep,when one feels an impulse to soar
RAMMOHAN

rammohan
Posting Yak Master

212 Posts

Posted - 2009-12-15 : 22:56:07

i am geeting error : incorrect syntax near '1'

at the line :
Select TOP 1 @SeqNo = TriumphResultsID from TriumphResults WITH (TABLOCKX)

can some one please help me out

One can never consent to creep,when one feels an impulse to soar
RAMMOHAN

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-16 : 01:18:42

How are you executing the code?
I dont see any errors on that

Madhivanan

Failing to plan is Planning to fail

rammohan
Posting Yak Master

212 Posts

Posted - 2009-12-16 : 01:22:49

i am executing above code in query analyzer of sqlserver 2000.

i connected to this database server by using windows authentication

One can never consent to creep,when one feels an impulse to soar
RAMMOHAN

rammohan
Posting Yak Master

212 Posts

Posted - 2009-12-16 : 01:31:09

i tried this one also:

set @SeqNo = (select top 1 ResultsID from Results WITH (TABLOCKX)
where Status = 1 order by ResultsID )

But i am still facing the same problem.

One can never consent to creep,when one feels an impulse to soar
RAMMOHAN

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-16 : 03:27:52

What happens when you remove WITH (TABLOCKX)?

Madhivanan

Failing to plan is Planning to fail

rammohan
Posting Yak Master

212 Posts

Posted - 2009-12-16 : 03:51:45

Hi madhivanan,

after removing WITH (TABLOCKX) from query,

still the same problem is coming.

i just written this :

select @SeqNo = ResultsID from Results WITH (TABLOCKX)
where Status = 1 order by ResultsID

it is executed success fully.

but my doubt is,

when i run :

select ResultsID from Results WITH (TABLOCKX)
where Status = 1 order by ResultsID

it shows 110 records.

but if written this :
select @SeqNo = ResultsID from Results WITH (TABLOCKX)
where Status = 1 order by ResultsID

i think it will get run time problem right?

pls suggest me a alternate way insted of using TOP.

One can never consent to creep,when one feels an impulse to soar
RAMMOHAN

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-16 : 03:54:32

Ok

Instead of

Select TOP 1 @SeqNo = TriumphResultsID from TriumphResults WITH (TABLOCKX)
where CampStatus = 1 order by TriumphResultsID

use

Select @SeqNo = min(TriumphResultsID) from TriumphResults WITH (TABLOCKX)
where CampStatus = 1

Madhivanan

Failing to plan is Planning to fail

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources