[dba-SQLServer] dba-SQLServer Digest, Vol 61, Issue 38

Hi John:  A few thoughts unrelated to the copy database issue:

I'm not sure I follow everything you do, but it sounds like you receive raw data, scrub it, then use it as a basis of reports (run queries, send the results to the client...).  It also sounds like you physically save the recordset results of those queries, and that the underlying base table does not change.

If that is the case, why not save only the queries you run, rather than the rows they return?  Create the queries as either views, or better yet build views and call them from stored procedures that can accept where clauses, topN parameters, etc.?  That approach would mean you don't have to manage tables, or entire databases, and instead only manage views and stored procedures.  That, by the way, is a use of sql server that is more in line with how it was designed.

One approach might be to have some base views, and a table that might look like:

tblOrders
Customer
RequestDate
BaseView
WhereClause
TopClause
Etc.

Some sample rows might be:

Abc Company
3/1/08
Column1, Column2 FROM dbo.vwSomeBaseView
WHERE BLAH = 'Blah' AND BLAHBLAH= ' BlahB%'
Top 3000

Abc Company
3/12/08
Column1, Column2, Column3 FROM dbo.vwSomeOtherBaseView
WHERE Humbug = 'Blah' AND Yada= ' BlahB%'
Top 10000

Using this approach, you only need to build a smaller number of views, you keep a record of who asked for what, when, and you can recreate the actual query very simply.

A stored procedure to do that might look like:

CREATE PROCEDURE usp_GetResultSet
@Customer VARCHAR(50)
, at Date DATETIME
AS
DECLARE @strSQL VARCHAR(500)
DECLARE @Top VARCHAR(50)
SELECT @strSQL= 'SELECT ' + ISNULL(TopClause,'') + BaseView + WhereClause
        FROM tblOrders WHERE Customer=@Customer AND RequestDate=@Date

PRINT @strSQL --just for debugging...
EXEC(@strSQL)


Calling this procedure as usp_GetResultSet 'Abc Company','3/12/08'
would give you:
SELECT Top 10000 Column1, Column2, Column3 FROM dbo.vwSomeOtherBaseView
WHERE Humbug = 'Blah' AND Yada= ' BlahB%'

Voila, no messy storing of new tables, etc.  I dunno, maybe that helps keep things tidier?






What I am (was, I have given up) trying to do is get some very simple help
on some very simple things, or they should be simple.  I have already built
a template database for "filling orders" from this database.  My client asks
me for a count (about 16 counts actually) of all the people who... Grouped
by.  SIMPLE stuff, and I have all of that working (in sql server) and have
for a long time (years).  I modify a couple of existing views to do the
Where... Part, then manually run the count views.  Copy the result "table"
into a dedicated sheet of an excel workbook.  16 pages, one page for each
"count", by state, by income bracket, by age bands and so forth.  All
working quite well.  BTW I keep these as a dedicated database for that order
(named after the order in fact) because I may need it later.  Not
sophisticated but simple and it works.

That is the "template database".

In fact there are two different kinds of orders.  The second kind is "OK,
now give me N thousand names / addresses from that count you gave me
before".  Again, I have been doing this stuff for quite some time, using a
strange mixture of SQL Server, a third party Address Validation program
running on a server here in my office, and Access (for the final phase).  I
have been doing all of that for months as well (years actually, I am just
getting much more efficient at it lately).  I decided that I could automate
this second type of order such that many of the bits and pieces were just
done by stored procedures.  I have done that as well.  All on my own, no
Priests involved.


The contents of this e-mail message and its attachments are covered by the Electronic Communications Privacy Act (18 U.S.C. 2510-2521) and are intended solely for the addressee(s) hereof. If you are not the named recipient, or the employee or agent responsible for delivering the message to the intended recipient, or if this message has been addressed to you in error, you are directed not to read, disclose, reproduce, distribute, disseminate or otherwise use this transmission.  If you have received this communication in error, please notify us immediately by return e-mail or by telephone, 530-893-3520, and delete and/or destroy all copies of the message immediately.