not sure if this is the simplified version you want.

ALTER PROCEDURE [dbo].[get_SClinicals_list]

@DateFrom datetime='18000101', /*lowest possible*/

@DateToIn datetime='99990101', /*highest possible*/

@mName varchar(50)=NULL,

@ccName varchar(20)=NULL

AS

SET NOCOUNT ON

IF @mName is null...

Mark,

Have you tried running the query using "query analyzer" instead of in enterprise manager?

If you have a job, you should also check if the account of sql agent has sufficient...

Does Access 2003 have the latest service pack applied ?(To rule out some possible bugs)

perhaps you can find some indication of the error in the windows event logs (searching on sql server).

Start->execute->eventvwr

It is likely that the form is corrupt.

Have you recreated the form from scratch (since you imported some objects)?

There is a field Hostname you can place a filter on, but this will probably be the webserver.

Have you checked default connection-settings between test and production server?

Is there a difference...

I'm not familiar with informatica. The main issues we had with importing from oracle to sql server are dates<1800.

Someone circumvented this in a recent post. The trick was to create a view dbo.VW_NOW (name of choice)with as definition: select getdate() as today

From the function use select today from...

I assume it recompiles since there is an explicit command alter procedure.

Have a trace running (sql profiler) at night monitoring for cpu-intensive queries?

Are these spikes or continuous (performance monitor on windows-system)?

It could be a different execution plan between the stored procedure and the extracted sql code due parameter sniffing. The query plan could help here.

To capture the sql-statements you can...

You could divide the delete in a set of smaller batches.

pseudocode

SELECT  1

WHILE @@ROWCOUNT>0

BEGIN

DELETE FROM test where id in (SELECT TOP 100 from test where deletecondition=true)

WAITFORDELAY ...

END

You might search the web on sp_attach_single_file_db. Make sure you have it backed up first.

There must be a more elaborate page than this one

http://www.mssqlcity.com/FAQ/BackRest/ResMaster.htm

Hello,

There is a page about locking in sql server 7,2000 where the key is described.

See http://support.microsoft.com/kb/224453/