Niranga
hi all,
what is this error and how to resolve it.
Could not find database ID 102. Database may not be activated yet or may be in transition.
this is the query which i work on.
/******** Outpatient Referrals to Consultants from GP and Other Referral Sources **********/
declare
@pct varchar(10),
@Speciality varchar(50),
@StartDate datetime,
@EndDate datetime
set @pct = 'central'
set @Speciality = null
set @StartDate = '01/01/2007'
set @EndDate = '01/06/2007'
IF (@pct IS NULL AND @Speciality IS NULL)
BEGIN
SELECT Specialty, Sum(GMP) As GMP, Sum(GPWritten) As GPWritten, Sum(Others) As Others
FROM
(
SELECT
sp.Description As Specialty,
(
SELECT
count(rf1.Refrl_refNo) As GMP
FROM
Refs rf1
INNER JOIN Specs sp1 ON rf1.RefTo_Spect_RefNo = sp1.Spect_RefNo
WHERE
rf1.Refrl_refNo =rf.Refrl_refNo
AND rf.Sorrf_RefNo = '5300' --GMP
GROUP BY
sp.Description
) As GMP,
(
SELECT
count(rf1.Refrl_refNo) As GMP
FROM
Refs rf1
INNER JOIN Specs sp1 ON rf1.RefTo_Spect_RefNo = sp1.Spect_RefNo
WHERE
rf1.Refrl_refNo =rf.Refrl_refNo
AND rf.Sorrf_RefNo = '2003641' --GPWritten
GROUP BY
sp.Description
) As GPWritten,
(
SELECT
count(rf1.Refrl_refNo)
FROM
Refs rf1
INNER JOIN Specs sp1 ON rf1.RefTo_Spect_RefNo = sp1.Spect_RefNo
WHERE
rf1.Refrl_refNo =rf.Refrl_refNo
AND rf.Sorrf_RefNo <> '2003641' --NOT GP Written
AND rf.Sorrf_RefNo <> '5300' --NOT GMP
GROUP BY
sp.Description
) As Others
FROM
Refs rf
INNER JOIN Specs sp ON rf.RefTo_Spect_RefNo = sp.Spect_RefNo
INNER JOIN GEOGRAPHICAREA GA ON dbo.udf_RetreaveGorgraphicAreaC
WHERE
rf.Recvd_Dttm BETWEEN CONVERT(DATETIME,@StartDate, 103) AND CONVERT(DATETIME,@EndDate + 1,103)
AND (GA.GeogAreaDesc LIKE 'Central' OR GA.GeogAreaDesc LIKE 'North' OR GA.GeogAreaDesc LIKE 'South')
AND (sp.description = 'Community Consultant Paediatrics'
OR sp.description = 'Paediatrics'
OR sp.description = 'Community Medicine'
OR sp.description = 'Audiological Medicine')
AND (rf.ARCHV_FLAG ='N')
GROUP BY
sp.Description , rf.Refrl_refNo, rf.Sorrf_RefNo
) As Listing
GROUP BY Specialty
END
ELSE IF (@pct is null)
BEGIN
SELECT Specialty, Sum(GMP) As GMP, Sum(GPWritten) As GPWritten, Sum(Others) As Others
FROM
(
SELECT
sp.Description As Specialty,
(
SELECT
count(rf1.Refrl_refNo ) As GMP
FROM
Refs rf1
INNER JOIN Specs sp1 ON rf1.RefTo_Spect_RefNo = sp1.Spect_RefNo
WHERE
rf1.Refrl_refNo =rf.Refrl_refNo
AND rf.Sorrf_RefNo = '5300' --GMP
GROUP BY
sp.Description
) As GMP,
(
SELECT
count(rf1.Refrl_refNo) As GMP
FROM
Refs rf1
INNER JOIN Specs sp1 ON rf1.RefTo_Spect_RefNo = sp1.Spect_RefNo
WHERE
rf1.Refrl_refNo =rf.Refrl_refNo
AND rf.Sorrf_RefNo = '2003641' --GPWritten
GROUP BY
sp.Description
) As GPWritten,
(
SELECT
count( rf1.Refrl_refNo)
FROM
Refs rf1
INNER JOIN Specs sp1 ON rf1.RefTo_Spect_RefNo = sp1.Spect_RefNo
WHERE
rf1.Refrl_refNo =rf.Refrl_refNo
AND rf.Sorrf_RefNo <> '2003641' --NOT GP Written
AND rf.Sorrf_RefNo <> '5300' --NOT GMP
GROUP BY
sp.Description
) As Others
FROM
Refs rf
INNER JOIN Specs sp ON rf.RefTo_Spect_RefNo = sp.Spect_RefNo
INNER JOIN GEOGRAPHICAREA GA ON dbo.udf_RetreaveGorgraphicAreaC
WHERE
rf.Recvd_Dttm BETWEEN CONVERT(DATETIME,@StartDate, 103) AND CONVERT(DATETIME,@EndDate + 1,103)
AND (GA.GeogAreaDesc LIKE 'Central' OR GA.GeogAreaDesc LIKE 'North' OR GA.GeogAreaDesc LIKE 'South')
AND (sp.description like @Speciality)
AND (rf.ARCHV_FLAG ='N')
GROUP BY
sp.Description, rf.Refrl_refNo, rf.Sorrf_RefNo
) As Listing
GROUP BY Specialty
END
ELSE IF (@Speciality IS NULL)
BEGIN
SELECT Specialty, Sum(GMP) As GMP, Sum(GPWritten) As GPWritten, Sum(Others) As Others
FROM
(
SELECT
sp.Description As Specialty,
(
SELECT
count(rf1.Refrl_refNo) As GMP
FROM
Refs rf1
INNER JOIN Specs sp1 ON rf1.RefTo_Spect_RefNo = sp1.Spect_RefNo
WHERE
rf1.Refrl_refNo =rf.Refrl_refNo
AND rf.Sorrf_RefNo = '5300' --GMP
GROUP BY
sp.Description
) As GMP,
(
SELECT
count(rf1.Refrl_refNo ) As GMP
FROM
Refs rf1
INNER JOIN Specs sp1 ON rf1.RefTo_Spect_RefNo = sp1.Spect_RefNo
WHERE
rf1.Refrl_refNo =rf.Refrl_refNo
AND rf.Sorrf_RefNo = '2003641' --GPWritten
GROUP BY
sp.Description
) As GPWritten,
(
SELECT
count(rf1.Refrl_refNo)
FROM
Refs rf1
INNER JOIN Specs sp1 ON rf1.RefTo_Spect_RefNo = sp1.Spect_RefNo
WHERE
rf1.Refrl_refNo =rf.Refrl_refNo
AND rf.Sorrf_RefNo <> '2003641' --NOT GP Written
AND rf.Sorrf_RefNo <> '5300' --NOT GMP
GROUP BY
sp.Description
) As Others
FROM
Refs rf
INNER JOIN Specs sp ON rf.RefTo_Spect_RefNo = sp.Spect_RefNo
INNER JOIN GEOGRAPHICAREA GA ON dbo.udf_RetreaveGorgraphicAreaC
WHERE
rf.Recvd_Dttm BETWEEN CONVERT(DATETIME,@StartDate, 103) AND CONVERT(DATETIME,@EndDate + 1,103)
AND (GA.GeogAreaDesc LIKE @pct) --OR @pct IS NULL
AND (sp.description = 'Community Consultant Paediatrics'
OR sp.description = 'Paediatrics'
OR sp.description = 'Community Medicine'
OR sp.description = 'Audiological Medicine')
AND (rf.ARCHV_FLAG ='N')
GROUP BY
sp.Description, rf.Refrl_refNo, rf.Sorrf_RefNo
) As Listing
GROUP BY Specialty
END
ELSE
BEGIN
SELECT Specialty, Sum(GMP) As GMP, Sum(GPWritten) As GPWritten, Sum(Others) As Others
FROM
(
SELECT
sp.Description As Specialty,
(
SELECT
count(rf1.Refrl_refNo) As GMP
FROM
Refs rf1
INNER JOIN Specs sp1 ON rf1.RefTo_Spect_RefNo = sp1.Spect_RefNo
WHERE
rf1.Refrl_refNo =rf.Refrl_refNo
AND rf.Sorrf_RefNo = '5300' --GMP
GROUP BY
sp.Description
) As GMP,
(
SELECT
count(rf1.Refrl_refNo ) As GMP
FROM
Refs rf1
INNER JOIN Specs sp1 ON rf1.RefTo_Spect_RefNo = sp1.Spect_RefNo
WHERE
rf1.Refrl_refNo =rf.Refrl_refNo
AND rf.Sorrf_RefNo = '2003641' --GPWritten
GROUP BY
sp.Description
) As GPWritten,
(
SELECT
count(rf1.Refrl_refNo)
FROM
Refs rf1
INNER JOIN Specs sp1 ON rf1.RefTo_Spect_RefNo = sp1.Spect_RefNo
WHERE
rf1.Refrl_refNo =rf.Refrl_refNo
AND rf.Sorrf_RefNo <> '2003641' --NOT GP Written
AND rf.Sorrf_RefNo <> '5300' --NOT GMP
GROUP BY
sp.Description
) As Others
FROM
Refs rf
INNER JOIN Specs sp ON rf.RefTo_Spect_RefNo = sp.Spect_RefNo
INNER JOIN GEOGRAPHICAREA GA ON dbo.udf_RetreaveGorgraphicAreaC
WHERE
rf.Recvd_Dttm BETWEEN CONVERT(DATETIME,@StartDate, 103) AND CONVERT(DATETIME,@EndDate + 1,103)
AND (GA.GeogAreaDesc LIKE @pct) --OR @pct IS NULL
AND (sp.description = @Speciality)
AND ( rf.ARCHV_FLAG ='N')
GROUP BY
sp.Description, rf.Refrl_refNo, rf.Sorrf_RefNo
) As Listing
GROUP BY
Specialty
ORDER BY
Specialty
END
and this is my udf
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER FUNCTION [dbo].[udf_RetreaveGorgraphicAreaCode] (@ReftoProcaRefno VARCHAR(12))
RETURNS CHAR(3)
AS
BEGIN
DECLARE @GeogAreaCode CHAR(3)
SET @GeogAreaCode = (SELECT PCGR.GeogAreaCode AS GACODE
FROM ProfCarerGeographicArea PCGR
INNER JOIN PROFCARERS PC ON PCGR.PROFCA_REFNO = PC.PROCA_REFNO
WHERE PC.PROCA_REFNO = @ReftoProcaRefno)
IF (@GeogAreaCode IS NULL)
BEGIN
SET @GeogAreaCode = ''
END
RETURN @GeogAreaCode
END
thanks
As Janos Berke check that URL.It seams to be known bug in SQL Server.
Fix: Install the Microsoft SQL Server 2000 Service Pack 4 on your server.