Hi Team,

I've issue with catch exception and printing it in the log file which I specified. All my print statements are going into that file except the one which are in the catch block.

I'm calling a procedure in Jobs.

  SET @sql = '
  BULK INSERT ' + DB_NAME() + '..' + @table_name + '
  FROM ''' +@currentInputFile +  '''
  WITH (
        FIELDTERMINATOR = ''' + @Delimiter + ''',
     KEEPIDENTITY,
     DATAFILETYPE = ''char'',
     TABLOCK,
     MAXERRORS =20,
     ROWTERMINATOR=''' + @RowTerminator +''')'
  begin  try
     EXEC (@sql)
  end try

  begin catch

DECLARE @ErrorMessage NVARCHAR(4000)
SELECT @ErrorMessage = ERROR_MESSAGE()
print ErrorMessage

end catch

I'm calling this procedure in a job. In Job I gave Output file path where all my pront statement will log in to that file. But my error message in catch in not getting printed.

Please, help me how to catch exception into file. Its very urgent. Please help

Thanks in advance...

Fix:

print @ErrorMessage

You should log errors to a table not to a file.

You can export the table periodically to a file with bcp for example.

Actually I've modified thiis query from BCP to Bulk insert as I'm daily getting some error using BCP. So, Now every thing working fine But I'm unable to catch the exception.

The error which I'm getting BCP is  "

Error : SQLState = 08001, NativeError = 0

Error = [Microsoft][SQL Server Native Client 10.0]Unable to complete login process due to delay in opening server connection

Actually, this procedure will call data from files and inserts into tables. So, after loading some tables I'm getting above error. As a result that particualr table is not loading into DB

I increased timout value from 30 - 300secs. Still I'm getting connection timeout.

So, Alternatively I used Bulk insert. Now I'm not getting any problem in loading table. But if any bad data is there in any row while retriving date from file, that row is skipping. That is also fine.

But I need the reason why that one row is not inserted.

Example : A row has datefiled and its value is 12-02-2103. Then I need to get Datetime exception in log file. So, that we can come to know that due to datetime exception a row is not inserted into the table, which is not happening currently...

This is my problem. Please suggest

There should be no problem in catch to print statement in log file 

In your code 

print ErrorMessage

it should be 

print @ErrorMessage

you are missing @

Also try directly printing

print error_message() 

I run this code its logging print statement in log file 

BEGIN try
    RAISERROR ('abc',
               10,
               1)
END try

BEGIN catch
    PRINT Error_message()
END catch 

Amish Shah

http://blog.sqltechie.com

you can use bcp to log all errors to a file, by using the parameter -e err_file

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

you can output all the data including the error if you are using shell command and  '>> output.txt'

http://www.microsoft.com/resources/documentation/windows/xp/all/proddocs/en-us/redirection.mspx?mfr=true

I dont want BCP, I need it in Bulk insert please

may I know where that error message has been printed? Is it in some output file which is in ur disk, I need to that exception should be printed in a output file which I gave. All statements are pritning except which is in c