Lookup table insert, update, and delete...

All,

Just wondering if anyone is aware of a SQL server shareware utility that places a front end on a table to manage insert, update, and delete of rows on a lookup table.

We can certainly write this but before reinventing the wheel I figure I'd ask and see.

Many Thanks,

IsaacThe DB Explorer works extensively with look-ups. You will have to define the relationship by referential constraints in the database, or as navigation paths in the client front-end.

You find all resources at MayeticVillage (http://www.MayeticVillage.com/DB-Explorer)

I'll be glad to answer all your questions about it.|||You can edit table data directly in Enterprise Manager.|||You can even use the query analyzer to maintain your data by DML statements, but I invite you to experience the ease of working with the DB Explorer.|||Gosh, I had no idea that DB Explorer was "shareware".

And all this time I've been going to the trouble of right-clicking on any table in Enterprise Manager and selecting "Open Table/Return All Rows"...|||The idea behind the DB Explorer is to exploit the essence of the relational model: the relationship between entities. To work with single tables / views is simpel, any tool including the Enterprise Manager can do so. To put a table / view into a relational context is normally the exclusive domain of client application development, but the DB Explorer is able to generate a user interface on-the-fly, including lookups.

Moreover, you can instantly use the relationships to navigate through your data: from a customer record to the related order records, or vise versa from an order to the corresponding customer. Even within the Enterprise Manager with its knowledge of the existing relations, you would have to write SQL to do so; the DB Explorer works (almost) completely without SQL in the GUI.

Actually, the DB Explorer isn't shareware but freeware. The only restriction is that it can be used in a stand-alone / Client/Server configuration only. As soon as you want to use the tool in a three-tier or/and multi-user configuration, an evaluation period of 1 month will start.

Why don't you give it a try?|||The problem I have with using enterprise mgr or query analyzer is that the users who will be doing the updates are very untechnical.

Ideally, I'd like to provide just a nice front end to the 4 or 5 tables that matter to them.

I can't overwhelm them with database names, a zillion other tables in a large list of tables, and cryptic column names etc etc etc.

Thanks,

Isaac|||Then your cheapest solution is to create an Access Data Project linked to your SQL Server database, with an entry form for each table.|||...Even cheaper would be to create updateable views and grant all permissions to those rather than the tables themselves.|||I think he is looking for a simple user interface, but it would definitely be a good idea to create the views you suggested, along with a login/role that only has access to those views, before allowing people into the database through an Access Data Project. Once they get into the .ADP file, they can get into anything their login allows, or that is poorly secured.|||The problem I have with using enterprise mgr or query analyzer is that the users who will be doing the updates are very untechnical.

Ideally, I'd like to provide just a nice front end to the 4 or 5 tables that matter to them.

I can't overwhelm them with database names, a zillion other tables in a large list of tables, and cryptic column names etc etc etc.
Isaac

Please note that the DB Explorer allows you to present just the tables you need, with functional table and field names. There is also a quite complex access control mechanism integrated, which would allow you to grant read-write access to lookup tables, and read-only access or no access at all to other tables.|||Please note that the poster requested a shareware solution. He did not ask for you to push your companys product by touting all the things it does which can also be done through Enterprise Manager.

DBForums has a Marketplace forum if you wish to advertise.|||Please note that the poster requested a shareware solution. He did not ask for you to push your companys product by touting all the things it does which can also be done through Enterprise Manager.

Which part of the freeware property or extended functionality which is definitively NOT possible with Enterprise Manager didn't you understand?|||So your software is free? My apologies...|||Apologies granted. :cool:

Donatations, however, are always appreciated.|||Curious. Why does your website offer a trial-version of DB Explorer if it is freeware?

I don't want to bother with the demo version. Please post the link where I can just download the entire thing for free.

Thanks!|||Curious. Why does your website offer a trial-version of DB Explorer if it is freeware?

I don't want to bother with the demo version. Please post the link where I can just download the entire thing for free.

Thanks!

Thank you for pointing me for that, the label is misleading. I've changed it. Note, that for trying the software, you will need to have MDAC version 2.8 or higher.

Looking for suggestion of how to manage a DB file when it getting too big.

Hi all,

At our facility, we have a > 350 gigs database and the DB is breaking into 2 files. One file is our Image table with the size of 300 gigs. The other file contains all of the other tables. The image file is getting bigger and bigger everyday, I am worry that eventually when a data file getting too big it could lead into problem. - DB fragmentatio, hard to manage etc

My question is anyone has run into this problem, and how do you solve it?

I am open for any suggestion.

BTW: I am using SQL 2000.

Thanks in advance.

Use filegroups, Assign the frequently modified tables in one filegroup while those that don't get modified frequently be assigned in another filegroup. When you create filegroups, you also modify your backup strategies.|||

Since your basic problem is with growth of your images, you need to come up with a mechanism for managing those.

If this is SQL Server 2005, then I would implement partitioning against that table on your identifier. That will allow you to split the table across multiple filegroups and files. If this is SQL Server 2000, then you can add multiple files to the filegroup that this table resides on (the files don't necessarily need to be in the same directory or even drive).

It doesn't really matter that they are images or discrete data, managing them occurs using the same process. You need to rely on one or more filegroups each with one or more files in them.

|||

Thanks all for your suggestions. The main problem we are having is we have one huge table (300 gigs) reside in one file group and spliting a table across multiple files is not an option for us since we are using SQL2k, (Might have to wait until we upgrade to 2005) but I'm going to use your suggestions with other databases

Thanks

|||I don't understand why not. I am NOT suggesting splitting across multiple fileGROUPS, because that would require an upgrade to 2005. I am suggesting adding FILES to the existing filegroup. This is absolutely supported and possible in SQL Server 2000. The data in that single table would then be written across multiple FILES within a single filegroup and would at least give you options on managing the storage space.|||

Hmm, I obviously didn't think of this possibility. Data could be written across multiple files! it makes perfect sense now. damm, how could I not see this :-(- sorry for misunderstood your comment and thanks for enlightent me, Michael.

Appreciated.

|||Hello Michael Hotek,

I had gone through the post. But did not understood how it will help the issue.
I have one similar problem here. We aer using MS SQL 2000, where there is only one table in the database where scanned document images are storing and database is growing by 2 to 3 GB per day. Currently it is upto 300 GB. For safety reason we are planning to partition/split the data in the table according to one key field 'book_no' so that there will be seperate partitions for each book_no. At the end we need to combine all together to form the original table.
Please guide/suggest me how to split the table with the query like 'SELECT * FROM doc_master WHERE book_no=1' and split the entire table for seperate book_no's and later combine them together to form the original table?

Right now I am planning like this: create demmy databases for each book_no's with same structure as doc_master and use "INSERT...SELECT...WHERE book_no=?' statement to fill these databases and delete them from original database. Whether it will ok with out harming data? Please suggest.|||

Rajeev,

My problem is I can't reduce the size of my image db, I can't archive (at least in SQL 2000) I can't partition my table either and I don't want my data file growing too big. So spliting a single data file of a table into multiple data files (like what Michael suggested) is a way to go. I don't think this way will gain much in performance or anything but having a smaller data files would help me to easily manage files. I also store these smaller data files into separate drives etc. so the sugesstion did help me in the way of managing the physicall data file.

I think what you are trying to do is separate data into separate databases, in my opinion, this could lead into more complication since now you have to manage multiple databases, mutiple db permissions, db maintenances and it might also hurting your application performance etc. in the end you are not gaining much.

|||

You can add data files to a SQL 2000 database, within the same filegroup.

What this will accomplish is that the new data inserted into your table will start spreading to both files. It will not automatically rebalance existing data between files.

Have you experienced specific problems as a result of having a large table, or are you just anticipating that you might?

What is the storage that this database is on?

Looking for suggestion of how to manage a DB file when it getting too big.

Hi all,

At our facility, we have a > 350 gigs database and the DB is breaking into 2 files. One file is our Image table with the size of 300 gigs. The other file contains all of the other tables. The image file is getting bigger and bigger everyday, I am worry that eventually when a data file getting too big it could lead into problem. - DB fragmentatio, hard to manage etc

My question is anyone has run into this problem, and how do you solve it?

I am open for any suggestion.

BTW: I am using SQL 2000.

Thanks in advance.

Use filegroups, Assign the frequently modified tables in one filegroup while those that don't get modified frequently be assigned in another filegroup. When you create filegroups, you also modify your backup strategies.|||

Since your basic problem is with growth of your images, you need to come up with a mechanism for managing those.

If this is SQL Server 2005, then I would implement partitioning against that table on your identifier. That will allow you to split the table across multiple filegroups and files. If this is SQL Server 2000, then you can add multiple files to the filegroup that this table resides on (the files don't necessarily need to be in the same directory or even drive).

It doesn't really matter that they are images or discrete data, managing them occurs using the same process. You need to rely on one or more filegroups each with one or more files in them.

|||

Thanks all for your suggestions. The main problem we are having is we have one huge table (300 gigs) reside in one file group and spliting a table across multiple files is not an option for us since we are using SQL2k, (Might have to wait until we upgrade to 2005) but I'm going to use your suggestions with other databases

Thanks

|||I don't understand why not. I am NOT suggesting splitting across multiple fileGROUPS, because that would require an upgrade to 2005. I am suggesting adding FILES to the existing filegroup. This is absolutely supported and possible in SQL Server 2000. The data in that single table would then be written across multiple FILES within a single filegroup and would at least give you options on managing the storage space.|||

Hmm, I obviously didn't think of this possibility. Data could be written across multiple files! it makes perfect sense now. damm, how could I not see this :-(- sorry for misunderstood your comment and thanks for enlightent me, Michael.

Appreciated.

|||Hello Michael Hotek,

I had gone through the post. But did not understood how it will help the issue.
I have one similar problem here. We aer using MS SQL 2000, where there is only one table in the database where scanned document images are storing and database is growing by 2 to 3 GB per day. Currently it is upto 300 GB. For safety reason we are planning to partition/split the data in the table according to one key field 'book_no' so that there will be seperate partitions for each book_no. At the end we need to combine all together to form the original table.
Please guide/suggest me how to split the table with the query like 'SELECT * FROM doc_master WHERE book_no=1' and split the entire table for seperate book_no's and later combine them together to form the original table?

Right now I am planning like this: create demmy databases for each book_no's with same structure as doc_master and use "INSERT...SELECT...WHERE book_no=?' statement to fill these databases and delete them from original database. Whether it will ok with out harming data? Please suggest.|||

Rajeev,

My problem is I can't reduce the size of my image db, I can't archive (at least in SQL 2000) I can't partition my table either and I don't want my data file growing too big. So spliting a single data file of a table into multiple data files (like what Michael suggested) is a way to go. I don't think this way will gain much in performance or anything but having a smaller data files would help me to easily manage files. I also store these smaller data files into separate drives etc. so the sugesstion did help me in the way of managing the physicall data file.

I think what you are trying to do is separate data into separate databases, in my opinion, this could lead into more complication since now you have to manage multiple databases, mutiple db permissions, db maintenances and it might also hurting your application performance etc. in the end you are not gaining much.

|||

You can add data files to a SQL 2000 database, within the same filegroup.

What this will accomplish is that the new data inserted into your table will start spreading to both files. It will not automatically rebalance existing data between files.

Have you experienced specific problems as a result of having a large table, or are you just anticipating that you might?

What is the storage that this database is on?

Looking for Lab files from class

I recently took a class on SQL 2005. The classwork had lab files but I did not manage to hang on to the files. Are those files available for DL from MS?

I am specifically looking for the files from Module 7 "Planning for Source Control, Unit Testing, and Deployment"

TIA

I am certainly interested in hearing what the MS policy is with respect to this type of question; looks interesting.

I have placed a note in the REPORTING forum so that I can get an answer as to how this needs to get handled.

|||

My experience as a trainer (MCT), is that it has been a longstanding situation where class materials are available to class participants only. And lab files, etc., are on the CDs that the class participant is provided along with the training manual(s). And that Microsoft will not replace materials to any 'class participants'.

Your best option may be to contact the class provider and see if they will replace your 'lost' class materials. Someo of the training providers are a bit more helpful in this way than others. I don't think that you will get anywhere with Microsoft.

You can sign up for, and use course 2781 modules on-line at:

https://www.microsoft.com/learning.com/