• Replies 3 replies
  • Subscribers 337 subscribers
  • Views 92 views
  • Users 0 members are here
Related

NCOA Processing report (v11) import problem

Jason Kaiser
$organization

Has anyone been having problems with the NCOA processing report since moving to v11?   We are using MBS to process the data as we normally do.  Exporting worked fine, but I keep getting the following error when trying to bring the processed data back in with the Import mode:

Select Error: SQLSTATE = 42000    Microsoft OLE DB Provider for SQL Server    D:\Tessitura\import_data.txt  is not a valid file or the process is unable to access the file.

Seemingly, there is only one parameter changed (selecting street2 or street3) in v11.

I have tried sticking the file in several different locations, both physical and network paths, to no avail.  I have also tried giving full directory access to Everyone without any luck.   One caveat is that I did a live-to-test after exporting so that I could do the import in Test before executing in live.  I don't see what this would cause a failure, but it is one difference in the otherwise standard process we follow.

I feel like I may be missing something obvious here...or not....any ideas?

 

Parents
  • $organization

    I have narrowed this down to the procedure AP_BCP_FILE, which is called during the report execution.  Specifically the following section taken from the procedure is triggering my error:

     --Check FilePath first
     if @bcp_in_out = 'in'
       begin
      Exec master.dbo.xp_fileexist @file_name, @FileExists OUTPUT
      If @FileExists <> 1
        Begin
       Select @error_message =  @file_name + ' is not a valid file or the process is unable to access the file.'
       select @return_value = -101
       Raiserror(@error_message, 11, 2)
        End
       end

    Interestingly, if I simply call the procedure xp_fileexist against my import file path maually via a new query window, it validates to 1, which in the context of the code above would actually check out as OK and "pass" the file existance check. 

Reply
  • $organization

    I have narrowed this down to the procedure AP_BCP_FILE, which is called during the report execution.  Specifically the following section taken from the procedure is triggering my error:

     --Check FilePath first
     if @bcp_in_out = 'in'
       begin
      Exec master.dbo.xp_fileexist @file_name, @FileExists OUTPUT
      If @FileExists <> 1
        Begin
       Select @error_message =  @file_name + ' is not a valid file or the process is unable to access the file.'
       select @return_value = -101
       Raiserror(@error_message, 11, 2)
        End
       end

    Interestingly, if I simply call the procedure xp_fileexist against my import file path maually via a new query window, it validates to 1, which in the context of the code above would actually check out as OK and "pass" the file existance check. 

Children
  • $organization in reply to Jason Kaiser

    That suggests to me either a permissions problem or a formatting problem with the file.  Be warned, BCP does not fail gracefully.  I've never successfully managed to rollback a transaction from a BCP error, and my experiences with NCOA processing suggest that often user-owned views will be generated and linger in the system on file access failures.  These should be sought out and deleted.

  • $organization in reply to Gawain Lavers

    Thanks for the reply.  

    On a whim, I executed the Import in Live and guess what, it imported sucessfully....well almost, the generated report crashed; however, checking tw_ncoa_incoming for my session indicates all records there.  So this seems to be an issue specific to test not being able to bcp for some reason...probably permissions like you said.

    I think at this point what I will do is another live-to-test to get the imported data into test so that I can process it there. 

    My brain hurts.