Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Operand type clash inserting ntext Expand / Collapse
Posted Wednesday, December 17, 2008 7:45 AM


Group: General Forum Members
Last Login: Friday, February 8, 2013 7:56 AM
Points: 16, Visits: 142
Here is what I am trying to do:

update MyTable
set [value] =
(select BulkColumn FROM OPENROWSET(Bulk 'C:\MyFile.txt', SINGLE_BLOB) as blob)

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

The table:
CREATE TABLE [dbo].[MyTable](
[column1] [varchar](256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[column2] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[value] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[column4] [datetime] NULL,
[column5] [varchar](32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

I get the same error if instead of update I do an Insert into. My confusion is I do not see the datatype "varbinary(max) in play anywhere here.
Post #621233
Posted Wednesday, December 17, 2008 8:36 AM



Group: General Forum Members
Last Login: Wednesday, November 25, 2015 9:11 PM
Points: 10,681, Visits: 14,286
I'm not real familiar with OPENROWSET, but it appears that the OPENROWSET command in SQL Server 2005 is using varbinary(max) as the SQL Server datat type for SINGLE_BLOB in the process.

Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #621290
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse