September 25, 2013 at 8:26 am
I am trying to create a stored procedure that has 5 steps in it. the procedure is failing at step 3 with the full error message "Could not locate entry in sysdatabases for database 'dbo'. No entry was found with that name. Make sure that the name is entered in correctly [SQL State 08004] [Error 911]. I checked the spelling and it is correct. What else could cause this error? Any help is appreciated.
September 25, 2013 at 8:54 am
Sounds like somewhere a table/procedure is being referenced incorrectly. dbo.something.somethingelse, rather than databasename.dbo.something. Post the code?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 25, 2013 at 8:59 am
This is the code in step 3
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sproc_Infmtx_003_MonthlyAR]
AS
--- Added 4 Drop table code to avoid duplication 9/11/2013 TD
IF OBJECT_ID('tmp_ARDetail') is not null
BEGIN
DROP TABLE tmp_ARDetail;
END
IF OBJECT_ID('tmp_AR') is not null
BEGIN
DROP TABLE tmp_AR;
END
IF OBJECT_ID('tmp_CalcAR') is not null
BEGIN
DROP TABLE tmp_CalcAR;
END
IF OBJECT_ID('tmp_ARCalc') is not null
BEGIN
DROP TABLE tmp_ARCalc;
END
/************************************************
*** UPDATE THESE VARIABLES BEFORE PROCESSING ***
***********************************************/
-- Set Variables
DECLARE @curpd int,@agingdt as datetime
SET @curpd = 380
SET @agingdt = '9/4/2013'
/******************
*** END UPDATE ***
*****************/
/* CALCULATE BEGINNING A/R */
-- Post Data
SELECT
'ARC_Infmtx' as UCI
,chgid as ChgID
,dat.doschg as DosDt
,dat.postdtchg as ChgPostDt
,@curpd as arpdid
,dat.cptcode as CptCode
,CAST('PROCEDURE' as varchar(255)) as CptDesc
,dat.cptcomp as CptComp
,isnull(bprv.provname_lf,'UNAPPLIED BILLING PROVIDER') as BillingProvider
,isnull(rprv.provname_lf,'UNAPPLIED RENDERING PROVIDER') as RenderingProvider
,isnull(fac.facdesc,'UNAPPLIED FACILITY') as Facility
,isnull(pos.posdesc,'UNAPPLIED PLACE OF SERVICE') as PlaceOfService
,isnull(dpt.dptdesc,'UNAPPLIED DEPARTMENT') as Department
,isnull(modal.modaldesc,'UNAPPLIED MODALITY') as Modality
,isnull(ref.refname_lf,'UNAPPLIED REF PROVIDER') as ReferringProvider
,(case when dat.curinsmne = 'Guarantor' then 'SELFPAY'
else isnull(cptyp.ptypemne,'UNAPPLIED PTYPE') end) as PType
,(case when dat.trantype = 1 then dat.amt else 0 end) as ChgAmt
,(case when (dat.trantype = 4 and trncd.pmtcat = 1) then dat.amt else 0 end) as PmtGuarAmt
,(case when (dat.trantype = 4 and trncd.pmtcat <> 1) then dat.amt else 0 end) as PmtInsAmt
,(case when (dat.trantype = 3 and dat.crcat = 1) then dat.amt else 0 end) as AdjContrAmt
,(case when (dat.trantype = 3 and dat.crcat <> 1) then dat.amt else 0 end) as AdjWOAmt
,(case when Len(LTrim(RTrim(ref.refname_assoc))) > 0
then ref.refname_assoc
else 'NO REFERRING GROUP' end) as RefGroup
INTO tmp_ARDetail
FROM infmtx_dat_Transactions dat
LEFT JOIN infmtx_dic_Provider bprv on dat.billprov = bprv.provid
LEFT JOIN infmtx_dic_Provider rprv on dat.rendprov = rprv.provid
LEFT JOIN infmtx_dic_Facility fac on dat.facid = fac.facid
LEFT JOIN infmtx_dic_POS pos on dat.posid = pos.posid
LEFT JOIN infmtx_dic_Department dpt on dat.dptid = dpt.dptid
LEFT JOIN infmtx_dic_Modality modal on dat.modalid = modal.modalid
LEFT JOIN infmtx_dic_RefProvider ref on dat.refprovid = ref.refid
LEFT JOIN infmtx_dic_TransCode trncd on dat.trantype = trncd.trantype
AND dat.transcode = trncd.trancodemne
LEFT JOIN infmtx_dic_Insurance cins on dat.curinsmne = cins.insmne
LEFT JOIN infmtx_dic_PType cptyp on cins.irptcat = cptyp.ptypemne
WHERE dat.rptpd < @curpd;
-- Set CPT Description
UPDATE tmp_ARDetail
SET CptDesc = cpt.cptdesc
FROM tmp_ARDetail ar
INNER JOIN infmtx_dic_CPT cpt ON ar.cptcode = cpt.cptcode AND ar.cptcomp = cpt.cptcomp;
-- Summarize on Charge Level
SELECT
tmp.UCI
,tmp.DosDt
,tmp.ChgPostDt
,tmp.arpdid
,tmp.CptCode
,tmp.CptDesc
,tmp.CptComp
,tmp.BillingProvider
,tmp.RenderingProvider
,tmp.Facility
,tmp.PlaceOfService
,tmp.Department
,tmp.Modality
,tmp.ReferringProvider
,tmp.PType
,Sum(tmp.ChgAmt) as Charges
,Sum(tmp.PmtGuarAmt) as GuarantorPmts
,Sum(tmp.PmtInsAmt) as InsurancePmts
,Sum(tmp.AdjContrAmt) as ContrAdjs
,Sum(tmp.AdjWOAmt) as WriteOffs
,CAST(0 as decimal(18,2)) as CurBalance
,@agingdt as AgeDt
,0 as DOSDys
,0 as AgeBucketInt
,'000_000_Days' as AgeBucketString
,'N' as Over90DaysFlag
,'N' as Over120DaysFlag
,tmp.RefGroup
INTO tmp_AR
FROM tmp_ARDetail tmp
GROUP BY tmp.UCI,tmp.DosDt,tmp.ChgPostDt,tmp.arpdid,tmp.CptCode,tmp.CptDesc,tmp.CptComp
,tmp.BillingProvider,tmp.RenderingProvider,tmp.Facility,tmp.PlaceOfService
,tmp.Department,tmp.Modality,tmp.ReferringProvider,tmp.PType,tmp.RefGroup;
-- Drop Temp Table
DROP TABLE tmp_ARDetail;
/*** Calculate Current Balance and remove Zero balances ***/
-- Current Balance
UPDATE tmp_AR SET CurBalance = (Charges - GuarantorPmts - InsurancePmts - ContrAdjs - WriteOffs);
-- Delete Zero Records
DELETE FROM tmp_AR WHERE CurBalance = 0;
--select Sum(CurBalance) from tmp_AR;
/*** Calculate Aging Data ***/
-- Calculate Days
UPDATE tmp_AR SET DOSDys = isnull(CAST((AgeDt - DosDt) as int),0);
-- Set Bucket
UPDATE tmp_AR SET AgeBucketInt = (case when DOSDys < 31 then 0
when (DOSDys > 30 and DOSDys < 61) then 1
when (DOSDys > 60 and DOSDys < 91) then 2
when (DOSDys > 90 and DOSDys < 121) then 3
when (DOSDys > 120 and DOSDys < 151) then 4
when (DOSDys > 150 and DOSDys < 181) then 5
else 6 end);
-- Set Bucket Description
UPDATE tmp_AR SET AgeBucketString = (case when AgeBucketInt = 0 then '000_030_Days'
when AgeBucketInt = 1 then '031_060_Days'
when AgeBucketInt = 2 then '061_090_Days'
when AgeBucketInt = 3 then '091_120_Days'
when AgeBucketInt = 4 then '121_150_Days'
when AgeBucketInt = 5 then '151_180_Days'
else '181_Over' end);
-- Set Over 90 and Over 120 Flags
UPDATE tmp_AR SET Over90DaysFlag = 'Y' WHERE AgeBucketInt > 2;
UPDATE tmp_AR SET Over120DaysFlag = 'Y' WHERE AgeBucketInt > 3;
-- Post to Calc Table as Begining Balance
SELECT
ar.UCI
,pd.monasdt as BillMonth
,pd.billpddiff as BillMonthDiff
,pd.fy as BillYear
,pd.fypddiff as BillYearDiff
,ar.CptCode
,ar.CptDesc
,ar.CptComp
,ar.BillingProvider
,ar.RenderingProvider
,ar.Facility
,ar.PlaceOfService
,ar.Department
,ar.Modality
,ar.ReferringProvider
,ar.PType
,(CAST(ar.CurBalance as decimal(38,2))) as BegAR
,(CAST(0 as decimal(38,2))) as Charges
,(CAST(0 as decimal(38,2))) as Payments_Guar
,(CAST(0 as decimal(38,2))) as Payments_Ins
,(CAST(0 as decimal(38,2))) as Adjustments_Contr
,(CAST(0 as decimal(38,2))) as Adjustments_WriteOffs
,(CAST(0 as decimal(38,2))) CurrentBalance
,ar.AgeBucketInt as Bucket_Number
,ar.AgeBucketString as Bucket_Desc
,ar.Over90DaysFlag
,ar.Over120DaysFlag
,ar.RefGroup
INTO tmp_CalcAR
FROM tmp_AR ar
LEFT JOIN Master_dic_Period pd ON ar.arpdid = pd.pd;
-- Drop Temp Table
DROP TABLE tmp_AR;
/*** POST CURRENT MONTH DATA ***/
-- Post Data
SELECT
'ARC_Infmtx' as UCI
,chgid as ChgID
,dat.doschg as DosDt
,dat.postdtchg as ChgPostDt
,@curpd as arpdid
,isnull(cpt.cptcode,'ZYZYZ') as CptCode
,isnull(cpt.cptdesc,'UNAPPLIED CPT') as CptDesc
,isnull(cpt.cptcomp,'G') as CptComp
,isnull(bprv.provname_lf,'UNAPPLIED BILLING PROVIDER') as BillingProvider
,isnull(rprv.provname_lf,'UNAPPLIED RENDERING PROVIDER') as RenderingProvider
,isnull(fac.facdesc,'UNAPPLIED FACILITY') as Facility
,isnull(pos.posdesc,'UNAPPLIED PLACE OF SERVICE') as PlaceOfService
,isnull(dpt.dptdesc,'UNAPPLIED DEPARTMENT') as Department
,isnull(modal.modaldesc,'UNAPPLIED MODALITY') as Modality
,isnull(ref.refname_lf,'UNAPPLIED REF PROVIDER') as ReferringProvider
,(case when dat.curinsmne = 'Guarantor' then 'SELFPAY'
else isnull(cptyp.ptypemne,'UNAPPLIED PTYPE') end) as PType
,(case when dat.trantype = 1 then dat.amt else 0 end) as ChgAmt
,(case when (dat.trantype = 4 and trncd.pmtcat = 1) then dat.amt else 0 end) as PmtGuarAmt
,(case when (dat.trantype = 4 and trncd.pmtcat <> 1) then dat.amt else 0 end) as PmtInsAmt
,(case when (dat.trantype = 3 and dat.crcat = 1) then dat.amt else 0 end) as AdjContrAmt
,(case when (dat.trantype = 3 and dat.crcat <> 1) then dat.amt else 0 end) as AdjWOAmt
,(case when Len(LTrim(RTrim(ref.refname_assoc))) > 0
then ref.refname_assoc
else 'NO REFERRING GROUP' end) as RefGroup
INTO tmp_ARDetail
FROM infmtx_dat_Transactions dat
LEFT JOIN infmtx_dic_CPT cpt on dat.cptid = cpt.cptid
LEFT JOIN infmtx_dic_Provider bprv on dat.billprov = bprv.provid
LEFT JOIN infmtx_dic_Provider rprv on dat.rendprov = rprv.provid
LEFT JOIN infmtx_dic_Facility fac on dat.facid = fac.facid
LEFT JOIN infmtx_dic_POS pos on dat.posid = pos.posid
LEFT JOIN infmtx_dic_Department dpt on dat.dptid = dpt.dptid
LEFT JOIN infmtx_dic_Modality modal on dat.modalid = modal.modalid
LEFT JOIN infmtx_dic_RefProvider ref on dat.refprovid = ref.refid
LEFT JOIN infmtx_dic_TransCode trncd on dat.trantype = trncd.trantype
AND dat.transcode = trncd.trancodemne
LEFT JOIN infmtx_dic_Insurance cins on dat.curinsmne = cins.insmne
LEFT JOIN infmtx_dic_PType cptyp on cins.irptcat = cptyp.ptypemne
WHERE dat.rptpd = @curpd;
-- Summarize on Charge Level
SELECT
tmp.UCI
,tmp.DosDt
,tmp.ChgPostDt
,tmp.arpdid
,tmp.CptCode
,tmp.CptDesc
,tmp.CptComp
,tmp.BillingProvider
,tmp.RenderingProvider
,tmp.Facility
,tmp.PlaceOfService
,tmp.Department
,tmp.Modality
,tmp.ReferringProvider
,tmp.PType
,Sum(tmp.ChgAmt) as Charges
,Sum(tmp.PmtGuarAmt) as GuarantorPmts
,Sum(tmp.PmtInsAmt) as InsurancePmts
,Sum(tmp.AdjContrAmt) as ContrAdjs
,Sum(tmp.AdjWOAmt) as WriteOffs
,CAST(0 as decimal(18,2)) as CurBalance
,@agingdt as AgeDt
,0 as DOSDys
,0 as AgeBucketInt
,'000_000_Days' as AgeBucketString
,'N' as Over90DaysFlag
,'N' as Over120DaysFlag
,tmp.RefGroup
INTO tmp_AR
FROM tmp_ARDetail tmp
GROUP BY tmp.UCI,tmp.DosDt,tmp.ChgPostDt,tmp.arpdid,tmp.CptCode,tmp.CptDesc,tmp.CptComp
,tmp.BillingProvider,tmp.RenderingProvider,tmp.Facility,tmp.PlaceOfService
,tmp.Department,tmp.Modality,tmp.ReferringProvider,tmp.PType,tmp.RefGroup;
-- Drop Temp Table
DROP TABLE tmp_ARDetail;
-- Calculate Aging Data
-- Calculate Days
UPDATE tmp_AR SET DOSDys = isnull(CAST((AgeDt - DosDt) as int),0);
-- Set Bucket
UPDATE tmp_AR SET AgeBucketInt = (case when DOSDys < 31 then 0
when (DOSDys > 30 and DOSDys < 61) then 1
when (DOSDys > 60 and DOSDys < 91) then 2
when (DOSDys > 90 and DOSDys < 121) then 3
when (DOSDys > 120 and DOSDys < 151) then 4
when (DOSDys > 150 and DOSDys < 181) then 5
else 6 end);
-- Set Bucket Description
UPDATE tmp_AR SET AgeBucketString = (case when AgeBucketInt = 0 then '000_030_Days'
when AgeBucketInt = 1 then '031_060_Days'
when AgeBucketInt = 2 then '061_090_Days'
when AgeBucketInt = 3 then '091_120_Days'
when AgeBucketInt = 4 then '121_150_Days'
when AgeBucketInt = 5 then '151_180_Days'
else '181_Over' end);
-- Set Over 90 and Over 120 Flags
UPDATE tmp_AR SET Over90DaysFlag = 'Y' WHERE AgeBucketInt > 2;
UPDATE tmp_AR SET Over120DaysFlag = 'Y' WHERE AgeBucketInt > 3;
-- Post to Calc Table
INSERT INTO tmp_CalcAR (UCI,BillMonth,BillMonthDiff,BillYear,BillYearDiff,CptCode,CptDesc,CptComp
,BillingProvider,RenderingProvider,Facility,PlaceOfService,Department
,Modality,ReferringProvider,PType,BegAR,Charges,Payments_Guar
,Payments_Ins,Adjustments_Contr,Adjustments_WriteOffs,CurrentBalance
,Bucket_Number,Bucket_Desc,Over90DaysFlag,Over120DaysFlag,RefGroup )
SELECT
ar.UCI
,pd.monasdt
,pd.billpddiff
,pd.fy
,pd.fypddiff
,ar.CptCode
,ar.CptDesc
,ar.CptComp
,ar.BillingProvider
,ar.RenderingProvider
,ar.Facility
,ar.PlaceOfService
,ar.Department
,ar.Modality
,ar.ReferringProvider
,ar.PType
,(CAST(0 as decimal(38,2))) as BegAR
,(CAST(ar.Charges as decimal(38,2)))
,(CAST(ar.GuarantorPmts as decimal(38,2)))
,(CAST(ar.InsurancePmts as decimal(38,2)))
,(CAST(ar.ContrAdjs as decimal(38,2)))
,(CAST(ar.WriteOffs as decimal(38,2)))
,(CAST(0 as decimal(38,2)))
,ar.AgeBucketInt
,ar.AgeBucketString
,ar.Over90DaysFlag
,ar.Over120DaysFlag
,ar.RefGroup
FROM tmp_AR ar
LEFT JOIN Master_dic_Period pd ON ar.arpdid = pd.pd;
-- Drop Temp Table
DROP TABLE tmp_AR;
-- Summarize AR Calc
SELECT
UCI
,BillMonth
,BillMonthDiff
,BillYear
,BillYearDiff
,CptCode
,CptDesc
,CptComp
,BillingProvider
,RenderingProvider
,Facility
,PlaceOfService
,Department
,Modality
,ReferringProvider
,PType
,Sum(BegAR) as B_AR
,Sum(Charges) as Chgs
,Sum(Payments_Guar) as GuarPmts
,Sum(Payments_Ins) as InsPmts
,Sum(Adjustments_Contr) as ContrAdjs
,Sum(Adjustments_WriteOffs) as WOAdjs
,Sum(CurrentBalance) as CurBal
,Bucket_Number
,Bucket_Desc
,Over90DaysFlag
,Over120DaysFlag
,RefGroup
INTO tmp_ARCalc
FROM tmp_CalcAR
GROUP BY UCI,BillMonth,BillMonthDiff,BillYear,BillYearDiff,CptCode,CptDesc,CptComp,BillingProvider
,RenderingProvider,Facility,PlaceOfService,Department,Modality,ReferringProvider
,PType,Bucket_Number,Bucket_Desc,Over90DaysFlag,Over120DaysFlag,RefGroup;
-- Drop Temp Table
DROP TABLE tmp_CalcAR;
-- Calculate Ending A/R
UPDATE tmp_ARCalc SET CurBal = (B_AR + Chgs - GuarPmts - InsPmts - ContrAdjs - WOAdjs);
-- Delete Zero Records
DELETE FROM tmp_ARCalc WHERE CurBal = 0;
-- Post to Live Table
INSERT INTO rpt_Infmtx_MonthlyAR (UCI,BillMonth,BillMonthDiff,BillYear,BillYearDiff,CPTCode,CPTDesc
,CPTComp,BillingProvider,RenderingProvider,Facility,PlaceOfService
,Department,Modality,ReferringProvider,PType,BeginningAR,Charges
,GuarantorPayments,InsurancePayments,ContractualAdjustments
,WriteOffAdjustments,EndingAR,AgeBucket_Number,AgeBucket_Desc
,Over90Flag,Over120Flag,RefGroup )
SELECT UCI,BillMonth,BillMonthDiff,BillYear,BillYearDiff,CptCode,CptDesc,CptComp,BillingProvider
,RenderingProvider,Facility,PlaceOfService,Department,Modality,ReferringProvider,PType
,B_AR,Chgs,GuarPmts,InsPmts,ContrAdjs,WOAdjs,CurBal,Bucket_Number,Bucket_Desc,Over90DaysFlag
,Over120DaysFlag,RefGroup
FROM tmp_ARCalc;
-- Drop Temp Table
DROP TABLE tmp_ARCalc;
/**** Clear out a month previously posted
DELETE FROM rpt_Infmtx_MonthlyAR WHERE BillMonth = '11/1/2012';
***/
September 25, 2013 at 9:05 am
Nothing in there that could throw that error. What's the entire of job step 3?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 25, 2013 at 10:48 am
I have attached the screen shots of the failure. If I run this procedure individually I don't get an error, so you comment makes sense. I am not sure how to answer your question.
September 25, 2013 at 10:53 am
I have added the screen shot of the step itself in case I made a mistake.
September 25, 2013 at 11:13 am
I'm not downloading word docs.
What is the code for 'step 3' (whatever step 3 is)? The piece that, when you run it, you get an error.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 25, 2013 at 2:34 pm
When I look at the job step properties under the general tab
StepName: MonthlyAR,
Type is Transact SQL,
Database is ARC_Rpts,
Command is exec dbo.sproc.Infmtx_003_MonthlyAR.
Job step properties under under the advanced tab
On success action go to next step
On failure action Quit the job reporting failure
I have already posted all the code for step 3: Monthly AR in today's post @ 10:59:12 AM
I get the error when I run the SQL Server agent job ARC_Processing.
When I review the Job Activity Monitor I see step 1 and step 2 succeeded. Step ID 3 Failed , Job Name ARC_Procesing, StepName MonthlyAR , Message "Could not locate entry in sysdatabases for database 'dbo'. No entry was found with that name. Make sure that the name is entered in correctly [SQL State 08004] [Error 911]
September 25, 2013 at 2:42 pm
There's your problem
exec dbo.sproc.Infmtx_003_MonthlyAR
That states that the database name is dbo, the schema is sproc and the procedure name is Infmtx_003_MonthlyAR. Since you're getting errors, that's obviously not the case, so specify the correct database and schema names for the procedure
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 25, 2013 at 2:51 pm
Thanks for your patience. I thought I had copied and pasted to avoid errors but I was wrong. The command needs to be exec dbo.sproc_Infmtx_003_MonthlyAR with an underscore after sproc not a . Thanks a million!!
December 11, 2017 at 4:11 am
vba317 - Wednesday, September 25, 2013 8:26 AMI am trying to create a stored procedure that has 5 steps in it. the procedure is failing at step 3 with the full error message "Could not locate entry in sysdatabases for database 'dbo'. No entry was found with that name. Make sure that the name is entered in correctly [SQL State 08004] [Error 911]. I checked the spelling and it is correct. What else could cause this error? Any help is appreciated.
Hi All
I faced the same issue.
We need to use Square brackets for database name
ie
use [database]
instead of
use database
Hope it helps.
December 16, 2017 at 7:50 am
vba317 - Wednesday, September 25, 2013 2:51 PMThanks for your patience. I thought I had copied and pasted to avoid errors but I was wrong. The command needs to be exec dbo.sproc_Infmtx_003_MonthlyAR with an underscore after sproc not a . Thanks a million!!
So the database name was the database to which the session was connected at that time and the procedure belongs to dbo. Two dots in the earlier command made SQL to take dbo as a database.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply