Interesting.

Thanks for sharing.

I would change 2 things

*Set rs2 = conn.Execute(sql)

        While Not rs2.EOF

... This will hold locks on non-fetched data

to

set rs2=conn.execute(sql)

if rs2.recordcount>0 then

rs2.movelast

rs2.movefirst

While Not rs2.EOF

... all data retrieved, no remaining locks

* ...

We had a similiar problem before. It was solved when we enabled connection pooling. The connection(s) are closed when they are no longer needed?

Have you stresstested the raid-configuration of the 2005 sql server?

depends if the function is based on a datacolumn (needs calculation per record) or if the function is based on a condition (calculated once, calculated value is than a candidate...

The amount of reads are a good indication of I/O. Since it says 30000 instead of 110000 it should be faster. A look at the actual execution plan wouldn't hurt.

Are you connecting via ip or naming? Sql authentication or windows authentication?

Have you found any info in the windows error log? (start->execute->eventvwr)

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.