Tag Archives: SQL Image Viewer

Extracting binary data from a nvarchar(max) column!

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 Native Client drivers then resolved the connection and upload issue.

Locking data sets in SQL Image Viewer

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.

Extracting attachments from an Access database using SQL Image Viewer

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 beginning SQL Image Viewer 11, the same query above now returns this:

 

Connecting to SQL Server via ODBC using SQL authentication

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.

Redirecting OLE-Object linked files during export

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.

Exporting content from Navision Incoming Document Attachment table

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.

Setting up a SQL Image Viewer export task in Windows Task Scheduler

SQL Image Viewer Professional Edition allows you to save your export tasks and run them unattended using the SQL Image Viewer command line interface.  This blog describes how to set up a Windows Task Scheduler recurring task to run SQL Image Viewer export tasks.

You must first set up and save an export settings file using the SQL Image Viewer GUI, described here.   Before setting up the export task in Task Scheduler, we should make sure that the export task can be ran successfully from the command prompt.

To do that, open a command prompt window and navigate to the folder where the SQL Image Viewer command line interface executable file (SQLImageViewerCmd.exe) is located.

Enter the command line interface file name, and provide the fully qualified export settings file name as the first parameter.  You must enclose the parameter in double quotes if it contains spaces.

Run the command and check that the export task ran successfully.

Now we can start up the Task Scheduler.  We will create a basic task in this example.

Enter a name and description for the task.

Select your required frequency to run the task, and the frequency settings on the subsequent page.

Select the Start a program action.

In the program settings window, enter the fully qualified file name to SQLImageViewerCmd.exe, enclosed in double quotes.  For the arguments, enter the fully qualified file name to the export settings file, enclosed in double quotes.  For the Start in value, enter the fully qualified folder name of the folder where SQLImageViewerCmd.exe is located, without the double quotes.

And you’re done.

You can run the task to check if everything has been set up correctly.

A log file is automatically created every time the SQL Image Viewer command line interface runs.  The folder where the logs are stored is displayed in the GUI:

You would inspect the log files if your export task no longer runs successfully.

Using the column name in your file naming convention in SQL Image Viewer

In SQL Image Viewer 10.2, we’ve added a new option to the system values for your file naming convention.

Using this example:

Each row contains 2 binary-type columns.  We can name our exported images using the ProductPhotoID column, but to identify the source column for the images, we could previously only use the column index

resulting in file names like these:

The first part of the file name is the ProductPhotoID value, formatted to 4 digits, and the second part is the column index (columns 2 and 3).

From SQL Image Viewer 10.2 onwards, you can now use the column name as part of the naming convention.

which is used to name the exported files

You can select this new option (columnname) in the File Naming Convention page.

If you want to use a different value for the column name, simply use the alias syntax applicable to your database engine.  For SQL Server, this would be using the AS option e.g.

How to view image data type in SQL Server

The image data type in SQL Server is frequently used to store variable length binary data.  It does not have to store only ‘images’, though most people associate that data type with images.

You may occasionally need to view the type of data that’s stored, but SQL Server Management Studio can only display the binary representation of the data (we’re using the AdventureWorks sample database here).

SQL Image Viewer is one tool that can display the image directly in the result set.

Interesting points to note:

  • the image format was never provided.  SQL Image Viewer can read the binary data and determine the image format itself.  It recognizes png, gif, jpg, tiff, bmp, wmf, emf, dicom, and raw camera formats.
  • details of the image like size and resolution are provided automatically

Though its name might suggest otherwise, SQL Image Viewer doesn’t just support ‘images’.  It recognizes a wide variety of file formats, including Microsoft Office documents, OpenOffice documents, PDF, epub, multimedia files (avi, wav, mp3, mp4, svg, swf, flv, mkv, webm, m4v, mov, 3gp), and compressed archives (zip, 7zip, rar, bzip2, gzip).

For PDFs, SQL Image Viewer is able to display a thumbnail of the first page, and also the number of pages in the file.

Once retrieved, you can export the binary data.  You can export them to a spreadsheet:

or simply export them as individual files:

You can name the files using values from other columns.  You can also convert the exported images to other formats:

For images that contain EXIF values, you can view these values using the internal viewer:

SQL Image Viewer supports Oracle, PostgreSQL, MySQL, MariaDB, Microsoft Access, SQLite and ODBC supported databases.

Thus, you can easily view the data that’s stored in

  • binary and varbinary columns in Oracle databases
  • bytea columns in PostgreSQL databases
  • binary and varbinary columns in MySQL and MariaDB databases
  • binary and varbinary columns in Microsoft Access databases
  • OLE-Object columns in Microsoft Access databases

Download a free 14-day trial now and see how SQL Image Viewer can make working with image data types in SQL Server so much easier.

Using the newer Excel format (xlsx) in your exports

When SQL Image Viewer first supported exporting your result sets to Excel spreadsheets, it only supported the older .xls format.

Since version 9.7 (released in December 2019), the newer Excel format (.xlsx) is now supported.  However, because SQL Image Viewer uses your last export settings when you choose the Excel file name, you may still be using the older .xls format.

To export your data and images to the new Excel format, simply change the file extension from xls to xlsx.

For new users, and users who have never exported to Excel previously, the default extension used is xlsx,  so no action is required on your part to use the new Excel format.

The main advantage in using the xlsx format is that the maximum number of rows in your worksheet increases to 1,048,576 from 65,536 rows.