Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Finding row number which caused the error in Table value parameter insertion


Finding row number which caused the error in Table value parameter insertion

Author
Message
SQL006
SQL006
SSC Veteran
SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)

Group: General Forum Members
Points: 222 Visits: 1295
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
Erland Sommarskog
Erland Sommarskog
SSC Eights!
SSC Eights! (931 reputation)SSC Eights! (931 reputation)SSC Eights! (931 reputation)SSC Eights! (931 reputation)SSC Eights! (931 reputation)SSC Eights! (931 reputation)SSC Eights! (931 reputation)SSC Eights! (931 reputation)

Group: General Forum Members
Points: 931 Visits: 866
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
SQL006
SQL006
SSC Veteran
SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)

Group: General Forum Members
Points: 222 Visits: 1295
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)
Erland Sommarskog
Erland Sommarskog
SSC Eights!
SSC Eights! (931 reputation)SSC Eights! (931 reputation)SSC Eights! (931 reputation)SSC Eights! (931 reputation)SSC Eights! (931 reputation)SSC Eights! (931 reputation)SSC Eights! (931 reputation)SSC Eights! (931 reputation)

Group: General Forum Members
Points: 931 Visits: 866
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
SQL006
SQL006
SSC Veteran
SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)

Group: General Forum Members
Points: 222 Visits: 1295
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.
Erland Sommarskog
Erland Sommarskog
SSC Eights!
SSC Eights! (931 reputation)SSC Eights! (931 reputation)SSC Eights! (931 reputation)SSC Eights! (931 reputation)SSC Eights! (931 reputation)SSC Eights! (931 reputation)SSC Eights! (931 reputation)SSC Eights! (931 reputation)

Group: General Forum Members
Points: 931 Visits: 866
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
mister.magoo
mister.magoo
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2266 Visits: 7821
Surely this is a case for validation at the front end?

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

MM


select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);




  • 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

  • Erland Sommarskog
    Erland Sommarskog
    SSC Eights!
    SSC Eights! (931 reputation)SSC Eights! (931 reputation)SSC Eights! (931 reputation)SSC Eights! (931 reputation)SSC Eights! (931 reputation)SSC Eights! (931 reputation)SSC Eights! (931 reputation)SSC Eights! (931 reputation)

    Group: General Forum Members
    Points: 931 Visits: 866
    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
    Dennis Post
    Dennis Post
    SSC-Enthusiastic
    SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)

    Group: General Forum Members
    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
    schleep
    schleep
    Mr or Mrs. 500
    Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)

    Group: General Forum Members
    Points: 520 Visits: 1286
    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?



    Go


    Permissions

    You can't post new topics.
    You can't post topic replies.
    You can't post new polls.
    You can't post replies to polls.
    You can't edit your own topics.
    You can't delete your own topics.
    You can't edit other topics.
    You can't delete other topics.
    You can't edit your own posts.
    You can't edit other posts.
    You can't delete your own posts.
    You can't delete other posts.
    You can't post events.
    You can't edit your own events.
    You can't edit other events.
    You can't delete your own events.
    You can't delete other events.
    You can't send private messages.
    You can't send emails.
    You can read topics.
    You can't vote in polls.
    You can't upload attachments.
    You can download attachments.
    You can't post HTML code.
    You can't edit HTML code.
    You can't post IFCode.
    You can't post JavaScript.
    You can post emoticons.
    You can't post or upload images.

    Select a forum

































































































































































    SQLServerCentral


    Search