Post reply

How to ignore error in a SQL statements

  • November 9, 2008 at 7:01 am

    #383335

    Hi All,

    I am using multiple Bulk insert queries to import data from multiple text files in to a sql server table. The transactions is failing in the middle if f there is a error with any one of the file.

    I want to ignore the error to complete for rest of the transactions.

    Please advice me how to use ignore function.

    BR,

    Parthi

  • November 9, 2008 at 7:57 am

    #896042

    Hi Parthi,

    So you have a number of BULK INSERT statements wrapped in BEGIN TRANSACTION.... COMMIT/ROLLBACK? That implies that these BULK INSERT operations should happen either ALL of them or NONE of them. What you are asking for is telling us that this is not true so you should remove your transaction.

    If you are talking about errors WITHIN one single file, you can use the @maxerrors option of the BULK INSERT command to control the behaviour (depending on exactly what error you run into).

    HTH!

    /Elisabeth

    elisabeth@sqlserverland.com
    MCITP | MCT
    http://sqlblog.com/blogs/elisabeth_redei/
    http://linkedin.com/in/elisabethredei

  • November 9, 2008 at 11:40 pm

    #896126

    Hi Elisabeth,

    Thanks for your information..

    I was trying to use 400 Bulk Insert commands in query analyser without BEGIN TRANSACTION....ROLLBACK\COMMIT...

    I was getting error if the source text file didnt have complete data to import all columns in the table and not allowing other BULK INSERT commands to run.

    I tried using higher integer values for 'maxerrors' but it was not working.

    Please suggest me how to ignore this kind of errors,

    [Bulk Insert Task] Error: An error occurred with the following error message: "Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.Bulk load: An unexpected end of file was encountered in the data file

    BR,

    Parthi

  • November 9, 2008 at 11:52 pm

    #896128

    add "go" command after each bulk insert statement.

  • February 22, 2009 at 4:25 am

    #948266

    Instead of null just pass blank value. If you inserting from text file, replace all Null to blank. and execute the statement.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply