1

This never happened before, the apd doesnt return any error messsages, just calls the stored proc (delete statement) but doesn't complete, no error message.

This only happens when calling the sp on SQL Server 2005, it's fine with the same query in SQL Server 2000.

It doesnt make any sense! Please Help!! I've run out of ideas, I will try any suggestion.

Thanks in advance, Joseph

pnuts
  • 58,317
  • 11
  • 87
  • 139
Joscion
  • 13
  • 4

1 Answers1

0

Do you have access to SQL Profiler? If so as a first step you could use that to see if the request is actually being made successfully and is as you expect.

If it is and is just taking a long time to execute have a look at sys.dm_os_waiting_tasks to see if it is blocked.

You can also follow the advice in this answer to have a look at the execution plan and see if it seems reasonable.

Community
  • 1
  • 1
Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • Thanks for your reply, I have tried setting "set LOCK_TIMEOUT = -1, I had thought it could be a record lock, but that also fails :( – Joscion Jun 23 '10 at 11:53
  • @Joscion - You need to monitor it and find out what the problem is before applying a solution! Did you have any luck looking in `sys.dm_os_waiting_tasks` whilst it hangs? – Martin Smith Jun 23 '10 at 12:06
  • Hi, Sorry if this sounds silly, but I'm a newbie with profiler, is the sys.dm_os_waiting_tasks within the text of the profiler? – Joscion Jun 23 '10 at 12:17
  • I can only see events like "Lock acquired" the "lock Released" within milliseconds of each other – Joscion Jun 23 '10 at 12:26
  • @Josicon. No if you do `select * from sys.dm_os_waiting_tasks` I was hoping you would see a nice obvious block! Also what does it say for reads, writes, and CPU time for your query if you do `select * from sys.dm_exec_requests`? – Martin Smith Jun 23 '10 at 12:40
  • I couldn't see it anything related to it there, but when I select who2, it sits under "awaiting command", nothing else after that – Joscion Jun 23 '10 at 12:43
  • Under sys.dm_exec_requests, I see its says "running" elapsed time "84", cpu & read & write are all "0" – Joscion Jun 23 '10 at 12:49
  • So in Profiler if you capture the `SP:StmtStarting` and `RPC:Starting` events does your query show up at all? – Martin Smith Jun 23 '10 at 13:05
  • Strange. So you can definitely see the connection from your Application via sp_who2 and it was "awaiting command" but the execute doesn't work. How are you executing it? Is it via code? – Martin Smith Jun 23 '10 at 13:24
  • Yeah, Via VBA in the ADP. IS an error trap probin my VBA in ADP? – Joscion Jun 23 '10 at 13:27
  • @Joscion - Hopefully something simple like that! – Martin Smith Jun 23 '10 at 13:31
  • Sorry, just realised the sp it's calling a linked server aswell, – Joscion Jun 23 '10 at 13:32
  • Thanks Martin, you are great for taking the time to explore the possibilities, – Joscion Jun 23 '10 at 13:33
  • 1
    As Edison said, he found 2000 ways of how not to work a light bulb :) Thanks again. – Joscion Jun 23 '10 at 13:34