March 8, 2015 at 2:51 pm
The field conclusions is of type text
insert into ACRC_443_ECHO_DATA_Event_Echo ( conclusions )
VALUES
(0x4e6f207468726f6d627573206f722073706f6e74616e656f757320636f6e747261737420696e206c6566742061747269756d2f6c6566742061747269616c20617070656e646167652e0d0a4e6f206c65667420746f20726967687420636f6c6f7220666c6f77206163726f737320696e74657261747269616c2073657074756d2e0d0a53746174757320706f7374206d697472616c2076616c7665207265706169722e );
Why do I get this error:
Msg 206, Level 16, State 2, Line 4
Operand type clash: varbinary is incompatible with text
How do I overcome the error
March 8, 2015 at 3:52 pm
Quick thoughts, don't use TEXT, use VARCHAR(MAX) or NVARCHAR(MAX) instead,
BOL: ntext , text, and image data types will be removed in a future version of Microsoft 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.
To solve your problem, convert the input to VARCHAR(MAX) before inserting
😎
Quick sample
USE tempdb;
GO
SET NOCOUNT ON;
DECLARE @TBL TABLE (conclusions TEXT NULL);
INSERT INTO @TBL(conclusions) VALUES(CONVERT(VARCHAR(MAX),0x4e6f207468726f6d627573206f722073706f6e74616e656f757320636f6e747261737420696e206c6566742061747269756d2f6c6566742061747269616c20617070656e646167652e0d0a4e6f206c65667420746f20726967687420636f6c6f7220666c6f77206163726f737320696e74657261747269616c2073657074756d2e0d0a53746174757320706f7374206d697472616c2076616c7665207265706169722e,1));
March 8, 2015 at 4:05 pm
mw112009 (3/8/2015)
The field conclusions is of type text
insert into ACRC_443_ECHO_DATA_Event_Echo ( conclusions )
VALUES
(0x4e6f207468726f6d627573206f722073706f6e74616e656f757320636f6e747261737420696e206c6566742061747269756d2f6c6566742061747269616c20617070656e646167652e0d0a4e6f206c65667420746f20726967687420636f6c6f7220666c6f77206163726f737320696e74657261747269616c2073657074756d2e0d0a53746174757320706f7374206d697472616c2076616c7665207265706169722e );
Why do I get this error:
Msg 206, Level 16, State 2, Line 4
Operand type clash: varbinary is incompatible with text
How do I overcome the error
Because the field you are trying to insert into is of type TEXT and the constant you are trying to insert is of type varbinary(163).
You overcome this error by inserting the character string which for some unknown reason you have representedas varbinary. Presumably you have access to teh original character string, which was presumably
'No thrombus or spontaneous contrast in left atrium/left atrial appendage.
No left to right color flow across interatrial septum.
Status post mitral valve repair.'
If you don't have the original text, you can convert varbinary to varchar easily enough, it's utterly trivial : just cast the value to VARCHAR(MAX) : instead of 0x4e6f207468726f6d627573206f722073706f6e74616e656f757320636f6e747261737420696e206c6566742061747269756d2f6c6566742061747269616c20617070656e646167652e0d0a4e6f206c65667420746f20726967687420636f6c6f7220666c6f77206163726f737320696e74657261747269616c2073657074756d2e0d0a53746174757320706f7374206d697472616c2076616c7665207265706169722e
use
CAST( 0x4e6f207468726f6d627573206f722073706f6e74616e656f757320636f6e747261737420696e206c6566742061747269756d2f6c6566742061747269616c20617070656e646167652e0d0a4e6f206c65667420746f20726967687420636f6c6f7220666c6f77206163726f737320696e74657261747269616c2073657074756d2e0d0a53746174757320706f7374206d697472616c2076616c7665207265706169722e
AS varchar(MAX) )
But: why are you using a TEXT field? You've posted your question in an SQL 2012 Server forum, and the TEXT type was first deprecated in SQL Server 2005 and is not going to be available much longer. Maybe you should look at changing the data type to VARCHAR(MAX) if the text is ever longer than 8000 characters or to varchar(8000) if it's never longer than that (of course that doesn't solve the convert from varbinary problem, you'll still have to either do that conversion or find the original text to insert instead of the varbinary mess).
Tom
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply