Tag Archives: MySQL

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.

SQL blob to file

This post shows you how to export and extract your SQL blob to file using SQL Blob Export.  You can download a free 14-day trial here.

Start SQL Blob Export, and click on the Export images and files item.

Enter your project details if you plan to reuse the export settings.  Otherwise, you can just accept the default values.

Enter the connection details to connect to your database server.

Select the table you want to export your blobs from.  Tables containing blob columns are highlighted in green.

You can also enter a SQL command to select the blobs you want to export.  We will use the following SQL command to retrieve our blobs.

Now you need to tell SQL Blob Export how to name the exported files.

Enter the folder to store the files in.

You can ignore the OLE Object types section if your blobs are not stored in Microsoft Access OLE Object-type fields.

In the Other types section, enter the naming convention for your files.

We use the value <ProductPhotoID:0000>_<%column%> to name our files.  <ProductPhotoID> is one of the columns we retrieved using our SQL command, and is a number.  The suffix :0000 means we want SQL Blob Export to format the number to 4 digits e.g. 14 is formatted to 0014, 234 is formatted to 0234 etc.

We can use any column names to name our exported file, as long as the column exists in the table we chose to export, or is a column retrieved by our SQL command.

The <%column%> value is a system value indicating the index of the column storing the blob.  Remember that our SQL command selects 2 blob columns (ThumbnailPhoto and LargePhoto), so our naming convention needs to be able to identify from which column the file was extracted from.

Once we have set up our export options, SQL Blob Export will extract the SQL blob to file.

One important thing you should note is that we did not tell SQL Blob Export the type of blob that’s stored in our tableSQL Blob Export can identify the blob type and use the correct file extension accordingly.  It can identify most image formats (jpg, png, tif, bmp, gif, tga, emf, wmf), Office files (doc, docs, xls, xlsx, ppt, pptx), Open Office files, PDFs, and many more.  For a complete list of recognised file types, see here.