[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

convert nvarchar(max) to varbinary(max)

Posted on 2012-03-31
6
Medium Priority
?
2,480 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

650 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