Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
notk
Starting Member
2 Posts |
 Posted - 2003-08-01 : 07:20:36
|
| 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.tiffand 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 |
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2003-08-01 : 13:48:24
|
| 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. |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-08-01 : 21:26:26
|
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. |
|
|
|
notk
Starting Member
2 Posts |
Posted - 2003-08-04 : 07:29:59
|
| 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 |
|
|
|
|
|
|
|
|
Migration from Access db to SQL-server