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