Different behavor with image and varbinary(max)?

Hello,
I am having problem with the different behavior of image and varbinary(max).
I thought these two types are compatible, but seeing some different behavior.

> Cannot insert literal value into "varbinary(max)", but we can with "image".
> "varbinary(max)" and "xml" data type seems to be compatible, but "image" is not.
Can someone tell me if this is expected behavior?
1. If I create table using "image" data type, I can insert string data into
the table.
create table kmimage(c1 image);
insert into kmimage values ('<value> my blob c1 a </value>');
select * from kmimage;
c1
0x3C76616C75653E206D7920626C6F622063312061203C2F76 616C75653E
2. If I create table using varbinary(max), I cannot insert string data.
create table kmvarbin(c1 varbinary(max));
insert into kmvarbin values ('<value> my blob c1 a </value>');
==> Implicit conversion from data type varchar to varbinary(max) is not
allowed. Use the CONVERT function to run this query.
No problem using hexadecimal representation.
insert into kmvarbin values
(0x3C76616C75653E206D7920626C6F622063312061203C2F7 6616C75653E);
select * from kmvarbin;
c1
0x3C76616C75653E206D7920626C6F622063312061203C2F76 616C75653E
Thank you very much for your help.
KM
> Can someone tell me if this is expected behavior?
This is documented in the SQL 2005 Books Online
(ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/a87d0850-c670-4720-9ad5-6f5a22343ea8.htm).
Implicit conversion from varchar to image is allowed. Conversion from
varchar to varbinary must be explicit.
Why are you storing character data in a column defined as binary?
Hope this helps.
Dan Guzman
SQL Server MVP
"KM" <KM@.discussions.microsoft.com> wrote in message
news:17377F49-0BBD-4895-8FA6-173FD4214C38@.microsoft.com...
> Hello,
> I am having problem with the different behavior of image and
> varbinary(max).
> I thought these two types are compatible, but seeing some different
> behavior.
>
> Can someone tell me if this is expected behavior?
> 1. If I create table using "image" data type, I can insert string data
> into
> the table.
> create table kmimage(c1 image);
> insert into kmimage values ('<value> my blob c1 a </value>');
> select * from kmimage;
> c1
> --
> 0x3C76616C75653E206D7920626C6F622063312061203C2F76 616C75653E
> 2. If I create table using varbinary(max), I cannot insert string data.
> create table kmvarbin(c1 varbinary(max));
> insert into kmvarbin values ('<value> my blob c1 a </value>');
> ==> Implicit conversion from data type varchar to varbinary(max) is not
> allowed. Use the CONVERT function to run this query.
> No problem using hexadecimal representation.
> insert into kmvarbin values
> (0x3C76616C75653E206D7920626C6F622063312061203C2F7 6616C75653E);
> select * from kmvarbin;
> c1
> --
> 0x3C76616C75653E206D7920626C6F622063312061203C2F76 616C75653E
> Thank you very much for your help.
> KM
>

Different behavor with image and varbinary(max)?

Hello,
I am having problem with the different behavior of image and varbinary(max).
I thought these two types are compatible, but seeing some different behavior
.
[vbcol=seagreen]
> Cannot insert literal value into "varbinary(max)", but we can with "image"
.
> "varbinary(max)" and "xml" data type seems to be compatible, but "image" is not.[/
vbcol]
Can someone tell me if this is expected behavior?
1. If I create table using "image" data type, I can insert string data into
the table.
create table kmimage(c1 image);
insert into kmimage values ('<value> my blob c1 a </value>');
select * from kmimage;
c1
--
0x3C76616C75653E206D7920626C6F6220633120
61203C2F76616C75653E
2. If I create table using varbinary(max), I cannot insert string data.
create table kmvarbin(c1 varbinary(max));
insert into kmvarbin values ('<value> my blob c1 a </value>');
==> Implicit conversion from data type varchar to varbinary(max) is not
allowed. Use the CONVERT function to run this query.
No problem using hexadecimal representation.
insert into kmvarbin values
(0x3C76616C75653E206D7920626C6F622063312
061203C2F76616C75653E);
select * from kmvarbin;
c1
--
0x3C76616C75653E206D7920626C6F6220633120
61203C2F76616C75653E
Thank you very much for your help.
KM> Can someone tell me if this is expected behavior?
This is documented in the SQL 2005 Books Online
(ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/a87d0850-c670-4720-9ad5
-6f5a22343ea8.htm).
Implicit conversion from varchar to image is allowed. Conversion from
varchar to varbinary must be explicit.
Why are you storing character data in a column defined as binary?
Hope this helps.
Dan Guzman
SQL Server MVP
"KM" <KM@.discussions.microsoft.com> wrote in message
news:17377F49-0BBD-4895-8FA6-173FD4214C38@.microsoft.com...
> Hello,
> I am having problem with the different behavior of image and
> varbinary(max).
> I thought these two types are compatible, but seeing some different
> behavior.
>
> Can someone tell me if this is expected behavior?
> 1. If I create table using "image" data type, I can insert string data
> into
> the table.
> create table kmimage(c1 image);
> insert into kmimage values ('<value> my blob c1 a </value>');
> select * from kmimage;
> c1
> --
> 0x3C76616C75653E206D7920626C6F6220633120
61203C2F76616C75653E
> 2. If I create table using varbinary(max), I cannot insert string data.
> create table kmvarbin(c1 varbinary(max));
> insert into kmvarbin values ('<value> my blob c1 a </value>');
> ==> Implicit conversion from data type varchar to varbinary(max) is not
> allowed. Use the CONVERT function to run this query.
> No problem using hexadecimal representation.
> insert into kmvarbin values
> (0x3C76616C75653E206D7920626C6F622063312
061203C2F76616C75653E);
> select * from kmvarbin;
> c1
> --
> 0x3C76616C75653E206D7920626C6F6220633120
61203C2F76616C75653E
> Thank you very much for your help.
> KM
>