ms access adp calls a stored proc (SQL Server ) but just hangs in 2005 but ok in 2000

views:

answers:

+1 Q:

ms access adp calls a stored proc (SQL Server ) but just hangs in 2005 but ok in 2000

Hi 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

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.

Martin Smith 2010-06-23 11:21:18

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 2010-06-23 11:53:35

@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 2010-06-23 12:06:30

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 2010-06-23 12:17:38

I can only see events like "Lock acquired" the "lock Released" within milliseconds of each other

Joscion 2010-06-23 12:26:29

@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 2010-06-23 12:40:30

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 2010-06-23 12:43:03

Joscion 2010-06-23 12:49:13

So in Profiler if you capture the `SP:StmtStarting` and `RPC:Starting` events does your query show up at all?

Martin Smith 2010-06-23 13:05:27

No it doesnt, Not at all !

Joscion 2010-06-23 13:12:31

So that's it right? I'm not starting anything?

Joscion 2010-06-23 13:18:38

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 2010-06-23 13:24:03

Yeah, Via VBA in the ADP. IS an error trap probin my VBA in ADP?

Joscion 2010-06-23 13:27:23

@Joscion - Hopefully something simple like that!

Martin Smith 2010-06-23 13:31:07

Sorry, just realised the sp it's calling a linked server aswell,

Joscion 2010-06-23 13:32:31

Thanks Martin, you are great for taking the time to explore the possibilities,

Joscion 2010-06-23 13:33:14

As Edison said, he found 2000 ways of how not to work a light bulb :) Thanks again.

Joscion 2010-06-23 13:34:01

ansaurus

tags:

views:

answers:

ms access adp calls a stored proc (SQL Server ) but just hangs in 2005 but ok in 2000

related questions