You can use the sp_who2 stored procedure to check the current connections from time to time. It runs much faster than enterprise manager activity.

You can also monitor a lot of...

SELECT a FROM testTable  -- works

SELECT a.a FROM testTable a INNER JOIN testTable b ON a.a = b.a -- does not work 

Is there an error message or what do you mean with does not work?

Sandy,

There are many reasons to use stored procedures instead of concatenating strings to create the command. One is performance (by caching query plans), second is security (see sql injection attacks),...

What is BDE you are talking about?

So Ado works with sql authentication?

Keep in mind, to fix the problem however, I do not need to restart SQL Server box, just the IIS Server.  Not sure if that makes a difference or not.

It...

Thanks for the info. So the problem is reduced to a specific table.

Not sure if SET TRANSACTION ISOLATION LEVEL READUNCOMMITTED  propagates to a linked server.

If you use the sql profiler,...

That can be the case when SQL Server is set to Windows Authentication only.

In mixed mode you can login with a userid/password.

Are the execution plans the same on all server?

You can see how much memory uses with performance monitor & sql counters.

The new server, does it has the same RAID/SAN implementation?

Linked...

Could it be a timeout?

CREATE PROCEDURE dbo.USP_FIND_FREE_ROOM

(@ReqCapacity tinyint

,@StartDate datetime

,@EndDate datetime

,@NeedOHP bit=NULL /*0=not needed,1=needed,NULL=don't check*/

,@NeedAV bit=NULL

)

AS

SET NOCOUNT ON

SELECT DISTINCT RoomName FROM dbo.Room Room

INNER JOIN dbo.RoomCalendar RoomCalendar

ON Room.RoomID = RoomCalendar.RoomID

AND Room.Capacity...

I don't know the option but I would say not.

Have they supplied you with valid reasons why they want the option?

Any errors in the eventviewer/sql server logs?

Possible reasons for memory leaks

Cursors aren't properly released

Any calls with sp_OACreate not properly released.

Lingering transactions

Can you test it with a very simple query, specifying a timeout if necessary?

Start a trace with sql profiler on the linked server and look how far the query got.

Start a sql trace with SQL profiler.

With a bit of luck you can catch the hostname.

Because the local user accounts aren't validated by the domain controller -> untrusted.