October 10, 2014 at 9:04 am
Hi,
I have a stored proc that pulls data from a very old pervasive database into SQL.
This process sometimes fails due to network issues.
I would like to insure that if it fails that it rolls back any partial changes - I will then set the job to retry if it sees a failure.
Here's the simple statement:
-- Load APHDR Data
--TRUNCATE TABLE AllStarWebAP.dbo.APHDR
DELETE FROM AllStarWebAP.dbo.APHDR
WHERE Company = @Company
SELECT @SQLString = N' INSERT INTO AllStarWebAP.dbo.APHDR
SELECT '''+ @Company +''' AS Company, * FROM OPENQUERY
("10.190.1.28", ''SET FMTONLY OFF; SET NOCOUNT ON;EXEC asint.dbo.Rpt_LoadApData '''''+@Company+''''', ''''APHDR'''' '')'
EXEC sp_executesql @SQLString
October 10, 2014 at 10:51 am
I'm assuming you don't want the DELETE to occur if the insert fails. The biggest issue with doing it this way is that you need DTC to be working to wrap it in a transaction. What I'd suggest to avoid this is to load the external data into a temp table and then in an explicit transaction DELETE and reload the local table from the temp table in a transaction. Like this:
-- Load APHDR Data
CREATE TABLE #aphdr
(
COLUMN list
);
SELECT
@SQLString = N' INSERT INTO #aphdr
SELECT ''' + @Company + ''' AS Company, * FROM OPENQUERY
("10.190.1.28", ''SET FMTONLY OFF; SET NOCOUNT ON;EXEC asint.dbo.Rpt_LoadApData ''''' + @Company + ''''', ''''APHDR'''' '')'
EXEC sp_executesql @SQLString
BEGIN TRANSACTION;
BEGIN TRY
INSERT INTO AllStarWebAP.dbo.APHDR
SELECT
*
FROM
#aphdr AS A;
--TRUNCATE TABLE AllStarWebAP.dbo.APHDR
DELETE FROM
AllStarWebAP.dbo.APHDR
WHERE
Company = @Company;
IF @@TRANCOUNT > 0
BEGIN;
COMMIT TRANSACTION;
END;
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE();
IF @@TRANCOUNT > 0
BEGIN;
ROLLBACK TRANSACTION;
END;
END CATCH;
You may need to make #aphdr a global temporary table ##aphdr or a permanent staging table.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 10, 2014 at 4:09 pm
You might want to look into external management of this type of long running distributed transaction. The BizTalk server[/url] is build around managing this sort of thing..
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 13, 2014 at 6:45 am
Thanks Jack.
This looks like exactly what I was looking for.
I'll give it a test in a day or two.
Thanks again!
October 13, 2014 at 7:30 am
Grant Fritchey (10/10/2014)
You might want to look into external management of this type of long running distributed transaction. The BizTalk server[/url] is build around managing this sort of thing..
I agree that some other process: Biz Talk, SSIS, etc... might be the best long-term solution. Just wanted to give the OP something that might work better.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 13, 2014 at 7:42 am
I guess my question would be, why aren't you running this from the external server instead of having to use DTC?
--Jeff Moden
Change is inevitable... Change for the better is not.
October 14, 2014 at 4:59 am
Jack Corbett (10/13/2014)
Grant Fritchey (10/10/2014)
You might want to look into external management of this type of long running distributed transaction. The BizTalk server[/url] is build around managing this sort of thing..I agree that some other process: Biz Talk, SSIS, etc... might be the best long-term solution. Just wanted to give the OP something that might work better.
No arguments. But you do want to get people thinking in that direction.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 14, 2014 at 9:40 am
Thanks so much guys. Really appreciated.
Do I need to change when I send the commit if there is more than one statement? (Do I wrap the whole thing in one trans)
For example :
DECLARE @SQLString nvarchar(2000)
-- Load APHDR Data
--TRUNCATE TABLE AllStarWebAP.dbo.APHDR
DELETE FROM AllStarWebAP.dbo.APHDR
WHERE Company = @Company
SELECT @SQLString = N' INSERT INTO AllStarWebAP.dbo.APHDR
SELECT '''+ @Company +''' AS Company, * FROM OPENQUERY
("10.190.1.28", ''SET FMTONLY OFF; SET NOCOUNT ON;EXEC asint.dbo.Rpt_LoadApData '''''+@Company+''''', ''''APHDR'''' '')'
EXEC sp_executesql @SQLString
-- Load APHDRH Data
--TRUNCATE TABLE AllStarWebAP.dbo.APHDRH
DELETE FROM AllStarWebAP.dbo.APHDRH
WHERE Company = @Company
SELECT @SQLString = N' INSERT INTO AllStarWebAP.dbo.APHDRH
SELECT '''+ @Company +''' AS Company, * FROM OPENQUERY
("10.190.1.28", ''SET FMTONLY OFF; SET NOCOUNT ON;EXEC asint.dbo.Rpt_LoadApData '''''+@Company+''''', ''''APHDRH'''' '')'
EXEC sp_executesql @SQLString
-- Load APLIN Data
--TRUNCATE TABLE AllStarWebAP.dbo.APLIN
DELETE FROM AllStarWebAP.dbo.APLIN
WHERE Company = @Company
SELECT @SQLString = N' INSERT INTO AllStarWebAP.dbo.APLIN
SELECT '''+ @Company +''' AS Company, * FROM OPENQUERY
("10.190.1.28", ''SET FMTONLY OFF; SET NOCOUNT ON;EXEC asint.dbo.Rpt_LoadApData '''''+@Company+''''', ''''APLIN'''' '')'
EXEC sp_executesql @SQLString
October 14, 2014 at 10:49 am
First - one smallish comment: SQL INJECTION. This one's ripe for it. seeing the dynamic query concatenated with some input (and with the Web APP mention in your table names) just brought that one to mind.
As to whether to wrap the statements: what's the actual requirement? If the intent is to make sure that ALL THREE tables get reloaded in order to keep anything, then - yes. If you need to ensure that the table a loaded (i.e. that each pair of DELETE/INSERT commit or fail together), then no.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 14, 2014 at 11:11 am
Matt Miller (#4) (10/14/2014)
First - one smallish comment: SQL INJECTION. This one's ripe for it. seeing the dynamic query concatenated with some input (and with the Web APP mention in your table names) just brought that one to mind.As to whether to wrap the statements: what's the actual requirement? If the intent is to make sure that ALL THREE tables get reloaded in order to keep anything, then - yes. If you need to ensure that the table a loaded (i.e. that each pair of DELETE/INSERT commit or fail together), then no.
Thanks Matt.
Yes, the (new, sorry) requirement is that all three get loaded. So, I wrap all three in the begin trans?.. and so I only need the catch try's at the beginning and end (once)?
October 14, 2014 at 12:31 pm
krypto69 (10/14/2014)
Matt Miller (#4) (10/14/2014)
First - one smallish comment: SQL INJECTION. This one's ripe for it. seeing the dynamic query concatenated with some input (and with the Web APP mention in your table names) just brought that one to mind.As to whether to wrap the statements: what's the actual requirement? If the intent is to make sure that ALL THREE tables get reloaded in order to keep anything, then - yes. If you need to ensure that the table a loaded (i.e. that each pair of DELETE/INSERT commit or fail together), then no.
Thanks Matt.
Yes, the (new, sorry) requirement is that all three get loaded. So, I wrap all three in the begin trans?.. and so I only need the catch try's at the beginning and end (once)?
In short - yes. If the entire statement above has to commit or fail in full, then you only want one BEGIN TRAN construct and one TRY/Catch construct. You'd probably want to make sure that you check the intermediate results (as Jack did previously) so that you know whether the INSERT actually put anything in (and optionally RAISERROR or THROW an error if it doesn't), but a single transaction and a single TRY catch should be able to handle this case (knowing what little I know of your code of course).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 20, 2014 at 7:18 am
Great.
Thanks so much for all your help Matt.
So do I create a new temp table for every insert?
Do I have this correct? I think I am doing what you said with the catch try's
-- Load APHDR Data
CREATE TABLE #aphdr
(
COLUMN list
);
CREATE TABLE #APLIN
(
COLUMN list
);
SELECT
@SQLString = N' INSERT INTO #aphdr
SELECT ''' + @Company + ''' AS Company, * FROM OPENQUERY
("10.190.1.28", ''SET FMTONLY OFF; SET NOCOUNT ON;EXEC asint.dbo.Rpt_LoadApData ''''' + @Company + ''''', ''''APHDR'''' '')'
EXEC sp_executesql @SQLString
SELECT @SQLString = N' INSERT INTO AllStarWebAP.dbo.APLIN
SELECT '''+ @Company +''' AS Company, * FROM OPENQUERY
("10.190.1.28", ''SET FMTONLY OFF; SET NOCOUNT ON;EXEC asint.dbo.Rpt_LoadApData '''''+@Company+''''', ''''APLIN'''' '')'
EXEC sp_executesql @SQLString
BEGIN TRANSACTION;
BEGIN TRY
INSERT INTO AllStarWebAP.dbo.APHDR
SELECT
*
FROM
#aphdr AS A;
--TRUNCATE TABLE AllStarWebAP.dbo.APHDR
DELETE FROM
AllStarWebAP.dbo.APHDR
WHERE
Company = @Company;
IF @@TRANCOUNT > 0
BEGIN;
COMMIT TRANSACTION;
END;
INSERT INTO AllStarWebAP.dbo.APLIN
SELECT
*
FROM
#APLIN AS A;
--TRUNCATE TABLE AllStarWebAP.dbo.APHDR
DELETE FROM
AllStarWebAP.dbo.APLIN
WHERE
Company = @Company;
IF @@TRANCOUNT > 0
BEGIN;
COMMIT TRANSACTION;
END;
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE();
IF @@TRANCOUNT > 0
BEGIN;
ROLLBACK TRANSACTION;
END;
END CATCH;
Viewing 12 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply