We recently added high DPI support to some of our applications so that they render better when user displays are scaled to 125% or more. We may have missed 1 or 2 items, so if you encounter any GUI elements that are oversized or undersized, we would appreciate it very much if you could let us know at support@yohz.com.
The applications we’ve added high DPI support for are:
Never thought this would happen, but I was stumped by my own application for a good 24 hours before realizing what was happening. I’m getting too old for this ****
A user wrote in asking why SQL Image Viewer could identify XML content in his SQL Server table, but he could not use his database’s XML functions to query the XML data.
No problem, or so I thought. Using a subset of the database he sent, I could see the first few bytes of the XML content and also query the size in SSMS:
Ok, so that’s about 119 Kb of data. In SQL Image Viewer, the following is returned:
So far so good, SQL Image Viewer identified the XML content. Now what happens when I try to use the XML function in SSMS:
SQL Server raised an error: XML parsing: line 1, character 2, illegal xml character
Ok, so what’s wrong with character 2? Back in SQL Image Viewer, I opened the hex viewer, and the XML header looked fine:
Ok then, I decided to export the XML file to disk, and could open it in my browser. Then it stuck me that the file was over 2 MB in size, but the blob size was only 119 Kb.
What was going on here? In the hex viewer I scrolled to the end of the XML content, and it too was indicating the content was about 2.6 MB in size.
Why was SSMS and SQL Image Viewer reporting the blob size as 119 Kb, but when viewed in the hex viewer or exported, the content was closer to 2.6 MB in size?
This being a SQL Server database, my first thought was that row or column compression was in place. However, the compression/decompression would have been done transparently and wouldn’t be showing up this way. I checked anyway, and sure enough, no row/column compression was active.
Feeling rather lost, I explained the situation to the user hoping they might shed some light on what was going on. Later, in the shower, while clearing my head (literally and figuratively), it stuck me: zlib streams.
Basically, if the blob content is a zlib compressed stream, SQL Image Viewer automatically decompresses the stream, identifies the content type, and lets you work with the decompressed data. That was exactly what was happening here – the zlib compressed stream of 119 Kb was being uncompressed to 2.6 MB, identified as an XML file, and subsequent viewing and exporting allowed me to work with the uncompressed data. I had totally forgotten about this feature!
This also explained why the XML functions could not work directly on the blob content – it was a zlib stream and not a XML text file.
Next time, I should compare the first few bytes as displayed in SSMS against the values displayed in SQL Image Viewer. It would then have been immediately clear that we were working with a compressed stream.
And that is how my own application fooled me (or my memory is just getting poorer).
SimpleIndex is an application that stores PDF files in SQL Server databases. The PDFs are stored in the General table, in the Image column. That column is of the SQL Server image type, generally used to store binary data (or blobs).
We recently had a user that could not export the PDF files from his SimpleIndex database. The user was very helpful to send us the original PDF file, and the PDF file content as stored in his database for comparison. Turns out that SimpleIndex first converts the binary data in your PDF files to a unicode text string, then stores this unicode text.
This is certainly a strange way to go about storing a file in a column that has a data type that’s perfectly suited to storing binary files. In addition to making it difficult to extract the PDF file, it doubles the storage requirements. Whatever the reason, SQL Image Viewer could not identify and display the PDF files.
So the first thing we did was to convert the exported ‘mangled’ PDF files back to their original state. We added this feature to our UTF8Tool application.
Now the user could export the content from his database, then use this tool to convert those files into proper PDF files.
However, the user wanted to be able to query and view the PDF files from within SQL Image Viewer. We ended up with the easiest option – we created a second column to store the proper PDF content, then create an application to convert the mangled PDF files from the first column and store them in this new column.
It’s a very basic unpolished conversion application, and you can download and use this application freely.
IMPORTANT NOTE: This application updates a column in your database, so please make a backup of your database first, in case things don’t turn out exactly the way you want it.
Currently, this application supports only SQL Server databases. If you need a version that supports another database engine, or if you require some modifications, drop us a line at support@yohz.com.
Now that the user could display the PDF directly in SQL Image Viewer, we had another issue. By default, SQL Image Viewer only displays the first page of each PDF file that it detects e.g.
This made it difficult to review each PDF file in details. Fortunately, SQL Image Viewer (professional edition) supports custom layouts. With custom layouts, you can choose to display a specific number of pages from your PDF files, and also the size of the pages e.g.
Now the user could easily preview more of his PDF files prior to exporting them.
In summary:
We recently had a user who needed to extract files from a SQL Server database created by ProgressCRM and needed help. He was helpful enough to send us a couple of the sample files that were not extracted correctly.
It turned out that each file that was being uploaded was stored in an OLE container. Normally, our products (SQL Image Viewer, Access OLE Export, and SQL Blob Export) can extract the embedded files, but in this case, the entire OLE container itself had a 12-byte prefix, most likely added by ProgressCRM for their internal use.
So the task was to first extract the content without the 12-byte prefix, and then let SQL Blob Export handle the extraction of the files from the OLE container.
Luckily, this was easy enough to do in a SQLServer database. We could use the SUBSTRING command to retrieve only the content from the 13-th byte onwards e.g.
SELECT SUBSTRING(CAST(<the column containing the binary data> AS VARBINARY(MAX)), 13, DATALENGTH(content)) FROM <the table name>
So if you find that your application is uploading your files with their own prefix data, you can use the SUBSTRING function (in SQL Server) to remove the prefix so that our products can recognize the actual file content and export them for you correctly.
Recently we had a user that needed to extract PDF files stored in a SQL Server nvarchar(max) column. Now, you wouldn’t normally be able to store binary files correctly in a text column, but the application they used (Deacom) first encoded the PDF file to UTF-8 text, then stored the UTF-8 text into the text column.
While SQL Image Viewer (and SQL Blob Export/Access OLE Export) could extract the UTF-8 encoded data from the table and store it in a file, the resulting PDF file cannot be opened because the data contained therein is invalid. So we had to provide a small utility to convert the UTF-8 encoded file back to its original contents. You can read more about the utility here.
After converting the PDF files, the user then compressed the PDF files and now wanted to upload the PDF files back into the database. They used SQL File Import to perform the upload, but first the PDF files had be to UTF-8 encoded again, otherwise the application (Deacom) could not open the PDF files. That can also be one using the above utility.
Finally, we had one last issue where only 8 Kb of the encoded PDF file was being uploaded. The cause was traced to the use of the MDAC/ODBC driver to connect to SQL Server.
In our database products, when you connect to a SQL Server instance, the default is to let the application select the best available drivers.
By design, our products will use the SQL Server Native Client drivers if available, and only use MDAC/ODBC drivers when the Native Client drivers are not available. However, MDAC/ODBC drivers limit text fields to only 8000 bytes.
When the user attempted to use the Native Client driver, the connection failed.
Installing and using the SQL Server Native Client drivers then resolved the connection and upload issue.
We recently fixed a bug in SQL Image Viewer where locking data sets was not working and resulted in errors. It would seem that not many users are aware of this useful feature, so here’s more details on what locking does.
When you run a query and want to keep the results but need to run another query, your options in most SQL tools is to set up another connection and run the other query. Or run another instance of the application to be able to run the other query.
In SQL Image Viewer, you can lock the current data set so that it is not replaced by the results of the new query. Say we want to keep this data set containing 99 rows in SQL Image Viewer:
Click on the Lock data set button to keep that data set.
Once we do that, the page caption changes to indicate the data set has been locked.
Now when we run our other query, its results is displayed on a different tab.
We can then easily switch between the 2 data sets to view/compare the results. You can lock as many data sets as you require, subject to having enough storage on your computer.
To unlock a data set, select the data set, then click on the Unlock data set button button. The data set will be immediately released and closed.
So basically locking allows you to persist data sets across queries, instead of having to create additional sessions.
Access has a curiously interesting data type – attachment.
I say interesting because it does so much, but much of it is done behind the scenes. To the user, it’s just so easy to attach one or or more files to that column.
For starters, some file types seem to be compressed automatically by Access. I attached a 23 MB text file, and the database size only increased by 3 MB, which is about the size of what my text file would have compressed to.
Also, a single attachment-type column contains 4 sub-columns in them – FileName, FileType, FileData and FileURL (for Sharepoint databases). These are the sub-columns we need to include in our query in SQL Image Viewer when we want to extract the files from the attachment-type columns.
Say we have a table defined as such:
with the following data:
Row 1 has 1 attachment, and row 2 has 2 attachments. If we run a query that selects just the 3 columns from the table in SQL Image Viewer, we get this:
The files column, which is the attachment-type column, displays only the names of the attached files, and is returned as a text column. If however we selected the sub-columns:
Access returns 3 rows instead of 2. Because our 2nd row contained 2 attachments, Access returns 2 data rows, one for each attachment.
Thus, to extract the data from the attachment-type columns in SQL Image Viewer, we need to select the filedata sub-column of the attachment-type column.
Since we added support for attachment-type columns in SQL Image Viewer 11, the same query above now returns this:
Download a 14-day trial of SQL Image Viewer now and experience how easy it is to extract attachments from your Access databases.
We recently had a user who had his SQL Server hosted in a data center, and needed to extract files from his database. He could connect to the instance remotely, but it took too long to extract the files this way. He tried connecting to the database via another computer in the data center, but received the ‘TCP Provider: An existing connection was forcibly closed by the remote host.‘ error.
This suggests a network configuration setting issue. Instead of getting the data center to change the server settings, we instead set up an ODBC connection and used that connection in SQL Blob Export.
Using Windows authentication to connect to the instance via ODBC was easy enough. However, if using SQL Server authentication, the password is not stored in the ODBC settings, and the connection would still fail.
In order to connect to the SQL Server instance via ODBC using SQL Server authentication, choose the Connection string option instead.
Click on the Browse button
and select the ODBC connection to your SQL Server database.
The SQL Server ODBC connection manager will then prompt you to enter the password.
Enter the password in the provided area and click OK. The ODBC connection string is then filled out, together with your password.
Click on the Connect button. SQL Blob Export should now successfully connect to your SQL Server database using SQL Server authentication.
The same steps apply to our other database products that support SQL Server and ODBC connections.
If you require further assistance, you can post your questions in our support forum.
In SQL Image Viewer 10.3, and Access OLE Export/SQL Blob Export 3.18 we added the option to redirect where to look for OLE-Object linked files during the export process.
A common issue when using OLE-Object linked files is that the location of the linked files have changed. For e.g. you may have previously linked files from the g:\customer_invoices\ folder, but say those files have now been moved to g:\archive\customer_invoices\2018\.
Previous versions of SQL Image Viewer will fail to locate these items during the export process. In version 10.3, you will now find a redirect button in the Image and File Options screen.
In Access OLE Export/SQL Blob Export, you will find the Redirect button in the Options screen.
Click on that button to bring up the Redirect window. Here, you can define how the old folders map to the new folders. For our previous example, we would enter the following:
Now during the export process, when SQL Image Viewer fails to find the files in the original folder, it will look to the redirection rules defined here and look in the new folder for the files. It will then export the files that it finds. You can enter multiple redirection entries, and each entry must be on a new line.
Note that the redirection entries are essentially a search/replace process. Anything entered prior to the redirection symbol (>) is replaced with the value entered after the symbol. Thus, the entries are not just limited to redirecting entire paths.
In SQL Image Viewer 10.3, we added support for Navision compressed files stored in the Incoming Document Attachment table.
When you run a query on the Incoming Document Attachment table, the Content field will display the following if it contains compressed data.
To extract the contents, click on the Export images and files button.
This opens the Export images and files dialog.
Walk through the steps, and SQL Image Viewer will extract and export the files stored in the Content field for you.
Learn more about SQL Image Viewer here, and download a free 14-day trial now and see how SQL Image Viewer can export your Navision compressed content in minutes.