Inserting BLOB image datatype - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Inserting BLOB image datatype

Author

Topic

capella07
Starting Member

46 Posts

Posted - 2006-11-20 : 11:30:53

I'm using SQL Server 2000.

I have a very simple Insert Into...Select From statement that includes inserting an image datatype from one table to the other. However, when I execute it in Query Analyzer I get the following error:

Server: Msg 8152, Level 16, State 2, Line 1
String or binary data would be truncated.
The statement has been terminated.

I have verified that both tables' image columns are of the image datatype and set to (the default) size 16.

Any idea how to deal with this, anyone?

Thanks!

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-11-20 : 11:35:10

care to show the statement?

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

capella07
Starting Member

46 Posts

Posted - 2006-11-20 : 11:36:29

Sorry, that WOULD help, huh?

INSERT INTO tblLoadOLE
(POD,
BookingNumber,
OrigSubject,
DateAppended,
[FileName],
OLEFile)

Select
POD,
BookingNumber,
Subject,
DateAppended,
[FileName],
fileImage

from emailWithAttachments
where (emailWithAttachments.POD like 'Q%') or (emailWithAttachments.POD like 'L%')

f u cn rd ths, u cn gt a gd jb n prgrmng

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-11-20 : 11:46:29

i guess OLEFile and fileImage are both image datatypes?

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

capella07
Starting Member

46 Posts

Posted - 2006-11-20 : 11:47:13

That is correct. Same size, also (16).

f u cn rd ths, u cn gt a gd jb n prgrmng

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-11-20 : 11:52:28

Why do you suspect it is the problem with IMAGE data type? Are other data types (and length) same in both tables?

Harsh Athalye
India.
"Nothing is Impossible"

capella07
Starting Member

46 Posts

Posted - 2006-11-20 : 11:54:54

Heh, ya know I should have thought of such a simple option as that.

I'll check out the other columns & post back here.

f u cn rd ths, u cn gt a gd jb n prgrmng

capella07
Starting Member

46 Posts

Posted - 2006-11-20 : 12:00:46

I humbly return with my tail between my legs - indeed, the image column was not the problem - it was another: nvarchar 255 to char 5!

Thanks guys

f u cn rd ths, u cn gt a gd jb n prgrmng

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2006-11-20 : 13:08:54

About images/BLOB data type, care to read...

http://www.sqlteam.com/item.asp?ItemID=986
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=37980
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=45564

capella07
Starting Member

46 Posts

Posted - 2006-11-20 : 13:18:06

Thanks, afrika, but the column is of the image DATATYPE, saving a file as a binary image, not an image as in a picture.

f u cn rd ths, u cn gt a gd jb n prgrmng

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-11-20 : 13:43:19

image is just another name for array of bytes.
you can store word documents in there if you want.

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-11-21 : 11:15:14

http://www.aspfaq.com/show.asp?id=2149

Madhivanan

Failing to plan is Planning to fail

capella07
Starting Member

46 Posts

Posted - 2006-11-21 : 11:24:22

Thanks for the link, madhivanan.

While researching how to put this table together it became crystal clear that the best way to go is to just populate the cell in the table with text info on the file path. I will eventually be able to do that, but the immediate situation/database use dictated saving it as an image datatype.

f u cn rd ths, u cn gt a gd jb n prgrmng

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-11-21 : 12:04:27

>>f u cn rd ths, u cn gt a gd jb n prgrmng

Yes I could read that. So can I get good job?

Madhivanan

Failing to plan is Planning to fail

capella07
Starting Member

46 Posts

Posted - 2006-11-21 : 12:12:23

Heh, I personally can't help you, but I guess you passed the first exam!

Congratulations, you're now a certified Cryptic Letters Reader!

f u cn rd ths, u cn gt a gd jb n prgrmng

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources