Solved

convert nvarchar(max) to varbinary(max)

Posted on 2012-03-31
6
2,201 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
  • 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 250 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 15

Accepted Solution

by:
santhimurthyd earned 250 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

820 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