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 calculated value into excel

I'm using VB to write some data to an Excel spreadsheet.
I use the following vb/sql statement to get the data I need:
ADO_rs.Source = "select least(2+(0.044117*CT_OFF_GEN),8)/count(*)as mydata2
calculate avg from """ + "6b_NRatio" + """.1 where ct_text like 'on/off
it produces the following correct output:
ct_off_time mydata2
-- --
05-MAY-03 18:57:57.7 1.97044
24-JAN-03 11:46:07.8 8
16-AUG-03 09:58:27.9 5.30701
......
16-AUG-03 21:22:57.7 6.09582
10-DEC-02 16:46:37.6 8
--
AVG 5.25773
If I could change the above code where I could store the "calculate avg as
mydata3" I could then put the data into excel.
But I can't do this
How can I store the AVG 5.25773 into a variable so I can use it in Excel
like so:
ADO_rs.Open
ActiveCell.Offset(x, 5).Value = ADO_rs.Fields!AVG GOES HERE
Maybe there is an easier way to do this' Any suggestions'
DavidHi
This seems to be very Excel specific SQL and therefore not really covered by
SQL server!
At a guess you will need to move the Average function into the select
columns
John
"Aster99" <Aster99@.home.net> wrote in message
news:%23igwDlOKEHA.204@.TK2MSFTNGP10.phx.gbl...
> I'm using VB to write some data to an Excel spreadsheet.
> I use the following vb/sql statement to get the data I need:
> ADO_rs.Source = "select least(2+(0.044117*CT_OFF_GEN),8)/count(*)as
mydata2
> calculate avg from """ + "6b_NRatio" + """.1 where ct_text like 'on/off
> it produces the following correct output:
> ct_off_time mydata2
> -- --
> 05-MAY-03 18:57:57.7 1.97044
> 24-JAN-03 11:46:07.8 8
> 16-AUG-03 09:58:27.9 5.30701
> ......
> 16-AUG-03 21:22:57.7 6.09582
> 10-DEC-02 16:46:37.6 8
> --
> AVG 5.25773
> If I could change the above code where I could store the "calculate avg as
> mydata3" I could then put the data into excel.
> But I can't do this
> How can I store the AVG 5.25773 into a variable so I can use it in Excel
> like so:
> ADO_rs.Open
> ActiveCell.Offset(x, 5).Value = ADO_rs.Fields!AVG GOES HERE
> Maybe there is an easier way to do this' Any suggestions'
> David
>sql

Putting calculated value into excel

I'm using VB to write some data to an Excel spreadsheet.
I use the following vb/sql statement to get the data I need:
ADO_rs.Source = "select least(2+(0.044117*CT_OFF_GEN),8)/count(*)as mydata2
calculate avg from """ + "6b_NRatio" + """.1 where ct_text like 'on/off
it produces the following correct output:
ct_off_time mydata2
-- --
05-MAY-03 18:57:57.7 1.97044
24-JAN-03 11:46:07.8 8
16-AUG-03 09:58:27.9 5.30701
......
16-AUG-03 21:22:57.7 6.09582
10-DEC-02 16:46:37.6 8
--
AVG 5.25773
If I could change the above code where I could store the "calculate avg as
mydata3" I could then put the data into excel.
But I can't do this :(
How can I store the AVG 5.25773 into a variable so I can use it in Excel
like so:
ADO_rs.Open
ActiveCell.Offset(x, 5).Value = ADO_rs.Fields!AVG GOES HERE
Maybe there is an easier way to do this' Any suggestions'
DavidHi
This seems to be very Excel specific SQL and therefore not really covered by
SQL server!
At a guess you will need to move the Average function into the select
columns
John
"Aster99" <Aster99@.home.net> wrote in message
news:%23igwDlOKEHA.204@.TK2MSFTNGP10.phx.gbl...
> I'm using VB to write some data to an Excel spreadsheet.
> I use the following vb/sql statement to get the data I need:
> ADO_rs.Source = "select least(2+(0.044117*CT_OFF_GEN),8)/count(*)as
mydata2
> calculate avg from """ + "6b_NRatio" + """.1 where ct_text like 'on/off
> it produces the following correct output:
> ct_off_time mydata2
> -- --
> 05-MAY-03 18:57:57.7 1.97044
> 24-JAN-03 11:46:07.8 8
> 16-AUG-03 09:58:27.9 5.30701
> ......
> 16-AUG-03 21:22:57.7 6.09582
> 10-DEC-02 16:46:37.6 8
> --
> AVG 5.25773
> If I could change the above code where I could store the "calculate avg as
> mydata3" I could then put the data into excel.
> But I can't do this :(
> How can I store the AVG 5.25773 into a variable so I can use it in Excel
> like so:
> ADO_rs.Open
> ActiveCell.Offset(x, 5).Value = ADO_rs.Fields!AVG GOES HERE
> Maybe there is an easier way to do this' Any suggestions'
> David
>

Putting calculated value into excel

I'm using VB to write some data to an Excel spreadsheet.
I use the following vb/sql statement to get the data I need:
ADO_rs.Source = "select least(2+(0.044117*CT_OFF_GEN),8)/count(*)as mydata2
calculate avg from """ + "6b_NRatio" + """.1 where ct_text like 'on/off
it produces the following correct output:
ct_off_time mydata2
-- --
05-MAY-03 18:57:57.7 1.97044
24-JAN-03 11:46:07.8 8
16-AUG-03 09:58:27.9 5.30701
.......
16-AUG-03 21:22:57.7 6.09582
10-DEC-02 16:46:37.6 8
AVG 5.25773
If I could change the above code where I could store the "calculate avg as
mydata3" I could then put the data into excel.
But I can't do this
How can I store the AVG 5.25773 into a variable so I can use it in Excel
like so:
ADO_rs.Open
ActiveCell.Offset(x, 5).Value = ADO_rs.Fields!AVG GOES HERE
Maybe there is an easier way to do this? Any suggestions?
David
Hi
This seems to be very Excel specific SQL and therefore not really covered by
SQL server!
At a guess you will need to move the Average function into the select
columns
John
"Aster99" <Aster99@.home.net> wrote in message
news:%23igwDlOKEHA.204@.TK2MSFTNGP10.phx.gbl...
> I'm using VB to write some data to an Excel spreadsheet.
> I use the following vb/sql statement to get the data I need:
> ADO_rs.Source = "select least(2+(0.044117*CT_OFF_GEN),8)/count(*)as
mydata2
> calculate avg from """ + "6b_NRatio" + """.1 where ct_text like 'on/off
> it produces the following correct output:
> ct_off_time mydata2
> -- --
> 05-MAY-03 18:57:57.7 1.97044
> 24-JAN-03 11:46:07.8 8
> 16-AUG-03 09:58:27.9 5.30701
> ......
> 16-AUG-03 21:22:57.7 6.09582
> 10-DEC-02 16:46:37.6 8
> --
> AVG 5.25773
> If I could change the above code where I could store the "calculate avg as
> mydata3" I could then put the data into excel.
> But I can't do this
> How can I store the AVG 5.25773 into a variable so I can use it in Excel
> like so:
> ADO_rs.Open
> ActiveCell.Offset(x, 5).Value = ADO_rs.Fields!AVG GOES HERE
> Maybe there is an easier way to do this? Any suggestions?
> David
>