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.






Re: What is this error

Janos Berke


hi,

Maybe this should help: http://support.microsoft.com/kb/834688/

Regards,

Janos







Re: What is this error

Manivannan.D.Sekaran

Post your sp code (Sample)





Re: What is this error

Niranga

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_RetreaveGorgraphicAreaCode(RF.REFTO_PROCA_REFNO) = GA.GeogAreaCode
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_RetreaveGorgraphicAreaCode(RF.REFTO_PROCA_REFNO) = GA.GeogAreaCode
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_RetreaveGorgraphicAreaCode(RF.REFTO_PROCA_REFNO) = GA.GeogAreaCode
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_RetreaveGorgraphicAreaCode(RF.REFTO_PROCA_REFNO) = GA.GeogAreaCode
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









Re: What is this error

Manivannan.D.Sekaran

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.