October 30, 2019 at 5:27 pm
I have a log table that gets an insert right at the beginning in order to show the process started. Then it should do the merge/insert and either mark it as failed or succeeded.
The initial 'in progress' insert is not happening and the failure is not being logged either. I have purposely tried inserting an invalid column to make sure it errors. The error shows however nothing is logged; either the initial or the update. Here is my code:
declare @rowCount int
insert into
dbo.Log_Extract (/*importID*/ targetTable, startTime, endTime, [status])
values
(
/*Identity Column*/
'[dbo.SF_CustomerID]' -- Always this value for this procedure
, getdate()
, null
, 'P' -- In progress always at start
);
begin try
merge dbo.SF_CustomerID as target
using dbo.Extract_SF_CustomerID as source
on (target.[Internal ID] = source.[Internal ID])
-- when not matched, insert new records
when not matched by target
then insert
(
[Internal ID1] -- incorrect value for testing failure
, [Customer ID]
)
values
(
source.[Internal ID]
, source.[Customer ID]
);
set @rowCount = (select @@rowcount);
end try
begin catch -- On failure; check agent log history for possibly more detailed error information
update
dbo.Log_Extract
set
rowsAdded = @rowCount
, [status] = 'F'
where
targetTable = '[dbo.SF_CustomerID]' and [status] = 'P';
end catch
update -- On success change status to S
dbo.Log_Extract
set
endTime = getdate()
, rowsAdded = @rowCount
, [status] = 'S'
where
targetTable = '[dbo.SF_CustomerID]' and [status] = 'P';
What am I doing wrong? I've read several posts here and have done what they've said but still nothing. I've tried begin/end, begin tran/commit tran or rollback tran and still the same results.
October 30, 2019 at 6:51 pm
Please provide a CREATE TABLE statement for the dbo.LogExtract table. You can script that out using SSMS. We have no way to test your code to see what's going on without that, at a minimum. Additionally, we'll need some mocked up data and both CREATE TABLE and INSERT statements for your source and target tables in your merge. Not much can be done when the problem isn't obvious and we have no idea what we are dealing with beyond your code. It's kind of like trying to open a jar of pickles and all you have are cat's paws instead of hands, plus you're a blind cat because you can't see the data you need (in this analogy, the location of the pickle jar) to be able to understand the nature of the problem.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
October 30, 2019 at 6:56 pm
Where you attempt to insert with an incorrect column name it results in a compile time error. Sql Server is not able to come up with an execution plan for your code because the Sql syntax is not correct. What you appear to be trying to capture with your logging are run-time errors (which are due to constraint violations, math divide by zero errors, etc...). To force a run-time error you could either use THROW or SELECT 1/0. This code needs a lot of work tho -- could you post the entire procedure?
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply