July 25, 2012 at 2:36 pm
That looks correct. Did you try it? It might make things more clear if instead of calling RAISERROR yourself change the try block to something that will raise and error.
BEGIN TRY
select 1/0 --This would simulate an error as the result of the sql execution.
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
SELECT @ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
-- Use RAISERROR inside the CATCH block to return
-- error information about the original error that
-- caused execution to jump to the CATCH block.
RAISERROR (@ErrorMessage, -- Message text.
@ErrorSeverity, -- Severity.
@ErrorState -- State.
);
END CATCH;
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 25, 2012 at 2:43 pm
It's is fairly simple to get the counts for inserts, updates, and deletes from the MERGE statement output.
Example:
set nocount on
go
create table #source (
PKintnot null primary key clustered,
Col1intnot null )
create table #target (
PKintnot null primary key clustered,
Col1intnot null )
go
insert into #source
select 1, 1 union all
select 2, 2 union all
select 3, 3 union all
select 4, -4 union all
select 8, -8 union all
select 9, -9
insert into #target
select 1, -1 union all
select 2, -2 union all
select 4, -4 union all
select 5, -5 union all
select 6, -6 union all
select 7, -7
print '#source before'
select * from #source
print '#target before'
select * from #target
declare @merge_PK table ( PK_deleted int, PK_insertedint, [Action] nvarchar(10) )
merge#target t
using
#source s
on t.PK = s.PK
when not matched by target and s.Col1 > -9
then
insert(
PK,
Col1
)
values(
PK,
Col1
)
when matched and s.Col1 <> t.Col1
then
update
set
t.PK= s.PK,
t.Col1= s.Col1
when not matched by source and t.Col1 < -5
then
delete
output
deleted.PK, inserted.PK , $action
into
@merge_PK ;
print 'Merge action Counts'
select
Insert_Count = sum(case when PK_deleted is null and PK_inserted is not null then 1 else 0 end) ,
Update_Count = sum(case when PK_deleted is not null and PK_inserted is not null then 1 else 0 end) ,
Delete_count = sum(case when PK_deleted is not null and PK_inserted is null then 1 else 0 end)
from
@merge_PK
select
Insert_Count = sum(case when [Action] = N'INSERT' then 1 else 0 end) ,
Update_Count = sum(case when [Action] = N'UPDATE' then 1 else 0 end) ,
Delete_count = sum(case when [Action] = N'DELETE' then 1 else 0 end)
from
@merge_PK
select
[Action] , [RowCount] = count(*)
from
@merge_PK
group by
[Action]
print '#target after'
select * from #target
go
drop table #source
drop table #target
Results
#source before
PK Col1
----------- -----------
1 1
2 2
3 3
4 -4
8 -8
9 -9
#target before
PK Col1
----------- -----------
1 -1
2 -2
4 -4
5 -5
6 -6
7 -7
Merge action Counts
Insert_Count Update_Count Delete_count
------------ ------------ ------------
2 2 2
Insert_Count Update_Count Delete_count
------------ ------------ ------------
2 2 2
Action RowCount
---------- -----------
DELETE 2
INSERT 2
UPDATE 2
#target after
PK Col1
----------- -----------
1 1
2 2
3 3
4 -4
5 -5
8 -8
July 25, 2012 at 3:18 pm
Sorry. I did mention that I had converted the SELECTS to an INSERT INTO TABLE but it was not obvious.
In the CATCH Block of the offending Stored Procedure I'm calling the GetErrorInfo.
I'm not sure how I would do this?
CREATE PROCEDURE usp_GetErrorInfo
AS
INSERT INTO ETLLoggingR2.dbo.ErrorLogSP (ErrorProcedure,ErrorNumber,ErrorSeverity,ErrorState,ErrorLine,ErrorMessage)
SELECT
ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage;
GO
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 25, 2012 at 3:26 pm
What is the question? I am not sure where you are stuck. You built your proc to log the error. Now just raise it again inside your catch to have that return the error to the caller.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 25, 2012 at 3:31 pm
Sean Lange (7/25/2012)
What is the question? I am not sure where you are stuck. You built your proc to log the error. Now just raise it again inside your catch to have that return the error to the caller.
Sorry. I don't know the syntax?
Here is my catch.
BEGIN CATCH
EXECUTE usp_GetErrorInfo;
END CATCH;
This my my SP:
CREATE PROCEDURE usp_GetErrorInfo
AS
INSERT INTO ETLLoggingR2.dbo.ErrorLogSP (ErrorProcedure,ErrorNumber,ErrorSeverity,ErrorState,ErrorLine,ErrorMessage)
SELECT
ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage;
GO
Thank you very much for your help and patience.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 25, 2012 at 3:37 pm
Welsh Corgi (7/25/2012)
Sean Lange (7/25/2012)
What is the question? I am not sure where you are stuck. You built your proc to log the error. Now just raise it again inside your catch to have that return the error to the caller.Sorry. I don't know the syntax?
Here is my catch.
BEGIN CATCH
EXECUTE usp_GetErrorInfo;
END CATCH;
This my my SP:
CREATE PROCEDURE usp_GetErrorInfo
AS
INSERT INTO ETLLoggingR2.dbo.ErrorLogSP (ErrorProcedure,ErrorNumber,ErrorSeverity,ErrorState,ErrorLine,ErrorMessage)
SELECT
ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage;
GO
Thank you very much for your help and patience.
It looks to me like you know the syntax. Did you try it? I don't know what you are missing. Are you stuck trying to raise the error again? You posted a RAISERROR call earlier, just do it again.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 25, 2012 at 3:46 pm
If I execute:
EXEC .usp_Insert_Into_Problem_Type_Codes
I get:
Command(s) completed successfully.
But the CATCH Block fired of and Inserted a record into the Error Table because I did not TRUNCATE the Table which resulted in a FK Violation.
Does that help make it a little clearer?
Thanks for your help.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 25, 2012 at 4:02 pm
Welsh Corgi (7/25/2012)
Sean Lange (7/25/2012)
What is the question? I am not sure where you are stuck. You built your proc to log the error. Now just raise it again inside your catch to have that return the error to the caller.Sorry. I don't know the syntax?
Here is my catch.
BEGIN CATCH
EXECUTE usp_GetErrorInfo;
END CATCH;
This my my SP:
CREATE PROCEDURE usp_GetErrorInfo
AS
INSERT INTO ETLLoggingR2.dbo.ErrorLogSP (ErrorProcedure,ErrorNumber,ErrorSeverity,ErrorState,ErrorLine,ErrorMessage)
SELECT
ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage;
GO
Thank you very much for your help and patience.
I don't think the procedure would work the way you want:
1. ERROR_NUMBER(), ERROR_SEVERITY() would be in the context of the usp_GetErrorInfo stored procedure, not the calling procedure. If you want to pass them to that procedure, you should pass them as parameters from inside the catch block. They will return null if used outside the catch block (see example below).
2. You have to account for open transactions. If you call usp_GetErrorInfo and then rollback the transaction, then the insert in usp_GetErrorInfo will be rolled back. You should only call it after the transaction is rolled back.
begin try
select 1/0
end try
begin catch
SELECT 'Inside Catch 1',
ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage;
SELECT 'Inside Catch 2',
ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage;
end catch
SELECT 'After Catch',
ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage;
July 25, 2012 at 4:28 pm
I'm confused.:unsure:
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 25, 2012 at 4:58 pm
I thought that it could be as simple as a return code to indicate that there was an error.
This procedure will be executed in an SSIS Package.
If someone knows of a better way I'm all ears.
Thanks.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 25, 2012 at 10:54 pm
Welsh Corgi (7/25/2012)
I'm confused.:unsure:
Confused about what? TRY/CATCH or MERGE or something else.
July 26, 2012 at 7:12 am
Welsh Corgi (7/25/2012)
I thought that it could be as simple as a return code to indicate that there was an error.This procedure will be executed in an SSIS Package.
If someone knows of a better way I'm all ears.
Thanks.
You should not try to use a return code, use RAISERROR that sounds like what you want to happen. I assume you want your SSIS package to know there was an error. That way you can have an ON SUCCESS and ON FAILURE right? If you use a return code the step is successful. If you raise an error, it will fail.
I would recommend changing your usp_GetErrorInfo proc to receive all that data as parameters. Then you can log the error and raise it again. The problem with the way you are doing it is that your error values will get reset if there is an error in usp_GetErrorInfo. If you use parameters they will not change.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 26, 2012 at 11:36 am
Welsh Corgi (7/25/2012)
I thought that it could be as simple as a return code to indicate that there was an error.This procedure will be executed in an SSIS Package.
If someone knows of a better way I'm all ears.
Thanks.
If you're running this within SSIS - expose the inserts and updates within SSIS and use its error handling. That way you can compensate for the errors immediately (it would allow you to insert the rows that work, and "fix" the rows that error, and other interesting things like that).
This is one of those cases where doing too much in T-SQL will prevent you from leveraging some nice instrumentation made available to you in SSIS native.
----------------------------------------------------------------------------------
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?
July 26, 2012 at 7:33 pm
Matt Miller (#4) (7/26/2012)
Welsh Corgi (7/25/2012)
I thought that it could be as simple as a return code to indicate that there was an error.This procedure will be executed in an SSIS Package.
If someone knows of a better way I'm all ears.
Thanks.
If you're running this within SSIS - expose the inserts and updates within SSIS and use its error handling. That way you can compensate for the errors immediately (it would allow you to insert the rows that work, and "fix" the rows that error, and other interesting things like that).
This is one of those cases where doing too much in T-SQL will prevent you from leveraging some nice instrumentation made available to you in SSIS native.
I will research that but do you have any good articles that address this, preferable with screen shots?
It differs how you handle an error with an OLE DB Source and Destination as opposed to an Execute SQL Task, correct?
It may be helpful to still have the error handling in the Stored Procedure, that was I can identify which row the error occurred on in addition to SSIS Error hAndling?
Any tips would be greatly appreciated.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 27, 2012 at 5:01 am
I do not see an option to redirect errors from an Execute SQL Task?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 15 posts - 16 through 30 (of 35 total)
You must be logged in to reply to this topic. Login to reply