April 1, 2009 at 10:43 am
I Have recently had a sql server upgrade from sql 2000 to 2005. I have a stored procedure that links to one of our older servers that has sql 2000 installed on it. When I carry out the stored procedure I get the following error.
Msg 468, Level 16, State 9, Procedure sp_NSTS_Matching_Outpatient, Line 603
Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.
the Stored procedure is as follows:
USE [SollisWorking]
GO
/****** Object: StoredProcedure [dbo].[sp_NSTS_Matching_Outpatient] Script Date: 04/01/2009 17:04:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[sp_NSTS_Matching_Outpatient] (
@fromMonth VARCHAR(6),
@toMonth VARCHAR(6)
)
AS
TRUNCATE TABLE NSTS_Matching_Outpatient
CREATE TABLE #NSTS_PATIENT_UNION (
NHS_NUMBER nvarchar (11) NOT NULL,
UPD_ON_1 datetime,
DATE_OF_BIRTH datetime,
UPD_ON_2 datetime,
DATE_OF_DEATH datetime,
UPD_ON_3 datetime,
SURNAME nvarchar (30),
UPD_ON_4 datetime,
FORENAME nvarchar (30),
UPD_ON_5 datetime,
SEX nvarchar (1),
UPD_ON_6 datetime,
ADDRESS_LINE_1 nvarchar (30),
ADDRESS_LINE_2 nvarchar (30),
ADDRESS_LINE_3 nvarchar (30),
ADDRESS_LINE_4 nvarchar (30),
ADDRESS_LINE_5 nvarchar (30),
UPD_ON_7 datetime,
POSTCODE nvarchar (8),
UPD_ON_8 datetime,
POSTING nvarchar (5),
UPD_ON_9 datetime,
WARD_DISTRICT_CODE varchar (4),
WARD_DISTRICT_NAME varchar (50),
GP nvarchar (8),
GP_NAME nvarchar (50),
UPD_ON_10 datetime,
GP_PRACTICE nvarchar (6),
GP_PRACTICE_NAME nvarchar (50),
UPD_ON_11 datetime,
GP_PCT nvarchar (8),
GP_PCT_NAME nvarchar (50),
UPD_ON_12 datetime,
LOOKUP_ID nvarchar (11) NOT NULL,
EFFECTIVE_FROM datetime NOT NULL,
END_DATE datetime,
RESIDENT bit,
REGISTERED bit,
CONSTRAINT PK_NSTS_PATIENT_UNION PRIMARY KEY CLUSTERED
(
NHS_NUMBER,
LOOKUP_ID,
EFFECTIVE_FROM
) )
INSERT INTO #NSTS_PATIENT_UNION
SELECT NHS_NUMBER, UPD_ON_1, DATE_OF_BIRTH, UPD_ON_2, DATE_OF_DEATH, UPD_ON_3, SURNAME, UPD_ON_4, FORENAME, UPD_ON_5, SEX, UPD_ON_6, ADDRESS_LINE_1, ADDRESS_LINE_2, ADDRESS_LINE_3, ADDRESS_LINE_4, ADDRESS_LINE_5, UPD_ON_7, POSTCODE, UPD_ON_8, POSTING, UPD_ON_9, WARD_DISTRICT_CODE, WARD_DISTRICT_NAME, GP, GP_NAME, UPD_ON_10, GP_PRACTICE, GP_PRACTICE_NAME, UPD_ON_11, GP_PCT, GP_PCT_NAME, UPD_ON_12, LOOKUP_ID, EFFECTIVE_FROM,
GETDATE() AS END_DATE, RESIDENT, REGISTERED
FROM BRENTREPODB02.Olympus.dbo.NSTS_PATIENT
INSERT INTO #NSTS_PATIENT_UNION
SELECT *
FROM BRENTREPODB02.Olympus.dbo.NSTS_PATIENT_HISTORY
/**** PASS 1 ****/
/**** NHS Number matches *****/
INSERT INTO
NSTS_Matching_Outpatient (
ATTENDANCE_ID,
PROVIDER_CODE,
HOSPITAL_NO,
MONTH_OF_ATTENDANCE,
ATTENDANCE_DATE,
SPECIALTY,
REFERRAL_SOURCE,
REFERRER_CODE,
SUS_NHSNO,
NSTS_NHSNO,
SUS_DOB,
NSTS_DOB,
SUS_POSTCODE,
NSTS_POSTCODE,
SUS_SEX,
NSTS_SEX,
SUS_GP,
NSTS_GP,
SUS_PRACTICE,
PASS)
SELECT
SUS_OP.Attendance_ID,
LEFT(SUS_OP.PAS_data_source, 3),
SUS_OP.PAS_Hospital_Number,
SUS_OP.month_of_attendance,
SUS_OP.Date_of_Attendance,
SUS_OP.Specialty_Code,
SUS_OP.Source_of_Referral_Code,
SUS_OP.Referrer_Code,
SUS_OP.NHS_Number,
NSTS.NHS_NUMBER,
SUS_OP.Date_of_Birth AS SUS_DOB,
NSTS.DATE_OF_BIRTH AS NSTS_DOB,
REPLACE(SUS_OP.Postcode_of_Usual_Address, ' ', '') AS SUS_POSTCODE,
REPLACE(NSTS.POSTCODE, ' ', '') AS NSTS_POSTCODE,
SUS_OP.Sex AS SUS_SEX,
NSTS.SEX AS NSTS_SEX,
SUS_OP.GP_Code AS SUS_GP,
NSTS.GP AS NSTS_GP,
SUS_OP.Practice_Code AS SUS_PRACTICE,
1 AS PASS
FROM
nwcscmdsdata.dbo.Outpatient_CMDS_Data SUS_OP INNER JOIN
#NSTS_PATIENT_UNION NSTS ON SUS_OP.NHS_Number = NSTS.NHS_NUMBER AND
SUS_OP.Date_of_Attendance >= NSTS.EFFECTIVE_FROM AND
SUS_OP.Date_of_Attendance <= NSTS.END_DATE
WHERE
SUS_OP.month_of_attendance BETWEEN @fromMonth AND @toMonth AND
SUS_OP.Purchaser_ID LIKE '5K5%' AND
NOT ISNULL(SUS_OP.Contract_Line_Number,'') = 'NONCHARGE'
/**** NHS Number (pre-historic patient record) matches *****/
INSERT INTO
NSTS_Matching_Outpatient (
ATTENDANCE_ID,
PROVIDER_CODE,
HOSPITAL_NO,
MONTH_OF_ATTENDANCE,
ATTENDANCE_DATE,
SPECIALTY,
REFERRAL_SOURCE,
REFERRER_CODE,
SUS_NHSNO,
NSTS_NHSNO,
SUS_DOB,
NSTS_DOB,
SUS_POSTCODE,
NSTS_POSTCODE,
SUS_SEX,
NSTS_SEX,
SUS_GP,
NSTS_GP,
SUS_PRACTICE,
PASS)
SELECT
SUS_OP.Attendance_ID,
LEFT(SUS_OP.PAS_data_source, 3),
SUS_OP.PAS_Hospital_Number,
SUS_OP.month_of_attendance,
SUS_OP.Date_of_Attendance,
SUS_OP.Specialty_Code,
SUS_OP.Source_of_Referral_Code,
SUS_OP.Referrer_Code,
SUS_OP.NHS_Number,
NSTS.NHS_NUMBER,
SUS_OP.Date_of_Birth AS SUS_DOB,
NSTS.DATE_OF_BIRTH AS NSTS_DOB,
REPLACE(SUS_OP.Postcode_of_Usual_Address, ' ', '') AS SUS_POSTCODE,
REPLACE(NSTS.POSTCODE, ' ', '') AS NSTS_POSTCODE,
SUS_OP.Sex AS SUS_SEX,
NSTS.SEX AS NSTS_SEX,
SUS_OP.GP_Code AS SUS_GP,
NSTS.GP AS NSTS_GP,
SUS_OP.Practice_Code AS SUS_PRACTICE,
1 AS PASS
FROM
#NSTS_PATIENT_UNION NSTS INNER JOIN
(SELECT NHS_NUMBER, MIN(EFFECTIVE_FROM) AS EFFECTIVE_FROM
FROM #NSTS_PATIENT_UNION
GROUP BY NHS_NUMBER) NSTS_GROUPED ON NSTS.NHS_NUMBER = NSTS_GROUPED.NHS_NUMBER AND
NSTS.EFFECTIVE_FROM = NSTS_GROUPED.EFFECTIVE_FROM INNER JOIN
nwcscmdsdata.dbo.Outpatient_CMDS_Data SUS_OP ON
NSTS_GROUPED.NHS_NUMBER COLLATE SQL_Latin1_General_CP1_CI_AS = SUS_OP.NHS_Number LEFT OUTER JOIN
NSTS_Matching_Outpatient ON SUS_OP.Attendance_ID = NSTS_Matching_Outpatient.ATTENDANCE_ID
WHERE
SUS_OP.month_of_attendance BETWEEN @fromMonth AND @toMonth AND
SUS_OP.Purchaser_ID LIKE '5K5%' AND
NOT ISNULL(SUS_OP.Contract_Line_Number,'') = 'NONCHARGE' AND
NSTS_Matching_Outpatient.ATTENDANCE_ID IS NULL
/**** PASS 2 ****/
/**** DOB/Postcode matches *****/
INSERT INTO
NSTS_Matching_Outpatient (
ATTENDANCE_ID,
PROVIDER_CODE,
HOSPITAL_NO,
MONTH_OF_ATTENDANCE,
ATTENDANCE_DATE,
SPECIALTY,
REFERRAL_SOURCE,
REFERRER_CODE,
SUS_NHSNO,
NSTS_NHSNO,
SUS_DOB,
NSTS_DOB,
SUS_POSTCODE,
NSTS_POSTCODE,
SUS_SEX,
NSTS_SEX,
SUS_GP,
NSTS_GP,
SUS_PRACTICE,
PASS)
SELECT
SUS_OP.Attendance_ID,
LEFT(SUS_OP.PAS_data_source, 3),
SUS_OP.PAS_Hospital_Number,
SUS_OP.month_of_attendance,
SUS_OP.Date_of_Attendance,
SUS_OP.Specialty_Code,
SUS_OP.Source_of_Referral_Code,
SUS_OP.Referrer_Code,
SUS_OP.NHS_Number AS SUS_NHSNO,
NSTS.NHS_NUMBER AS NSTS_NHSNO,
SUS_OP.Date_of_Birth AS SUS_DOB,
NSTS.DATE_OF_BIRTH AS NSTS_DOB,
REPLACE(SUS_OP.Postcode_of_Usual_Address, ' ', '') AS SUS_POSTCODE,
REPLACE(NSTS.POSTCODE, ' ', '') AS NSTS_POSTCODE,
SUS_OP.Sex AS SUS_SEX,
NSTS.SEX AS NSTS_SEX,
SUS_OP.GP_Code AS SUS_GP, NSTS.GP AS NSTS_GP,
SUS_OP.Practice_Code AS SUS_PRACTICE, 2 AS PASS
FROM
nwcscmdsdata.dbo.Outpatient_CMDS_Data SUS_OP INNER JOIN
#NSTS_PATIENT_UNION NSTS ON
SUS_OP.Date_of_Attendance >= NSTS.EFFECTIVE_FROM AND
SUS_OP.Date_of_Birth = NSTS.DATE_OF_BIRTH AND
REPLACE(SUS_OP.Postcode_of_Usual_Address, ' ', '') = REPLACE(NSTS.POSTCODE, ' ', '') AND
SUS_OP.Sex = NSTS.SEX AND
SUS_OP.Date_of_Attendance <= NSTS.END_DATE LEFT OUTER JOIN
(SELECT DATE_OF_BIRTH, POSTCODE, SEX
FROM (SELECT NHS_NUMBER, DATE_OF_BIRTH, POSTCODE, SEX
FROM #NSTS_PATIENT_UNION NSTS
GROUP BY DATE_OF_BIRTH, POSTCODE, SEX, NHS_NUMBER
HAVING NOT NHS_NUMBER = 'UNALLOCATED') PATIENTS_GROUPED
GROUP BY DATE_OF_BIRTH, POSTCODE, SEX
HAVING COUNT(*) > 1) DUPLICATES ON
NSTS.DATE_OF_BIRTH = DUPLICATES.DATE_OF_BIRTH AND
NSTS.POSTCODE = DUPLICATES.POSTCODE AND
NSTS.SEX = DUPLICATES.SEX LEFT OUTER JOIN
NSTS_Matching_Outpatient ON SUS_OP.Attendance_ID = NSTS_Matching_Outpatient.ATTENDANCE_ID
WHERE
SUS_OP.month_of_attendance BETWEEN @fromMonth AND @toMonth AND
SUS_OP.Purchaser_ID LIKE '5K5%' AND
NOT ISNULL(SUS_OP.Contract_Line_Number,'') = 'NONCHARGE' AND
NSTS_Matching_Outpatient.ATTENDANCE_ID IS NULL AND
SUS_OP.NHS_Number = '' AND
NOT NSTS.NHS_NUMBER = 'UNALLOCATED' AND
DUPLICATES.DATE_OF_BIRTH IS NULL
/**** DOB/Postcode (pre-historic record) matches *****/
INSERT INTO
NSTS_Matching_Outpatient (
ATTENDANCE_ID,
PROVIDER_CODE,
HOSPITAL_NO,
MONTH_OF_ATTENDANCE,
ATTENDANCE_DATE,
SPECIALTY,
REFERRAL_SOURCE,
REFERRER_CODE,
SUS_NHSNO,
NSTS_NHSNO,
SUS_DOB,
NSTS_DOB,
SUS_POSTCODE,
NSTS_POSTCODE,
SUS_SEX,
NSTS_SEX,
SUS_GP,
NSTS_GP,
SUS_PRACTICE,
PASS)
SELECT
SUS_OP.Attendance_ID,
LEFT(SUS_OP.PAS_data_source, 3),
SUS_OP.PAS_Hospital_Number,
SUS_OP.month_of_attendance,
SUS_OP.Date_of_Attendance,
SUS_OP.Specialty_Code,
SUS_OP.Source_of_Referral_Code,
SUS_OP.Referrer_Code,
SUS_OP.NHS_Number AS SUS_NHSNO,
NSTS.NHS_NUMBER AS NSTS_NHSNO,
SUS_OP.Date_of_Birth AS SUS_DOB,
NSTS.DATE_OF_BIRTH AS NSTS_DOB,
REPLACE(SUS_OP.Postcode_of_Usual_Address, ' ', '') AS SUS_POSTCODE,
REPLACE(NSTS.POSTCODE, ' ', '') AS NSTS_POSTCODE,
SUS_OP.Sex AS SUS_SEX,
NSTS.SEX AS NSTS_SEX,
SUS_OP.GP_Code AS SUS_GP,
NSTS.GP AS NSTS_GP,
SUS_OP.Practice_Code AS SUS_PRACTICE,
2 AS PASS
FROM
(SELECT DATE_OF_BIRTH, POSTCODE, SEX
FROM (SELECT NHS_NUMBER, DATE_OF_BIRTH, POSTCODE, SEX
FROM #NSTS_PATIENT_UNION NSTS
GROUP BY DATE_OF_BIRTH, POSTCODE, NHS_NUMBER, SEX
HAVING NOT NHS_NUMBER = 'UNALLOCATED') PATIENTS_GROUPED
GROUP BY DATE_OF_BIRTH, POSTCODE, SEX
HAVING COUNT(*) > 1) DUPLICATES RIGHT OUTER JOIN
NSTS_Matching_Outpatient RIGHT OUTER JOIN
nwcscmdsdata.dbo.Outpatient_CMDS_Data SUS_OP INNER JOIN
#NSTS_PATIENT_UNION NSTS ON SUS_OP.Date_of_Birth = NSTS.DATE_OF_BIRTH AND
REPLACE(SUS_OP.Postcode_of_Usual_Address, ' ', '') = REPLACE(NSTS.POSTCODE, ' ', '') AND
SUS_OP.Sex = NSTS.SEX INNER JOIN
(SELECT DATE_OF_BIRTH, POSTCODE, SEX, MIN(EFFECTIVE_FROM) AS EFFECTIVE_FROM
FROM #NSTS_PATIENT_UNION
GROUP BY DATE_OF_BIRTH, POSTCODE, SEX) NSTS_GROUPED ON NSTS.DATE_OF_BIRTH = NSTS_GROUPED.DATE_OF_BIRTH AND
NSTS.POSTCODE = NSTS_GROUPED.POSTCODE COLLATE SQL_Latin1_General_CP1_CI_AS AND NSTS.SEX = NSTS_GROUPED.SEX AND
NSTS.EFFECTIVE_FROM = NSTS_GROUPED.EFFECTIVE_FROM ON NSTS_Matching_Outpatient.ATTENDANCE_ID = SUS_OP.Attendance_ID ON
DUPLICATES.DATE_OF_BIRTH = NSTS_GROUPED.DATE_OF_BIRTH AND DUPLICATES.POSTCODE = NSTS_GROUPED.POSTCODE AND
DUPLICATES.SEX = NSTS_GROUPED.SEX
WHERE
SUS_OP.month_of_attendance BETWEEN @fromMonth AND @toMonth AND
SUS_OP.Purchaser_ID LIKE '5K5%' AND
NOT ISNULL(SUS_OP.Contract_Line_Number,'') = 'NONCHARGE' AND
NSTS_Matching_Outpatient.ATTENDANCE_ID IS NULL AND
SUS_OP.NHS_Number = '' AND
NOT NSTS.NHS_NUMBER = 'UNALLOCATED' AND
DUPLICATES.DATE_OF_BIRTH IS NULL
/***** Unallocated NHS Number matches *****/
INSERT INTO
NSTS_Matching_Outpatient (
ATTENDANCE_ID,
PROVIDER_CODE,
HOSPITAL_NO,
MONTH_OF_ATTENDANCE,
ATTENDANCE_DATE,
SPECIALTY,
REFERRAL_SOURCE,
REFERRER_CODE,
SUS_NHSNO,
NSTS_NHSNO,
SUS_DOB,
NSTS_DOB,
SUS_POSTCODE,
NSTS_POSTCODE,
SUS_SEX,
NSTS_SEX,
SUS_GP,
NSTS_GP,
SUS_PRACTICE,
PASS)
SELECT
SUS_OP.Attendance_ID,
LEFT(SUS_OP.PAS_data_source, 3),
SUS_OP.PAS_Hospital_Number,
SUS_OP.month_of_attendance,
SUS_OP.Date_of_Attendance,
SUS_OP.Specialty_Code,
SUS_OP.Source_of_Referral_Code,
SUS_OP.Referrer_Code,
SUS_OP.NHS_Number AS SUS_NHSNO,
NSTS.NHS_NUMBER AS NSTS_NHSNO,
SUS_OP.Date_of_Birth AS SUS_DOB,
NSTS.DATE_OF_BIRTH AS NSTS_DOB,
REPLACE(SUS_OP.Postcode_of_Usual_Address, ' ', '') AS SUS_POSTCODE,
REPLACE(NSTS.POSTCODE, ' ', '') AS NSTS_POSTCODE,
SUS_OP.Sex AS SUS_SEX,
NSTS.SEX AS NSTS_SEX,
SUS_OP.GP_Code AS SUS_GP,
NSTS.GP AS NSTS_GP,
SUS_OP.Practice_Code AS SUS_PRACTICE,
2 AS PASS
FROM
nwcscmdsdata.dbo.Outpatient_CMDS_Data SUS_OP INNER JOIN
#NSTS_PATIENT_UNION NSTS ON
SUS_OP.Date_of_Attendance >= NSTS.EFFECTIVE_FROM AND
SUS_OP.Date_of_Birth = NSTS.DATE_OF_BIRTH AND
REPLACE(SUS_OP.Postcode_of_Usual_Address, ' ', '') = REPLACE(NSTS.POSTCODE, ' ', '') AND
SUS_OP.Sex = NSTS.SEX AND
SUS_OP.Date_of_Attendance <= NSTS.END_DATE LEFT OUTER JOIN
(SELECT DATE_OF_BIRTH, POSTCODE, SEX
FROM (SELECT LOOKUP_ID, DATE_OF_BIRTH, POSTCODE, SEX
FROM #NSTS_PATIENT_UNION NSTS
WHERE NHS_NUMBER = 'UNALLOCATED'
GROUP BY DATE_OF_BIRTH, POSTCODE, SEX, LOOKUP_ID) PATIENTS_GROUPED
GROUP BY DATE_OF_BIRTH, POSTCODE, SEX
HAVING COUNT(*) > 1) DUPLICATES ON
NSTS.DATE_OF_BIRTH = DUPLICATES.DATE_OF_BIRTH AND
NSTS.POSTCODE = DUPLICATES.POSTCODE AND
NSTS.SEX = DUPLICATES.SEX LEFT OUTER JOIN
NSTS_Matching_Outpatient ON SUS_OP.Attendance_ID = NSTS_Matching_Outpatient.ATTENDANCE_ID
WHERE
SUS_OP.month_of_attendance BETWEEN @fromMonth AND @toMonth AND
SUS_OP.Purchaser_ID LIKE '5K5%' AND
NOT ISNULL(SUS_OP.Contract_Line_Number,'') = 'NONCHARGE' AND
NSTS_Matching_Outpatient.ATTENDANCE_ID IS NULL AND
SUS_OP.NHS_Number = '' AND
NSTS.NHS_NUMBER = 'UNALLOCATED' AND
DUPLICATES.DATE_OF_BIRTH IS NULL
/***** Unallocated NHS Number matches (pre-historic records) *****/
INSERT INTO
NSTS_Matching_Outpatient (
ATTENDANCE_ID,
PROVIDER_CODE,
HOSPITAL_NO,
MONTH_OF_ATTENDANCE,
ATTENDANCE_DATE,
SPECIALTY,
REFERRAL_SOURCE,
REFERRER_CODE,
SUS_NHSNO,
NSTS_NHSNO,
SUS_DOB,
NSTS_DOB,
SUS_POSTCODE,
NSTS_POSTCODE,
SUS_SEX,
NSTS_SEX,
SUS_GP,
NSTS_GP,
SUS_PRACTICE,
PASS)
SELECT
SUS_OP.Attendance_ID,
LEFT(SUS_OP.PAS_data_source, 3),
SUS_OP.PAS_Hospital_Number,
SUS_OP.month_of_attendance,
SUS_OP.Date_of_Attendance,
SUS_OP.Specialty_Code,
SUS_OP.Source_of_Referral_Code,
SUS_OP.Referrer_Code,
SUS_OP.NHS_Number AS SUS_NHSNO,
NSTS.NHS_NUMBER AS NSTS_NHSNO,
SUS_OP.Date_of_Birth AS SUS_DOB,
NSTS.DATE_OF_BIRTH AS NSTS_DOB,
REPLACE(SUS_OP.Postcode_of_Usual_Address, ' ', '') AS SUS_POSTCODE,
REPLACE(NSTS.POSTCODE, ' ', '') AS NSTS_POSTCODE,
SUS_OP.Sex AS SUS_SEX,
NSTS.SEX AS NSTS_SEX,
SUS_OP.GP_Code AS SUS_GP,
NSTS.GP AS NSTS_GP,
SUS_OP.Practice_Code AS SUS_PRACTICE,
2 AS PASS
FROM
(SELECT DATE_OF_BIRTH, POSTCODE, SEX
FROM (SELECT LOOKUP_ID, DATE_OF_BIRTH, POSTCODE, SEX
FROM #NSTS_PATIENT_UNION NSTS
WHERE NHS_NUMBER = 'UNALLOCATED'
GROUP BY DATE_OF_BIRTH, POSTCODE, LOOKUP_ID, SEX) PATIENTS_GROUPED
GROUP BY DATE_OF_BIRTH, POSTCODE, SEX
HAVING COUNT(*) > 1) DUPLICATES RIGHT OUTER JOIN
NSTS_Matching_Outpatient RIGHT OUTER JOIN
nwcscmdsdata.dbo.Outpatient_CMDS_Data SUS_OP INNER JOIN
#NSTS_PATIENT_UNION NSTS ON SUS_OP.Date_of_Birth = NSTS.DATE_OF_BIRTH AND
REPLACE(SUS_OP.Postcode_of_Usual_Address, ' ', '') = REPLACE(NSTS.POSTCODE, ' ', '') AND
SUS_OP.Sex = NSTS.SEX INNER JOIN
(SELECT DATE_OF_BIRTH, POSTCODE, SEX, MIN(EFFECTIVE_FROM) AS EFFECTIVE_FROM
FROM #NSTS_PATIENT_UNION
GROUP BY DATE_OF_BIRTH, POSTCODE, SEX) NSTS_GROUPED ON NSTS.DATE_OF_BIRTH = NSTS_GROUPED.DATE_OF_BIRTH AND
NSTS.POSTCODE = NSTS_GROUPED.POSTCODE COLLATE SQL_Latin1_General_CP1_CI_AS AND NSTS.SEX = NSTS_GROUPED.SEX AND
NSTS.EFFECTIVE_FROM = NSTS_GROUPED.EFFECTIVE_FROM ON NSTS_Matching_Outpatient.ATTENDANCE_ID = SUS_OP.Attendance_ID ON
DUPLICATES.DATE_OF_BIRTH = NSTS_GROUPED.DATE_OF_BIRTH AND DUPLICATES.POSTCODE = NSTS_GROUPED.POSTCODE AND
DUPLICATES.SEX = NSTS_GROUPED.SEX
WHERE
SUS_OP.month_of_attendance BETWEEN @fromMonth AND @toMonth AND
SUS_OP.Purchaser_ID LIKE '5K5%' AND
NOT ISNULL(SUS_OP.Contract_Line_Number,'') = 'NONCHARGE' AND
NSTS_Matching_Outpatient.ATTENDANCE_ID IS NULL AND
SUS_OP.NHS_Number = '' AND
NSTS.NHS_NUMBER = 'UNALLOCATED' AND
DUPLICATES.DATE_OF_BIRTH IS NULL
/**** Insert uncertain matches (more than one DOB/postcode/sex per NHS no ****/
INSERT INTO
NSTS_Matching_Outpatient (
ATTENDANCE_ID,
PROVIDER_CODE,
HOSPITAL_NO,
MONTH_OF_ATTENDANCE,
ATTENDANCE_DATE,
SPECIALTY,
REFERRAL_SOURCE,
REFERRER_CODE,
SUS_NHSNO,
SUS_DOB,
SUS_POSTCODE,
SUS_SEX,
SUS_GP,
SUS_PRACTICE,
PASS)
SELECT
SUS_OP.Attendance_ID,
LEFT(SUS_OP.PAS_data_source, 3),
SUS_OP.PAS_Hospital_Number,
SUS_OP.month_of_attendance,
SUS_OP.Date_of_Attendance,
SUS_OP.Specialty_Code,
SUS_OP.Source_of_Referral_Code,
SUS_OP.Referrer_Code,
SUS_OP.NHS_Number AS SUS_NHSNO,
SUS_OP.Date_of_Birth AS SUS_DOB,
REPLACE(SUS_OP.Postcode_of_Usual_Address, ' ', '') AS SUS_POSTCODE,
SUS_OP.Sex AS SUS_SEX,
SUS_OP.GP_Code AS SUS_GP,
SUS_OP.Practice_Code AS SUS_PRACTICE,
2 AS PASS
FROM
nwcscmdsdata.dbo.Outpatient_CMDS_Data SUS_OP INNER JOIN
(SELECT DATE_OF_BIRTH, POSTCODE, SEX
FROM (SELECT NHS_NUMBER, DATE_OF_BIRTH, POSTCODE, SEX
FROM #NSTS_PATIENT_UNION NSTS
GROUP BY DATE_OF_BIRTH, POSTCODE, SEX, NHS_NUMBER
HAVING NOT NHS_NUMBER = 'UNALLOCATED') PATIENTS_GROUPED
GROUP BY DATE_OF_BIRTH, POSTCODE, SEX
HAVING COUNT(*) > 1) DUPLICATES ON SUS_OP.Date_of_Birth = DUPLICATES.DATE_OF_BIRTH AND
REPLACE(SUS_OP.Postcode_of_Usual_Address, ' ', '') = REPLACE(DUPLICATES.POSTCODE, ' ','') COLLATE SQL_Latin1_General_CP1_CI_AS AND
SUS_OP.Sex = DUPLICATES.SEX COLLATE SQL_Latin1_General_CP1_CI_AS LEFT OUTER JOIN
NSTS_Matching_Outpatient ON SUS_OP.Attendance_ID = NSTS_Matching_Outpatient.ATTENDANCE_ID
WHERE
SUS_OP.month_of_attendance BETWEEN @fromMonth AND @toMonth AND
SUS_OP.Purchaser_ID LIKE '5K5%' AND
NOT ISNULL(SUS_OP.Contract_Line_Number,'') = 'NONCHARGE' AND
NSTS_Matching_Outpatient.ATTENDANCE_ID IS NULL AND
SUS_OP.NHS_Number = ''
/**** PASS 3 ****/
/**** Insert remaining unfound attendance records ****/
INSERT INTO
NSTS_Matching_Outpatient (
ATTENDANCE_ID,
PROVIDER_CODE,
HOSPITAL_NO,
MONTH_OF_ATTENDANCE,
ATTENDANCE_DATE,
SPECIALTY,
REFERRAL_SOURCE,
REFERRER_CODE,
SUS_NHSNO,
SUS_DOB,
SUS_POSTCODE,
SUS_SEX,
SUS_GP,
SUS_PRACTICE,
PASS)
SELECT
SUS_OP.Attendance_ID,
LEFT(SUS_OP.PAS_data_source, 3),
SUS_OP.PAS_Hospital_Number,
SUS_OP.month_of_attendance,
SUS_OP.Date_of_Attendance,
SUS_OP.Specialty_Code,
SUS_OP.Source_of_Referral_Code,
SUS_OP.Referrer_Code,
SUS_OP.NHS_Number AS SUS_NHSNO,
SUS_OP.Date_of_Birth AS SUS_DOB,
REPLACE(SUS_OP.Postcode_of_Usual_Address, ' ', '') AS SUS_POSTCODE,
SUS_OP.Sex AS SUS_SEX,
SUS_OP.GP_Code AS SUS_GP,
SUS_OP.Practice_Code AS SUS_PRACTICE,
3 AS PASS
FROM
nwcscmdsdata.dbo.Outpatient_CMDS_Data SUS_OP LEFT OUTER JOIN
NSTS_Matching_Outpatient ON SUS_OP.Attendance_ID = NSTS_Matching_Outpatient.ATTENDANCE_ID
WHERE
SUS_OP.month_of_attendance BETWEEN @fromMonth AND @toMonth AND
SUS_OP.Purchaser_ID LIKE '5K5%' AND
NOT ISNULL(SUS_OP.Contract_Line_Number,'') = 'NONCHARGE' AND
NSTS_Matching_Outpatient.ATTENDANCE_ID IS NULL
/**** Update Practice from current GP record ****/
UPDATE NSTS_Matching_Outpatient
SET
NSTS_PRACTICE = GP.PRACTICE_CODE
FROM
NSTS_Matching_Outpatient INNER JOIN
BRENTREPODB02.Olympus.dbo.NACS_GENERAL_PRACTITIONER GP ON NSTS_Matching_Outpatient.NSTS_GP = GP.GP_CODE AND
NSTS_Matching_Outpatient.ATTENDANCE_DATE >= GP.GP_PRACTICE_START
WHERE
GP.GP_PRACTICE_END >= NSTS_Matching_Outpatient.ATTENDANCE_DATE OR GP.GP_PRACTICE_END IS NULL
/**** Update Practice from historic GP record ****/
UPDATE NSTS_Matching_Outpatient
SET
NSTS_PRACTICE = GP.PRACTICE_CODE
FROM
NSTS_Matching_Outpatient INNER JOIN
BRENTREPODB02.Olympus.dbo.NACS_GENERAL_PRACTITIONER_HISTORY GP ON NSTS_Matching_Outpatient.NSTS_GP = GP.GP_CODE AND
NSTS_Matching_Outpatient.ATTENDANCE_DATE >= GP.GP_PRACTICE_START
WHERE
(GP.GP_PRACTICE_END >= NSTS_Matching_Outpatient.ATTENDANCE_DATE OR GP.GP_PRACTICE_END IS NULL) AND
NSTS_Matching_Outpatient.NSTS_PRACTICE IS NULL
/**** Update PCT from Olympus ****/
UPDATE NSTS_Matching_Outpatient
SET
SUS_PCT = SUS_PCT.PCT_CODE,
NSTS_PCT = NSTS_PCT.PCT_CODE
FROM
BRENTREPODB02.Olympus.dbo.GP_PRACTICE NSTS_PRACTICE LEFT OUTER JOIN
BRENTREPODB02.Olympus.dbo.PCT NSTS_PCT ON NSTS_PRACTICE.PCT_KEY = NSTS_PCT.PCT_KEY RIGHT OUTER JOIN
NSTS_Matching_Outpatient ON
NSTS_PRACTICE.GP_PRACTICE_CODE COLLATE SQL_Latin1_General_CP1_CI_AS = NSTS_Matching_Outpatient.NSTS_PRACTICE LEFT OUTER JOIN
BRENTREPODB02.Olympus.dbo.GP_PRACTICE SUS_PRACTICE LEFT OUTER JOIN
BRENTREPODB02.Olympus.dbo.PCT SUS_PCT ON SUS_PRACTICE.PCT_KEY = SUS_PCT.PCT_KEY ON
NSTS_Matching_Outpatient.SUS_PRACTICE = SUS_PRACTICE.GP_PRACTICE_CODE COLLATE SQL_Latin1_General_CP1_CI_AS
/**** Update POD ****/
UPDATE NSTS_Matching_Outpatient
SET POD = 'OP' +
CASE WHEN dbo.udf_calculateAge(Date_of_Birth,Date_of_Attendance) < 17
THEN 'CH' ELSE '' END +
CASE WHEN First_Attendance = 1
THEN 'FA' ELSE 'FUP' END
FROM
NSTS_Matching_Outpatient INNER JOIN
nwcscmdsdata..Outpatient_CMDS_Data SUS_OP ON
NSTS_Matching_Outpatient.ATTENDANCE_ID = SUS_OP.Attendance_ID
DROP TABLE #NSTS_PATIENT_UNION
The BRENTREPODB02 server has the sql 2000 and server collation Latin1_General_CI_AS. The sql2005 is BrentVMSollis and has the collation SQL_Latin1_General_CP1_CI_AS and is the server I am running the stored procedure from. I am not sure to resolve this issue. Any help would be appreciated.
April 1, 2009 at 11:04 am
Hi
Try this in lines 600 to 611 of your procedure (starting from ALTER PROCEDURE):
/**** Update Practice from historic GP record ****/
UPDATE NSTS_Matching_Outpatient
SET
NSTS_PRACTICE = GP.PRACTICE_CODE
FROM
NSTS_Matching_Outpatient INNER JOIN
BRENTREPODB02.Olympus.dbo.NACS_GENERAL_PRACTITIONER_HISTORY GP ON NSTS_Matching_Outpatient.NSTS_GP COLLATE SQL_Latin1_General_CP1_CI_AS = GP.GP_CODE AND
NSTS_Matching_Outpatient.ATTENDANCE_DATE >= GP.GP_PRACTICE_START
WHERE
(GP.GP_PRACTICE_END >= NSTS_Matching_Outpatient.ATTENDANCE_DATE OR GP.GP_PRACTICE_END IS NULL) AND
NSTS_Matching_Outpatient.NSTS_PRACTICE IS NULL
If this does not fix all problems either go-on with next lines (the line number is shown in error message).
Greets
Flo
How to Post Data/Code to get the best Help How to Post Performance Problems
April 2, 2009 at 9:43 am
I put that in and it worked. Could you explain how you knew where to put it. I thought the statement COLLATE SQL_Latin1_General_CP1_CI_AS would be with the BRENTREPODB02 alias?
April 2, 2009 at 10:24 am
eseosaoregie (4/2/2009)
I put that in and it worked. Could you explain how you knew where to put it. I thought the statement COLLATE SQL_Latin1_General_CP1_CI_AS would be with the BRENTREPODB02 alias?
Telling the truth I was a little proud by myself :hehe:
The secret is you gave me a hint with your error message:
Msg 468, Level 16, State 9, Procedure sp_NSTS_Matching_Outpatient, Line 603
Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.
If you read the error message exactly you see the error was specified in line 603. SQL Server starts line counting at your "ALTER PROCEDURE" statement and usually points to a statement-block (in this case the UPDATE statement). You told that the collation of your "BRENTREPODB02" was different to your local server. The only position where you join your local server and the remote server in this block was line 607, your JOIN. Done 😉
Greets
Flo
How to Post Data/Code to get the best Help How to Post Performance Problems
April 2, 2009 at 10:38 am
Many thanks. That explains a lot. Cheers
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply