Experience from the Field: Resolving the OMPM issue “Type of column ‘WarningInfo’ in table ‘osWarning’ is too small to hold data”

My name is Anthony Cafarelli and I’m a Consultant with Microsoft Consulting Services who recently compiled a list of useful advice that I gained while performing OMPM scans at client sites. In this blog post I would like to share some of that knowledge and I hope that anyone reading it finds it useful!

The issue I am going to focus on in this blog post is an import error. This error is caused when the scanned files have very long file names, in excess of 250 characters. Thus it isn’t an extremely common error but these steps will help you address the import if you do find this situation. When importing data into the OMPM database you may receive the following error:

Type of column ‘WarningInfo’ in table ‘osWarning’ is too small to hold data

What this error indicates is that the file name and path in an XML file you are trying to import is too long for the “Warninginfo” field in osWarning table. Because of this length issue, the information is not imported into the database and the XML file is skipped. Typically this shows up with a Last Accessed or Last Modified date warning, so the files usually wouldn’t be a concern to not have in the database. However, if it was part of a .cab file containing multiple XML files (and most likely it is, and most likely that CAB contains 10,000 files unless you modified the offscan.ini file to change those settings). And, this is important to note, if any XML file contained in a CAB file cannot import, then none of those files make it into the database. At this point, you have a few options:

1)      Ignore that the CAB file did not import and base your results on the other CAB/XML files that did import correctly.

2)      Extract the CAB file and import each XML.

3)      Modify the database.

If option 1 is acceptable, there is nothing more for me to say here. That is the easiest option, but you do lose a significant amount of data that may be useful.

Option 2 is an interesting one. Out of the 2 options I listed that address the issue, this is one that has a lot of work required for the technician, but significantly increases the import time into the database.

Just to give a little background in a simplified way: The reason why the entire CAB doesn’t import when a single XML file has an error is because of the way OMPM does the import. The CAB is extracted by the import process and every XML file is parsed all at once. This significantly (VERY significantly) speeds up the import of a CAB file but also reduces the ability to address errors.

When you extract the XML files you will be able to get the (on average) 9,999 other XML files imported into your database. I haven’t actually timed it and compared it, but I would say the import of the individual XML files is at least 10x slower, if not more. There is another way to increase the import speed, but it involves more work from the technician (and this is my preferred method because I hate modifying the database due to the supportability concerns, and I will get into that a little more below). Here is the modified option 2:

1)      Extract the CAB file.

2)      Use the findstr command to locate the extracted XML file that is the one with the error.

3)      Delete that XML file.

4)      Re-package the CAB file with the remaining files.

With this method, you maintain the high import speed and address the file with the long name. Using findstr and deleting the XML file are pretty straight-forward, so I’m not going to get into them. But, finding a good way to re-package the CAB can be tricky. My best advice is to go to this page (yep, another TechNet page) and implement the PowerShell scripts listed:

https://technet.microsoft.com/en-us/magazine/2009.04.heyscriptingguy.aspx?pr=blog

Once you’ve re-compressed into another CAB file, you can import it and still maintain your high speed import! Pretty good trick, right?

Now let’s talk about Option 3. I have very mixed emotions about this one. It’s easy and it’s effective but it definitely pushes supportability. The simple explanation of this approach is this: The oswarning field in the database isn’t big enough to hold the data we are trying to put into it, so let’s just make the bucket larger. I found 2 methods for doing this. And apparently, based on what I have written so far, I love numbered lists, so here’s another one:

1)      Use SQL Management Studio to modify the field size.

2)      Modify the files that OMPM uses to create the database so that every database you create has the larger field size to start.

Using SQL Management Studio is fairly straight-forward, but depending on your version of SQL it can be slightly different. I’m not going to get in depth into that solution, so if you are unsure of it, either find your favorite SQL resource (friend, colleague, book, blog, etc.) and research it or use the second method.  

The second method requires you fire up a text editor. Notepad.exe is my favorite, so that’s what I’m going to use as an example. Start notepad and open ProvisionDB.sql in the OMPM/Database/Include folder.

Once you have the file opened search for “oswarning” and click Find Next.

You will see the following:

Here you will see the WarningInfo field (at 255 characters). Simply change the number to something higher (let’s say 285) and save the file. Now every time you run the createdb command, the new database will have a larger field. NOTE: This will not modify your existing databases, so make sure you create a new database and run your imports there. You’ll want to pull any files out of the OMPMimported folder that you imported into the old database so you can re-import then into the new one.

The Office Compatibility team is aware of this limitation and is reviewing this challenge for future updates.

Hope this was helpful for anyone reading it! I plan on writing a few more blog posts about other issues and “creative” resolutions I found in the field.

Anthony

Comments

• Anonymous
  January 01, 2003
  Hi Andy, I've seen this error reported once before and never could track down the issue. I know you have a lot of effort invested in OMPM and I'm sorry it's not working flawlessly for you. Any chance you'd be interested in trying Telemetry Dashboard instead?

• Anonymous
  January 01, 2003
  Andy, I'm asking around for you. I searched my email archives and didn't get any hits. I'll let you know what I hear.

• Anonymous
  January 01, 2003
  Matthew, you can post your OMPM experiences on this blog or in the Office Compat forum here: social.technet.microsoft.com/.../threads What were the issues you hit with SQL and Server 2008 R2? I believe we tested on these versions for the recent update to OMPM in late July 2011.

• Anonymous
  September 04, 2011
  I also found a similar issue with the column "ErrorInfo" in the osError table.  Applied the same fix as per above by increasing the nvarchar field and it fixed the issue. BTW - any sort of Forum or Blog I'd be able to post some of the issues I've had to deal with in using OMPM so others don't have to waste a week or so with some of the "unwritten" issues that I've had?

• Anonymous
  September 05, 2011
  I also found the above fix worked for another similar error ("Type of column ‘ErrorInfo’ in table ‘osError’ is too small to hold data") by expanding the ErrorInfo field in the table. Now, if only I could rewind about 2 weeks to know this plus other things with OMPM I would have saved myself plenty of time.  No.1 time saver would have been to always install SQL Server 2005 on 32-bit Windows Server 2003.  Anything above that was a major issue - especially 64-bit.  Any chance the OMPM team might be working on a product that import successfully with Server 2008 R2 and SQL Server 2008 R2?

• Anonymous
  November 14, 2012
  Has anyone come accross the HRESULT = 0x80070008 error in the xml logs created whilst running a scan? Need some advise on what this is and how to fix it. Thanks

• Anonymous
  November 16, 2012
  The comment has been removed

• Anonymous
  December 06, 2012
  Eveer seen this? I have imported some OMPM scans into the SQL Express database (1 database per scan) and on one of them, it reports that the import was successful and by using the SQL Management Studio I can see that it was going by the size of the DB but after connecting to the database which it seems to do OK when using OMPM.accdr there is no data.  The SCan ID shows in the drop down but there are 0 records. It also sometimes gives the following error message: ODBC --CALL FAILED. [Microsoft][ODBC SQL Server Driver]Query timeout expired (#0) Can anyone please advise? Thanks

• Anonymous
  March 13, 2013
  Thanks for the information. Also try Long Path Tool. It helped me with Error 1320 on Win 7. :)