Inserting file data into a varbinary(max) column

The following are ways I've found to import binary file data into a VARBINARY column on SQL Server 2005.

OPENROWSET BULK

This method avoids the need for a front end tool such as SSIS.

CREATE TABLE documentTable(Document varbinary(max)) 

INSERT INTO documentTable(Document) 
SELECT * FROM 
OPENROWSET(BULK N'C:\ImageFile.png', SINGLE_BLOB) as imagedata

Note: The file path is with respect to the SQL Server. I.e. it is loading a image file from the servers file system.

See Also:

• Insert binary data like images into SQL Server without front-end application
• MSDN:OPENROWSET (Transact-SQL) (See
  D. Using OPENROWSET to bulk insert file data into a varbinary(max) column)

SSIS

If SSIS is available another option was to use a SSIS package as per SSIS – Importing Binary Files Into A VARBINARY(MAX) Column.

Scripting out to XML with base64 encoding

SELECT * FROM [documentTable] for xml raw, BINARY BASE64

DECLARE @xmlDoc xml
SET @xmlDoc = 
'

'

This data can then be scripted in.

DECLARE @xmlDoc xml
SET @xmlDoc = 
'

'

DECLARE @idoc int

EXEC sp_xml_preparedocument @idoc OUTPUT, @xmlDoc

INSERT INTO [myTable] ([Document])
SELECT [udfBase64Decode]([Document]) as [Document] FROM openxml(@idoc, '/ROOT/row') 
 WITH (
  
  [Document] varchar(max) '@Document'
  )

EXEC sp_xml_removedocument @idoc

Using 32-bit DLLs on a 64-bit machine exposed to a .NET Web Application via COM+

I recently had some challenges calling 32-bit DLLs from a third party from a ASP.NET Web Application via COM+ on a 64-bit machine running Windows Server 2008. The 3rd party DLLs also required an ODBC connection to the database.

Issue 1 - Exception: Retrieving the COM class factory for component with CLSID {XXXX} failed due to the following error: 80040154.

I'd registered the DLLs in Windows\sysWOW64 using the version of regsrv32 in that folder.

Calls to the third party DLL worked from unit tests in Visual Studio but failed from the Web Application hosted in IIS on the same machine with the 80040154 error.

Changing the application pool to "Enable 32-Bit Applications" ([enable32BitAppOnWin64]) resolved the issue.

Issue 2 - The ODBC connection was not available

I'd created the ODBC connectino using %SystemRoot%\system32\odbcad32.exe. Again, this worked fine for the VS unit tests but fell over when run from IIS.

Using odbccad32.exe from the sysWOW32 folder to create the ODBC connection resolved the issue.

Issue 3 - Calling the 32-bit COM class from the WCFTestClient.exe resulted in the 80040154 (Class not registered) exception again

Wcftestclient.exe will need to be flagged to run in as 32-bit by using the CorFlags.exe tool. E.g.

corflags.exe /32BIT+ wcftestclient.exe

See also:

• Flipping bits on managed images to make them load with the right bitness...
• Debugging WCF service with x86 dependencies on a x64 bit machine

LINQ To SQL - "String must be exactly one character long.."

I used LINQ to SQL to throw together a quick data access layer to a third party database. In theory in will only be a temporary measure.

When trying to retrieve a record I got the following error:

System.FormatException: String must be exactly one character long..
System.Convert.ToChar(String value, IFormatProvider provider)
System.String.System.IConvertible.ToChar(IFormatProvider provider)
System.Convert.ChangeType(Object value, Type conversionType, IFormatProvider provider)
System.Data.Linq.DBConvert.ChangeType(Object value, Type type)
Read_Name(ObjectMaterializer`1 )
MoveNext()
...

Turns out the issue is related to a VarChar(1) NOT NULL column on the table. The Code Generation has interpreted this as char (System.Char). Changing this to string (System.String) got the system going again. There is some risk now of assigning a value longer than one charater but I can live with that for a temporary solution.

Firing an ASP.NET postback for a control on page load

The following can be used to automatically fire the post back event from an ASP.NET control when the page is done loading client side. Using ClientScript.GetPostBackEventReference() avoids the need to manually create the __doPostBack script using the controls client id.

    string triggerScript = ClientScript.GetPostBackEventReference(this.btnRefreshData, string.Empty);
    Page.ClientScript.RegisterStartupScript(this.GetType(), "PostbackControlScript", triggerScript, true);

If using an UpdatePanel with partial postback use the following instead:

    string triggerScript = ClientScript.GetPostBackEventReference(this.btnRefreshData, string.Empty);
    if(ScriptManager.GetCurrent(Page).IsInAsyncPostBack)
    {
        ScriptManager.RegisterStartupScript(this.btnRefreshData, this.GetType(), "PostbackControlScriptUpdate", triggerScript, true);
    }
    else
    {
        Page.ClientScript.RegisterStartupScript(this.GetType(), "PostbackControlScript", triggerScript, true);

IE8 security warning when mixing http and https content

Ran into an issue with IE8 showing the following prompt with every page view under SSL.

Security Warning
Do you want to view only the webpage content that was delivered securely?
This webpage contains content that will not be delivered using a secure HTTPS
connection, which could compromise the security of the entire webpage.

Turns out the addition of the Microsoft AJAX CDN service is the cause in this particular case. Changing the script link to use SSL resolves the issue.

Programmatic log4net configuration at runtime

I've been looking for a way to enable/disable a log4net appender at runtime through code. Doing so would remove the need to remote to the production web server to alter the logging config file.

The blog post Tweaking log4net Settings Programmatically has a extension method to perform actions on certain types of appender.

Visual Studio 2010 CSS colour picker

Came across a Colour picker dialog for use when editing CSS files in the Visual Studio 2010 editor.

Right click on the style and select Build Style or
Styles (Menu) > Build Style