2

I have a question: can I load .docx files into a column documentbody with a datatype of ntext into my table testing.dbo.table00 using a SQL query?

I'm getting an error message

Msg 206, Level 16, State 2, Line 2
Operand type clash: varbinary(max) is incompatible with ntext

Code:

INSERT INTO [Testing].[dbo].[table00](documentbody) 
    SELECT BulkColumn
    FROM OPENROWSET(BULK N'C:\Users\XXX\filename.docx', SINGLE_BLOB) as TheFile

I have also tried the following query:

INSERT INTO [Testing].[dbo].[table00](documentbody) 
    SELECT BulkColumn
    FROM OPENROWSET(BULK N'C:\Users\XXX\filename.docx', CLOB) as TheFile

But I'm getting this error:

Msg 155, Level 15, State 1, Line 4
'CLOB' is not a recognized BULK OPENROWSET provider option.

How can I solve the error? Thanks

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
grc
  • 85
  • 2
  • 11
  • 1
    `ntext`, `text`, and `image` data types will be removed in a future version of SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use `nvarchar(max)`, `varchar(max)`, and `varbinary(max)` instead. [See details here](http://msdn.microsoft.com/en-us/library/ms187993.aspx). Plus: storing a Word file results in a **binary** stream of bytes - use `varbinary` - not a textual `varchar(max)` column ..... – marc_s Mar 22 '17 at 08:41

1 Answers1

2

It says in the error - you are trying to insert varbinary data into a column that is ntext data type. You should change column documentbody to a varbinary data type. You can do this using the syntax:

ALTER TABLE table_name
ALTER COLUMN column_name datatype;

Further reading - binary and varbinary (Transact-SQL)

Georgi Raychev
  • 1,288
  • 1
  • 13
  • 26
  • 1
    Thanks! managed to load it in after changing the column into a varbinary data type. – grc Mar 22 '17 at 07:42