Putting SQL Artifacts Into Source Code Control

What are the best options for putting SQL Server artifacts like triggers
into source code control, and then automating moving the objects from human
readable files back into the database as triggers?
Are any of the good options cheap?
WillWe use VSS for source control of all DDL and code. This is far from
"automated" and requires a good bit of discipline on the part of your
developers. I've heard the following product is pretty good...
http://www.nobhillsoft.com/randolph.aspx .
HTH
MJKulangara
http://sqladventures.blogspot.com|||Will (westes-usc@.noemail.nospam) writes:
> What are the best options for putting SQL Server artifacts like triggers
> into source code control, and then automating moving the objects from
> human readable files back into the database as triggers?
> Are any of the good options cheap?
For cheap in terms of money, I can offer http://www.abaris.se/abaperls/
which is the toolset we use to load SQL object, build databases and
create change scripts. Not necessarily cheap in terms of labour hours,
we've been using this since 1996, and we constantly refining it, so it's
a bit complex by now. :-)
There are a couple of third-party tools out there, like DBGhost and
a few more that I don't remember the name of.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi
Along with the other suggestion you can always use EM or DMO to script into
individual files and then load them up. I suggest that you have specific
areas for different types of objects to avoid clutter.
John
"Will" <westes-usc@.noemail.nospam> wrote in message
news:3tCdnR6hmOwAo3neRVn-pA@.giganews.com...
> What are the best options for putting SQL Server artifacts like triggers
> into source code control, and then automating moving the objects from
> human
> readable files back into the database as triggers?
> Are any of the good options cheap?
> --
> Will
>

putting names of objects to control-flow loop creating objects

please help newbie

I need to create a lot of objects the same type (let's say: schemas)
I wish to use paramerized block in loop to do so.
- how to put names of my objects to such control-flow?

belss you for helpfireball wrote:

Quote:

Originally Posted by

please help newbie
>
I need to create a lot of objects the same type (let's say: schemas)
I wish to use paramerized block in loop to do so.
- how to put names of my objects to such control-flow?
>
>
>
belss you for help

Firstly the obvious question: Why? Where are the names from these
objects coming from? If you can write a query to extract the names then
you could just use Query Analyzer or Management Studio to paste those
names into an editable script and then run the script directly. That
way there is no need for a loop.

If you must do it programmatically then you'll have to do something
with dynamic SQL. See:

http://sommarskog.se/dynamic_sql.html
Personally I'd say that if you have so many schemas that you need a
loop to create them then you definitely have too many schemas... or you
are using them in a highly unconventional manner.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||Uzytkownik "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.orgnapisal w

Quote:

Originally Posted by

Where are the names from these objects coming from

from text file I got

Quote:

Originally Posted by

if you have so many schemas that you need a loop

first idea is to build scripts in engineer maneer (reusing code blocks - not
to copy them - so managing any changes is better)

second reason - I got my analysis data model in RaRose, which doesn't really
support automatization of creating data model. I use creating
quasi-sqlserver scripts, so I have object names and so. It's not really
large amount of objects (a few schemas, about 100 tables) - but it does
change any time.
Sorry if it sounds a bit werid :-) I'm a newbie

third idea was to create that way descriptions to my obiects (tables,
attributes) in a loop (having descriptions in xls file, for example)

All hints will be appeciated.

ps:

Quote:

Originally Posted by

then you definitely have too many schemas...

you are definitely right.

Quote:

Originally Posted by

are using them in a highly unconventional manner.

like, let's say - to make some perfiormance tests/statistics? (I really
don't do so :-))|||fireball (fireball@.onet.kropka.eu) writes:

Quote:

Originally Posted by

Uzytkownik "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.orgnapisal

Quote:

Originally Posted by

>Where are the names from these objects coming from

>
from text file I got

But text file is not SQL, but you have to transform it to SQL?

Doing this from SQL is not really fun. If you are on SQL 2005, you
could do this through the CLR, but you would still go through hoops.
Do this from a client application: Perl, VBscript or whatever your
favourite may be.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Uzytkownik "Erland Sommarskog" <esquel@.sommarskog.senapisal w wiadomosci

Quote:

Originally Posted by

But text file is not SQL

well, the point is to put it into script.sql any way which I will be able to
fetch it into my loop|||fireball (fireball@.onet.kropka.eu) writes:

Quote:

Originally Posted by

Uzytkownik "Erland Sommarskog" <esquel@.sommarskog.senapisal w wiadomosci
>

Quote:

Originally Posted by

>But text file is not SQL

>
well, the point is to put it into script.sql any way which I will be
able to fetch it into my loop

Without having seen your file, it's difficult to tell, but it does not sound
as if trying to read it from SQL is a very good idea. You probably much
better off doing this in a client language.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx