One of the differences between the new OOXML file formats (e.g. .xlsx, .xlsm, .docx, .docm) that were released with Office 2007 and the older binary file formats (eg. .xls, .doc) is that each OOXML file extension has a unique Multipurpose Internet Mail Extension (MIME) type. This is a departure from the MIME types for the binary file formats, where the same MIME type could apply to several file extensions.

For example, the binary “application/vnd.ms-excel” MIME type applies to the .xls, .xlt, and .xla file extensions, but OOXML .xlsx and .xltx have separate MIME types: “application/vnd.openxmlformats-officedocument.spreadsheetml.sheet” and “application/vnd.openxmlformats-officedocument.spreadsheetml.template,” respectively.

An unexpected issue

The change in MIME types can cause unexpected issues to occur. I recently helped a customer who was experiencing strange behavior in his upload application. In his application, he was using FileUp to upload files to a SQL server database, in addition to some information about the files. When the customer tried to upload an XLS file, the upload completed successfully, but if he tried to upload an XLSX file, he started getting error messages.

Oddly enough, if he renamed the file to use the XLS file extension, the upload completed without error, but the file name in the data base was obviously incorrect.

What is going on?

When trying to perform this upload in an ASP.NET application using a SqlConnection, the error message was:

An error occurred: String or binary data would be truncated. The statement has been terminated.

This error was thrown because the customer was trying to insert a string with the file MIME type (also referred to as the content type of the file) into a VARCHAR(50) field. Since the MIME type of an .xls file is “application/vnd.ms-excel”, which is only 25 characters long, there was no issue inserting the MIME type of the uploaded file into the data base column. The issue came when trying to upload an .xlsx file because the .xlsx MIME type is “application/vnd.openxmlformats-officedocument.spreadsheetml.sheet”, which is 65 characters long.

Since the length of the MIME type was longer than the VARCHAR field allowed, the data would have been truncated, hence the error message.

Solution

To resolve the issue, the customer changed his column data type from VARCHAR(50) to VARCHAR(MAX) to accommodate for the longer MIME types.

Troubleshooting this issue in Classic ASP

When I tried to reproduce this issue in a classic ASP application with an OleDBConnection the error message returned was a bit more cryptic:

Microsoft SQL Native Client error ‘80040e21’
Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

The “Multiple-step” error message is a generic error that meant several things went wrong while connecting and working with the data base. In this case it was because there was a content mismatch because the data being inserted was larger than the data type of the column allowed. The “Multiple-step” error can also occur if there is a problem with the connection string, or if there is a problem with the OLEDB_SERVICES registry entry.

Other resources

Here is a list of the MIME types for the OOXML file formats:

Follow

Related posts: