Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Finding row number which caused the error in Table value parameter insertion Expand / Collapse
Author
Message
Posted Monday, September 2, 2013 4:08 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, November 3, 2014 10:08 PM
Points: 182, Visits: 1,001
Hi
I am trying to write a Bulk import procedure, i am using table value parameter to insert thousands of rows in the table.
The problem is if any error happens,i am not able to know which row in the table value parameter caused it.
I am not using transaction in stored procedure
here is the code

--Table creation 
CREATE TABLE DataImport (id int identity(1,1),Name varchar(2))

Insert INTO DataImport
values('aa'),
('bb'),
('cc'),
('dd')

--SELECT * FROM DataImport

--Table type creation
CREATE TYPE udt_Table AS TABLE (ud_name varchar(3))

--stored procedure
CREATE PROC Usp_DataImport
@TVP [dbo].[udt_Table] READONLY
AS
BEGIN
DECLARE @Insert Table (ActionType nvarchar(10),NewestName varchar(3),OldestName vaRCHAR(3))

MERGE DataImport AS [TARGET]
USING @TVP AS [SOURCE]
ON [TARGET].Name = [SOURCE].ud_name
WHEN MATCHED THEN
UPDATE
SET Name = [SOURCE].ud_name
WHEN NOT MATCHED THEN
INSERT (Name)
VALUES([SOURCE].ud_name)
OUTPUT $ACTION, Inserted.Name,Deleted.Name INTO @Insert;

SELECT * FROM @Insert
END


--Executing Procedure

--First proc Exection sucessfully done
DECLARE @dd AS dbo.udt_Table

INSERT INTO @dd
values('ee'),
('aa'),
('XX'),
('GG'),
('66')

EXEC Usp_DataImport @TVP = @dd

--Second proc Exection fail this will generate error due to varchar length (String validation can be done frontend to avoid this problem...i am using this to generate the error)

Here i want to get at which row error happens...so that i will return to application to make changes and then insert

DECLARE @dd AS dbo.udt_Table

INSERT INTO @dd
values('ee'),
('aa'),
('XX'),
('GG'),
('GGG')
('66')

EXEC Usp_DataImport @TVP = @dd




Thanks
Post #1490576
Posted Monday, September 2, 2013 3:58 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 9:35 AM
Points: 811, Visits: 738
Unfortunately, this is one of the drawbacks with working set-based. If you insert rows one a time, finding the bad data is trivial. But that is of course far less efficient. A possible strategy is to trap the error with TRY-CATCH and resort to a loop in case of an error.

There is very good Connect request for this problem on https://connect.microsoft.com/SQLServer/feedback/details/774754/new-virtual-table-errors-it-would-analogous-to-the-deleted-and-inserted-tables#details that you could vote for.


Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Post #1490699
Posted Monday, September 2, 2013 9:28 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, November 3, 2014 10:08 PM
Points: 182, Visits: 1,001
Erland Sommarskog (9/2/2013)
Unfortunately, this is one of the drawbacks with working set-based. If you insert rows one a time, finding the bad data is trivial. But that is of course far less efficient. A possible strategy is to trap the error with TRY-CATCH and resort to a loop in case of an error.

There is very good Connect request for this problem on https://connect.microsoft.com/SQLServer/feedback/details/774754/new-virtual-table-errors-it-would-analogous-to-the-deleted-and-inserted-tables#details that you could vote for.


Thanks for the reply Erland Sommarskog
I voted that feature ...microsoft should add that feature.

you said to use TRY-CATCH ...but the problem is any error happens it rollback all the transaction..i am not using any transaction may be it is using batch transaction which causing it rollback.

Any workaround or i have to use loop to insert the data(Performance issue)
Post #1490737
Posted Tuesday, September 3, 2013 5:14 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 9:35 AM
Points: 811, Visits: 738
As you long as your stored procedure is not part of an outer transaction, it is doable:

BEGIN TRY
MERGE ...
END TRY
BEGIN CATCH
DECLARE cur CURSOR STATIC LOCAL FOR
SELECT .. FROM @tvp
OPEN cur
WHILE 1 = 1
BEGIN
FETCH cur INTO @var1....
IF @@fetch_status <> 0
BREAK

BEGIN TRY
MERGE ....
END TRY
BEGIN CATCH
PRINT 'Errors for ' + ....
END CATCH
END
DEALLOCATE cur
END CATCH

But if your procedure is part of a greater transaction, this may not work as the error may doom the transaction and you cannot do the fallback.

And of course it is a burden to maintain two code paths..


Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Post #1490884
Posted Tuesday, September 3, 2013 6:01 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, November 3, 2014 10:08 PM
Points: 182, Visits: 1,001
We are handling this from frontend ..if any error comes we rollback and divide the number of rows and insert it...so this process will go on until all the rows insert.

Post #1490897
Posted Tuesday, September 3, 2013 2:13 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 9:35 AM
Points: 811, Visits: 738
Yeah, that works too. And slicing the data in half is certainly a possibility. If you insert millions of rows, and you only have a single errors this may be more efficient than to do a loop one-by-one. Particularly, if you are lucky and the first half is good. (Then you know that you need to slice the second half already.)



Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Post #1491088
Posted Tuesday, September 3, 2013 4:22 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 5:27 PM
Points: 1,806, Visits: 5,863
Surely this is a case for validation at the front end?

There always used to be a rule that applications validate input...


MM


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1491112
    Posted Wednesday, September 4, 2013 1:18 AM


    SSC Eights!

    SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

    Group: General Forum Members
    Last Login: Today @ 9:35 AM
    Points: 811, Visits: 738
    mister.magoo (9/3/2013)
    Surely this is a case for validation at the front end?

    There always used to be a rule that applications validate input...


    Yes and no. First of all, it depends on the kind of application. It may be an ETL application, and in that case you don't validate in the client, because all you have is files. (And I more or less assume that this is ETL.)

    But even if the data comes from user entry, the business rules may be too complicate to warrant to duplication of them in the front-end. Things like mandatory fields, foreign keys etc are simple to enforce, but you may have more complex dependencies.


    Erland Sommarskog, SQL Server MVP, www.sommarskog.se
    Post #1491157
    Posted Wednesday, September 4, 2013 7:33 AM
    SSC-Enthusiastic

    SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

    Group: General Forum Members
    Last Login: Thursday, March 6, 2014 1:35 AM
    Points: 175, Visits: 547
    Are you able to put a try catch block around your SP and use SCOPE_IDENTITY()
    http://msdn.microsoft.com/query/dev10.query?appId=Dev10IDEF1&l=EN-US&k=k%28SCOPE_IDENTITY_TSQL%29;k%28SQL11.SWB.TSQLRESULTS.F1%29;k%28SQL11.SWB.TSQLQUERY.F1%29;k%28DevLang-TSQL%29&rd=true




    For better, quicker answers on T-SQL questions, read Jeff Moden's suggestions.

    "Million-to-one chances crop up nine times out of ten." ― Terry Pratchett, Mort
    Post #1491296
    Posted Thursday, September 5, 2013 5:17 AM
    SSC-Addicted

    SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

    Group: General Forum Members
    Last Login: Yesterday @ 5:53 AM
    Points: 433, Visits: 990
    In your stored proc, why not simply check for rows which will cause an error (datalength > 2, in this case) , select them and return a message to the client?


    Post #1491719
    « Prev Topic | Next Topic »

    Add to briefcase

    Permissions Expand / Collapse