Bulk insert

• Subramanyam Krishnamurthy-120784

  SSC Enthusiast

  Points: 123

  More actions

  February 13, 2006 at 2:38 am

  #110979

  When I try to execute SP, which contains Bulk insert command, the contents of the file are processed  correctly. Later there is a graceful exit from the SP.

  However when the file can not be found, then the bulk insert terminates the SP/Batch and the error is not trapped. (I mean error number is not available in @@Error).

  Is there any way we can trap this error?

  ---

  Kindest Regards,

  Mani
  

• Subramanyam Krishnamurthy-120784

  SSC Enthusiast

  Points: 123

  More actions

  February 13, 2006 at 10:06 pm

  #620573

  Same was happening with xp_cmdshell as well. But using no_output option did resolve the issue with xp_cmdshell.

  Any help in this regard is much appreciated.

  ---

  Kindest Regards,

  Mani
  

• rangab

  Old Hand

  Points: 303

  More actions

  February 14, 2006 at 11:24 am

  #620786

  you can use a xp_fileexists function to check whether the file exists or not(EXEC master..xp_fileexist 'c:\boot.ini') , it will return 3 columns , if file exists file exists column returns 1 and 0 if file not exists,

  example code:

  create table #fileinfo(

  FileExists bit , IsDirectory bit ,ParentDirectoryExists bit)

  Insert #fileinfo(FileExists,IsDirectory,ParentDirectoryExists)

  EXEC master..xp_fileexist 'c:\boot.ini'

  IF (select FileExists from #fileinfo) = 1

  begin

         BULKINSERT .....logic here

  end

  else

  begin

           raiserror ()....logic here...

  end

   

  
  bondada