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

sql-server tsql openrowset
user9969 picture user9969 · Mar 16, 2014 · Viewed 15.9k times · Source

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

Razvan Socol picture Razvan Socol · Mar 28, 2014

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.

Related questions

Using OPENROWSET to dynamically retrieve SP results when SP contains # temp tables

My Scenario I'm working on a database which will contain many details from various Stored Procedures in different databases across the entire server. The information I'm attempting to gather now is, "What does the SP output?" In searching I've found …

Read More
How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More
How do I perform an IF...THEN in an SQL SELECT?

How do I perform an IF...THEN in an SQL SELECT statement? For example: SELECT IF(Obsolete = 'N' OR InStock = 'Y' ? 1 : 0) AS Saleable, * FROM Product

Read More