How to apply database design changes?

Hi all, I am not sure if this question has a one line answer or I have to
read an entire book on the subject. Here is my problem... I am developing VB
..NET based web app with MS SQL Server 2000 as backend. The beta version of
the system is already in use from the main server. Meanwhile I keep changing
the database design (including editing stored proc and/or views) on my
development server. Now how could I migrate the changes I have made to the
main server without loosing the data already there?
Any reference to an online guide would also be helpful.
Thanks in advance
Raj
Since you posted the question in .replication forum. I guess the first
question everyone has is:
Does the database set up for replication?
"Raj" wrote:

> Hi all, I am not sure if this question has a one line answer or I have to
> read an entire book on the subject. Here is my problem... I am developing VB
> .NET based web app with MS SQL Server 2000 as backend. The beta version of
> the system is already in use from the main server. Meanwhile I keep changing
> the database design (including editing stored proc and/or views) on my
> development server. Now how could I migrate the changes I have made to the
> main server without loosing the data already there?
> Any reference to an online guide would also be helpful.
> Thanks in advance
> --
> Raj
>
|||Jack, I was not sure initially where to post this question, so posted here. I
just created a publisher of my development database and selected all the four
objects (tables, stored proc, views, and UDFs) for publishing.
I do not want to publish the data, but only the schema. The replication
wizard gave me a warning that columns with INT IDENTITY property would be
converted to just INT, which, if happens, will screw up my database.
Any guidance would be appreciated
Thanks
"Jack" wrote:
[vbcol=seagreen]
> Since you posted the question in .replication forum. I guess the first
> question everyone has is:
> Does the database set up for replication?
> "Raj" wrote:
|||If this is the case, then I would not recommend Replication. Tables, views,
stored procedures, and functions all can create and alter using SQL script.
You just need to keep track of the changes you want to make, put them in a
script or scripts, then apply the changes to production database on regular
basics (i.e. once a week).
"Raj" wrote:
[vbcol=seagreen]
> Jack, I was not sure initially where to post this question, so posted here. I
> just created a publisher of my development database and selected all the four
> objects (tables, stored proc, views, and UDFs) for publishing.
> I do not want to publish the data, but only the schema. The replication
> wizard gave me a warning that columns with INT IDENTITY property would be
> converted to just INT, which, if happens, will screw up my database.
> Any guidance would be appreciated
> Thanks
> "Jack" wrote:
|||Raj,
It seems either you got mixed up or I don't understand your problem.
1. What are you using replication for? Just to manage schema?
2. What type of replication are you using? My guess is you are using
Transactional.
3. How many servers are you managing in production?
4. Have you got your development and production servers mixed up? If yes,
bad idea!
Please clarify the above.
Normally if you want to deploy database changes from development to
production you would be keeping change scripts so you could apply them in an
ordered fashion, especially if table changes are involved. Adding columns
requires special handling when applying to replicated database, and cannot
be done using the conventional way. Please let me know your exact
requirements
Sorry for not being able to help more at this moment.
Raj Moloye.
"Raj" <Raj@.discussions.microsoft.com> wrote in message
news:D23DFA51-E948-4F9F-B4ED-A7AEE156829A@.microsoft.com...[vbcol=seagreen]
> Jack, I was not sure initially where to post this question, so posted
> here. I
> just created a publisher of my development database and selected all the
> four
> objects (tables, stored proc, views, and UDFs) for publishing.
> I do not want to publish the data, but only the schema. The replication
> wizard gave me a warning that columns with INT IDENTITY property would be
> converted to just INT, which, if happens, will screw up my database.
> Any guidance would be appreciated
> Thanks
> "Jack" wrote:
|||Thanks Raj Moloye and Jack.
I only need to migrate the schema changes from development server to main
server. I do no need to synchronize the data.
I only created replication agent for self-study (just to know what the heck
is it..). Based on your reply I guess replication is not the answer to my
problem.
You rightly mentioned that I need to place all the changes in a script, and
apply them to the main server regularly. I just do not know how to do it,
particularly the changes I make to the tables.
Please advise me the best way to achieve this.
Thanks again
BTW: could you also guide me to any online replication docoment?
"Khooseeraj Moloye" wrote:

> Raj,
> It seems either you got mixed up or I don't understand your problem.
> 1. What are you using replication for? Just to manage schema?
> 2. What type of replication are you using? My guess is you are using
> Transactional.
> 3. How many servers are you managing in production?
> 4. Have you got your development and production servers mixed up? If yes,
> bad idea!
> Please clarify the above.
> Normally if you want to deploy database changes from development to
> production you would be keeping change scripts so you could apply them in an
> ordered fashion, especially if table changes are involved. Adding columns
> requires special handling when applying to replicated database, and cannot
> be done using the conventional way. Please let me know your exact
> requirements
> Sorry for not being able to help more at this moment.
> Raj Moloye.
>
> "Raj" <Raj@.discussions.microsoft.com> wrote in message
> news:D23DFA51-E948-4F9F-B4ED-A7AEE156829A@.microsoft.com...
>
>
|||If you make changes to a table. For example, you added a column:
ALTER TABLE table
ADD column DATATYPE ........
As far as stored procedures, views, and functions. You can simple use the
ALTER command to update them unless your company has rules for those changes.
The difference between changing the schema of a table versus others is that
you don't want to change the entire schema of the table, you just want to
apply the necessary changes to it.
Book On Line provides a lot of informatioin on Replication already. This
forum is a great resource also especially there are some replication experts
checking this forum all the time.
"Raj" wrote:
[vbcol=seagreen]
> Thanks Raj Moloye and Jack.
> I only need to migrate the schema changes from development server to main
> server. I do no need to synchronize the data.
> I only created replication agent for self-study (just to know what the heck
> is it..). Based on your reply I guess replication is not the answer to my
> problem.
> You rightly mentioned that I need to place all the changes in a script, and
> apply them to the main server regularly. I just do not know how to do it,
> particularly the changes I make to the tables.
> Please advise me the best way to achieve this.
> Thanks again
> BTW: could you also guide me to any online replication docoment?
> "Khooseeraj Moloye" wrote:
|||To replicate schema changes only, I'd recommend Redgate's
SQLCompare tool.
Rgds,
Paul Ibison
|||Thanks Jack,
I use enterprise manager to edit tables, views etc. I think it will be a
pain to record all the changes I make to the tables as a script manually. I
am probably making changes to the tables, views etc all the time, then how is
it possible/feasible to record them all in a script?
Is there any alternative?
"Jack" wrote:
[vbcol=seagreen]
> If you make changes to a table. For example, you added a column:
> ALTER TABLE table
> ADD column DATATYPE ........
> As far as stored procedures, views, and functions. You can simple use the
> ALTER command to update them unless your company has rules for those changes.
> The difference between changing the schema of a table versus others is that
> you don't want to change the entire schema of the table, you just want to
> apply the necessary changes to it.
> Book On Line provides a lot of informatioin on Replication already. This
> forum is a great resource also especially there are some replication experts
> checking this forum all the time.
> "Raj" wrote:
|||It is very important to record all the changes you want to make to your
production database even though it's a pain. That's why the DBA's like
myself still have jobs. :-) Views, stored procedures, and functions should
not be a problem. You can just right-click on the object from EM > All Tasks
> Generate SQL script. But when it comes to tables, I think it's better for
you to learn how to use T-SQL to make modifications. You can generate the
SQL script on the tables from EM also if you want to get yourself familiar
with the syntax first.
"Raj" wrote:
[vbcol=seagreen]
> Thanks Jack,
> I use enterprise manager to edit tables, views etc. I think it will be a
> pain to record all the changes I make to the tables as a script manually. I
> am probably making changes to the tables, views etc all the time, then how is
> it possible/feasible to record them all in a script?
> Is there any alternative?
> "Jack" wrote:

How to Add ControlParameter to SqlDataSource at Runtime?

Hi!

My question is exactly the subject.

My Web Form has only a GridView and a DetailsView, there is no SqlDataSource at project time, i create the SqlDataSource at runtime using code like this in the Page_Load event: (I NEED IT TO BE CREATED DYNAMICALLY)

1Dim SQLDSAs SqlDataSource =New SqlDataSource()23 SQLDS.ID ="CustomerDataSource"4 SQLDS.ConnectionString = ConfigurationManager.ConnectionStrings("connectionstring").ConnectionString5 SQLDS.SelectCommand ="select customerid,companyname,contactname,country from customers"6 SQLDS.InsertCommand ="insert into customers(customerid,companyname,contactname,country) values(@.customerid,@.companyname,@.contactname,@.country)"7 SQLDS.UpdateCommand ="update customers set companyname=@.companyname,contactname=@.contactname,country=@.country where customerid=@.customerid"8 SQLDS.DeleteCommand ="delete from customers where customerid=@.customerid"910 SQLDS.UpdateParameters.Add(New Parameter("companyname"))11 SQLDS.UpdateParameters.Add(New Parameter("contactname"))12 SQLDS.UpdateParameters.Add(New Parameter("country"))13 SQLDS.UpdateParameters.Add(New Parameter("customerid"))1415 Page.Controls.Add(SQLDS)1617If Not Page.IsPostBackThen18 GridView1.DataKeyNames =New String() {"customerid"}19 GridView1.DataSourceID = SQLDS.ID2021' ... and so on

The DetailsView1 uses the same SqlDataSource to show data, but i could not find a way to synchronize the DetailsView1 with the GridView1 when a record is selected in the GridView1.

How can I synchronize the DetailsView?

I played with the ControlParameter but i can't find either how to add a ControlParameter in code, is there a way? Every place talking about ControlParameter shows something like this:

 
1<asp:SqlDataSource ID="SqlDataSource1" runat="server"2 ConnectionString="<%$ ConnectionStrings:Pubs%>"3 SelectCommand="SELECT [au_id], [au_lname], [au_fname], [state] FROM [authors] WHERE [state] = @.state">4 <SelectParameters>5 <asp:ControlParameter Name="state" ControlID="DropDownList1" PropertyName="SelectedValue" />6 </SelectParameters>7</asp:SqlDataSource>8
 

Ok. OK. But my SqlDataSource is created dynamically. Any ideas on how to solve this problem?

Thanks!

Here is how to add it in code:

ControlParameter cp = new ControlParameter("state", "DropDownList1", "SelectedValue");

SqlDataSource1.SelectParameters.Add(cp);

|||cant get it to work.|||

using Northwind database.

i just want to synchronize the DetailsView to show the record selected in the GridView.

thanks!

the code of the web form:

ASPX.VB

1Imports SqlHelper2Imports System.Data3Imports System.Data.SqlClient45PartialClass Customers6Inherits System.Web.UI.Page78910Protected Sub Page_Load(ByVal senderAs Object,ByVal eAs System.EventArgs)Handles Me.Load1112Dim SQLDS_GVAs SqlDataSource =New SqlDataSource()13Dim SQLDS_DVAs SqlDataSource =New SqlDataSource()1415' GridView16 SQLDS_GV.ID ="CustomerDataSource_GV"17 SQLDS_GV.ConnectionString = ConfigurationManager.ConnectionStrings("connectionstring").ConnectionString18 SQLDS_GV.SelectCommand ="select customerid,companyname,contactname,country from customers"19 SQLDS_GV.InsertCommand ="insert into customers(customerid,companyname,contactname,country) values(@.custid,@.companyname,@.contactname,@.country)"20 SQLDS_GV.UpdateCommand ="update customers set companyname=@.companyname,contactname=@.contactname,country=@.country where custid=@.custid"21 SQLDS_GV.DeleteCommand ="delete from customers where customerid=@.custid"2223 SQLDS_GV.UpdateParameters.Add(New Parameter("companyname"))24 SQLDS_GV.UpdateParameters.Add(New Parameter("contactname"))25 SQLDS_GV.UpdateParameters.Add(New Parameter("country"))26 SQLDS_GV.UpdateParameters.Add(New Parameter("custid"))2728'DetailsView29 SQLDS_DV.ID ="CustomerDataSource_DV"30 SQLDS_DV.ConnectionString = ConfigurationManager.ConnectionStrings("connectionstring").ConnectionString31 SQLDS_DV.SelectCommand ="select customerid,companyname,contactname,country from customers"32 SQLDS_DV.InsertCommand ="insert into customers(customerid,companyname,contactname,country) values(@.custid,@.companyname,@.contactname,@.country)"33 SQLDS_DV.UpdateCommand ="update customers set companyname=@.companyname,contactname=@.contactname,country=@.country where customerid=@.custid"34 SQLDS_DV.DeleteCommand ="delete from customers where customerid=@.custid"3536 SQLDS_DV.UpdateParameters.Add(New Parameter("companyname"))37 SQLDS_DV.UpdateParameters.Add(New Parameter("contactname"))38 SQLDS_DV.UpdateParameters.Add(New Parameter("country"))39 SQLDS_DV.UpdateParameters.Add(New Parameter("custid"))4041'SQLDS_DV.SelectParameters.Add("customerid", "GridView1.SelectedValue")4243 Page.Controls.Add(SQLDS_GV)44 Page.Controls.Add(SQLDS_DV)4546If Not Page.IsPostBackThen4748 GridView1.DataKeyNames =New String() {"customerid"}49 GridView1.DataSourceID = SQLDS_GV.ID5051' CRIAR AS COLUNAS MANUALMENTE52 GridView1.AutoGenerateColumns =False53 GridView1.AllowPaging =True54 GridView1.PageSize = 105556Dim bf1As BoundField =New BoundField()57Dim bf2As BoundField =New BoundField()58Dim bf3As BoundField =New BoundField()59Dim bf4As BoundField =New BoundField()6061 bf1.HeaderText ="ID"62 bf1.DataField ="customerid"63 bf1.ReadOnly =True64 bf1.SortExpression ="customerid"6566 bf2.HeaderText ="Empresa"67 bf2.DataField ="companyname"68 bf2.SortExpression ="companyname"6970 bf3.HeaderText ="Contato"71 bf3.DataField ="contactname"72 bf3.SortExpression ="contactname"7374 bf4.HeaderText ="País"75 bf4.DataField ="country"76 bf4.SortExpression ="country"7778Dim cfAs CommandField =New CommandField()79 cf.ButtonType = ButtonType.Button80 cf.ShowCancelButton =True81 cf.ShowEditButton =True82 cf.ShowSelectButton =True83 cf.ShowDeleteButton =True8485 cf.CancelText ="Cancelar"86 cf.EditText ="Alterar"87 cf.DeleteText ="Excluir"88 cf.UpdateText ="Gravar"89 cf.SelectText ="Selecionar"9091 GridView1.Columns.Add(cf)92 GridView1.Columns.Add(bf1)93 GridView1.Columns.Add(bf2)94 GridView1.Columns.Add(bf3)95 GridView1.Columns.Add(bf4)96'GridView1.Columns.Add(bf5)9798 ' ****************************99100 'Dim cp As ControlParameter = New ControlParameter("customerid", "GridView1", "SelectedValue")101 'SQLDS_DV.SelectParameters.Add(cp)102 'SQLDS_DV.SelectParameters.Add("customerid", "GridView1.SelectedValue")103104105 DetailsView1.DataKeyNames =New String() {"customerid"}106 DetailsView1.DataSourceID = SQLDS_DV.ID107108 DetailsView1.AutoGenerateRows =False109110 Dim bf1_DVAs BoundField =New BoundField()111Dim bf2_DVAs BoundField =New BoundField()112Dim bf3_DVAs BoundField =New BoundField()113Dim bf4_DVAs BoundField =New BoundField()114115 bf1_DV.HeaderText ="ID"116 bf1_DV.DataField ="customerid"117 bf1_DV.ReadOnly =True118 bf1_DV.SortExpression ="customerid"119120 bf2_DV.HeaderText ="Empresa"121 bf2_DV.DataField ="companyname"122 bf2_DV.SortExpression ="companyname"123124 bf3_DV.HeaderText ="Contato"125 bf3_DV.DataField ="contactname"126 bf3_DV.SortExpression ="contactname"127128 bf4_DV.HeaderText ="País"129 bf4_DV.DataField ="country"130 bf4_DV.SortExpression ="country"131132Dim cf_DVAs CommandField =New CommandField()133 cf_DV.ButtonType = ButtonType.Button134 cf_DV.ShowCancelButton =True135 cf_DV.ShowEditButton =True136 cf_DV.ShowSelectButton =True137 cf_DV.ShowDeleteButton =True138139 cf_DV.CancelText ="Cancelar"140 cf_DV.EditText ="Alterar"141 cf_DV.DeleteText ="Excluir"142 cf_DV.UpdateText ="Gravar"143 cf_DV.SelectText ="Selecionar"144145 DetailsView1.Fields.Add(bf1_DV)146 DetailsView1.Fields.Add(bf2_DV)147 DetailsView1.Fields.Add(bf3_DV)148 DetailsView1.Fields.Add(bf4_DV)149 DetailsView1.Fields.Add(cf_DV)150151'SQLDS_DV.SelectParameters.Add("customerid", TypeCode.String, "GridView1.SelectedValue")152153Dim cpAs ControlParameter =New ControlParameter("custid", TypeCode.String,"GridView1", GridView1.SelectedValue.ToString())154 SQLDS_DV.SelectParameters.Add(cp)155156 SQLDS_GV.DataBind()157 SQLDS_DV.DataBind()158End If159 End Sub160161 Protected Sub GridView1_SelectedIndexChanged(ByVal senderAs Object,ByVal eAs System.EventArgs)Handles GridView1.SelectedIndexChanged162'DetailsView1.DataBind()163 'DetailsView1.DataKey = GridView1.SelectedDataKey164End Sub165166End Class167

ASPX 

1<%@. Page Language="VB" AutoEventWireup="false" CodeFile="Customers.aspx.vb" Inherits="Customers" %>23<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">45<html xmlns="http://www.w3.org/1999/xhtml" >6<head runat="server">7 <title>Untitled Page</title>8</head>9<body>10 <form id="form1" runat="server">11 <div>12 <asp:GridView ID="GridView1" runat="server" CellPadding="4" ForeColor="#333333" GridLines="None" AllowSorting="True" AutoGenerateColumns="False">13 <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />14 <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />15 <EditRowStyle BackColor="#999999" />16 <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />17 <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />18 <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />19 <AlternatingRowStyle BackColor="White" ForeColor="#284775" />20 </asp:GridView>21 <br />22 <asp:DetailsView ID="DetailsView1" runat="server" CellPadding="4" ForeColor="#333333"23 GridLines="None" Height="50px" Width="125px" AutoGenerateDeleteButton="True" AutoGenerateEditButton="True" AutoGenerateInsertButton="True" DefaultMode="Edit">24 <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />25 <CommandRowStyle BackColor="#E2DED6" Font-Bold="True" />26 <EditRowStyle BackColor="#999999" />27 <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />28 <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />29 <FieldHeaderStyle BackColor="#E9ECF1" Font-Bold="True" />30 <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />31 <AlternatingRowStyle BackColor="White" ForeColor="#284775" />32 </asp:DetailsView>3334 </div>35 </form>36</body>37</html>38

How to add a newline sequence

I have a SQL 2005 stored procedure to generate an email when passed parameters such as receipient, subject etc

One of the paramteres passed to it is @.body which is the body text of the message. I want to be able to add a couple of blank lines and then some footer information. This is working right now except I can't find the right way to add newlines into the string within the store procedure, so my footer information just tags right on after the bodytext.

I have tried \n but that literally adds the two characters \ and n

Can anyone advise how to generate newlien sequences in T-SQL.

Regards

Clive

Use the CHAR function e.g. CHAR(13) + CHAR(10)

Seehttp://doc.ddart.net/mssql/sql70/ca-co_4.htm