jwcolby
jwcolby at colbyconsulting.com
Thu Mar 27 10:54:28 CDT 2008
LOL, not at all. David is a nice guy responding with friendly suggestions.
No knuckle rapping required. ;-)
John W. Colby
Colby Consulting
www.ColbyConsulting.com
-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Eric Barro
Sent: Thursday, March 27, 2008 11:25 AM
To: 'Discussion concerning MS SQL Server'
Subject: Re: [dba-SQLServer] dba-SQLServer Digest, Vol 61, Issue 38
Oh oh...here we go again...you might do well to read the archives David,
otherwise you'll get a rap on your knuckles from JC.
-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of David Lewis
Sent: Thursday, March 27, 2008 8:02 AM
To: dba-sqlserver at databaseadvisors.com
Subject: Re: [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.
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com