jwcolby
jwcolby at colbyconsulting.com
Wed Mar 26 23:48:34 CDT 2008
David, A refreshing bit of reality, thanks for that. My problems are really pretty simple. I think most here can't believe that but they really are. I am building a small relational database, which allows me to track the business side of my business. I am doing that mostly as an exercise in using VB.Net to manipulate SQL Server, and that database is not my problem. SQL Server can be viewed as simply a data store. The "Priests" don't want you to believe that but that is at heart what it is. All the rest is stuff to help manage the data store. If your job is managing a transactional database for Bank of America or something similar, then yea, you need every one of those specialists, in spades. OTOH, there are many cases where the requirements are much less stringent. Mine is one of those. My requirement is for a system that can handle single tables with millions to hundreds of millions of rows, with dozens to hundreds of fields. But in the end they are just SINGLE tables, unrelated to anything else. And furthermore there is not a transaction in site! I just import massive fixed width files, do some minor manipulations to get the field sizes fixed (and that isn't even strictly necessary), then build a bunch of cover index on sets of fields. Each list has name / address info which I do pull out into a separate table, related back to the original by a PK, but again, a one to one. Once the ORIGINAL table is built, it is never modified. EVER. I do build indexes but that is all. The lists just sit there. I take the name / address info and ship it out for address validation, then import it back in. Since it relates one to one with the original table, I now have valid addresses in one table and the rest of the data in the original table. No transactions. As such, I don't need probably 99% of the fancy stuff that SQL Server provides, at least not for this project. It is just a data store. 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. I came here to this list because there was one specific piece that I hadn't a clue about, but I thought would be dead simple to the Priests. I need to copy that template to a database with a new name. I can (and do) copy the database using the wizard, I just figured that it would be a few lines of something, embedded in a SP and I could then add that to my list of existing SPs that automate what I do. Alas, all I got were lectures. The Priests do not like that I do not find their priesthood something that I wish to join, nor that I do not desire to spend the rest of my life memorizing arcane TSQL syntax. In fact it pisses them off royally that I even find it arcane, though of course it is to the laity. In my mind, I should not have to be a Priest. That is not what I do. I get by with most of it, I just needed one (seemingly) simple piece. So, in the end you are right, Access wouldn't fit the bill. I need SQL Server to be a fast, large data store for an EXTREMELY simple structure of entirely static data. As you can plainly see from the above description, I do not need to be a Priest, I just need a quick answer. I need (still need) to be able to do one thing that I had not figured out, and I got a lot of (dare I say) CRAP that simply reminds me why I do not come here often. I have come here a few times and always go away shaking my head at the lectures about stuff that is no use to me. And BOL. And now even lectures about spam and bulk mail. What makes it REALLY sad is that the Priests think they are doing me a favor with the lectures! Priests are like that though. ;-) My fervent hope is that some of those on this list will take this to heart and just decide that helping someone do something is enough. I helped start this list in the hope that I could get help and it is virtually useless to me. Hasta Luego and may you find yourself useful to a nubee some day. 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 David Lewis Sent: Wednesday, March 26, 2008 11:29 AM To: dba-sqlserver at databaseadvisors.com Subject: [dba-SQLServer] QLRE: Copy a database using a stored procedureor Hi John: I don't think this will really 'help' you at this point, but it may make it a little easier for you to accept your situation the next time you start getting annoyed... You accepted a job that requires (form the sounds of it) a full-scale rdms -- Access wouldn't fit the bill. All the functionality that Access wrapped up neatly into gui's is in sql server, but not necessarily in a gui, and generally in a much more powerful and flexible form. But, as you are discovering, each of the things you are trying to do requires quite a learning curve. That isn't the fault of sql server -- it was built for needs that presuppose a fairly deep bench. Most places that use all the functions that you are using have a person(s) that specialize in each of the functions. There is a T-sql guy, an SSIS guy, a dba for tuning, backups and restores, etc. You are wearing many hats, and all the hats are pretty sophisticated. To take a very simple example, if you are used to building queries in Access using its gui, you've got a repertoire of approaches to getting the data you want. When you move to sql server, the gui for building queries is pretty crude, and if you take the plunge and go to the screen to write raw t-sql, you begin to discover many many many t-sql commands that either you didn't know existed, or had only heard of but had never tried (I am speaking from my own experience). Many of these turn problems that in access were a pain in the neck to solve, into pretty something trivial. I am speaking of sub-queries, case statements, correllated sub-queries, derived tables, etc. etc. etc. Using these in Access is nearly impossible, or actually impossible, so Access people generally never learn to use them. At least that was my experience (I didn't learn to write t-sql until I moved to sql server, and now I shudder at the thought of having to use access' gui to build a query). This example holds true for all the tools you are trying to use. Not a pleasant or comfortable state of affairs for you, but that is the way it is. When I have found myself in a similar position in the past, for example completely stuck on how to use DTS (now SSIS), I looked for a person who could work side by side with me to get things done; in that way I was able to learn from an expert (or at least someone with a lot more experience than I). That means spending money on a consultant, and I don't know how that fits with the economics of this job, but either way you look at it you need training or help, and that generally doesn't come cheap. Hope this helps (but of course I realize it likely just pissed you off more!) David 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