[dba-SQLServer] Copy a database using a storedprocedure or function

Paul Nielsen pauln at sqlserverbible.com
Wed Mar 26 16:16:36 CDT 2008


>> SQL Server (interface) is BARELY superior to dbase II in a few areas and
much worse in
others.

I don't believe you've ever used the SSMS in the way I've been promoting. If
you did, you'd be in love with how easy and quickly you get the work done.
So you have no right complaining about what you refuse to try. 


>> nor do I need a gigabyte disk full of directories full of sql statements
all neatly cataloged 

If you understood client/server, you'd realize how ridiculous that sounds.
Most SQL Server databases I've written are in a single script 20K - 100K. 

>> I just need a small template database with about 30 views and 20 stored
procedures.

I know what you're trying to do. I can bill it in my sleep. Every database I
build is a template style database. I know exactly how to do that specific
task the easiest way possible. 

As I've been trying to tell you, the ***EASY*** way to build a template
database is by a single script that creates the database, creates the
tables, views and procs. Open script, point to server. Edit db name. press
F5. Done. I've done this hundreds of times.

Ever hear of Agile development? Doing it your way is the old way. The Agile
way is faster and better. For agile development use 1 script for DDL code,
one script for sample test data, and a third script for unit testing. I can
go through iterations much faster than you can develop and test even in
Access. SSMS screams developer performance when you know how to develop in a
way that works with client server rather than fighting against it.

>> I am here on this forum as a know nothing nubee, who will NEVER know
1/100th what you know ABOUT SQL SERVER.

Then why not listen when I say, "you're doing it the hard way, this way is
much easier?" Why insist that my advice is stone-aged? 

---
You literally have no idea what you're asking for. And when I and others try
to tell you how to do what you want the *easy* way, you kick, scream, and
insult - complaining that it's too hard, but you really have no idea what
we're even suggesting. Rather than saying, "Hmmm, ok how would I do that?",
you insult those offering best practices and advice.

How many times has a client told you that their specific application is
different and unique, when you knew all along that they just needed xyz? And
when you tried to help, they just refused to even hear what you told them. 

I've built many large SQL Server apps that brought in Gbs of mainframe data,
cleaned and ETL'd it into a DW. One that sounds almost exactly like the one
you're working on. I can help you avoid the pitfalls you don't even know
about. Yet. 

So you don't need FKs? You still need a PK. So you need to select random
rows? You think you need to add a column and index it? Did you look up the
tablesample suggestion? Tablesample with order by newID() is what you need.

I'll say it again. No one who is efficient with SQL Server develops using
the click interface of SSMS. Smart SQL Server db developers write and
execute scripts because they know that DDL is valuable source code and the
database is the execution of the source code, not where you deposit source
code. And because it's easier and faster.

In Access, you just built up a database by clicking, maybe opening up an
event and adding some code, and then you copied the database around. That's
fine for Access, but it's a pain in SQL Server. You complain it's a pain, I
show you how to avoid the pain, you insult and rant more. 

The reasons you hate SQL Server are ALL due to your old style of development
- not SQL Server, not BOL, not the tool.

You want to fly a 777 but you complain because it's different than driving
your old truck.

Now, I have a chapter to finish. 

-Paul  






-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Wednesday, March 26, 2008 2:18 PM
To: 'Discussion concerning MS SQL Server'
Subject: Re: [dba-SQLServer] Copy a database using a storedprocedure or
function

Eric,

Part of the problem though is that SQL Server can do many different things.
I don't need any of the stuff you mentioned.  My lists are not relational,
they are one long wide list.  I split off the name and address and maintain
that as a separate list but still not parent / child, rather 1-1.  I don't
need all that stuff but I DO need sql server.  It is the only thing powerful
enough to process a list of 100 million names with 150 fields and indexes
etc.  So my point is that I really and truly DO NOT NEED all of that stuff.
I need a small handful of concepts, most of which I have already learned.  I
am here now trying to copy a database (for example).  Never got that far.  I
do not want, nor do I need a gigabyte disk full of directories full of sql
statements all neatly cataloged.  I just need a small template database with
about 30 views and 20 stored procedures.

Someday soon I will not even need that as I move my logic to .NET but right
now I do.

So telling me to go memorize BOL is simply a dreadful and unrealistic
response to my questions.  Even if I could, I would never use it again in
the next two years and what would be the point of all that effort?

You really and truly need to get that there are an entire spectrum of uses
for SQL Server from an IBM to a tiny 5 man virtual company processing
address lists.  I have to know 10 times as much stuff ENTIRELY UNRELATED TO
SQL SERVER as I do stuff related to sql server, just to handle the little
address list company.

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: Wednesday, March 26, 2008 2:17 PM
To: Discussion concerning MS SQL Server
Subject: Re: [dba-SQLServer] Copy a database using a storedprocedure or
function

Gustav,

It's no different than moving from dBASE to Access and then from Access to
SQL server.

Access changed the programming paradigm from what we were used to in dBASE
to an event-driven paradigm. The simplest things that one could do with
dBASE code was such a pain to figure out with Access 1.0 since the paradigm
shifted to an event-driven approach. There was a learning curve to overcome.
Ditto when moving from Access which spoiled us with the GUI drag and drop
functionality for querying data. Enterprise Manager for SQL server was such
a pain to grasp simply because SQL server wasn't made to have a GUI.

I hated the idea that SQL server didn't have the same GUI that Access
had...after all, it was made by the same company -- Micro$oft. But
oftentimes the GUI just gets in the way. Same deal with Sharepoint. I'm a
nuts and bolts type of guy who likes to "peek under the hood" to see how the
"engine" is working its magic. The GUI becomes a better tool if you know
exactly how things work "under the hood".

Stored procedures, user-defined functions that return either values or
result sets, temporary tables and temporary table variables, data
partioning, scheduled jobs, role-based security (integrated with windows or
native sql server), full-text cataloging for search engine-like
functionality, support for VB, scripting objects, replication, etc... --
these are the things that make SQL server the workhorse it is.

Sure it takes time to learn these and be good at it. But isn't that true
with the other products (dBASE, Access, etc..)?

If we really wanted something simple and easy to use, why not buy a MAC?

Eric
---------------------------------------------------
From: Gustav Brock <Gustav at cactus.dk>
Date: 2008/03/26 Wed PM 12:20:11 CDT
To: dba-sqlserver at databaseadvisors.com
Subject: Re: [dba-SQLServer] Copy a database using a stored procedure	or
function

Hi John, Robert, Paul et al

We use SQL Server as a data store and not as an "application". I'm a firm
believer that only low-level logic should be handled by the engine while any
high-level business-rule-style logic belongs to a middle-tier or the
frontend or - if that is not possible - should be fully maintained by and
controlled from either of these. One reason for this is exactly what you are
arguing about, that it takes a life's work to become an excellent T-SQL
programmer and neither I nor the company I work for can allocate such
resources.

Also, this discussion reminds me of the days of DOS. I cannot count how many
batch files I wrote in the eighties and nineties for just about anything
including full screen login systems for NetWare and sophisticated menu
systems. PC Magazine in those days had a column of advanced batch
programming often beyond belief. I've kept it all somewhere, but since
Windows 95 arrived it has never been used and users stopped requesting such
services. I will never be used again, but this is where I understand John;
when I go to that SQL editor it is close to typing in Edlin - you only miss
the piping! Wow, that was great (wiping a small tear). 
When you move to VS and its great environment you feel the difference.

Having worked with the manager of version 6.5 years back, however, the
current manager represents a vast improvement, and it is quite nice that you
can run it together with Visual Studio which overlaps quite a lot while you
have the same look and feel.

/gustav

>>> jwcolby at colbyconsulting.com 26-03-2008 16:40:56 >>>
Robert,

Making judgments about what I do for a living is unhelpful and in fact
downright inappropriate.  Peoples careers range from terrorists to Catholic
bishops.  I don't see you becoming a priest.  ONE thing that I do is a
database for a company that sells address lists to companies that send bulk
mail.  I also write databases for non-profit organizations that help parents
of children with disabilities.  And many other databases.  I am not killing
people, I am filling a very valid request of a very valid company.

Bitching, yea I am guilty.  

To say that I do not know what the previous versions are like is not true
and beside the point.  I know what dbaseII was like in 1985 and I expect the
tools 23 years later to be superior (in user interface).  SQL Server
(interface) is BARELY superior to dbase II in a few areas and much worse in
others.  You can make excuses for the tool all you want, it still SUCKS.
Call it bitching if you want, I am simply calling your favorite spade a
spade.  It SUCKS.  Alright?  Not complaining, simply stating the obvious.
Having spent your entire adult life working in it may make it SEEM (to you)
like it doesn't SUCK but, in fact, it SUCKS.

Now, if you wish to take offense at my stating that the tool sucks, have at.
Notice that Gustav pretty much also stated that the tool (interface) sucks.
Rant at him a bit why don't you.  

The emperor has no clothes, though he remains a pretty powerful emperor.

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 Robert L.
Stewart
Sent: Wednesday, March 26, 2008 9:21 AM
To: dba-sqlserver at databaseadvisors.com
Subject: Re: [dba-SQLServer] Copy a database using a stored procedure
orfunction

Paul,

Probably not.

John,

There is no such thing as a SQL Server USER. If you do not want to hire a
DBA/SQL Programmer, then you are it, like it or not.

3rd party tools are there for a reason. They were there for Access also. If
you don't like the FREE tools, buy one that you do like. Since you have no
idea what the previous versions of SQL Server were like, you have no
reference point for the current version.  I have used and programmed with
SQL Server since 3.21. It has come a long, long way since then.

I gave you a 2/3's completed solution, written as it should have been, in
T-SQL. Stop the bitching and complaining. If you need help, you have
received it from this list. But, I have to agree with Francisco, you got the
help even if you are enabling spamming and junk mail.

Robert

At 08:25 PM 3/25/2008, you wrote:
>Date: Tue, 25 Mar 2008 19:25:44 -0600
>From: "Paul Nielsen" <pauln at sqlserverbible.com>
>Subject: Re: [dba-SQLServer] Copy a database using a    store
>         procedureorfunction
>To: "'Discussion concerning MS SQL Server'"
>         <dba-sqlserver at databaseadvisors.com>
>Message-ID: <004401c88ee0$51061230$f3123690$@com>
>Content-Type: text/plain;       charset="us-ascii"
>
>But you are using version control for all source code including DDL 
>code, right?
>
>-Paul
>
>
>
>
>-----Original Message-----
>From: dba-sqlserver-bounces at databaseadvisors.com
>[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of 
>jwcolby
>Sent: Tuesday, March 25, 2008 7:03 PM
>To: 'Discussion concerning MS SQL Server'
>Subject: Re: [dba-SQLServer] Copy a database using a store 
>procedureorfunction
>
>I am not criticizing anybody, I am simply pointing out that there are 
>many different jobs.  Mine is not SQL Server Administrator, nor SQL 
>Server programmer.  Mine is SQL Server user.  SQL Server is an 
>immensely powerful system where the human interface is just emerging 
>from the stone age.  Like anyone familiar with the stone age tools, 
>they are good enough to get the job done, if you happen to be familiar 
>with the stone age tools.  If you are familiar with high powered rifles 
>and razor sharp knives,and airplanes to get to the hunt, the stone age
hunting tools are ... well.. Stone age.


_______________________________________________
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

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com


__________ NOD32 2975 (20080326) Information __________

This message was checked by NOD32 antivirus system.
http://www.eset.com





More information about the dba-SQLServer mailing list