Solved

convert nvarchar(max) to varbinary(max)

Posted on 2012-03-31
6
2,067 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
0
 
LVL 22

Author Comment

by:senad
Comment Utility
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
Comment Utility
It seems it cant be done properly from what i read on the links.
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
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 video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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.

762 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now