image data types are invalid for local variables (using openrowset cannot make it work)

Question

Using Sql server 2005

I need to call a stored procedure that has many parameters and one which is an image column that is used for word document. I cannot change database since it's not under my control. I cannot seem to set a result of an openrowset to a variable as I get

The text, ntext, and image data types are invalid for local variables.

What is a work around?

Below is the code that cannot make it work.

DECLARE BinaryContent image
SET @BinaryContent=(SELECT BulkColumn FROM OPENROWSET(BULK N'C:\mydoc.docx',    SINGLE_BLOB) blob


 EXEC   [dbo].[InsertWordDocument]
    @Id = NEWID,
    @Name = N'DocName',
    @Description = N'DescriptionToChange',
    @BinaryColumn =@BinaryContent

How can I make the above work?

Many thanks.

Answer 1

Use varbinary(max) instead of image. A varbinary(max) variable can be passed as a value for an image parameter.

The ntext, text, and image data types are deprecated and will be removed in a future version of SQL Server.