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.

score 18 · Answer 1 · answered Mar 28 '14 at 06:13

18

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.

answered Mar 28 '14 at 06:13

Razvan Socol

5,426
2
20
32

Thanks @rsocol. I used varbinary(max) and it worked. I was struggling with it since 4 hours. – Mayur Dhingra Mar 17 '15 at 10:00
and for ntext use nvarchar(max) – donstack Jan 28 '16 at 09:09

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

1 Answers1