?
Solved

convert nvarchar(max) to varbinary(max)

Posted on 2012-03-31
6
Medium Priority
?
2,396 Views
Last Modified: 2012-04-01
It seems every time I import something from an Access database to SQL server the data ends up different. I converted an absolute database table to Access and then imported the Access table to SQL server 2012. A blob field (contains jpeg pictures) from absolute database got converted to memo in Access. In sql server it ended up as nvarchar(max). It displays my pictures (in my application) ok but I wonder... once (during an import)  the pictures were converted to varbinary(max) on the sql server (and also displayed ok in my application).
So my question is this :
what is the difference between nvarchar(max) and varbinary(max) ?
From what I can read Varchar(max) stores a maximum of 2 147 483 647 characters which is 2GB of data just like varbinary(max).So what is the difference ?
So if I were to convert the data how would I go about it ?  I thought of adding an extra column (NEWPICTURES)  to my table,set it as varbinary(max) and then convert all data from existing column PICTURES which is varchar(max). How do I do that ?

something like:  UPDATE mytable SET NEWPICTURES=CONVERT(VARBINARY(MAX),CONVERT(NVARCHAR(MAX),PICTURES))
0
Comment
Question by:senad
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 9

Expert Comment

by:OCDan
ID: 37790752
Just a quick question before I look at your problem.

Why are you storing pictures in a DB? It is generally considered very bad practice to do so.
0
 
LVL 22

Author Comment

by:senad
ID: 37790764
I am just learning sql server  and filestream will also be on the agenda. However, these are small pictures about half a MB so from what I can read filestreaming is for files bigger than 1 MB.
I have run the above sql queery - data got converted (i suppose) but I can not see the pictures in my application.
In sql server under the converted field all I can see is <Binary data>. my PICTURES display ¿R¿r.

And strange thing is that I can succesfully copy and paste from PICTURES to NEWPICTURES and data in NEWPICTURES then gets displayed.
0
 
LVL 9

Assisted Solution

by:OCDan
OCDan earned 1000 total points
ID: 37790769
OK fair enough, the standard practice is to store the absolute path to the images. Perhaps calling the image the same as the ID of the field it relates to:
e.g ProductID = 43524
Path = \\DBImages\Products\43524.jpg

Then pass this path through to your program as the path for the image.

Although SQL can hold images it isn't really designed to do so and thus isn't optimised for it. It makes editing the images themselves far more difficult, unlike storing them within your normal file structure where you can do whatever you want with an image. Get your front-end to do the leg work rather than SQL server.
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
LVL 15

Accepted Solution

by:
santhimurthyd earned 1000 total points
ID: 37790780
0
 
LVL 22

Author Comment

by:senad
ID: 37790792
why did not my pic convert to varbinary(max) properly so I can see them in my application ?
any ideas ?
anybody ?

any solution ? it seems I can not  convert nvarchar(max) to binary(max) properly.
0
 
LVL 22

Author Closing Comment

by:senad
ID: 37793147
It seems it cant be done properly from what i read on the links.
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question