Could not complete cursor operation because the table schema changed after the cursor was declared. [SQLSTATE 42000] (Error 16943). The step failed.
-
Hi All
I am a Junior and have a problem.W
e are running a Financial Proc during the night and I came across this Error when it failed. Scary part is that all record inserted into the database but the Step in the Job failedCan you help please
"Could not complete cursor operation because the table schema changed after the cursor was declared. [SQLSTATE 42000] (Error 16943). The step failed."
Here's the Proc
DECLARE @Commandline VARCHAR(500),
@IntRootDirectory VARCHAR(300),
@ERRORTEXT VARCHAR(500),
@error INT,
@FileName VARCHAR(50),
@rowcounter INT,
@V_LOANREFNO VARCHAR(50),
@V_STATUS VARCHAR(50),
@v_localfolder VARCHAR (350),
@v_debtfolder VARCHAR (350),
@v_filename VARCHAR(300),
@v_financialfolder VARCHAR (350),
@dbusername VARCHAR(50),
@v_query VARCHAR (350),
@databaselog VARCHAR (350),
@text VARCHAR (1000),
@ignoredfiles VARCHAR (1000)
--Get the location of the interfaceroot directory
EXEC @error=DBO.TM_GET_PROPERTIES @IntRootDirectory OUTPUT
IF @Error <> 0
BEGIN
SET @ERRORTEXT = 'Error in running the TM_GET_PROPERTIES procedure'
GOTO ERR_HANDLER
END
--Get the database username
EXEC @error=DBO.GETDBUSERNAME @dbusername OUTPUT
IF @Error <> 0
BEGIN
SET @ERRORTEXT = 'Error in running the GETDBUSERNAME procedure'
GOTO ERR_HANDLER
END
SET @databaselog=@IntRootDirectory+'\Database.log'
SET @text = convert(varchar, getdate(),100)+' Beginning AB_AUTOMATE_BATCH to process financial and overduebalance interfaces..'
EXEC DBO.logtext @text, @databaselog
SET @v_debtfolder = @IntRootDirectory+'/debt'
SET @v_financialfolder = @IntRootDirectory+'/financial'
--Import the local financial file names
EXEC @error = DBO.GET_DIR_FILE_LIST @v_financialfolder,0
IF @error <> 0
BEGIN
SET @errortext = 'Error in calling procedure GET_DIR_FILE_LIST for Financial folder'
GOTO ERR_HANDLER
END
--Runthrough each file that matches a financial stub format and execute them
DECLARE CURFINANCIALS CURSOR FOR
SELECT 'EXEC AB_FINANCIAL '''+ PATH+ ''''
FROM FilesInDirectory A
JOIN INTERFACEFILENAMES B ON A.PATH LIKE FILESTUB
AND B.INTERFACENAME = 'FINANCIAL'
OPEN CURFINANCIALS
FETCH NEXT FROM CURFINANCIALS INTO @v_query
WHILE (@@FETCH_STATUS <> -1)
BEGIN
exec (@v_query)
FETCH NEXT FROM CURFINANCIALS INTO @v_query
END
CLOSE CURFINANCIALS
DEALLOCATE CURFINANCIALS
SET @text = 'Financial Interfaces uploaded'
EXEC DBO.logtext @text, @databaselog
--Import the overdue balance interface file names and execute
EXEC @error = DBO.GET_DIR_FILE_LIST @v_debtfolder,0
IF @error <> 0
BEGIN
SET @errortext = 'Error in calling procedure GET_DIR_FILE_LIST for Debt folder'
GOTO ERR_HANDLER
END
--Runthrough each file that matches a overdue balance stub format and execute them
DECLARE CURODBALS CURSOR FOR
SELECT 'EXEC AB_OVERDUE_BALANCES '''+ PATH+ ''''
FROM FilesInDirectory A
JOIN INTERFACEFILENAMES B ON A.PATH LIKE FILESTUB
AND B.INTERFACENAME = 'OVERDUEBALANCE'
OPEN CURODBALS
FETCH NEXT FROM CURODBALS INTO @v_query
WHILE (@@FETCH_STATUS <> -1)
BEGIN
exec (@v_query)
FETCH NEXT FROM CURODBALS INTO @v_query
END
CLOSE CURODBALS
DEALLOCATE CURODBALS
SET @text = 'Overdue Balances Interfaces uploaded'
EXEC DBO.logtext @text, @databaselog
--Import the repopulation interface file names and execute
EXEC @error = DBO.GET_DIR_FILE_LIST @v_financialfolder,0
IF @error <> 0
BEGIN
SET @errortext = 'Error in calling procedure GET_DIR_FILE_LIST for Financial folder'
GOTO ERR_HANDLER
END
--Runthrough each file that matches a repopulation stub format and execute them
DECLARE CURREPOP CURSOR FOR
SELECT 'EXEC AB_RECONCILIATION '''+ PATH+ ''''
FROM FilesInDirectory A
JOIN INTERFACEFILENAMES B ON A.PATH LIKE FILESTUB
AND B.INTERFACENAME = 'REPOPULATION'
OPEN CURREPOP
FETCH NEXT FROM CURREPOP INTO @v_query
WHILE (@@FETCH_STATUS <> -1)
BEGIN
exec (@v_query)
FETCH NEXT FROM CURREPOP INTO @v_query
END
CLOSE CURREPOP
DEALLOCATE CURREPOP
SET @text = 'Repopulation Interfaces uploaded'
EXEC DBO.logtext @text, @databaselog
SET @text = convert(varchar, getdate(),100)+' AB_AUTOMATE_BATCH generation complete'
EXEC DBO.logtext @text, @databaselog
RETURN(0)
ERR_HANDLER:
IF (@ERRORTEXT != '')
BEGIN
SET @text = convert(varchar, getdate(),100)+' Error in running overduebalances, financial and repopulation interfaces: '+@ERRORTEXT
EXEC DBO.logtext @text, @databaselog
RETURN (1)
END -- Error Handler
GO
-
Maybe I am stating the obvious here, but what does the @v_query do? Is it modifying the table structure of the tables that are part of the cursor?
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor -
Hi
When I started here this Proc already existed. So I do not know what does the @v_query do
Sorry
-
I'm right with Kathi. If any of the stored procs being called in the cursors (AB_FINANCIAL, AB_OVERDUE_BALANCES, or AB_RECONCILIATION) modify the table structure of either FilesInDirectory or INTERFACEFILENAMES, you'll get this error. Another possibility is that someone was modifying the table structure of these tables right when the job was running.
-
Hi
Nothing was modified yesterday and the only thing that was added was transaction types ( has no impact).
And we only run our Day-End (Financial Proc) at night when all users are of the Front-End
And when we run this process we make sure that no Job run because a locks and blocks that it creates.
-
Is autoshrink turned on? Is anything reindexing the tables in question?
It might also help if we knew if this error happens every time it runs, occasionally, or just once.
-
I am receing an Error.
60118 row(s) affected)
(17904 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
,,,
0,0,0,0
(1 row(s) affected)
Server: Msg 203, Level 16, State 2, Line 239
The name '
--========================================================
Update ABTRACERSNEWMATTERS
Set jurisdiction = '01'
From
ABTRACERSNEWMATTERS a
Inner join
(Select Top 0 * From ABTRACERSNEWMATTERS where jurisdiction = 'N/A') b
ON a.IDNUMBER = b.IDNUMBER
AND a.LOANREFNO = b.LOANREFNO
--========================================================
--================================...
Please have a look at the code below
--Create Daily files of new matters and changed records:
--Process: After morning interfaces have been processed, run the following:
--Step 1: Run the following SQL:
begin
TRUNCATE TABLE ABTRACERSDATA_TEMP
insert into ABTRACERSDATA_TEMP
select DISTINCT
c.idnumber,
b.loanrefno,
c.firstname1,
c.surname,
c.title,
c.employercd2,
c.employercd2 as CompanyDescription,
a.balance,
convert(varchar, LoanStartDate,111) as LoanStartDate,
CONVERT(VARCHAR,(GETDATE() + (b.remainingterm * 31)),111) AS END_DATE,
(case when b.instalmentagreed is not null then b.instalmentagreed else b.instalment end),
(select case when dbo.NUMBERTOWORDS (b.instalmentagreed) is null then
(select case when dbo.NUMBERTOWORDS (b.instalment) is null then 'Two Hundred And Fifty Rands Zero Zero Cents'
ELSE dbo.NUMBERTOWORDS (b.instalment) end)
ELSE dbo.NUMBERTOWORDS (b.instalmentagreed) end) as AMOUNTINWORDS,
c.employercd1,
c.employeeclockno,
e.DLDWFIRSTREGION as region,
e.DLDWFIRSTSUBREGION as subregion,
'N/A' as jurisdiction,
c.email,
convert(varchar, getdate(), 111) as TodayDate,
(case when b.previousreasoncode = '182' then 'Confirmed not Deceased by Home Affairs'
when b.previousreasoncode = '184' then 'Admin Order Status Unknown'
when b.previousreasoncode = '531' then 'Insurance Claim - Insufficient Info'
when b.previousreasoncode = '534' then 'Insurance Claim - Insufficient Info'
when b.previousreasoncode = '841' then 'Admin Order - Insufficient Info'
else '' END)
AS comments,
(case when b.previousreasoncode = '182' then 'Obtain Signed Section 58'
when b.previousreasoncode = '184' then 'Obtain Admin Order application, Court Order, List of Creditors or Signed Section 58 with payment arrangement'
when b.previousreasoncode = '531' then 'Obtain Retrenchment letter, sworn affidavit, copy of ID and 6 months bank statement, if no bank statement it should be stipulated in affidavit.'
when b.previousreasoncode = '534' then 'Obtain Retrenchment letter, sworn affidavit, copy of ID and 6 months bank statement, if no bank statement it should be stipulated in affidavit.'
when b.previousreasoncode = '841' then 'Obtain Signed Section 58'
else '' END)
AS specialinstructions,
'' as alchemyindicator,
'' as insurancedeliverable,
(case when loanstartdate < '2004-06-24' then 15 else APR end) AS ContractInterestRate,
b.lastpaymentdate,
b.lastpaymentamount,
c.nokfullname,
b.loancode1,
getdate () as date_updated_to_law
from accounts a (NOLOCK), accountdetails b (NOLOCK), customerdetails c (NOLOCK),datawash2 e (NOLOCK)--, employers d
where
a.id = b.accounts1
and
a.customerid = c.customers1
and
a.id = e.accounts1
--c.employercd2 = d.e01subgroup
--and
and
b.reasoncode1 IN ('800','811','890')
and
a.status <> '1'
end
-- 1.102494
-- 2.102494
begin
DELETE FROM ABTRACERSDATA_TEMP
WHERE LOANREFNO NOT IN (SELECT LOANREFNO FROM ABTRACERSDATA)
AND LOANREFNO NOT IN (SELECT LOANREFNO FROM ABTRACERSADDRESSES)
AND LOANREFNO NOT IN (SELECT LOANREFNO FROM ABTRACERSTELEPHONES)
end
--step 2: Identify New Matters
begin
TRUNCATE TABLE ABTRACERSNEWMATTERS
INSERT INTO ABTRACERSNEWMATTERS
SELECT DISTINCT
IDNUMBER,
LOANREFNO,
FIRSTNAME1,
SURNAME,
TITLE,
EMPLOYERCD2,
companydescription,
BALANCE,
LoanStartDate,
END_DATE,
INSTALMENTAGREED,
amountinwords,
EMPLOYERCD1,
EMPLOYEECLOCKNO,
region,
subregion,
jurisdiction,
EMAIL,
TodayDate,
comments,
specialinstructions,
alchemyindicator,
insurancedeliverable,
contractinterestrate,
loancode1
FROM ABTRACERSDATA_TEMP
WHERE LOANREFNO NOT IN (SELECT LOANREFNO FROM ABTRACERSDATA)
and LOANREFNO IN (SELECT LOANREFNO FROM ABTRACERSADDRESSES)
OR
LOANREFNO NOT IN (SELECT LOANREFNO FROM ABTRACERSDATA)
and LOANREFNO IN (SELECT LOANREFNO FROM ABTRACERSTELEPHONES)
end
--(497 row(s) affected)
-- (497 row(s) affected)
--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
begin
update ABTRACERSNEWMATTERS
set jurisdiction = '03'
where region = 'Eastern Cape'
update ABTRACERSNEWMATTERS
set jurisdiction = '06'
where region in ('Central','Free State')
update ABTRACERSNEWMATTERS
set jurisdiction = '07'
where region = 'Western Cape'
update ABTRACERSNEWMATTERS
set jurisdiction = '08'
from ABTRACERSNEWMATTERS a left join accountdetails b
on a.loanrefno = b.loanrefno
left join datawash2 c
on c.accounts1 = b.accounts1
where region = 'KwaZulu Natal'
and c.persalindicator = 'Y'
end
--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
--Update Juridistcion in NewMatters
begin
Declare @total numeric
, @60 varchar(10)
, @20 varchar(10)
, @10 varchar(10)
Set @total = (Select COUNT(*) From ABTRACERSNEWMATTERS where jurisdiction = 'N/A' )
Set @60 = Convert(Numeric, (@total * 0.6))
Set @20 = Convert(Numeric, (@Total * 0.2))
Set @10 = Convert(Numeric, (@Total * 0.1))
Select @total, @60, @20,@10
Declare @sql varchar(3000)
Set @sql =
'
--========================================================
Update ABTRACERSNEWMATTERS
Set jurisdiction = ''01''
From
ABTRACERSNEWMATTERS a
Inner join
(Select Top ' +@60 + ' * From ABTRACERSNEWMATTERS where jurisdiction = ''N/A'') b
ON a.IDNUMBER = b.IDNUMBER
AND a.LOANREFNO = b.LOANREFNO
--========================================================
--========================================================
Update ABTRACERSNEWMATTERS
Set jurisdiction = ''02''
From
ABTRACERSNEWMATTERS a
Inner join
(Select Top ' + @20 + ' * From ABTRACERSNEWMATTERS where jurisdiction = ''N/A'') b
ON a.IDNUMBER = b.IDNUMBER
AND a.LOANREFNO = b.LOANREFNO
--========================================================
--========================================================
Update ABTRACERSNEWMATTERS
Set jurisdiction = ''04''
From
ABTRACERSNEWMATTERS a
Inner join
(Select Top ' + @10 + ' * From ABTRACERSNEWMATTERS where jurisdiction = ''N/A'') b
ON a.IDNUMBER = b.IDNUMBER
AND a.LOANREFNO = b.LOANREFNO
--========================================================
--========================================================
Update ABTRACERSNEWMATTERS
Set jurisdiction = ''05''
where jurisdiction = ''N/A''
--========================================================
'
-- --========================================================
-- Update ABTRACERSNEWMATTERS
-- Set jurisdiction = ''06''
-- From
-- ABTRACERSNEWMATTERS a
-- Inner join
-- (Select Top ' + @125 + ' * From ABTRACERSNEWMATTERS where jurisdiction = ''N/A'') b
-- ON a.IDNUMBER = b.IDNUMBER
-- AND a.LOANREFNO = b.LOANREFNO
-- --========================================================
--
-- --========================================================
-- Update ABTRACERSNEWMATTERS
-- Set jurisdiction = ''07''
-- From
-- ABTRACERSNEWMATTERS a
-- Inner join
-- (Select Top ' + @125 + ' * From ABTRACERSNEWMATTERS where jurisdiction = ''N/A'') b
-- ON a.IDNUMBER = b.IDNUMBER
-- AND a.LOANREFNO = b.LOANREFNO
-- --========================================================
--
-- --========================================================
-- Update ABTRACERSNEWMATTERS
-- Set jurisdiction = ''08''
-- From
-- ABTRACERSNEWMATTERS a
-- Inner join
-- (Select Top ' + @125 + ' * From ABTRACERSNEWMATTERS where jurisdiction = ''N/A'') b
-- ON a.IDNUMBER = b.IDNUMBER
-- AND a.LOANREFNO = b.LOANREFNO
-- --========================================================
Exec (@SQL)
-----------------------------------------------------------------------------------------------
--ADDED THE FOLLOWING PIECE OF CODE TO THE PROCESS FOR CHANGE REQUEST CO17299
--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
--Update Juridistcion in NewMatters
begin
Declare @total1 numeric
, @50_1 varchar(10)
, @50_2 varchar(10)
-- , @10 varchar(10)
Set @total1 = (Select COUNT(*) From ABTRACERSNEWMATTERS where jurisdiction = '07' )
Set @50_1 = Convert(Numeric, (@total1 * 0.5))
Set @50_2 = Convert(Numeric, (@Total1 * 0.5))
--Set @10 = Convert(Numeric, (@Total * 0.1))
Select @total1, @50_1, @50_2 --,@10
Declare @SQL1 varchar(3000)
Set @SQL1 =
'
--========================================================
Update ABTRACERSNEWMATTERS
Set jurisdiction = ''09''
From
ABTRACERSNEWMATTERS a
Inner join
(Select Top ' +@50_1 + ' * From ABTRACERSNEWMATTERS where jurisdiction = ''07'') b
ON a.IDNUMBER = b.IDNUMBER
AND a.LOANREFNO = b.LOANREFNO
--========================================================
--========================================================
Update ABTRACERSNEWMATTERS
Set jurisdiction = ''10''
From
ABTRACERSNEWMATTERS a
Inner join
(Select Top ' + @50_2 + ' * From ABTRACERSNEWMATTERS where jurisdiction = ''07'') b
ON a.IDNUMBER = b.IDNUMBER
AND a.LOANREFNO = b.LOANREFNO
--========================================================
'
EXEC @SQL1
end
-----------------------------------------------------------------------------------------------
--SELECT TOP 1 * FROM ABTRACERSNEWMATTERS
begin transaction
update ABTRACERSNEWMATTERS set jurisdiction = '01'
where loanrefno in (select a.loanrefno from accountdetails a,
creditcard b where a.accounts1 = b.accounts1)
COMMIT
BEGIN TRANSACTION
UPDATE ABTRACERSNEWMATTERS
SET CONTRACTINTERESTRATE = 15.5 WHERE JURISDICTION = '01' AND LOANSTARTDATE < '2004-07-01'
COMMIT
BEGIN TRANSACTION
UPDATE ABTRACERSNEWMATTERS
SET CONTRACTINTERESTRATE = 17 WHERE JURISDICTION <> '01' AND LOANSTARTDATE < '2004-07-01'
COMMIT
BEGIN TRANSACTION
UPDATE ABTRACERSNEWMATTERS
SET CONTRACTINTERESTRATE = 15.5 WHERE JURISDICTION = '01' AND CONTRACTINTERESTRATE = 0
COMMIT
BEGIN TRANSACTION
UPDATE ABTRACERSNEWMATTERS
SET CONTRACTINTERESTRATE = 17 WHERE JURISDICTION <> '01' AND CONTRACTINTERESTRATE = 0
COMMIT
BEGIN TRANSACTION
UPDATE ABTRACERSNEWMATTERS
SET CONTRACTINTERESTRATE = 17 WHERE JURISDICTION = '03' AND LOANSTARTDATE < '2004-07-01'
COMMIT
BEGIN TRANSACTION
UPDATE ABTRACERSNEWMATTERS
SET CONTRACTINTERESTRATE = 17 WHERE JURISDICTION = '06' AND LOANSTARTDATE < '2004-07-01'
COMMIT
BEGIN TRANSACTION
UPDATE ABTRACERSNEWMATTERS
SET CONTRACTINTERESTRATE = 17 WHERE JURISDICTION = '07' AND LOANSTARTDATE < '2004-07-01'
COMMIT
BEGIN TRANSACTION
UPDATE ABTRACERSNEWMATTERS
SET CONTRACTINTERESTRATE = 17 WHERE JURISDICTION = '08' AND LOANSTARTDATE < '2004-07-01'
COMMIT
end
--step 3: Identify ABTRACERS ADDRESSES changes
--IMPORT G:\interface\ABTRACERS\ADD.TXT INTO DBO.PHASEII.ABTRACERSADDRESSES_HOLD
--INSERT INTO ABTRACERSADDRESSES
--SELECT
--IDNUMBER,
--LOANREFNO,
--ADDRTYPE,
--LINE1,
--LINE2,
--LINE3,
--LINE4,
--LINE5,
--LINE6,
--DATEFROM,
--DATETO,
--GETDATE () AS date_inserted
--FROM
--ABTRACERSADDRESSES_HOLD
--step 4: Identify ABTRACERS TELEPHONES changes
--INSERT INTO ABTRACERSTELEPHONES
--SELECT
--IDNUMBER,
--LOANREFNO,
--PHTYPE,
--CODE,
--TELNO,
--DATEFROM,
--DATETO,
--GETDATE () AS date_inserted
--FROM
--ABTRACERSTELEPHONES_HOLD
--step 5: Identify ABTRACERS LOAN INFO changes, AND ADD THE LOAN INFO details for New Matters:
begin
truncate table ABTRACERSLOANINFO
Insert into ABTRACERSLOANINFO
Select
a.IDNUMBER,
a.LOANREFNO,
a.LOANSTARTDATE,
a.BALANCE,
a.INSTALMENTAGREED,
a.LASTPAYMENTDATE,
a.LASTPAYMENTAMOUNT
From ABTRACERSDATA_TEMP a (NOLOCK)
WHERE LOANREFNO NOT IN (SELECT LOANREFNO FROM ABTRACERSDATA)
Insert into ABTRACERSLOANINFO
Select
b.IDNUMBER,
b.LOANREFNO,
b.LOANSTARTDATE,
b.BALANCE,
b.INSTALMENTAGREED,
b.LASTPAYMENTDATE,
b.LASTPAYMENTAMOUNT
From ABTRACERSDATA a (NOLOCK), ABTRACERSDATA_TEMP b (NOLOCK)
WHERE a.LOANREFNO = B.LOANREFNO
AND a.LOANSTARTDATE <> B.LOANSTARTDATE
OR
a.LOANREFNO = B.LOANREFNO
AND a.BALANCE <> B.BALANCE
OR
a.LOANREFNO = B.LOANREFNO
AND a.INSTALMENTAGREED <> B.INSTALMENTAGREED
OR
a.LOANREFNO = B.LOANREFNO
AND a.LASTPAYMENTDATE <> B.LASTPAYMENTDATE
OR
a.LOANREFNO = B.LOANREFNO
AND a.LASTPAYMENTAMOUNT <> B.LASTPAYMENTAMOUNT
end
--XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
--XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
--step 6: Identify ABTRACERS CIF UPDATES changes:
begin
truncate table ABTRACERSCIFUPDATES
insert into ABTRACERSCIFUPDATES
select
a.IDNUMBER,
a.LOANREFNO,
a.FIRSTNAME1,
a.SURNAME,
a.TITLE,
a.NOKFULLNAME,
a.EMPLOYEECLOCKNO,
a.EMPLOYERCD1,
a.EMAIL
From ABTRACERSDATA a (NOLOCK)
WHERE a.LOANREFNO NOT IN (SELECT b.LOANREFNO FROM ABTRACERSDATA_TEMP b)
insert into ABTRACERSCIFUPDATES
select
a.IDNUMBER,
a.LOANREFNO,
a.FIRSTNAME1,
a.SURNAME,
a.TITLE,
a.NOKFULLNAME,
a.EMPLOYEECLOCKNO,
a.EMPLOYERCD1,
a.EMAIL
From ABTRACERSDATA a (NOLOCK), ABTRACERSDATA_TEMP b
WHERE a.LOANREFNO = b.LOANREFNO
AND a.FIRSTNAME1 <> b.FIRSTNAME1
OR
a.LOANREFNO = b.LOANREFNO
AND a.SURNAME <> b.SURNAME
OR
a.LOANREFNO = b.LOANREFNO
AND a.TITLE <> b.TITLE
OR
a.LOANREFNO = b.LOANREFNO
AND a.NOKFULLNAME <> b.NOKFULLNAME
OR
a.LOANREFNO = b.LOANREFNO
AND a.EMPLOYEECLOCKNO <> b.EMPLOYEECLOCKNO
OR
a.LOANREFNO = b.LOANREFNO
AND a.EMPLOYERCD1 <> b.EMPLOYERCD1
OR
a.LOANREFNO = b.LOANREFNO
AND a.EMAIL <> b.EMAIL
end
--XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
--step 7: Identify ABTRACERS BALANCE UPDATES changes:
begin
truncate table ABTRACERSBALANCEUPDATES
insert into ABTRACERSBALANCEUPDATES
SELECT
a.IDNUMBER,
a.LOANREFNO,
a.BALANCE,
a.INSTALMENTAGREED
FROM ABTRACERSDATA_TEMP a (NOLOCK)
WHERE a.LOANREFNO NOT IN (SELECT b.LOANREFNO FROM ABTRACERSDATA b)
insert into ABTRACERSBALANCEUPDATES
SELECT
a.IDNUMBER,
a.LOANREFNO,
a.BALANCE,
a.INSTALMENTAGREED
FROM ABTRACERSDATA_TEMP a (NOLOCK), ABTRACERSDATA b (NOLOCK)
WHERE a.LOANREFNO = b.LOANREFNO
AND
a.BALANCE <> b.BALANCE
OR
a.LOANREFNO = b.LOANREFNO
AND
a.INSTALMENTAGREED <> b.INSTALMENTAGREED
end
--XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
--STEP 8: Identify matters to be closed (Status Update) - Case when reason code <> 800 any more:
--id no, loanref, status, comment
begin
TRUNCATE TABLE ABTRACERSDELETIONS
INSERT INTO ABTRACERSDELETIONS
select a.idnumber, a.loanrefno, '0' as Comment
from ABTRACERSDATA_TEMP A, ACCOUNTDETAILS B
WHERE A.LOANREFNO = B.LOANREFNO
AND A.LOANREFNO NOT IN (SELECT B.LOANREFNO FROM ABTRACERSDATA B)
AND B.REASONCODE1 <> '890'
INSERT INTO ABTRACERSDELETIONS
select a.idnumber, a.loanrefno, '1' as Comment
from ABTRACERSDATA_TEMP A, ACCOUNTDETAILS B
WHERE A.LOANREFNO = B.LOANREFNO
AND A.LOANREFNO NOT IN (SELECT B.LOANREFNO FROM ABTRACERSDATA B)
AND B.REASONCODE1 = '890'
end
--XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
begin
INSERT INTO ABTRACERSDELETIONS
select a.idnumber, a.loanrefno,
(case
when B.PREVIOUSREASONCODE IN ('800','820') AND B.REASONCODE1 = '890' then '1'
when B.PREVIOUSREASONCODE = '890' AND B.REASONCODE1 = '811' then '2'
when B.PREVIOUSREASONCODE = '890' AND B.REASONCODE1 in ('802','803','804','805','806','807','808','809','812','830','870','600','601') then '3'
when B.PREVIOUSREASONCODE = '800' AND B.REASONCODE1 in ('802','803','804','805','806','807','808','809','812','830','870') then '9'
when B.PREVIOUSREASONCODE in ('800','811') AND B.REASONCODE1 in ('155','156','157') then '4'
when B.PREVIOUSREASONCODE in ('800','811','890') AND B.REASONCODE1 > '499' and B.REASONCODE1 < '600' then '4'
when B.PREVIOUSREASONCODE in ('800','811','890') AND B.REASONCODE1 > '399' and B.REASONCODE1 < '500' then '5'
when B.PREVIOUSREASONCODE in ('800','811','890') AND B.REASONCODE1 in ('163','164','815','899') then '6'
when B.PREVIOUSREASONCODE in ('800','811','890') AND B.REASONCODE1 = '159' then '7'
when B.PREVIOUSREASONCODE in ('800','811','890') AND B.REASONCODE1 = '158' then '8'
when B.PREVIOUSREASONCODE in ('800','811','890') AND B.REASONCODE1 in ('816','818') then '9'
when B.PREVIOUSREASONCODE in ('800','811') AND B.REASONCODE1 > '299' and B.REASONCODE1 < '400' then '10'
when B.PREVIOUSREASONCODE in ('800','811') AND B.REASONCODE1 > '599' and B.REASONCODE1 < '700' then '10'
when B.PREVIOUSREASONCODE in ('800','811') AND B.REASONCODE1 > '699' and B.REASONCODE1 < '800' then '10'
when B.PREVIOUSREASONCODE = '890' AND B.REASONCODE1 in ('155','156','157','158','159','163','164','169','814','816','817','818','897','899') then '11'
when B.PREVIOUSREASONCODE = '890' AND B.REASONCODE1 > '399' AND B.REASONCODE1 < '600' then '11'
when B.PREVIOUSREASONCODE = '811' AND B.REASONCODE1 = '820' then '12'
else '13' end)
as Comment
from
ABTRACERSDATA a (NOLOCK),
ACCOUNTDETAILS B (NOLOCK),
ACCOUNTS C (NOLOCK),
ACCOUNTCLASSIFICATIONS D (NOLOCK)
where
a.date_updated_to_law < b.reasoncodedate --B.REASONCODE > GETDATE () -1
AND
A.LOANREFNO = B.LOANREFNO
AND
B.ACCOUNTS1 = C.ID
AND
C.ID = D.ACCOUNTID
end
begin
-- Delete All loans in the Status file with an PREVIOUSREASONCODE 800 and reasoncode1 800
DELETE FROM ABTRACERSDELETIONS
where ABTRACERSDELETIONS.loanrefno in (select b.loanrefno from ABTRACERSDELETIONS a left join accountdetails b
on a.loanrefno = b.loanrefno
where a.comment = '13'
and b.PREVIOUSREASONCODE = '800'
and b.reasoncode1 = '800'
OR
(b.PREVIOUSREASONCODE = '890'
AND b.reasoncode1 = '890')
OR
(b.PREVIOUSREASONCODE = '820'
AND b.reasoncode1 = '820')
OR
(b.PREVIOUSREASONCODE = '811'
AND b.reasoncode1 = '811')
and ABTRACERSDELETIONS.loanrefno not in(select z.loanrefno from abtracersnewmatters z))
end
--END
--After the Agent job has successfully FTP'd the files, run the following:
--Truncate table ABTRACERSDATA -- This is the old data
--Insert into ABTRACERSDATA (select * from ABTRACERSDATA_TEMP) -- This is the new fixed population.
--select * from ABTRACERSNEWMATTERS WHERE IDNUMBER IS NULL --YES
--select * from ABTRACERSLOANINFO --YES
--select * from ABTRACERSCIFUPDATES --YES
--select * from ABTRACERSBALANCEUPDATES --YES
--select * from ABTRACERSDELETIONS -- YES
--SELECT IDNUMBER, LOANREFNO, THIRDPARTIES1, PRINTINDICATOR FROM ABTRACERSINITIALHANDOVERSTATUS --YES
--select * from ABTRACERSADDRESSES --YES -- 2,206,018
--SELECT * FROM ABTRACERSTELEPHONES --YES -- 1,565,667
--CREATE INITIALHANDOVERSTATUS TABLE:
--SELECT TOP 1 * FROM ABTRACERSDATA --LOANREFNO
--SELECT TOP 1 * FROM ACCOUNTDETAILS --ACCOUNTS1
--SELECT TOP 1 * FROM THIRDPARTY --ACCOUNTS1
--TRUNCATE TABLE ABTRACERSINITIALHANDOVERSTATUS
--INSERT INTO ABTRACERSINITIALHANDOVERSTATUS
--SELECT A.IDNUMBER, A.LOANREFNO, (CASE WHEN C.THIRDPARTIES1 IS NOT NULL THEN C.THIRDPARTIES1 ELSE 'DLBL' END) AS THIRDPARTIES1, (CASE WHEN B.REASONCODEDATE < '2006/03/23' THEN 'Y' ELSE 'Y' END) AS PRINTINDICATOR
--FROM THIRDPARTY C INNER JOIN
-- ACCOUNTDETAILS B ON C.ACCOUNTS1 = B.ACCOUNTS1 RIGHT OUTER JOIN
-- ABTRACERSDATA A ON B.LOANREFNO = A.loanrefno
--SELECT TOP 1 * FROM ABTRACERSINITIALHANDOVERSTATUS
--SELECT * FROM ABTRACERSDATA A where A.IDNUMBER+A.LOANREFNO
--NOT IN (SELECT B.IDNUMBER+B.LOANREFNO FROM ABTRACERSINITIALHANDOVERSTATUS B)
--select distinct(loanrefno) from abtracersnewmatters where loanrefno not in (select loanrefno from abtracersaddresses
-
No clue if this is your only problem, but one thing at a time. You have the statement "EXEC @SQL1" in at least one place in that code, without parentheses around the variable name. Also, I'd recommend printing out your strings before (or instead of, for now) executing them, in case it's something obvious such as a string being cut short, etc.
Edit: Also, while I'm not sure why you are using the begin/end pairs throughout the code, the "begin" in the code block:
--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
--Update Juridistcion in NewMatters
begin
Declare @total numeric
, @60 varchar(10)
, @20 varchar(10)
, @10 varchar(10)
...doesn't seem to have a corresponding "end" to go with it.
-
Good Morning
I have a VB Script below. I want to amend this script to wait for a control file. Can you please help
' VBS Script Generated by CuteFTP (TM) macro recorder.
' Generated at: 2007/03/01 02:15:46 PM.
' Create TEConnection object
Set MySite = CreateObject("CuteFTPPro.TEConnection")
' Initialize remote server host name, protocol, port, etc.
MySite.Host = "00.00.000.00"
MySite.Protocol = "FTP"
MySite.Port = 21
MySite.Retries = 30
MySite.Delay = 30
MySite.MaxConnections = 100
MySite.TransferType = "BINARY"
MySite.DataChannel = "DEFAULT"
MySite.AutoRename = "OFF"
' WARNING!!! SENSITIVE DATA: user name and password.
MySite.Login = "Lawactive"
MySite.Password = "LAWactive9"
MySite.SocksInfo = ""
MySite.ProxyInfo = ""
' Connect to remote server
MySite.Download "/Abtracers/*.*", "E:\xxxxx\xxxxxxx\ABTRACERS\"
MySite.RemoteRemove "/Abtracers/*.*"
MySite.Disconnect
' Initialize remote server host name, protocol, port, etc.
MySite.Host = "http://www.xxxxxxxx.co.za"
MySite.Protocol = "FTP"
MySite.Retries = 30
MySite.Delay = 30
MySite.MaxConnections = 100
MySite.TransferType = "BINARY"
MySite.DataChannel = "DEFAULT"
MySite.AutoRename = "OFF"
MySite.Login = "anonymous"
MySite.SocksInfo = ""
MySite.ProxyInfo = ""
' Connect to remote server
MySite.Connect
MySite.Upload "E:\xxxxxxx\xxxxxxxx\xxxxxxx\*.*", "/ToLaw/"
'Delete local file
MySite.LocalRemove "E:\xxxxxxx\xxxxxx\xxxxxxx\*.*"
MySite.Disconnect
-
Please tell me you didn't just publish the real login/password to your site...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
its defaults values. And its sorted thanks
-
Hi
I am trying to use Psexec from a SQL job conneting to a other Server but its failed. It's working from command prompt (cmd)
Here the script
psexec -d \\xxxxxxx c:\PROGRA~1\ccccccc\PHASEI~2\STARTN~1.BAT
Error message
Executed as user: xxxxx\xxxxx(server).
PsExec v1.3 - execute processes remotely Copyright (C) 2001
Mark Russinovich http://www.sysinternals.com Connecting to xxxxxx...
Couldn't access xxxxxxx: Access is denied. Process Exit Code -1. The step failed.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply