CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Alex Gordon
Alex Gordon

Reputation: 60882

Show all errors that will occur in SQL Server

I have a huge bulk insert that causes errors:

DECLARE @row_terminator CHAR;
SET @row_terminator = CHAR(10); -- or char(10)

DECLARE @stmt NVARCHAR(2000);

SET @stmt = '
  BULK INSERT accn_billed_procedures
   FROM ''F:\2011-Dec\accn_billed_procedures_201112062112.txt.gz''
   WITH 
      (
        firstrow=2,
FIELDTERMINATOR = ''|''  ,
ROWS_PER_BATCH=10000
   ,ROWTERMINATOR='''+@row_terminator+'''
   )'
exec sp_executesql @stmt;
SET @stmt = '
  BULK INSERT accn_billed_procedures
   FROM ''F:\2011-Dec\accn_billed_procedures_201112072112.txt.gz''
   WITH 
      (
        firstrow=2,
FIELDTERMINATOR = ''|''  ,
ROWS_PER_BATCH=10000
   ,ROWTERMINATOR='''+@row_terminator+'''
   )'
exec sp_executesql @stmt;
SET @stmt = '
  BULK INSERT accn_billed_procedures
   FROM ''F:\2011-Dec\accn_billed_procedures_201112082112.txt.gz''
   WITH 
      (
        firstrow=2,
FIELDTERMINATOR = ''|''  ,
ROWS_PER_BATCH=10000
   ,ROWTERMINATOR='''+@row_terminator+'''
   )'
exec sp_executesql @stmt;
SET @stmt = '
  BULK INSERT accn_billed_procedures
   FROM ''F:\2011-Dec\accn_billed_procedures_201112092112.txt.gz''
   WITH 
      (
        firstrow=2,
FIELDTERMINATOR = ''|''  ,
ROWS_PER_BATCH=10000
   ,ROWTERMINATOR='''+@row_terminator+'''
   )'
exec sp_executesql @stmt;
SET @stmt = '
  BULK INSERT accn_billed_procedures
   FROM ''F:\2011-Dec\accn_billed_procedures_201112102112.txt.gz''
   WITH 
      (
        firstrow=2,
FIELDTERMINATOR = ''|''  ,
ROWS_PER_BATCH=10000
   ,ROWTERMINATOR='''+@row_terminator+'''
   )'
exec sp_executesql @stmt;

I would like to know all the errors (or at least a lot of the errors) that are cause.

it doesnt matter to me if it will require me to run everything in order to get the list of errors.

I just want to whether it is possible to show all errors that a sql statement will generate in sql server 2008?

Upvotes: 0

Views: 265

Answers (2)

podiluska
podiluska

Reputation: 51514

BULK INSERT has an ERRORFILE parameter. ( http://msdn.microsoft.com/en-us/library/ms188365.aspx )

Upvotes: 1

MataHari
MataHari

Reputation: 318

http://msdn.microsoft.com/en-us/library/ms175976.aspx

BEGIN TRY
    -- Table does not exist; object name resolution
    -- error not caught.
    SELECT * FROM NonexistentTable;
END TRY
BEGIN CATCH
    SELECT 
        ERROR_NUMBER() AS ErrorNumber
        ,ERROR_MESSAGE() AS ErrorMessage;
END CATCH

Upvotes: 2

Related Questions