convert nvarchar(max) to varbinary(max)

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))
LVL 22
senadAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

OCDanCommented:
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
senadAuthor Commented:
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
OCDanCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

santhimurthydCommented:
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
senadAuthor Commented:
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
senadAuthor Commented:
It seems it cant be done properly from what i read on the links.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.