Help needed regarding an time Out error

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

General SQL Server Forums

New to SQL Server Programming

Help needed regarding an time Out error

Author

Topic

sravz
Starting Member

42 Posts

Posted - 2011-04-22 : 16:21:32

Hi Every one,

I am trying to solve this Issue.

I had a table with these values
ID_File| PartNumber(varchar) |TCHours(Decimal)| POHours(Decimal)
345 | this week schedule | 2.00 | 1.00
345 | No Perm reqd | 2.00 | 0.80
348 | The flight expenses|1.80 | 0.00
394 | 0 | 0.00 | 0.00

I Need to update the data automatically when user clicks update

what needs to be updated are only the single Records Id here(348,394) only when TcHours is 0.00 update PoHours to 1.00
Else when TCHours>0.00 then Update POHours to TCHours

In this case I wrote a cursor its working fine but giving this error.
and updating only 5 records
Time out expired.

when I click update again ,it repeats the same process.
If there is any other type of updating process please let me know.
Or If I can remove the error please let me know.

Thanks in Advance

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-04-22 : 16:49:28

To eliminate the error, you have to be properly indexed and have an efficient query. Right now, you've got a very inefficient query. I can't comment on your indexes as you haven't posted that information.

You just need this for you what you described, no ugly cursor:
UPDATE YourTable
SET PoHours = CASE WHEN TcHours = 0.00 THEN 1.00 ELSE TcHours END
WHERE ID_File IN (348,394)

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

sravz
Starting Member

42 Posts

Posted - 2011-04-25 : 09:30:58

Hey tara,

Thanks for the reply.Okay I had one more question

ID_File| PartNumber(varchar) |TCHours(Decimal)| POHours(Decimal)
345 | this week schedule | 2.00 | 1.00
345 | No Perm reqd | 2.00 | 0.80
348 | The flight expenses|1.80 | 0.00
394 | 0 | 0.00 | 0.00

In the above table what if i want to get the records with Single ID(Eg:ID=348,394,....) into an temp table.

What is the query I can write.

Please help me.

Thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-04-25 : 09:37:34

select t1.* from table as t1 inner join (select ID_file from table group by ID_file having count(ID_File)=1) as t2 on t1.ID_File=t2.ID_File

Madhivanan

Failing to plan is Planning to fail

sravz
Starting Member

42 Posts

Posted - 2011-04-25 : 09:46:39

Hey Madhivanan,

Thanks for the immediate reply.It worked but I want to put the result set in a temp table.I am new to sql
So can u please let me know how to do that?
and also can u explain how u had done the above query.
I just want to learn

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-04-25 : 09:52:54

select t1.* into #temp from table as t1 inner join (select ID_file from table group by ID_file having count(ID_File)=1) as t2 on t1.ID_File=t2.ID_File

The code select ID_file from table group by ID_file having count(ID_File)=1 gets rows with only single occurance of ID_File

Madhivanan

Failing to plan is Planning to fail

sravz
Starting Member

42 Posts

Posted - 2011-04-25 : 10:15:25

Thanks Madhivanan,

Good to know.Got to know how to write the result set into temp table.

Thanks,
Sravanthi

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources