I have a stored procedure that ends with an insert into a table on a linked server. It has been working for a long time and i'm not sure what changed in our environment (doubtless something), but basically here is what happens:
When I troubleshoot it by executing the code NOT as a stored procedure, it works fine and the insert takes place.
When I execute the stored procedure, there are no errors returned, but the insert does not take place.
Does anyone have any ideas? PS - I can't figure out how to post "code" on this site, not sure why just the last block shows up as code - sorry.
Code:
USE [THP_Reporting]
GO
/****** Object: StoredProcedure [dbo].[CLAIMSAUDIT_ClaimCountsImport_EZCAP] Script Date: 02/29/2016 19:58:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <ISAAC PISORS>
-- Create date: <10/27/2015>
-- Description: <TO BRING IN NEW PHP CLAIM COUNTS FOR THE CLAIMS AUDIT DATABASE>
-- =============================================
ALTER PROCEDURE [dbo].[CLAIMSAUDIT_ClaimCountsImport_EZCAP]
(
@LineofBusiness_EzcapTerm varchar(9), --examples: PHPCAID PHPCARE
@LineofBusiness_LegacyReference varchar(9)--examples: PHP AAHP
)
AS
BEGIN
declare @startdate date
declare @enddate date
--testing only /////////////////////////////
--declare @LineofBusiness_EzcapTerm varchar(9) --examples: PHPCAID PHPCARE
--declare @LineofBusiness_LegacyReference varchar(9)--examples: PHP AAHP
--set @LineofBusiness_EzcapTerm ='PHPCAID'
--set @LineofBusiness_LegacyReference = 'PHP'
--end testing only
set @startdate = CAST(getdate()-90 as DATE)
set @enddate = CAST(getdate() as date)
--Build up our happy little conglomeration of audit records and claim header records, with
--total claims worked:
IF OBJECT_ID('tempdb..#temp') IS NOT NULL BEGIN DROP TABLE #temp END
;
with cte as (
select
COUNT(distinct c.claimno) as 'TotalClaimsWorked',
ltrim(rtrim(audit.OP_ID)) as 'OPID',
--(select USERs.fullname from [phaz-phpdw-04.phaz.vhs.net].[bianalytics].dbo.claimsaudit_users users where users.ezcapopid = Audit.op_id) as 'Operator',
CAST(audit.timestamp_changed as DATE) as 'ChangeDate'
from
DatawarehouseCMS.dbo.AUDIT_DETAILS_V audit
inner join DatawarehouseCMS.dbo.CLAIM_MASTERS_V c on audit.CHANGE_REC_KEY = c.CLAIMNO
where
c.COMPANY_ID=@LineofBusiness_EzcapTerm
and cast(audit.TIMESTAMP_CHANGED as date) >= cast(GETDATE()-59 as date) --change back to 10 after remediation 2/8/2016
and cast(audit.TIMESTAMP_CHANGED as date) < cast(GETDATE() as date) --added 2/8/2016, don't pick up today's records. and put a false permanent ZERO.
and ltrim(rtrim(audit.OP_ID)) in (select users.EZCAPOPID from [phaz-phpdw-04.phaz.vhs.net].[BIAnalytics].dbo.CLAIMSAUDIT_USERS users)
and audit.FIELD_NAME='STATUS'
--and audit.NEW_VALUE in ('1','9')
and audit.NEW_VALUE in ('1') --changed 2/22/2016 per email conversation w/Kim Lindsey
group by ltrim(rtrim(audit.OP_ID)), CAST(audit.timestamp_changed as date)
)
select * into #temp from cte
--testing:
--select * from #temp
insert [phaz-phpdw-04.phaz.vhs.net].[bianalytics].dbo.claimsaudit_claimcounts
(MC400ChangeUserID, NumberClaimsProcessed, ProcessDate,
DBUserName, DBusername_tenet, DateImported, ChangeUserAndDate, LOB
)
select
temptable.OPID as 'MC400ChangeUserID', --old nomenclature,but keeping it the same for right now
temptable.TotalClaimsWorked as 'NumberClaimsProcessed',
temptable.ChangeDate as 'ProcessDate',
(select USERs.username from [phaz-phpdw-04.phaz.vhs.net].[bianalytics].dbo.claimsaudit_users users where users.ezcapopid = temptable.opid) as 'DBUserName',
(select top 1 isnull(USERs1.username_tenet,'Unknown') from [phaz-phpdw-04.phaz.vhs.net].[bianalytics].dbo.claimsaudit_users users1 where users1.ezcapopid = temptable.opid) as 'DBUserName_tenet',
getdate() as 'DateImported',
--CAST(temptable.[TotalClaimsWorked] as varchar(5)) + ltrim(rtrim(cast(temptable.[opid] as varchar(10)))) + CONVERT(varchar(8),temptable.[ChangeDate],112) + @LineofBusiness_LegacyReference as 'ChangeUserAndDate',
--as of 2/8/2016 gettign rid of number grouper - that was killing us as of 1/2016
cast(ltrim(rtrim(temptable.[opid])) as varchar(10)) + CONVERT(varchar(8),temptable.[ChangeDate],112) + @LineofBusiness_LegacyReference as 'ChangeUserAndDate',
@LineofBusiness_LegacyReference as 'LOB'
from
#temp temptable
left join [phaz-phpdw-04.phaz.vhs.net].bianalytics.dbo.CLAIMSAUDIT_CLAIMCOUNTS mainDB
on
cast(ltrim(rtrim(temptable.opid)) as varchar(10)) + CONVERT(varchar(8),temptable.ChangeDate,112) + @LineofBusiness_LegacyReference =
mainDB.ChangeUserAndDate
where
mainDB.ChangeUserAndDate is null --we haven't already put this record in for a given Date, Analyst, LOB,and Count
and
cast(temptable.changedate as date) > cast('2015-08-10' as date)
END
)
It might be a big task to then work out why the caller (or caller's ... caller) falls over and which child-process silently deleted its