Skip to main content

In our application there is a task “Importeren calculatiebestand (CUF-XML)” that should create a master record and multiple detail records based on the given task parameters.
 

 

The master record will be created based on the information underneath the header “Standaard optie” and the detail records should be created based on the content of the XML file. The XML data is imported with the following code:

 

 EXEC   ('      INSERT INTO import_xml      (         guid,         bron,         xml_data,         volledige_bestandsnaam,         ingevoerd_op,         ingevoerd_door,         gewijzigd_op,         gewijzigd_door      )      SELECT ''' + @import_xml_guid + ''',             ''CUF'',             xmldata,             ''' + @bestand + ''',             ''' + @datum_tekst + ''',             ''' + @gebruikersnaam + ''',             ''' + @datum_tekst + ''',             ''' + @gebruikersnaam + ''' FROM      (          SELECT *          FROM OPENROWSET (BULK ''' + @bestand + ''' , SINGLE_BLOB) AS xmldata      ) AS FileImport (XMLDATA)   ')

 

With a Windows GUI everything works fine. The problem appears with the web GUI.

The XML file can be seleced with the control File upload. I checked the folder .\App_Data\RadUploadTemp\ from the web GUI and the file is being uploaded as a temp file. But after executing the task, I got the following error message:

 

Cannot bulk load because the file "……..\DemoJPDS\CUF-XML\Extra heipalen.xml" could not be opened. Operating system error code 5(Access is denied.)

 

The XML file is not moved from the folder RadUploadTemp to the file storage location that is linked to the domain bestand_upload (type of the task parameter that contains the XML file). When I remove all code from the acual task, then the XML file is moved to the storage location.


I thought of pausing the task with WAITFOR DELAY before the starting the bulk import. But that did not make any difference.


Both the application pool user as well as the service account for SQL Server have sufficient rights in the specified folder. The application pool user also has the role bulkadmin.


I have seen the issue "Access denied when uploading file to folder without edit rights” (https://community.thinkwisesoftware.com/development-13/access-denied-when-uploading-file-to-folder-without-edit-rights-773) as well, but I could not see any simularity with this problem.


What can I do to fix this “Access is denied” error?

I think we do something similar, uploading (xml) files and process them from a industry format to the application model. For this we don't use file storage location (shares or whatever), but a  database storage location. The webgui (application pool) doesn't need additional permissions for this because it's just simply passed as a parameter when doing this in a task.

If you don't necessarily need to save the file (on disk) you could consider this. (Or you could save the file in the database anyway).

Hi René,

Thank you for your reply.

I do not need the file in the file system, so storing it in the database is an option. But this requires rewriting the import process, right? Or can I use the bulk import as shown above with the file saved in the database?

On the other hand: Both the application pool user and the SQL Server account have modify rights on the folder (storage location). This should work also, so I think this is a bug. It looks like the moved file (from temporary folder to the folder defined in the storage location) is still in use when the task code is being executed.

But this requires rewriting the import process, right? Or can I use the bulk import as shown above with the file saved in the database?

It doesn't require a major rewrite, you can omit the bulk import. You probably only need a cast from varchar(max) (which is the file type of the file upload domain) to xml. Same applies for JSON which can be parsed via SQL Server.

If you have another obscure file type (i.e. csv or tabular data) you could consider using an additional web service parsing the obscure file type to XML or JSON which can be processed by SQL Server.

I’ll consider this. Thank you for your response @René W!

Hello Johan,

The problem you are experiencing is a bug. I've created a tcp issue in your name (79570). We can discuss the bug in TCP
René's workaround sound like a good idea, as that way you also don’t have to periodically delete your upload folder.

 

Terms & ConditionsCookie settings