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.

Razvan Socol · Accepted Answer · 2014-03-28 06:13:10Z

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, 2014 at 6:13

Razvan Socol

5,7492 gold badges22 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Mayur Dhingra Over a year ago

Thanks @rsocol. I used varbinary(max) and it worked. I was struggling with it since 4 hours.

donstack Over a year ago

and for ntext use nvarchar(max)

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related