Blocking by a stored proc - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

Blocking by a stored proc

Author

Topic

pareshmotiwala
Constraint Violating Yak Guru

323 Posts

Posted - 2011-10-26 : 08:49:23

folks
I have a weird issue. I have an app that runs on IIS agains ta SQL2000 box. It runs this one stored proc X. When it runs, it creates a block and never lets it go. However, when I run the query build in, it works fine? Killing the thread leads to infinite wait on "Killed/Rollback".

Any insights?

Regards
Paresh Motiwala
Boston, USA

Cindyaz
Yak Posting Veteran

73 Posts

Posted - 2011-10-26 : 09:20:45

Does the SP refer to a linked server? What is the wait type for that SP in sys.dm_exec_waiting_tasks? If it's preemptive_oledbops then you'll have to restart sql services.
Can you post the text of the SP?

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-10-26 : 09:23:12

Does the SP do anything outside the server? xp_cmdshell, send mail for instance.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

pareshmotiwala
Constraint Violating Yak Guru

323 Posts

Posted - 2011-10-26 : 09:38:42

quote:
Originally posted by Cindyaz

Does the SP refer to a linked server? What is the wait type for that SP in sys.dm_exec_waiting_tasks? If it's preemptive_oledbops then you'll have to restart sql services.
Can you post the text of the SP?

This is the text of the stored proc:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

ALTER PROCEDURE [dbo].[bdsp_get_tkid_from_login]

@login varchar(50)

AS

-- 29 Aug 2011 L. McCourt - changed to use data warehouse, even tho i suspect this proc is not being used.

SET NOCOUNT ON

-- SELECT RTRIM(ISNULL(d.x,'00000')) [x] FROM dbo.directory d
-- WHERE d.ntloginname = @login

select rtrim(ltrim(x))
from linkedserver.database.dbo.view where Employment_Status_Code not in ('Pre','Wdw')
and rtrim(ltrim(Network_Login)) = @login

SET NOCOUNT OFF

Regards
Paresh Motiwala
Boston, USA

pareshmotiwala
Constraint Violating Yak Guru

323 Posts

Posted - 2011-10-26 : 09:39:20

quote:
Originally posted by nigelrivett

Does the SP do anything outside the server? xp_cmdshell, send mail for instance.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

I don't believe it does do anything outside the server Nigel.

Regards
Paresh Motiwala
Boston, USA

pareshmotiwala
Constraint Violating Yak Guru

323 Posts

Posted - 2011-10-26 : 09:40:09

quote:
Originally posted by pareshmotiwala

quote:
Originally posted by Cindyaz

Does the SP refer to a linked server? What is the wait type for that SP in sys.dm_exec_waiting_tasks? If it's preemptive_oledbops then you'll have to restart sql services.
Can you post the text of the SP?

I forgot to add, the linked server is sql2008 R2.
Regards
Paresh Motiwala
Boston, USA

Regards
Paresh Motiwala
Boston, USA

X002548
Not Just a Number

15586 Posts

Posted - 2011-10-26 : 14:33:52

well, that's fine...so how is it called??

That's my guess as to where the problem is.

Is it called from another sproc?

And is that REALLY the sproc?


select rtrim(ltrim(x)) 
from linkedserver.database.dbo.view	where Employment_Status_Code not in ('Pre','Wdw')
and rtrim(ltrim(Network_Login)) = @login

???

x

???

linkedserver.database.dbo.view

???

I'm thinking no

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

pareshmotiwala
Constraint Violating Yak Guru

323 Posts

Posted - 2011-10-27 : 08:31:46

Sorry Brett, it was a lame attempt at hiding our database structure due to confidentiality reasons.
Is there any extra information I can give? In the mean time, let me check with the powers to be, if I can indeed post the sproc.

Regards
Paresh Motiwala
Boston, USA

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-10-27 : 09:14:40

quote:
Originally posted by pareshmotiwala

quote:
Originally posted by nigelrivett

Does the SP do anything outside the server? xp_cmdshell, send mail for instance.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

I don't believe it does do anything outside the server Nigel.

Regards
Paresh Motiwala
Boston, USA

...
from linkedserver.database.dbo.view
...

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

X002548
Not Just a Number

15586 Posts

Posted - 2011-10-27 : 09:27:39

quote:
Originally posted by pareshmotiwala

Sorry Brett, it was a lame attempt at hiding our database structure due to confidentiality reasons.
Is there any extra information I can give? In the mean time, let me check with the powers to be, if I can indeed post the sproc.

Regards
Paresh Motiwala
Boston, USA

I don't care...just tell me how this sproc is called/executed?

By a Job, from another Sproc???

And if you can't post some concrete examples, how do you think we will be able to help?

post sp_lock and sp_who2 results

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

pareshmotiwala
Constraint Violating Yak Guru

323 Posts

Posted - 2011-10-27 : 11:05:54

So there is a webserver, our intranet(which is a third party app, we cannot reverse engineer it). IT talks to this database1 on SQL2000 from here it calls a storedproc to our Warehouse which is SQL2008 R2.

Further, I did look up MS articles about this, it seems DTC could play an important role in this too....Thanks for the interactivity.
Paresh

Regards
Paresh Motiwala
Boston, USA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources