There are about 12 now and I can see adding maybe 6 more over time. At this point we are not doing any reports or querying across DBs so that issue has not come up.

I guess my method must be not too terrible since I haven't had anyone calling me an idiot yet. <g> I guess I'll keep using my current system unless I find something better down the line.

>>Timothy,
>>
>>This thought had occurred to me also, but in this case, the system was designed by others and the decision was made a long time ago to create separate databases. Even at that, I mostly agree with the design. Last year one of the entities was sold and it was very easy to take that data and move it to another system. There is also never any cross-posting between the entities so this way keeps the sizes down and performance up.
>
>Thanks Bill, it makes sense, What is the most number of db's you think the server could end up containing which are all alike? This is just purely a question of interest regarding this topic. Don't take it as critical. I had worked on another project where this was done also and the name of the DB being different for each caused me some grief when I needed to gather data accross them. I felt then, they should have been all in one DB. Only reason why it is interesting.
>
>>
>>Bill
>>
>>>My curiosity comes more from the idea of duplicating a database many times. If this was done at different locations, it is understandable but I have seen situations where a master database is used to create a new database for several customers or other entities and I am not sure I understand this. I am not saying it is wrong here, don't get me wrong, but I am wondering why not have a key that identifies the entity within THE database. Would love to hear the thoughts about this. My thinking can always be adjusted :-)
>>>
>>>
>>>>That model db approach makes me wary. For one thing it doesn't seem clean. IMO model should be as stripped down and generic as possible, which is in fact the way it is designed. I wouldn't want to have a bunch of very specific SPs in there, doing things like retrieving a set of vendor names in an inventory app. The other problem I see here is it wouldn't do Bill any good since he is working with existing databases, not newly created ones.
>>>>
>>>>I don't really have a better answer, unfortunately. I was thinking of doing it through database connections, putting the SPs in the central database and obtaining a connection to that DB as well as the app-specific DB. But then you have the issue of the SP needing to know which DB the data is stored in. Pass the app-specific DB connection handle as a parameter? That would take some work to add the parameter to calling programs but seems like it would be straightforward.
>>>>
>>>>>If the sps are the same for every DB, if you put them in the model db they'll become part of each new DB you create.
>>>>>
>>>>>If they are enough different to require customizing in each DB you might take a look at how the templates work in SSMS and create templates.
>>>>>
>>>>>
>>>>>>I have recently joined a VFP project in progress. It is a very large VFP project using SQL Server 2008R2 for all data storage. It is an accounting system and has about a dozen separate databases, each storing the data for one accounting entity. There is one central DB that holds data that can be used for any of the companies such as defaults, look-ups, etc. I am doing a bunch of reports and decided to create Stored Procedures that I can pass parameters to, to provide the data for each report.
>>>>>>
>>>>>>I originally thought I could put all of the SP's in the central DB but I was unable to figure a way to pass the needed DB name and query that DB when doing it this way. I then decided to create the SP's in each DB but this seemed like a maintenance nightmare since there are currently 12 DB's and could be more going forward. I finally decided to create report programs that would create the stored procedures on the fly as needed when doing the reports. This is pretty quick and also has the SP advantages. So far it is working well but I am having second thoughts that this is the best way to do this. I just want to make sure I am doing what is best for the project.
>>>>>>
>>>>>>Any thoughts?
>>>>>>
>>>>>>Thanks,
>>>>>>Bill