Each time the underlying tables are dml modified the view needs to be recompiled.

Why are you using drop view instead of alter view?

You're welcome.

Make sure the antivirus isn't scanning the ldf, mdf,ndf files.

That is really weird.

What is the service pack of sql server & your client tools?

OS is up to date?

Any antivirus software present?

You can download it at

http://www.microsoft.com/downloads/details.aspx?FamilyID=BE6A2C5D-00DF-4220-B133-29C1E0B6585F&displaylang=en

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?