I suspect SQL2005 does better syntax checking.

Your derived table, NOTA, only returns the columns SOMA and NUMDU. so

ORDER BY NOTA.CODLIQ DESC

does not make sense.

SQL2000 must try to find any column...

Your first query has the edrs_no filter in the wrong place.

Try:

SELECT uniqueid, course_code, instance_code, awarding_body, delegate_name,

course_title, glh, Employer_postcode, edrs_no, recordmanager, delegateid, entityid

FROM

(

SELECT uniqueid, course_code, instance_code, awarding_body, delegate_name,

course_title, glh, Employer_postcode, edrs_no,...

No, British English is either 'dd/mm/yyyy' or 'dd/mm/yy'.

Also, regardless of how the date is set, 'yyyymmdd' will always convert to a datetime.

So, for your...

For the two date formats given the following will work:

-- set to european format.

SET DATEFORMAT dmy

DECLARE @t TABLE

(

CharDate char(10) NOT NULL

)

INSERT INTO @t

SELECT '31/12/2009' UNION ALL

SELECT '2009-06-30'

SELECT ISDATE(REPLACE(Chardate, '-', ''))...

With SQL2008 and the latest version of ADO you can even pass a table.

Without test data, and expected results, it is difficult to tell but maybe something like:

SET QUOTED_IDENTIFIER, ANSI_NULLS ON

GO

CREATE PROCEDURE dbo.P_SRE_T_CAL

@CURDATE datetime,

@ERROR varchar(50) OUTPUT,

@TRANIDUP varchar(8000)...

Either use exception handling in the calling procedure or try the following:

--Run subproc

DECLARE @err int

EXEC @err = spNewSubProc @params

IF @err 0

GOTO OnError

Try the universal data format:

SELECT count(*)

,[date]

FROM tbl1

WHERE [date] BETWEEN '20090501' AND '20090531 23:59:59.997'

GROUP BY [date]

Difficult to tell without sample data and expected results, but you seem not to be restricting the subqueries

by the STVTERM_DESC. In this case I would forget about subqueries and just...

Logically the INNER JOIN is done first. ie The INNER JOIN is the nested join.

Maybe something like:

-- *** Test Data ***

DECLARE @t TABLE

(

Item int NOT NULL

,Sales_Date smalldatetime NOT NULL

,Quantity int NOT NULL

,Amount money NOT NULL

)

INSERT INTO @t

SELECT 1, '20090222', 1, 5.00 UNION ALL

SELECT 1,...

This is a nested join. To make it readable I would recommend that you always use brakets:

SELECT *

FROM dbo.demog dm

RIGHT JOIN

(

dbo.packlist pl

JOIN dbo.kittype kt

ON pl.kittype = kt.kittype

)

ON dm.siteid =...

You should check this yourself by looking at the query plans.

I suspect the plans will be the same as the optimizer should sort out the best access path.

You could try putting sp_releaseapplock in the CATCH block as well as the TRY block.

As you seem to be using SESSION applocks I think you would have difficulty is determining...

WHERE Title LIKE SearchText + '%'

AND [Description] LIKE SearchText+ '%'

AND link LIKE SearchText + '%'