[dba-SQLServer] QLRE: Copy a database using a store procedure or function

Gustav Brock Gustav at cactus.dk
Tue Mar 25 17:55:00 CDT 2008

Hi John

How far have you pushed Visual Studio? I've found with 2005 that you can from inside this with DataTableAdapters etc. perform surprisingly many tasks which behind the scenes generate code directly in the database.
And with VS2008 you have LINQ which wraps much SQL in an OO style syntax much more in style with the other code you write.

This is some of the beauty of Visual Studio. Many ASP script kiddies still open a connection to the database and fire some SQL code created as a simple string. That way they use VS as classic ASP ten years ago which was horrible. The abstraction introduced by the Data Abstraction Layers "lifts" much of the coding to a level which you would expect today - indeed if you (like most of us here) are grown up with the power of Access and its GUI for building queries. 
And even if I respect - and think I understand - Paul's and Robert's arguments on writing pure SQL in the manager, I have the same feeling like you when I do so, that it can't be true I in this year have to sit and write GO in a primitive editor.


>>> jwcolby at colbyconsulting.com 25-03-2008 21:52 >>>
No Paul, you don't get it.  I don't want to code Sql server at all.  I
certainly don't want to edit a file on a directory in TSQL.  

I also don't want to go open a stall door, lead a horse out, put a harness
on it, hitch it up to my wagon, climb up and sit on a board and spend the
next hour bruising my butt getting three miles to the center of town.

Luckily I have a car.  Unfortunately I am still writing TSQL in SQL Server.

When you understand how TSQL equates to a horse and wagon you will
understand what I mean.  You are hitching up a wagon each time you do
anything in SQL Server and the sad part is, you shouldn't have to.

I can build entire systems in VB or Access in a matter of days or weeks.
Reams of forms, reports, queries.  Code that allows me to connect to the
internet, download files from an FTP site, open the files, import them into
the database, export the data back out, attach them to emails and send
results off to clients.  Writing SQL at all is a total waste of (my) time.

15 years ago designing one single form took days and weeks of programming in
c++.  Now I can drag and drop objects out onto forms, set properties, bind
them to data, enter data, see the data, report the data in hours.

You are still living in the "weeks to do a form" era, and you obviously like
it, and that is fine... for you.  It is not fine for me because I make my
living doing stuff quickly.

Please, do not EVER suggest to me editing TSQL in files on a disk.  The
probability that I will even read the post is somewhere between 0 and
negative infinity, and rapidly approaching negative infinity.

I'm trying to get WORK done here.  

Work BTW is not writing TSQL.  Work is receiving a job in an excel
spreadsheet attached to an email, creating a directory structure on my hard
drive to hold the pieces and parts, saving the order spreadsheet into that
directory structure, copying the SQL Server template database,  modifying a
view in the copied database to pull the right records, exporting the data to
a CSV file on an address validation server, processing that CSV file through
a third party Address Validation software, reloading the cleaned data (CSV
file) into SQL Server, deduping the addresses, selecting a random set N of
those address, Exporting them to a flat file, marking them as exported,
zipping the flat file and FTPing it or attaching it to an email, then
emailing the clients telling them the order went out.

All with a minimum of my time spent.  It should take me 1/2 hour (of my
time, MAXIMUM) to do all of that stuff.

Do we see how I truly do NOT want to be writing SQL (in a file on a
DISK!!!)?  Do we see how spending years learning TSQL AT ALL is not in the
cards?  I don't want to learn TSQL, I don't want to WRITE TSQL, I want to
build an application that builds my orders from a few pieces of information
typed into a form, logs the processor time used for the various tasks into
tables, executes the order, transmits the order to the client, computes a
bill for my processing time, and emails the bill to the client.  

In fact I REALLY want the client to type his own damned order information
into a form on the internet, and receive his order and a bill for my
processor time over the internet.  


John W. Colby
Colby Consulting
-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com 
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Paul
Sent: Tuesday, March 25, 2008 4:08 PM
To: 'Discussion concerning MS SQL Server'
Subject: Re: [dba-SQLServer] Copy a database usinga storeprocedure

Source code belongs in source code control. Developing in a gui is just
plain dangerous. 

All I'm saying is that when you code the sproc, do it in query editor and
then save the file. Don't use Object Explorer to create new proc, save it in
the database and then edit it by opening it from the database. It's the same
code, the difference is that the database is the destination of the code,
not where you keep it. You keep the code in source control. 

I do all my development from files in directories that I open, edit, and
then run. It's the fastest way to develop with SQL Server. 

If you develop with multiple databases and multiple severs then you really
need to work this way. You open you file, connect to whichever server you
want, and then press F5. It's fast, simple, and easy. 

In SQL Server lingo, any submission to the database is a batch. If you
highlight a query and press F5 - that's a batch. If the batch is saved in
the database, that's a stored proc. If you've been working with SQL Server
you've been doing batches. The question is only where do you want to save
your source code? 


More information about the dba-SQLServer mailing list