[dba-VB] How do you run SQL Server processes

Shamil,

 > Using table-driven process could be not quite right in C#/.Net world, which proposes many other 
options to implement generic solutions - I'd just start with "hardcoded" solutions using calls to a 
set of small "generic"

That is precisely what I do right now, and what I will do for the near future.


The table driven thing is simply that I write "processes" as discussed in this thread. A process 
might be:

Export To Accuzip: "Export dbX TblY to files in DirectoryZ".

However that breaks down onto a set of a handful of stored procedures, with passed in parameters. 
Now, the process "Export To Accuzip" is applied against 6 or 8 different databases every month, but 
ALSO against random databases which are orders.  Orders come in every month and they are a whole 
nother discussion, but PART of an order is the process Export to Accuzip.

DatabaseA exports files to its own directory, DatabaseB to its own directory, OrderAA to its own 
directory and so forth.  But the EXPORT itself is a very standard process.

So I am examining the concept of storing the process in a table, so that I can simply select (from a 
form:

Process X:

Use SP1, params
use SP4, Params
use SP9, Params

Process Y:

Use SP1, Params
Use SP2, Params
Use SP5, Params

Process Z:

Use...

Table driven (to the extent possible).

Then a supervisor application could execute a process (set of SPs), read out of a table, passing in 
only the database name and possibly a directory on disk.  Everything else is selected and set up in 
advance.  To see what a process is, just open a table, to modify the process, select different 
stored procedures.  This stuff is VERY standard, with just a few things that change from DB to db 
(the database name and where files are going) however I do occasionally shufffle around the interior 
processes (indexes created, fields used etc.  That stuff is internal to the SP and if I change that 
it is in the SP itself.  Then that change is applied to every database from then on.

I am not saying that this will work but as my understand and knowledge changes I am spending a LOT 
of time rewriting stuff.

John W. Colby
www.ColbyConsulting.com


Shamil Salakhetdinov wrote:
> Hi John,
> 
> <<<
> I am hoping to eventually make the entire process "table driven" where I can
> store (in a table) the 
> name of the sp, the parameters in a child table etc.
> Using table-driven process could be not quite right in C#/.Net world, which
> proposes many other options to implement generic solutions - I'd just start
> with "hardcoded" solutions using calls to a set of small "generic"
> classes/methods executing custom (MS SQL) database processing, when that set
> of methods/functions stabilizes I'd try to generalize/"generic-ize" it if
> that will be needed at all...
> 
> What is the purpose of your "going generic" for this sets of tasks/customer?
> 
> Isn't it "just for fun" of generic programming? 
> Would that "going generic" make your programming more effective? 
> 
> Developing/debugging/testing generic custom "table-driven"/"other-metadata
> media driven" solutions is always (much) more time consuming than
> "hardcoded" ones, and supporting "table-driven" generic custom solutions,
> especially in the context of constantly changing customer requirements also
> promise to be rather time consuming - I mean generic
> "table-"/metadata-driven solutions could only(?) pay back well in the case
> they are used on many deployment sites by many people. I can be wrong, just
> talking from my experience, which is not universal of course...
> 
> I'd better propose to use metadata descriptions/specs (stored in db tables
> or (XML) files), if you're so bored to write repetitive code, to generate
> "hardcoded" solutions, then make quick fixes to this generated hardcode to
> satisfy your customers' current requests, then (in free time) adjust code
> generator, then when everything will stabilize (will that ever happen?)
> develop truly generic "table-driven" solution.
> 
> You can also consider using F# and Windows Workflow Foundation (WF)...
> 
> The following presentation seems to be useful to get true picture of
> "generic vs. specific" solutions trade-offs:
> 
> http://www.infoq.com/presentations/Generic-Specific-Tradeoffs-Stefan-Tilkov
> 
> 
> Thank you.
> 
> --
> Shamil