Uploading Pictures in MSSQL 2000
-
Hi,
Is there a way to upload Image Data ( pictures ) from a different Database ( e.g. DB2 ) into SQL Server 2000 and 2005 directly using DTS ? Or we have to use the Bulk Insert utilities provided to upload data from a File ?
Thanks in Advance.
-
I don't know because I've never done it... why have I never done it??? Because, as most folks will tell you, it's usually a very bad idea to do so... usually, it's a much better idea to load the file name of the picture and let the GUI retrieve the picture using that file name.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jeff; I have to disagree. It is usually a very good thing to do, storing image/report/binary data with the SQL data.
- It is more consistent, as the binary data is with the related business data, and not impacted by the vagaries of gremlins in file systems (like server admins moving directories, users accidentally renaming files, etc).
- It certainly takes less space to have the binary data in the database, as there is not the wasted space at the end of every file to fill out the cluster.
- It is easier to backup, store, move, and generally keep the binary and business consistent.
I contend it is never a bad decision to put binary data in the database, and is usually a bad design to separate the data.
Amit: Yes, you can, but I am not sure on the DTS part. The Image datatypes can be consistent, since they are binary. As long as you know what that binary data represents (bitmap? jpg? executable program?) and that format is available for the application that will use it on the SQL Server side, you should be OK. Where problems sometimes occur on these cross-platform moves is incompatible file types, so you may need to export the data from the other database, convert it to another file type, move to the Windows platform, and write it into the SQL Server database. To get at the DB2 data from SQL Server, you will want to have OLEDB driver for accessing the DB2 database on the other platform (if AS/400 or Zos, you probably will install IBM Client Access on the SQL box), then create a linked server entry to the DB2 database table. You can then use regular TSQL queries to access the DB2 table using a 4-part table name.
Hope this helps
Mark -
Thanks Mark. I would be working on this migration project ( from DB2 to SQL Server ) sometime very soon. So, your advice should definitely help me with that.
-
Heh... not supposed to have "gremlins in file systems", Mark.
Do you also store XML in Text columns?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
And that's why we design systems to minimize the risk of "gremlins", isn't it?
And, yes, unless I know the XML will never be too large for varchar, I would store XML text in a text column in SQL2000. I suspect you would also?
Mark -
Nope... I break XML into the appropriate parts and store it as normalized data.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Well, that is true also, have to give that to you.
The instance I have in mind is XML documents coming in through my web service from various business partners. I do extract the relevant data from the XML and store it in table. But I also stored the original XML document as they sent it for record-keeping and archival purposes (We need to keep the "original copy" in case of audits or case disputes). That, in SQL2000, I store in a text column.
Mark -
Understood. Me? I export the data to a file and zip it so it won't be a duplication of data in the database.
Works kinda the same way with images... if a have Picture1.jpg as a file with just the name stored in the DB... the GUI can get to it just fine and with good performance. If I need to change the picture, I do a "snap rename" (drop old, rename new) in the operating system and it's done.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Where I work we have started to move a lot towards using image columns to store files in tables. Sometimes it's not always the GUI that needs to get at the file. In our case, it's a a web application running on a farm of web servers. With this kind of architecture your database becomes the equivalent of a file share - which in the web world is not always easy to deal with.
-
Thanks for the info Michael... I'm not much of a "web guy"... what makes it difficult to use "file shares" in the Web world... asking because I don't know and I may have to change my recommendations (and methods) about storing images in the database...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
To use shared directory in a web farm (or web garden), you need to have the file directory mapped and permissions set for each web server and site to access the directory. Then, you also have a bottleneck with the file directory being accessible and serving files from a location.
When the files are stored in the database in image column, they can be retrieved with the rest of the data related to that file/image, then streamed to the web client without having ever having it as a file. Each server/site in a web farm/garden gets the file/image itself.
Also, as I mentioned before, it saves on storage space, as SQL Server will store the image/file data more compactly that the 1 file per cluster on disk systems. In SQL Server, the file data will only take the space it needs plus the 16 bytes for the text pointer; each file is essential back-to-back with each other in the Text/Image page.
Mark -
File shares in a web environment are hard to deal with. Here are some reasons:
1. Even if your web application runs off only one web server, in a large corporation you frequently need to deal with many Windows domains. Handling the security here can be quite a nightmare.
2. Getting unix web servers to handle Windows shares can be done but it's not easy.
3. I have dealt with web farms consisting of 10 or more physical servers. For each one you need to deal with getting these shares to work from say your ASP pages. The administration of this in a large company is not trivial.
When all the web servers in your web farm connect to a single database, it all becomes a breeze to manage these files.
I have a friend who works for a company where the Windows domains are managed very poorly. Getting file shares to become reliable was hard - mostly due to incompentence in their IT staff. He thus built an entire .NET application which used an Oracle database to simulate file shares. Of course this is exaggerating things a bit and I would have simple left that company. But it shows the advantage of using a database to store files to overcome real and imagined limitations.
-
I put a lot of images in the db as well and believe its a valid technique. Always surprises me that even those that believe in SQL as a platform don't think it can handle images/blobs very well. I know SQL 2008 is supposed to have some kind of hybrid model that maybe will end the feud once and for all (but probably not!).
-
Thanks for the info guys... like I said, I may have to reevaluate my position on where to store images.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply