Need help capture error msg into another table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Need help capture error msg into another table

Author

Topic

NguyenL71
Posting Yak Master

228 Posts

Posted - 2013-11-15 : 12:11:23

Hi,

One of my co-worker told me I can do this to capture errors and insert into error table but when I
test it, it doesn't work. Here is what I try to accomplish. SQL 2012. In reality, I have more complicate queries than below.

1. Insert data FROM SourceEmployee INTO Employee table and capture emp_id and error msg insert into dbo.##temperror table
2. Continue on the process until no more record. Basically, skip the error records and do a while loop until end of record.

Any suggestions how to do these would greatly appreciate. Please see the output below.

Thank you so much in advance.

-------------------------------------------------------------------------------------

--DROP TABLE dbo.Employee;

CREATE TABLE [dbo].[Employee]
(
[emp_id] [int] NOT NULL,
[last_name] [varchar](20) NULL,
[first_name] [varchar](15) NOT NULL,
[salary] [money] NULL,
[email] [varchar](100) NOT NULL,
CONSTRAINT [XPKEmployee] PRIMARY KEY CLUSTERED ([emp_id] ASC )
) ON [PRIMARY]

GO

INSERT INTO dbo.Employee([emp_id],[last_name],[first_name],[salary],[email])
VALUES (1, 'Smith', 'John', 800.00, 'smithJohn@hotmail.com' ) ;

INSERT INTO dbo.Employee([emp_id],[last_name],[first_name],[salary],[email])
VALUES (2, 'Pham', 'Kristine', 200.00, 'PhamKristine@hotmail.com' ) ;

INSERT INTO dbo.Employee([emp_id],[last_name],[first_name],[salary],[email])
VALUES (3, 'Nguyen', 'Brandon', 1000.00, 'nguyenbrandon@hotmail.com' ) ;
GO

--DROP TABLE [dbo].[SourceEmployee];

CREATE TABLE [dbo].[SourceEmployee]
(
[emp_id] [int] NULL,
[first_name] [varchar](15) NULL,
[salary] [money] NULL,
[email] [varchar](50) NULL
) ON [PRIMARY]
GO

INSERT INTO dbo.SourceEmployee([emp_id],[first_name],[salary],[email])
VALUES (6, 'Peter', NULL, 'Peter@hotmail.com' ) ;

INSERT INTO dbo.SourceEmployee([emp_id],[first_name],[salary],[email])
VALUES (7, NULL, NULL, 'doe@hotmail.com' ) ;

INSERT INTO dbo.SourceEmployee([emp_id],[first_name],[salary],[email])
VALUES (8, NULL, 100.00, NULL ) ;
GO

DROP TABLE dbo.##temperror;
GO
CREATE TABLE dbo.##temperror
(
emp_id INT NULL,
errmsg VARCHAR(8000) NULL
)
GO

------------------------------------------------------------------------------------

--Testing...

DECLARE @TesVal BIT = 1
WHILE @TesVal = 1
BEGIN TRY

INSERT Employee (emp_id, last_name, first_name, salary, email)
SELECT emp_id, '', first_name, salary, email
FROM dbo.SourceEmployee

SET @TesVal = 0

END TRY

BEGIN CATCH

DECLARE
@ErrorNumber INT = ERROR_NUMBER(),
@ErrorSeverity INT = ERROR_SEVERITY(),
@ErrorState INT = ERROR_STATE(),
@ErrorProcedure NVARCHAR(126) = ERROR_PROCEDURE(),
@ErrorLine INT = ERROR_LINE(),
@ErrorMessage NVARCHAR(MAX) = REPLACE(ERROR_MESSAGE(), '''', '`')

SET @ErrorMessage = '<Error>'
+ '<ErrorNumber>' + ISNULL(CAST(@ErrorNumber AS NVARCHAR(100)), 'NULL') + '</ErrorNumber>'
--+ '<ErrorSeverity>' + ISNULL(CAST(@ErrorSeverity AS NVARCHAR(100)), 'NULL') + '</ErrorSeverity>'
--+ '<ErrorState>' + ISNULL(CAST(@ErrorState AS NVARCHAR(100)), 'NULL') + '</ErrorState>'
+ '<ErrorProcedure>' + ISNULL(@ErrorProcedure, 'NULL') + '</ErrorProcedure>'
+ '<ErrorLine>' + ISNULL(CAST(@ErrorLine AS NVARCHAR), 'NULL') + '</ErrorLine>'
+ '<ErrorMessage>' + ISNULL(@ErrorMessage, 'NULL') + '</ErrorMessage>'
+ '</Error>';

--PRINT @ErrorMessage
--INSERT ##temperror
--SELECT emp_id

RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);

--THROW
PRINT 'go here'

END CATCH;
go

--SELECT *
-- FROM dbo.Employee;
--GO

----------------------------------------------------------------------------------------------------------

-- Results want:
SELECT * FROM dbo.##temperror;

emp_id errmsg
------ -------
7 Msg 50000, Level 16, State 2, Line 46
<Error><ErrorNumber>515</ErrorNumber><ErrorProcedure>NULL</ErrorProcedure><ErrorLine>16</ErrorLine><ErrorMessage>Cannot insert the value NULL into column `first_name`, table `CDCTest.dbo.Employee`; column does not allow nulls. INSERT fails.</ErrorMessage></Error>

8 Msg 50000, Level 16, State 2, Line 46
<Error><ErrorNumber>515</ErrorNumber><ErrorProcedure>NULL</ErrorProcedure><ErrorLine>16</ErrorLine><ErrorMessage>Cannot insert the value NULL into column `first_name`, table `CDCTest.dbo.Employee`; column does not allow nulls. INSERT fails.</ErrorMessage></Error>

1 row insert to Employee table successfully. Should be emp_id = 6

--SELECT *
-- FROM dbo.Employee;
--GO

(6, 'Peter', NULL, 'Peter@hotmail.com' )

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-11-15 : 12:40:44

Here is one way:

DECLARE @EmpID INT;

SET @EmpID = (SELECT MIN(emp_id) FROM dbo.SourceEmployee);


WHILE @EmpID IS NOT NULL
BEGIN

	BEGIN TRY
		INSERT 
			dbo.Employee
			(
				[emp_id],
				[first_name],
				[salary],
				[email]				
			)
		SELECT
			[emp_id],
			[first_name],
			[salary],
			[email]			
		FROM
			dbo.SourceEmployee
		WHERE
			emp_id = @EmpID;


	END TRY
	BEGIN CATCH

		INSERT
			##temperror
			(
				emp_id,
				errmsg
			)
		VALUES
			(
				@EmpID,
				ERROR_MESSAGE()
			)

	END CATCH

	SET @EmpID = 
		(
			SELECT 
				MIN(emp_id) 
			FROM 
				dbo.SourceEmployee
			WHERE
				emp_id > @EmpID
		);

END


SELECT *
FROM ##temperror

SELECT *
FROM dbo.Employee

NguyenL71
Posting Yak Master

228 Posts

Posted - 2013-11-15 : 13:07:47

Thank you so much for taking the time to help. But the real queries, it's not one SourceEmployee table.

Thank you and have a great Weekend.

Here is the queries and I can't think how to capture all the errors in
the select columns.
WITH CTE1
AS
(SELECT
acct.accountId
,apl.AccountProductLine_ID
,pls.AccountProductLineStatusType_ID
,ROW_NUMBER() OVER (PARTITION BY acct.Entity_ID,apl.AccountProductLine_ID -- put NULL at bottom of list when it exists
ORDER BY pls.AccountProductLineStatusType_ID DESC, pls.EffectiveDate DESC, pls.AccountProductLineStatus_ID DESC) RowNumber
FROM Membership.Account acct
INNER JOIN Membership.AccountContract ac ON acct.Entity_ID = ac.Account_ID
AND ac.RenewalDate >= GETUTCDATE() and ac.IsDeleted=0
--AND ac.EffectiveDate <= GETUTCDATE()
--AND ac.EnrollmentDate <= GETUTCDATE() /* is this necessarY? */
INNER JOIN Membership.AccountProductLine apl ON ac.AccountContract_ID = apl.AccountContract_ID
-- AND ap.EffectiveDate <= GETUTCDATE()
-- AND ap.ApprovalDate <= GETUTCDATE() /* ??? */
INNER JOIN Membership.AccountProductLineStatus pls ON apl.AccountProductLine_ID = pls.AccountProductLine_ID
AND pls.EffectiveDate <= GETDATE()
AND pls.AccountProductLineStatusType_ID IN (2 /*ApproveContingent*/, 4/*InForce*/, 6/*Reinstated*/)
),
CTE2
AS
-- Now we are only looking at the active product lines as of today. Next check the Enrollment status as of today by getting the
-- status of the most recent effective date before today for each product and see if any of them active.
(SELECT
cte1.accountid
,cte1.AccountProductLineStatusType_ID
,cte1.AccountProductLine_ID
, ee.ProductLineType_ID
,es.EffectiveDate
,es.EnrollmentStatus_ID
, es.EnrollmentStatusType_ID
,ROW_NUMBER() OVER (PARTITION BY cte1.Entity_ID -- put NULL at bottom of list when it exists
ORDER BY es.EnrollmentStatusType_ID DESC, es.EffectiveDate DESC, es.EnrollmentStatus_ID DESC) RowNumber
FROM cte1
INNER JOIN Membership.AccountProductLinePlan app
ON cte1.AccountProductLine_ID = app.AccountProductLine_ID
-- AND app.EffectiveDate >= GETUTCDATE()
INNER JOIN Membership.Enrollment enr ON app.AccountProductLinePlan_ID = enr.AccountProductLinePlan_ID
-- AND enr.EffectiveDate >= GETUTCDATE()
--AND enr.EnrollmentDate >= GETUTCDATE() /* ??? */
INNER JOIN Membership.EnrollmentElection ee ON enr.EnrollmentElection_ID = ee.EnrollmentElection_ID
INNER JOIN Membership.EnrollmentStatus es ON ee.EnrollmentElection_ID = es.EnrollmentElection_ID
AND es.EnrollmentStatusType_ID IN (1 /*Pending*/, 3 /*Enrolled*/)
AND es.EffectiveDate <= GETDATE()
WHERE cte1.RowNumber=1
)
--SELECT * FROM CTE2
INSERT INTO [etlUMB].[UMBAccountMasterDetailTransform]
(
UMBAccountMasterHeader_ID,
RecordType,
AccountHolderFirstName,
AccountHolderMiddleInitial,
AccountHolderLastName,
AccountHolderSSN,
EmailAddress,
AccountHoldersAddressLine1,
AccountHoldersAddressLine2,
AccountHolderCity,
AccountHolderState,
AccountHolderCountry,
AccountHolderZipCode1,
AccountHolderZipCode2,
CustomerAccountNumber,
CustomerAccountType,
CustomerAccountSubType,
AccountIssuerStateCode,
CurrentBalance,
CreditLimit,
MinimumPaymentDue,
PastAmountDue,
CurrentStatementBalance,
PaymentDueDate,
InvoiceNumber,
InvoiceDate,
EligibilityIndicator,
PartnerKey,
CustomerID,
CustomerOnlineEnrollmentCode,
Filler,
Account_ID,
AccountHolderEntity_ID,
HIXGroupID,
IsCurrent,
UpdateDate,
SecurityUser_ID,
RootKey,
HashKey,
SourceSystemID
)
-- Try to catch any errors before inserting into a table.
SELECT
col1,
col2,
col3,
col4,
....
SourceSystemId

FROM Membership.Account acct
INNER JOIN dbo.Entity e
ON acct.accountid = e.account
AND e.EntityType_ID = 1 -- Group
INNER JOIN Membership.AccountContract ac ON acct.Entity_ID = ac.Account_ID
AND ac.RenewalDate >= GETUTCDATE() and ac.IsDeleted=0
LEFT JOIN cte2 ON acct.Entity_ID = cte2.Entity_ID AND cte2.RowNumber=1
INNER JOIN dbo.Organization o
ON acct.Entity_ID = o.Entity_ID
AND o.OrganizationType_ID = 1 -- Group
--LEFT JOIN dbo.Organization_To_CommonValue O2C
-- ON O2C.Organization_ID = o.Entity_ID
-- JOIN dbo.CommonValue CV -- FIX change to INNER JOIN in Production
-- ON CV.CommonValue_ID = O2C.CommonValue_ID
-- AND CV.Name = 'EIN'
LEFT OUTER JOIN [etlUMB].[UMBAccountMasterDetailMap] M
ON e.HIXID = M.SourceID
AND M.SourceSystemID = 'HIX'
WHERE e.MYHID NOT IN ( SELECT MYHID
FROM @TempGroup ) -- Exclude the dup val.
--LEFT OUTER JOIN Membership.EnrollmentStatus es
-- ON es.SourceSystem_ID=e.SourceSystem_ID;

---------------------------------------------------------

quote:

Originally posted by Lamprey

Here is one way:

DECLARE @EmpID INT;

SET @EmpID = (SELECT MIN(emp_id) FROM dbo.SourceEmployee);


WHILE @EmpID IS NOT NULL
BEGIN

	BEGIN TRY
		INSERT 
			dbo.Employee
			(
				[emp_id],
				[first_name],
				[salary],
				[email]				
			)
		SELECT
			[emp_id],
			[first_name],
			[salary],
			[email]			
		FROM
			dbo.SourceEmployee
		WHERE
			emp_id = @EmpID;


	END TRY
	BEGIN CATCH

		INSERT
			##temperror
			(
				emp_id,
				errmsg
			)
		VALUES
			(
				@EmpID,
				ERROR_MESSAGE()
			)

	END CATCH

	SET @EmpID = 
		(
			SELECT 
				MIN(emp_id) 
			FROM 
				dbo.SourceEmployee
			WHERE
				emp_id > @EmpID
		);

END


SELECT *
FROM ##temperror

SELECT *
FROM dbo.Employee

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources