SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 Migration from Access db to SQL-server
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

notk
Starting Member

Netherlands
2 Posts

Posted - 08/01/2003 :  07:20:36  Show Profile
Hello,

I've imported a few Access 2000 tables in a 120 day test SQL-2000 server. These tables were each 2gb of size and placed in individual mdb files. These tables are so big because they contain images (I know, this was not so smart to do to import images inside a database, but this was done before I came to work here). Now I’ve noticed the problems which the images give.
The old individual Access databases, actually the tables inside these db’s, were linked inside in another database where the people here work in and type in all the data and insert the images. I linked the SQL tables also in this Access database (where we work in). If I make a query from a table with all available fields, everything seems ok. Opening the query of this query goes very quick and browsing up and downs works also very rapidly.
Normally we use “union” queries to search in multiple tables for data. This took with the Access database a few (5-10) minutes to search for certain record. If I use this union query with the linked SQL tables than Access hangs. If I exclude the ‘image’ field from the union query, everything works very fast again, so the images are my problem here probably.

The people who use the database are searching the database for the image to print the image.

Maybe it is better to export the images out of SQL to multiple files in a to specify directory (most files are tiff files which contains several pages). I don’t know how to export the images out of SQL tables automatically and at the same time place a hyperlink where with the users can open the file immediately.
An ideal link for the files would be like this:

\\name.com\dfs\pictures\year\picturename1.tiff
\\name.com\dfs\pictures\year\picturename2.tiff
\\name.com\dfs\pictures\year\picturename3.tiff
and so on….

The exporting to a specific directory should then also number the files automatically and also place the hyperlink with name in the place where the file was (or in a separate new field, ‘hyperlinks’).

I am unfortunately not too experienced with the SQL server, since I’m trying this all out before we buy this program.

I’ve also seen the FAQ section how to link an image, but I don’t know where to place this ASP script. Do I have to place this script somewhere in the SQL-server table or in the Access database where the tables are linked?

I hope someone can help me with this.

With kind regards,

Mario

Edited by - notk on 08/01/2003 07:27:24

AjarnMark
SQL Slashing Gunting Master

USA
3246 Posts

Posted - 08/01/2003 :  13:48:24  Show Profile  Visit AjarnMark's Homepage
Mario,

First, let me say Thank You for actually reading the FAQ before you posted. It constantly amazes me how many people don't.

So, here are a few ideas... I would probably tackle this by creating a client application (in VB, ASP, Java, whatever) that could be kicked off to do this. I don't know of any (not to say it doesn't exist, I just don't know of it) way to use just SQL to output an image BLOB to a datafile. The ASP code in the FAQ is a good place to start, although if you're not familiar with ASP (web) development, you might be better off just using the concepts but putting them into a programming language you do know. This app would extract the BLOB and write it to a file using your naming convention, and then could update the SQL table with the new name.

As for other chanes... how are the tables organized? Are all images stored in a table of their own? If not, could they be moved there? If so, then you could do all of your querying on keywords, etc. first, and then at the last moment join the images table for the results display.

------------------------------------------------------
The more you know, the more you know you don't know.
Go to Top of Page

robvolk
Most Valuable Yak

USA
15676 Posts

Posted - 08/01/2003 :  21:26:26  Show Profile  Visit robvolk's Homepage
Actually, you can do this with a little command-line magic. Paste these commands into a text file and name it with a .BAT extension:
bcp "select EmployeeID, FirstName+'_'+LastName+'.pcx' from northwind..employees" queryout c:\employees.txt -T -S. -c -t"|"
for /f "tokens=1,2 delims=|" %%a in (c:\employees.txt) do textcopy /Cphoto /Dnorthwind /Temployees /W="WHERE employeeid=%%a" /O /Fc:\%%b -S. /Ulogin /Ppassword
What does this mess do?

The first bcp command outputs the rows from the Employees table into a text file. It constructs a file name from the first and last names to be used later by the FOR command.

The FOR command then reads the file and processes each line of data, which constructs ANOTHER bcp command to export each Photo column from the Employees table into a separate file. Although the extension is .PCX, I couldn't get the files to open in any of the picture editors I have. If your files are TIFF format you should be able to open them without incident, as long as you change the file extension in the batch file.

This is an example from the Northwind database, you would also need to modify the SELECT statement to match your database, table, and column names, and also modify the for command to output the files to the directory you wish.

You can find more information on the for command-line statement in Windows help. It's designed for recursive/repetetive processing of files, directories, and directory trees.

One other thing that may be an issue, or may not. MS Access stores some extra information in an OLE object field above and beyond the raw data. This extraneous data allows it to properly display an OLE object itself, however it may corrupt the format when exported to a file. SQL Server probably did not strip this extra data when it upsized the database, so you may not be able to open the file when it is exported directly. However, once the files are exported, if you examine them with a hex editor or even notepad, you may see this extra data and find a way to remove it from the file, either at export, or in the SQL Server table before export.

Edit: bcp doesn't work correctly, so I switched the example to use textcopy. Textcopy is not documented, but you can run textcopy/? and get its parameters. Also, I could not get it to use a trusted connection, so take the help with a grain of salt in that regard.

Unfortunately it STILL doesn't allow the exported images to be opened in an image editing program. Anyone happen to know what format the Northwind photos are in? PCX and BMP don't work.

Edited by - robvolk on 08/09/2003 11:25:11
Go to Top of Page

notk
Starting Member

Netherlands
2 Posts

Posted - 08/04/2003 :  07:29:59  Show Profile
Thanks ajarnmark and robolk for your answers.

I am really not very experienced with programming. I know a little of it, but to program something complete in vb? If I make a client in vb to the sql database I would be copying something what is already made in Access (in Access, can’t I also use vb in there?). We use an Access database (start.mdb for example) to navigate to the linked other databases (db1.mdb, db2.mdb, db3.mdb and more). In this start.mdb we give-in the information we want to collect for our orders. In MS-word we use predefined templates to set up documents needed for that department where it is used. In those templates are links to the start.mdb database. The users only have to fill in the order number to complete the template.

The images and the other data are stored together in one table for each part of the year (we needed several tables (reed databases) because the data was growing quickly to the 2 GB limit of MS Access. How could I export the images out of the table, to another new table. These tables have to be linked to each other off course. Otherwise the link to the data and images is lost. The images belong together with the data.

I am going to search in the Northwind database. Maybe I can find something witch I can use there.

Mario


Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000