I would see the book online for that topic.

If there was a large transaction going on ( like altering a table in enterprise manager) it will be rolled back (...

1)Is there any application active on the server except Windows 2003 and sql server? (Antivirus, IIS,...)

2) Ms full search is enabled?

1) Does it have a lot of recovering (rolling forward transactions) to do (see sql server logs)

2) It might be because of the tempdb is growing to its initial size

3) Anything...

I wouldn't expect any problems leaving compatibility at 80, because there aren't major differences between sql server 7 and sql server 2000.

In 2000 they added user defined functions,changable collations and better...

1)if you use awe, you need to have a hotfix:

http://support.microsoft.com/default.aspx?kbid=899761

2) some other discussions on SP4

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=5&messageid=182669&p=2

1) Are you using linked tables? If you, was the password saved when making them

2) Are you using DSN-less connections?

see http://www.connectionstrings.com/ for a list of connectionstrings.

3)You shouldn't use sa for...

Thanks for pointing that out.

Very likely.

Is the script holding locks during its executions?

Any cursors?

Use a left join instead of an inner join.

SELECT JPT.JobPosting_ID,

ISNULL(MADT.ApplicationsReceived,0),

 JPT.Job_Code,

 JPT.Job_Title,

 JPT.Job_Posting_Date,

 JPT.Job_Location_Type,

 JPT.Job_Location

FROM

 Job_Posting_Table JPT

LEFT JOIN /*jobpostings can be without madt, ApplicationsReceived will be null then*/

 (

 SELECT JobPosting_ID, COUNT(*) ApplicationsReceived

 FROM

  Mgmt_App_Detail_Table

 GROUP BY JobPosting_ID) MADT

ON

 JPT.JobPosting_ID...

unique index:

pro: speed up selects,DRI

cons: slows updates, deletes, inserts as the index needs to be maintained, space used

use : mostly if the fields are frequently searched upon

unique constraint:

pro: still maintains uniqueness, doesn't...

If you are not using the 64 bit versions of w3k & sql 2000, you have to use the /PAE boot switch and set the memory for sql server (fixed)...

My understanding was that if someone reads a row in the database then this row is locked for the duration of the read and that if someone tries to update...

It could be that the default database for the ASP was changed.

Like is faster since it can use indexes. (with the wildcard at the right)

Left is applied to all records. (no index)

You're welcome.