Adding a new row to SQL table

Hi all,

I have a database setup with a few rows and i would like to allow a user to create a new row of data. After some googling, I've deduced that I need to somehow use an onclick command to perform an insert command. here is my code as of right now:

protectedvoid ItemAdd_Click(object sender,EventArgs e)

{

int newnum = 4;int POnum = newnum ++;

string Item = textbox3.Text.ToString();

string Quantity = textbox4.ToString();

string Part = textbox5.ToString();

string Description = textbox6.Text.ToString();

string Price = textbox7.Text.ToString();string UOM = textbox8.Text.ToString();

SqlDataSource2.InsertParameters.Clear();

SqlDataSource2.InsertParameters.Add("@.Item", Item);

SqlDataSource2.InsertParameters.Add("@.Quantity", Quantity);

SqlDataSource2.InsertParameters.Add("@.Part", Part);

SqlDataSource2.InsertParameters.Add("@.Description", Description);

SqlDataSource2.InsertParameters.Add("@.Price", Price);

SqlDataSource2.InsertParameters.Add("@.UOM", UOM);

SqlDataSource1.InsertCommand ="insert into ItemMaster "+

"values (12, @.Item, @.Quantity, @.Part, @.Description, @.Price, @.UOM)";

SqlDataSource2.Insert();

}

here's the aspx:

<%@.PageLanguage="C#"MasterPageFile="~/MasterPage.master"AutoEventWireup="true"CodeFile="createpo.aspx.cs"Inherits="Subpgs_Purchasing_createpo"Title="Create A PO" %>

<asp:ContentID="Content1"ContentPlaceHolderID="ContentPlaceHolder1"Runat="Server">

<tablestyle="width: 600px; position: relative; top: 30px; height: 253px"cellpadding="10px">

<tr>

<tdstyle="width: 180px; text-align: left;"rowspan="2">

Select Vendor<br/>- or -<br/><asp:LinkButtonID="Linkbutton1"runat="server"OnClick="Linkbutton1_Click">Create Vendor</asp:LinkButton></td>

<tdstyle="width: 100px"rowspan="2">

<asp:ListBoxID="ListBox2"runat="server"DataSourceID="SqlDataSource1"DataTextField="Name"

DataValueField="ID"OnSelectedIndexChanged="ListBox2_SelectedIndexChanged"></asp:ListBox><asp:SqlDataSourceID="SqlDataSource1"runat="server"

ConnectionString="<%$ ConnectionStrings:ConnectionString %>"SelectCommand="SELECT * FROM [Vendor_info]"></asp:SqlDataSource>

</td>

<tdstyle="width: 180px; text-align: left;">

Job/Req.S.O. No.</td>

<tdstyle="width: 100px">

<asp:TextBoxID="Job"runat="server"></asp:TextBox></td>

</tr>

<tr>

<tdstyle="width: 180px; text-align: left;">

Terms</td>

<tdstyle="width: 100px">

<asp:TextBoxID="Terms"runat="server"></asp:TextBox></td>

</tr>

<tr>

<tdstyle="width: 180px; text-align: left;">

F.O.B.</td>

<tdstyle="width: 100px">

<asp:TextBoxID="FOB"runat="server"></asp:TextBox></td>

<tdstyle="width: 180px; text-align: left;">

Ship To:<br/>

Address:<br/>

City / State / Zip:</td>

<tdstyle="width: 100px">

<asp:TextBoxID="Ship1"runat="server"></asp:TextBox>

<asp:TextBoxID="Ship2"runat="server"></asp:TextBox>

<asp:TextBoxID="Ship3"runat="server"></asp:TextBox></td>

</tr>

</table>

<br/>

<br/>

<asp:SqlDataSourceID="SqlDataSource2"runat="server"ConnectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Purchase_Orders.mdf;Integrated Security=True;User Instance=True;Context Connection=False"

ProviderName="System.Data.SqlClient"SelectCommand="SELECT [Item], [Quantity], [Part], [Description], [Price], [UOM], [Ammount], [PONumber] FROM [ItemMaster]"DeleteCommand="DELETE FROM [ItemMaster] WHERE [PONumber] = @.PONumber"InsertCommand="INSERT INTO [ItemMaster] ([Item], [Quantity], [Part], [Description], [Price], [UOM], [Ammount], [PONumber]) VALUES (@.Item, @.Quantity, @.Part, @.Description, @.Price, @.UOM, @.Ammount, @.PONumber)"UpdateCommand="UPDATE [ItemMaster] SET [Item] = @.Item, [Quantity] = @.Quantity, [Part] = @.Part, [Description] = @.Description, [Price] = @.Price, [UOM] = @.UOM, [Ammount] = @.Ammount WHERE [PONumber] = @.PONumber">

<DeleteParameters>

<asp:ParameterName="PONumber"Type="Decimal"/>

</DeleteParameters>

<UpdateParameters>

<asp:ParameterName="Item"Type="String"/>

<asp:ParameterName="Quantity"Type="Decimal"/>

<asp:ParameterName="Part"Type="String"/>

<asp:ParameterName="Description"Type="String"/>

<asp:ParameterName="Price"Type="Decimal"/>

<asp:ParameterName="UOM"Type="String"/>

<asp:ParameterName="Ammount"Type="Decimal"/>

<asp:ParameterName="PONumber"Type="Decimal"/>

</UpdateParameters>

<InsertParameters>

<asp:ParameterName="Item"Type="String"/>

<asp:ParameterName="Quantity"Type="Decimal"/>

<asp:ParameterName="Part"Type="String"/>

<asp:ParameterName="Description"Type="String"/>

<asp:ParameterName="Price"Type="Decimal"/>

<asp:ParameterName="UOM"Type="String"/>

<asp:ParameterName="Ammount"Type="Decimal"/>

<asp:ParameterName="PONumber"Type="Decimal"/>

</InsertParameters>

</asp:SqlDataSource>

<br/>

<table>

<tr>

<td>Item #</td>

<td><asp:TextBoxID="textbox3"runat=server></asp:TextBox></td>

<td>Quantity</td>

<td><asp:TextBoxID="textbox4"runat=server></asp:TextBox></td>

<td>Part Number</td>

<td><asp:TextBoxID="textbox5"runat=server></asp:TextBox></td>

</tr>

<tr>

<td>Description</td>

<td><asp:TextBoxID="textbox6"runat=server></asp:TextBox></td>

<td>Unit Price</td>

<td><asp:TextBoxID="textbox7"runat=server></asp:TextBox></td>

<td>Unit of Measure</td>

<td><asp:TextBoxID="textbox8"runat=server></asp:TextBox></td>

</tr>

<tr>

<tdcolspan="6"align="center"><asp:ButtonID="ItemAdd"runat=servertext="Add Item"OnClick="ItemAdd_Click"/></td>

</tr>

</table>

<br/>

<br/>

<asp:GridViewID="GridView1"runat="server"AutoGenerateColumns="False"DataKeyNames="PONumber"

DataSourceID="SqlDataSource2">

<Columns>

<asp:CommandFieldShowEditButton="True"/>

<asp:BoundFieldDataField="Item"HeaderText="Item"SortExpression="Item"/>

<asp:BoundFieldDataField="Quantity"HeaderText="Quantity"SortExpression="Quantity"/>

<asp:BoundFieldDataField="Part"HeaderText="Part"SortExpression="Part"/>

<asp:BoundFieldDataField="Description"HeaderText="Description"SortExpression="Description"/>

<asp:BoundFieldDataField="Price"HeaderText="Price"SortExpression="Price"/>

<asp:BoundFieldDataField="UOM"HeaderText="UOM"SortExpression="UOM"/>

<asp:BoundFieldDataField="Ammount"HeaderText="Ammount"SortExpression="Ammount"/>

<asp:BoundFieldDataField="PONumber"HeaderText="PONumber"ReadOnly="True"SortExpression="PONumber"

Visible="False"/>

</Columns>

</asp:GridView>

<br/>

<br/>

<br/>

<asp:ButtonID="Button1"runat="server"Text="Submit Data"OnClick="Button1_Click"/> <br/>

<br/>

<asp:LabelID="Label1"runat="server"Text="Please Verify Information below!"Visible="False"></asp:Label><br/>

<br/>

<tablealign="left">

<tr>

<tdstyle="width: 200px; text-align: left;">

<asp:LabelID="Label4"runat="server"Text="Job/REQ.S.O. No."Visible="false"></asp:Label></td>

<tdstyle="width: 200px; text-align: left;">

<asp:LabelID="Label5"runat="server"></asp:Label></td>

</tr>

<tr>

<tdstyle="width: 200px; text-align: left;">

<asp:LabelID="Label7"runat="server"Text="Terms"Visible="false"></asp:Label></td>

<tdstyle="width: 200px; text-align: left;">

<asp:LabelID="Label8"runat="server"></asp:Label></td>

</tr>

<tr>

<tdstyle="width: 200px; text-align: left;">

<asp:LabelID="Label10"runat="server"Text="F.O.B."Visible="false"></asp:Label></td>

<tdstyle="width: 200px; text-align: left;">

<asp:LabelID="Label11"runat="server"></asp:Label></td>

</tr>

</table>

<asp:DetailsViewID="DetailsView1"runat="server"AutoGenerateRows="False"DataSourceID="SqlDataSource1"

Height="50px"Visible="False"Width="260px"BackColor="#CCCCCC"BorderColor="#999999"BorderStyle="Solid"BorderWidth="3px"CellPadding="4"CellSpacing="2"DataKeyNames="ID"ForeColor="Black">

<FooterStyleBackColor="#CCCCCC"/>

<EditRowStyleBackColor="#000099"Font-Bold="True"ForeColor="White"/>

<RowStyleBackColor="White"/>

<PagerStyleBackColor="#CCCCCC"ForeColor="Black"HorizontalAlign="Left"/>

<Fields>

<asp:BoundFieldDataField="ID"HeaderText="ID"ReadOnly="True"SortExpression="ID"/>

<asp:BoundFieldDataField="Name"HeaderText="Name"SortExpression="Name"/>

<asp:BoundFieldDataField="Street"HeaderText="Street"SortExpression="Street"/>

<asp:BoundFieldDataField="City"HeaderText="City"SortExpression="City"/>

<asp:BoundFieldDataField="State"HeaderText="State"SortExpression="State"/>

<asp:BoundFieldDataField="Zip"HeaderText="Zip"SortExpression="Zip"/>

<asp:BoundFieldDataField="Telephone Number"HeaderText="Telephone Number"SortExpression="Telephone Number"/>

<asp:BoundFieldDataField="Contact Information"HeaderText="Contact Information"

SortExpression="Contact Information"/>

<asp:BoundFieldDataField="E-mail address"HeaderText="E-mail address"SortExpression="E-mail address"/>

<asp:BoundFieldDataField="Fax Number"HeaderText="Fax Number"SortExpression="Fax Number"/>

<asp:BoundFieldDataField="Comments"HeaderText="Comments"SortExpression="Comments"/>

</Fields>

<HeaderStyleBackColor="Black"Font-Bold="True"ForeColor="White"/>

</asp:DetailsView>

<br/>

<br/>

<br/>

<asp:ButtonID="Button2"runat="server"Text="E-Mail PO"Visible="False"OnClick="Button2_Click"/>

</asp:Content>

whenever I run this, I receive the following error and am not sure how to correct it:

System.Data.SqlClient.SqlException: Must declare the scalar variable "@.Item".

If someone could please explain to me the process here of what is going ion it would be greatly appreciated. My understanding is the @.item is just a variable if you will so i'm not sure why its stating i need to declare it.

Thank you all!

Welcome to the community. I would suggest you use the InsertCode option to insert code in your posts from next time. Lets start from the starting and then we will remove errors as we go.

Gizm0:

string Item = textbox3.Text.ToString();

string Quantity = textbox4.ToString();

string Part = textbox5.ToString();

I am assuming these are your textboxes. You should use Text property of textboxes like you used for others. Also, you don't have to use ToString() method. Text property returns the textbox value as a string.

|||

Hi Bullpit.

Sorry about that, I didn't see the code Icon there. Anyhow, I made the changes and continue to receive the same error. At first glance, does it at least look like i'm on the right path? I've always been more hardware/network inclined so a lot of the google searches and forums I used to piece this together are still Greek to me.

Thanks for the response!

|||

I don't use SqlDataSource as such. Since you are writing some code, you can instead use this code to insert values to your database. Extend this example to add more parameters.

protected void ItemAdd_Click(object sender, EventArgs e) {// connection string to your database SqlConnection mySqlConnection =new SqlConnection("server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;");// open connection mySqlConnection.Open();// create command object to execute query SqlCommand mySqlCommand = mySqlConnection.CreateCommand();// set command text to the INSERT sql query mySqlCommand.CommandText = @."INSERT INTO Employee (Item, Quantity, Part...) VALUES( @.Item, @.Quantity, @.Part...)";// Add parameters mySqlCommand.Parameters.Add("@.Item", SqlDbType.VarChar); mySqlCommand.Parameters.Add("@.Quantity", SqlDbType.VarChar); mySqlCommand.Parameters.Add("@.Part", SqlDbType.VarChar);// set parameter values mySqlCommand.Parameters["@.Item"].Value = textbox3.Text; mySqlCommand.Parameters["@.Quantity"].Value = textbox4.Text; mySqlCommand.Parameters["@.Part"].Value = textbox5.Text;// execute query mySqlCommand.ExecuteNonQuery();// close connection mySqlConnection.Close();}

You will need these namespaces.

using System.Data;
using System.Data.SqlClient;

|||

Hey BullPit,

I really appreciate the help! I was able to get this working using your code above. Now it looks like I just need to validate the input boxes to make sure the correct data is being entered in the fields.

Again, thanks!

sql

adding a new column to a large table

SQL Server 2000
Windows 2000 Server
I need to add a new column to a table that will likely have millions of rows
at most customer sites. The table currently has one FK and 2 unclustered
indexes. The new column is an int column. When I run the script to add the
column on our test server (table has about 5 million rows) it takes about
1/2 hour. Unfortunately, we can't have that kind of down time on that
table.
Is there a typical method for doing this more efficiently? Would it help to
drop the indexes before and recreate them after or would that end up taking
the same amount of time anyway? What about creating a temp table, copying
that data into the new table and renaming it?Are you trying to add this column in the middle of the other columns, or at
the end of the other columns? If the middle, try adding to the end and see
if that sppeds things up, Im betting thats whats going on.
"Joel Lyons" <NOSPAMJOELL@.NOVARAD.NET> wrote in message
news:uyRcotfZFHA.1456@.TK2MSFTNGP15.phx.gbl...
> SQL Server 2000
> Windows 2000 Server
> I need to add a new column to a table that will likely have millions of
> rows at most customer sites. The table currently has one FK and 2
> unclustered indexes. The new column is an int column. When I run the
> script to add the column on our test server (table has about 5 million
> rows) it takes about 1/2 hour. Unfortunately, we can't have that kind of
> down time on that table.
> Is there a typical method for doing this more efficiently? Would it help
> to drop the indexes before and recreate them after or would that end up
> taking the same amount of time anyway? What about creating a temp table,
> copying that data into the new table and renaming it?
>|||Here's the script:
ALTER TABLE [dbo].[Images] ADD Version tinyint NOT NULL CONSTRAINT
DF_Images_Version DEFAULT 1
I'd guess that adds to the end, no?
"ChrisR" <noemail@.bla.com> wrote in message
news:%23caxC6fZFHA.3364@.TK2MSFTNGP12.phx.gbl...
> Are you trying to add this column in the middle of the other columns, or
> at the end of the other columns? If the middle, try adding to the end and
> see if that sppeds things up, Im betting thats whats going on.
>
> "Joel Lyons" <NOSPAMJOELL@.NOVARAD.NET> wrote in message
> news:uyRcotfZFHA.1456@.TK2MSFTNGP15.phx.gbl...
>> SQL Server 2000
>> Windows 2000 Server
>> I need to add a new column to a table that will likely have millions of
>> rows at most customer sites. The table currently has one FK and 2
>> unclustered indexes. The new column is an int column. When I run the
>> script to add the column on our test server (table has about 5 million
>> rows) it takes about 1/2 hour. Unfortunately, we can't have that kind of
>> down time on that table.
>> Is there a typical method for doing this more efficiently? Would it help
>> to drop the indexes before and recreate them after or would that end up
>> taking the same amount of time anyway? What about creating a temp table,
>> copying that data into the new table and renaming it?
>>
>|||Yes it does. I just realized you are adding these to customer sires. Are you
logging onto the boxes locally, or over the WAN?
"Joel Lyons" <NOSPAMJOELL@.NOVARAD.NET> wrote in message
news:utoFrFgZFHA.3876@.TK2MSFTNGP12.phx.gbl...
> Here's the script:
> ALTER TABLE [dbo].[Images] ADD Version tinyint NOT NULL CONSTRAINT
> DF_Images_Version DEFAULT 1
> I'd guess that adds to the end, no?
> "ChrisR" <noemail@.bla.com> wrote in message
> news:%23caxC6fZFHA.3364@.TK2MSFTNGP12.phx.gbl...
>> Are you trying to add this column in the middle of the other columns, or
>> at the end of the other columns? If the middle, try adding to the end and
>> see if that sppeds things up, Im betting thats whats going on.
>>
>> "Joel Lyons" <NOSPAMJOELL@.NOVARAD.NET> wrote in message
>> news:uyRcotfZFHA.1456@.TK2MSFTNGP15.phx.gbl...
>> SQL Server 2000
>> Windows 2000 Server
>> I need to add a new column to a table that will likely have millions of
>> rows at most customer sites. The table currently has one FK and 2
>> unclustered indexes. The new column is an int column. When I run the
>> script to add the column on our test server (table has about 5 million
>> rows) it takes about 1/2 hour. Unfortunately, we can't have that kind
>> of down time on that table.
>> Is there a typical method for doing this more efficiently? Would it
>> help to drop the indexes before and recreate them after or would that
>> end up taking the same amount of time anyway? What about creating a
>> temp table, copying that data into the new table and renaming it?
>>
>>
>|||We haven't run the script on an actual customer site yet, only on our test
server which is a close resemblance. When we upgrade the customers, we will
likely remote in and run the script on the actual server.
(thanks for taking the time to help)
"ChrisR" <noemail@.bla.com> wrote in message
news:uOYoAMgZFHA.580@.TK2MSFTNGP15.phx.gbl...
> Yes it does. I just realized you are adding these to customer sires. Are
> you logging onto the boxes locally, or over the WAN?
>
> "Joel Lyons" <NOSPAMJOELL@.NOVARAD.NET> wrote in message
> news:utoFrFgZFHA.3876@.TK2MSFTNGP12.phx.gbl...
>> Here's the script:
>> ALTER TABLE [dbo].[Images] ADD Version tinyint NOT NULL CONSTRAINT
>> DF_Images_Version DEFAULT 1
>> I'd guess that adds to the end, no?
>> "ChrisR" <noemail@.bla.com> wrote in message
>> news:%23caxC6fZFHA.3364@.TK2MSFTNGP12.phx.gbl...
>> Are you trying to add this column in the middle of the other columns, or
>> at the end of the other columns? If the middle, try adding to the end
>> and see if that sppeds things up, Im betting thats whats going on.
>>
>> "Joel Lyons" <NOSPAMJOELL@.NOVARAD.NET> wrote in message
>> news:uyRcotfZFHA.1456@.TK2MSFTNGP15.phx.gbl...
>> SQL Server 2000
>> Windows 2000 Server
>> I need to add a new column to a table that will likely have millions of
>> rows at most customer sites. The table currently has one FK and 2
>> unclustered indexes. The new column is an int column. When I run the
>> script to add the column on our test server (table has about 5 million
>> rows) it takes about 1/2 hour. Unfortunately, we can't have that kind
>> of down time on that table.
>> Is there a typical method for doing this more efficiently? Would it
>> help to drop the indexes before and recreate them after or would that
>> end up taking the same amount of time anyway? What about creating a
>> temp table, copying that data into the new table and renaming it?
>>
>>
>>
>|||I just realized...what about the default value? It has to apply that
default value to all the existing rows. Maybe that's what's taking a long
time. I wonder if disabling some level of logging during this update would
speed that up.
"ChrisR" <noemail@.bla.com> wrote in message
news:uOYoAMgZFHA.580@.TK2MSFTNGP15.phx.gbl...
> Yes it does. I just realized you are adding these to customer sires. Are
> you logging onto the boxes locally, or over the WAN?
>
> "Joel Lyons" <NOSPAMJOELL@.NOVARAD.NET> wrote in message
> news:utoFrFgZFHA.3876@.TK2MSFTNGP12.phx.gbl...
>> Here's the script:
>> ALTER TABLE [dbo].[Images] ADD Version tinyint NOT NULL CONSTRAINT
>> DF_Images_Version DEFAULT 1
>> I'd guess that adds to the end, no?
>> "ChrisR" <noemail@.bla.com> wrote in message
>> news:%23caxC6fZFHA.3364@.TK2MSFTNGP12.phx.gbl...
>> Are you trying to add this column in the middle of the other columns, or
>> at the end of the other columns? If the middle, try adding to the end
>> and see if that sppeds things up, Im betting thats whats going on.
>>
>> "Joel Lyons" <NOSPAMJOELL@.NOVARAD.NET> wrote in message
>> news:uyRcotfZFHA.1456@.TK2MSFTNGP15.phx.gbl...
>> SQL Server 2000
>> Windows 2000 Server
>> I need to add a new column to a table that will likely have millions of
>> rows at most customer sites. The table currently has one FK and 2
>> unclustered indexes. The new column is an int column. When I run the
>> script to add the column on our test server (table has about 5 million
>> rows) it takes about 1/2 hour. Unfortunately, we can't have that kind
>> of down time on that table.
>> Is there a typical method for doing this more efficiently? Would it
>> help to drop the indexes before and recreate them after or would that
>> end up taking the same amount of time anyway? What about creating a
>> temp table, copying that data into the new table and renaming it?
>>
>>
>>
>|||Yes, the defualt value will take a bit. But it doesnt seems to me like Ive
ever experienced that severe of slowness, which is what led to my other
questions. Creating with NULL will be much faster of course... but still
thats a long time. I guess try just to add with NULL for timing tests.
"Joel Lyons" <NOSPAMJOELL@.NOVARAD.NET> wrote in message
news:eLFnFbhZFHA.2520@.TK2MSFTNGP09.phx.gbl...
>I just realized...what about the default value? It has to apply that
>default value to all the existing rows. Maybe that's what's taking a long
>time. I wonder if disabling some level of logging during this update would
>speed that up.
> "ChrisR" <noemail@.bla.com> wrote in message
> news:uOYoAMgZFHA.580@.TK2MSFTNGP15.phx.gbl...
>> Yes it does. I just realized you are adding these to customer sires. Are
>> you logging onto the boxes locally, or over the WAN?
>>
>> "Joel Lyons" <NOSPAMJOELL@.NOVARAD.NET> wrote in message
>> news:utoFrFgZFHA.3876@.TK2MSFTNGP12.phx.gbl...
>> Here's the script:
>> ALTER TABLE [dbo].[Images] ADD Version tinyint NOT NULL CONSTRAINT
>> DF_Images_Version DEFAULT 1
>> I'd guess that adds to the end, no?
>> "ChrisR" <noemail@.bla.com> wrote in message
>> news:%23caxC6fZFHA.3364@.TK2MSFTNGP12.phx.gbl...
>> Are you trying to add this column in the middle of the other columns,
>> or at the end of the other columns? If the middle, try adding to the
>> end and see if that sppeds things up, Im betting thats whats going on.
>>
>> "Joel Lyons" <NOSPAMJOELL@.NOVARAD.NET> wrote in message
>> news:uyRcotfZFHA.1456@.TK2MSFTNGP15.phx.gbl...
>> SQL Server 2000
>> Windows 2000 Server
>> I need to add a new column to a table that will likely have millions
>> of rows at most customer sites. The table currently has one FK and 2
>> unclustered indexes. The new column is an int column. When I run the
>> script to add the column on our test server (table has about 5 million
>> rows) it takes about 1/2 hour. Unfortunately, we can't have that kind
>> of down time on that table.
>> Is there a typical method for doing this more efficiently? Would it
>> help to drop the indexes before and recreate them after or would that
>> end up taking the same amount of time anyway? What about creating a
>> temp table, copying that data into the new table and renaming it?
>>
>>
>>
>>
>|||On Tue, 31 May 2005 10:50:16 -0600, Joel Lyons wrote:
>Here's the script:
>ALTER TABLE [dbo].[Images] ADD Version tinyint NOT NULL CONSTRAINT
>DF_Images_Version DEFAULT 1
Hi Joel,
You're right (in your other post) - adding the default is what takes
time.
Consider breaking it in smaller steps:
1. Add a column, keep it nullable - should be fast.
ALTER TABLE dbo.Images
ADD Version tinyint
go
2. Fill the default value. This will take time, but if you use smallish
batches to process the data, locks will be released in between, so your
other applications will be able to proceed. If you have full recovery
model, then make sure that you back up the transaction log in between
(unless you don't mind it growing to accomodate all the changes).
SET ROWCOUNT 50000 -- experiment to get the optimal setting
DECLARE @.rc int
SET @.rc = 1
WHILE @.rc > 0
BEGIN
UPDATE dbo.Images
SET Version = 1
WHERE Version IS NULL
SET @.rc = @.@.ROWCOUNT
-- BACKUP LOG MyDatabase TO MyDatabaseLog
END
SET ROWCOUNT 0 -- never forget to reset ROWCOUNT!!
3. When all rows have been filled, set the NOT NULL constraint and the
DEFAULT:
ALTER TABLE dbo.Images
ALTER COLUMN Version tinyint NOT NULL
ALTER TABLE dbo.Images
ADD DEFAULT 1 FOR Version
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||When you allow nulls and do not provide a default value, it will be very
very quick, because the new rows are not materialized until the page is
re-written... It works sort of like a virtual column... when you provide a
default value, the values must ACTUALLY be written, so the time it would
take is relative to the IO speed.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Joel Lyons" <NOSPAMJOELL@.NOVARAD.NET> wrote in message
news:uyRcotfZFHA.1456@.TK2MSFTNGP15.phx.gbl...
> SQL Server 2000
> Windows 2000 Server
> I need to add a new column to a table that will likely have millions of
> rows at most customer sites. The table currently has one FK and 2
> unclustered indexes. The new column is an int column. When I run the
> script to add the column on our test server (table has about 5 million
> rows) it takes about 1/2 hour. Unfortunately, we can't have that kind of
> down time on that table.
> Is there a typical method for doing this more efficiently? Would it help
> to drop the indexes before and recreate them after or would that end up
> taking the same amount of time anyway? What about creating a temp table,
> copying that data into the new table and renaming it?
>|||Very interesting info - thanks all!
I'll try some of these ideas and let you know.
-Joel
"Joel Lyons" <NOSPAMJOELL@.NOVARAD.NET> wrote in message
news:uyRcotfZFHA.1456@.TK2MSFTNGP15.phx.gbl...
> SQL Server 2000
> Windows 2000 Server
> I need to add a new column to a table that will likely have millions of
> rows at most customer sites. The table currently has one FK and 2
> unclustered indexes. The new column is an int column. When I run the
> script to add the column on our test server (table has about 5 million
> rows) it takes about 1/2 hour. Unfortunately, we can't have that kind of
> down time on that table.
> Is there a typical method for doing this more efficiently? Would it help
> to drop the indexes before and recreate them after or would that end up
> taking the same amount of time anyway? What about creating a temp table,
> copying that data into the new table and renaming it?
>|||Joel,
Are you adding the new column executing a script in QA or doing it manually
in EM?
Do it in QA, using "alter table".
AMB
"Joel Lyons" wrote:
> SQL Server 2000
> Windows 2000 Server
> I need to add a new column to a table that will likely have millions of rows
> at most customer sites. The table currently has one FK and 2 unclustered
> indexes. The new column is an int column. When I run the script to add the
> column on our test server (table has about 5 million rows) it takes about
> 1/2 hour. Unfortunately, we can't have that kind of down time on that
> table.
> Is there a typical method for doing this more efficiently? Would it help to
> drop the indexes before and recreate them after or would that end up taking
> the same amount of time anyway? What about creating a temp table, copying
> that data into the new table and renaming it?
>
>|||Could you please show the script you use to add a column ?
Regards.
"Joel Lyons" wrote:
> SQL Server 2000
> Windows 2000 Server
> I need to add a new column to a table that will likely have millions of rows
> at most customer sites. The table currently has one FK and 2 unclustered
> indexes. The new column is an int column. When I run the script to add the
> column on our test server (table has about 5 million rows) it takes about
> 1/2 hour. Unfortunately, we can't have that kind of down time on that
> table.
> Is there a typical method for doing this more efficiently? Would it help to
> drop the indexes before and recreate them after or would that end up taking
> the same amount of time anyway? What about creating a temp table, copying
> that data into the new table and renaming it?
>
>|||Hi Joel,
DEFAULT constraint in your script is not an issue,
default value will only apply on new records, not existent records in your
table.
The real issue is NOT NULL - this will require prety huge I/O.
What you can do, run your script to add a new column without NOT NULL.
This will let you use a new column and populate it.
Then when you will able to schedule a time, make it NOT NULL.
Regards.
"Joel Lyons" wrote:
> SQL Server 2000
> Windows 2000 Server
> I need to add a new column to a table that will likely have millions of rows
> at most customer sites. The table currently has one FK and 2 unclustered
> indexes. The new column is an int column. When I run the script to add the
> column on our test server (table has about 5 million rows) it takes about
> 1/2 hour. Unfortunately, we can't have that kind of down time on that
> table.
> Is there a typical method for doing this more efficiently? Would it help to
> drop the indexes before and recreate them after or would that end up taking
> the same amount of time anyway? What about creating a temp table, copying
> that data into the new table and renaming it?
>
>|||Hi Joel,
DEFAULT constraint in your script is not an issue,
default value will only apply on new records, not existent records in your
table.
The real issue is NOT NULL - this will require prety huge I/O.
What you can do, run your script to add a new column without NOT NULL.
This will let you use a new column and populate it.
Then when you will able to schedule a time, make it NOT NULL.
Regards.
"Joel Lyons" wrote:
> SQL Server 2000
> Windows 2000 Server
> I need to add a new column to a table that will likely have millions of rows
> at most customer sites. The table currently has one FK and 2 unclustered
> indexes. The new column is an int column. When I run the script to add the
> column on our test server (table has about 5 million rows) it takes about
> 1/2 hour. Unfortunately, we can't have that kind of down time on that
> table.
> Is there a typical method for doing this more efficiently? Would it help to
> drop the indexes before and recreate them after or would that end up taking
> the same amount of time anyway? What about creating a temp table, copying
> that data into the new table and renaming it?
>
>

Adding a column to a table with 4 million rows

OK,
We have a quad P4 xeon 2.8ghz Intel server with 4 gig of ram connected to 1
terabyte of storage.
We have a table with 5 million rows and I added one smallint column to the
end.
It's been running now for an hour, showing very little I/O, and no one can
even connect using Enterprise Manager or so anything else for that matter.
Anyone have an idea on what could be going on here?
Kevin JacksonWhat SQL Server Version do you have ?
"Kevin Jackson" <kjackson@.powerwayinc.com> schrieb im Newsbeitrag
news:u4UUzLJfDHA.1872@.TK2MSFTNGP09.phx.gbl...
> OK,
> We have a quad P4 xeon 2.8ghz Intel server with 4 gig of ram connected to
1
> terabyte of storage.
> We have a table with 5 million rows and I added one smallint column to the
> end.
> It's been running now for an hour, showing very little I/O, and no one can
> even connect using Enterprise Manager or so anything else for that matter.
> Anyone have an idea on what could be going on here?
> Kevin Jackson
>|||Yes, there is a lock on that table to add the column. You might want to do
a sp_who2 for starters in that database and look at the ALTER TABLE command.
The CPU and Disk IO columns will give you a general idea of where the
activity is at. What SQL has to do is create a null value for 4 million
rows, which might take a while. Use Performance Monitor to track Page
writes/sec (SQL Server:Buffer Manager in SQL 2000), that may help give a
lowdown on the activity too.
--
***********************************
Andy S.
andy_mcdba@.yahoo.com
***********************************
"Kevin Jackson" <kjackson@.powerwayinc.com> wrote in message
news:u4UUzLJfDHA.1872@.TK2MSFTNGP09.phx.gbl...
> OK,
> We have a quad P4 xeon 2.8ghz Intel server with 4 gig of ram connected to
1
> terabyte of storage.
> We have a table with 5 million rows and I added one smallint column to the
> end.
> It's been running now for an hour, showing very little I/O, and no one can
> even connect using Enterprise Manager or so anything else for that matter.
> Anyone have an idea on what could be going on here?
> Kevin Jackson
>|||Normally, adding a new column should just make metadata changes, and not
take much time at all. Any locks will only be held while the metadata is
being changed, so it wouldn't be something that should cause a major impact.
How are you adding the new column? EM or QA? Are you supplying a default
value or allowing nulls? What version are you using?
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Kevin Jackson" <kjackson@.powerwayinc.com> wrote in message
news:u4UUzLJfDHA.1872@.TK2MSFTNGP09.phx.gbl...
> OK,
> We have a quad P4 xeon 2.8ghz Intel server with 4 gig of ram connected to
1
> terabyte of storage.
> We have a table with 5 million rows and I added one smallint column to the
> end.
> It's been running now for an hour, showing very little I/O, and no one can
> even connect using Enterprise Manager or so anything else for that matter.
> Anyone have an idea on what could be going on here?
> Kevin Jackson
>|||I believe when you add a column through EM, it copies all the rows to a temp
table with the new column defined, drops the old table and renames it back
again.
Am i right here ?
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%23W0LR6JfDHA.3284@.tk2msftngp13.phx.gbl...
> Normally, adding a new column should just make metadata changes, and not
> take much time at all. Any locks will only be held while the metadata is
> being changed, so it wouldn't be something that should cause a major
impact.
> How are you adding the new column? EM or QA? Are you supplying a default
> value or allowing nulls? What version are you using?
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Kevin Jackson" <kjackson@.powerwayinc.com> wrote in message
> news:u4UUzLJfDHA.1872@.TK2MSFTNGP09.phx.gbl...
> > OK,
> >
> > We have a quad P4 xeon 2.8ghz Intel server with 4 gig of ram connected
to
> 1
> > terabyte of storage.
> >
> > We have a table with 5 million rows and I added one smallint column to
the
> > end.
> >
> > It's been running now for an hour, showing very little I/O, and no one
can
> > even connect using Enterprise Manager or so anything else for that
matter.
> >
> > Anyone have an idea on what could be going on here?
> >
> > Kevin Jackson
> >
> >
>|||Found out we are having some sort of intermittent hardware failure talking
to our EMC drive subsystem...
That's why is was taking forever...
Thanks
"Kevin Jackson" <kjackson@.powerwayinc.com> wrote in message
news:u4UUzLJfDHA.1872@.TK2MSFTNGP09.phx.gbl...
> OK,
> We have a quad P4 xeon 2.8ghz Intel server with 4 gig of ram connected to
1
> terabyte of storage.
> We have a table with 5 million rows and I added one smallint column to the
> end.
> It's been running now for an hour, showing very little I/O, and no one can
> even connect using Enterprise Manager or so anything else for that matter.
> Anyone have an idea on what could be going on here?
> Kevin Jackson
>

Adding a blank line after a group of rows

I have an rdl report that shows rows group by category. I would like to include a blank line after each set of categories. Can anyone point me to how I would do that in the rdl.

thanks

idriss

Hi,

Add a blank Group Footer and increase the height of it as per your requirements.

HTH,
Suprotim Agarwal

--
http://www.dotnetcurry.com
--

Add values of distinct rows

Hello All,

I have a scenario wherein I wanted to add the values of distinct transaction number, how do I do that. i.e.

Financial organisation (GROUP (Company))

12/31/2003 606.60 approved 123 (Transaction Number) (1st Row)

5643577(Wok Report) (2nd Row)

01/05/2004 1000.00 approved 124 (Transaction Number) (to Satisfy $1000 three work reports were raised)

5643500(Wok Report)

5643501(Wok Report)

5643502(Wok Report)

02/05/2004 2000.00 approved 125 (Transaction Number) (to Satisfy $2000 three work reports were raised)

5643300(Wok Report)

5643301(Wok Report)

5643302(Wok Report)

Total 3606.60 (Total per Company group)

I want to add the visible row values only. Because the 1000.00 and 2000.00 was repeating for 01/05/2004 and 02/05/04 I am hinding the repeated values.

Please help me completing the above task.

Thanks,

Deepak Vodethela

Deekpak,

Unfortunatley, from my experiences, this is not possible as of SSRS 2005. I tried to do the same thing recently and I ended up changing my SQL to give me what I wanted.

|||

Thanks for the reply Todd. Can't we write a custom function to do the needful i.e. which has to check for the distict Transaction number and pick up the value and do the sum by group. I am not an expert at SSRS but just curious.

To change my query it will be a very big task. Right now I am joining 11 tables to get the required fields and if I want to add the sum on a group then it'll be a laborious task.

Any help would be appriciated.

Add runtime rows to sql express database

Hi All,

I have a c# project with an sql express database which is bound to a datagridview via a dataset.

I would like to allow the users to import data into the database from a text file.

How does one go about adding rows and filling in the column data programatically? Do I add the to datagridview? the database? the bindingsource?

Thank you,

Paul

hi Paul,

it depends on your requirements.. you can direclty load the database via BULK INSERT statement like in http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=723808&SiteID=1 ....
or you can read the flat file and parse it, loading a visual UI to be presented to the user for further manipulation, and finally load the destination database... if this is the case I'd just add the "imported" rows to the underlying DataView of the UI...

regards

|||

Thanks for the help and the links. I'll post back what I did.

Paul

|||

Wrote a class to convert the txt to xml but

it doens't work on a unbound grid. Oh well nice try...

Maybe I could unbin, set the data, and then

rebind. Looking into it now.

//this works fine on unbound grid.

dataSet1.ReadXml(filePath);

dataGridView1.DataSource = dataSet1;

dataGridView1.DataMember = "student";

Add rows to a DataSet without updating the MS SQL Server?

I am using ASP.NET 2.0 WebForms and I was trying to use a DataSet to add rows programatically without adding the actual records to the MS SQL Server Databases. Is this possible or should I be doing this another way?

DataSet myDS =newDataSet();
DataTable myTable =newDataTable("table1");
myTable.Columns.Add("col1",typeof(string));
myDS.Tables.Add(myTable);
myTable.Rows.Add("MyValue");

Thanks.

Yes it is possible.

add column

I have a table with only about 40 rows. For some reason we accidentally
forgot the primary key. Is it possible to add a column to this table that
adds a number to each row and also adds an increment of 1 when a new item is
added. While we are at it can we make this the primary key column.
I am new at this but I will take a stab. Maybe someone can look at my syntax
and tell me if I am on the right track.
the table name is tblProductSubcategories the column I need to add will be
called intSubcategoryID
Here is my go at it.
Alter Table tblProductSubcategories
Add
intSubcategoryID int identity not null
I know that is not exactly what is needed. Can someone help me with this
syntax. Much appreciated.
Laura K>> I have a table with only about 40 rows. For some reason we
accidentally forgot the primary key. <<
That is not an accident; that is a major design flaw.
each row and also adds an increment of 1 when a new item is added.
While we are at it can we make this the primary key column. <<
BY DEFINITION this is not a primary key!! Start over and do a real
design.|||It's almost done. You just forgot the primary key. Try this
Alter Table tblProductSubcategories
Add
intSubcategoryID int identity not null <B>PRIMARY KEY</B>
--
Lionel
"Laura K" wrote:

> I have a table with only about 40 rows. For some reason we accidentally
> forgot the primary key. Is it possible to add a column to this table that
> adds a number to each row and also adds an increment of 1 when a new item
is
> added. While we are at it can we make this the primary key column.
> I am new at this but I will take a stab. Maybe someone can look at my synt
ax
> and tell me if I am on the right track.
> the table name is tblProductSubcategories the column I need to add will be
> called intSubcategoryID
> Here is my go at it.
> Alter Table tblProductSubcategories
> Add
> intSubcategoryID int identity not null
>
> I know that is not exactly what is needed. Can someone help me with this
> syntax. Much appreciated.
> Laura K
>
>|||YES!! Just add another Column defined as Integer Identity... Say it's
Customers table, and the new PK will be CustomerID...
Alter Table Customers
(Add Column CustomerID Integer Identity Primary Key Not Null)
And there's NOTHING at all wrong with using this type of Primary Key... But
you should also, if at all possible, add another "key" in the form of a
unique constrraint or index, on whichever combination of "real" data columns
uniquely identifies each row... This will prevent the additon of two rows
(with different Idnetity values) from being added that represent the same
real world business object.
"Laura K" wrote:

> I have a table with only about 40 rows. For some reason we accidentally
> forgot the primary key. Is it possible to add a column to this table that
> adds a number to each row and also adds an increment of 1 when a new item
is
> added. While we are at it can we make this the primary key column.
> I am new at this but I will take a stab. Maybe someone can look at my synt
ax
> and tell me if I am on the right track.
> the table name is tblProductSubcategories the column I need to add will be
> called intSubcategoryID
> Here is my go at it.
> Alter Table tblProductSubcategories
> Add
> intSubcategoryID int identity not null
>
> I know that is not exactly what is needed. Can someone help me with this
> syntax. Much appreciated.
> Laura K
>
>|||Laura,
YES, you can!! Just add another Column defined as "Integer Identity
Primary Key"... Say it's Customers table, and the new PK will be CustomerID.
.
Alter Table Customers
(Add Column CustomerID Integer Identity Primary Key Not Null)
And there's NOTHING at all wrong with using this type of Primary Key... But
you should also, if at all possible, add another "key" in the form of a
unique constrraint or index, on whichever combination of "real" data columns
uniquely identifies each row... This will prevent the additon of two rows
(with different Idnetity values) from being added that represent the same
real world business object.
"Laura K" wrote:

> I have a table with only about 40 rows. For some reason we accidentally
> forgot the primary key. Is it possible to add a column to this table that
> adds a number to each row and also adds an increment of 1 when a new item
is
> added. While we are at it can we make this the primary key column.
> I am new at this but I will take a stab. Maybe someone can look at my synt
ax
> and tell me if I am on the right track.
> the table name is tblProductSubcategories the column I need to add will be
> called intSubcategoryID
> Here is my go at it.
> Alter Table tblProductSubcategories
> Add
> intSubcategoryID int identity not null
>
> I know that is not exactly what is needed. Can someone help me with this
> syntax. Much appreciated.
> Laura K
>
>|||Sorry, i forgot to remove the html tags from my answer. It should read:
Alter Table Employee
Add
intSubcategoryID int identity not null PRIMARY KEY
Lionel
"Lionel" wrote:
> It's almost done. You just forgot the primary key. Try this
> --
> Alter Table tblProductSubcategories
> Add
> intSubcategoryID int identity not null <B>PRIMARY KEY</B>
> --
>
> Lionel
>
> "Laura K" wrote:
>|||Thanks all will give it a try this evening. We actually didn't forget the
PK. We decided to use the subcat name but as I designed I got frustrated
with not having a number. Thus the change. Now when I do this will it add
the numbers incrementally to all 40 rows.
Laura
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:C8B1B527-538E-438C-A19A-26E665C25645@.microsoft.com...
> Laura,
> YES, you can!! Just add another Column defined as "Integer Identity
> Primary Key"... Say it's Customers table, and the new PK will be
> CustomerID...
> Alter Table Customers
> (Add Column CustomerID Integer Identity Primary Key Not Null)
> And there's NOTHING at all wrong with using this type of Primary Key...
> But
> you should also, if at all possible, add another "key" in the form of a
> unique constrraint or index, on whichever combination of "real" data
> columns
> uniquely identifies each row... This will prevent the additon of two rows
> (with different Idnetity values) from being added that represent the same
> real world business object.
>
> "Laura K" wrote:
>|||WOW you are so very helpful. I wish everyone could be as dramatic as you.
It really helps moral.
Laura K
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1116025782.902845.162170@.f14g2000cwb.googlegroups.com...
> accidentally forgot the primary key. <<
> That is not an accident; that is a major design flaw.
>
> each row and also adds an increment of 1 when a new item is added.
> While we are at it can we make this the primary key column. <<
> BY DEFINITION this is not a primary key!! Start over and do a real
> design.
>|||Yes, Although the order will be arbitrary... If you want the records
"numbered" in a specific Order, then you need to do something a bit
different... RU interested int that?
(Remember, since this is a "Surrogate" key, the actual value should not be
publicly visible or used by end-users, so the value shouldn't be important,
just that it's unique...)
"Laura K" wrote:

> Thanks all will give it a try this evening. We actually didn't forget th
e
> PK. We decided to use the subcat name but as I designed I got frustrated
> with not having a number. Thus the change. Now when I do this will it ad
d
> the numbers incrementally to all 40 rows.
> Laura
>
> "CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
> news:C8B1B527-538E-438C-A19A-26E665C25645@.microsoft.com...
>
>|||Joe,
Jeez, -- Only "killing people"?
You mean the fate of the entire space-time continumn is not at risk from
the use of Identity columns ? What a relief!! And all this time I thought..
.
THis is really good news !!
<grin>
Charly
"--CELKO--" wrote:

> My purpose is not to make you feel good. One of my purposes on
> newsgroups is to keep incompetent progammers from killing people. But
> all that matters in your world is your feelings, isn't it?
> If you don't know me or understand that statement, then Google prior
> postings about how people who screwed up schema designs by not knowing
> what 1NF was and a medical supply program to Africa I consulted on.
> You will probably kludge it with an IDENTITY column. But it needs a
> total re-design.
>

Add blank rows to table

Hello,
I need to create a report with a fixed number of table rows. For example:
the table is fixed at 20 rows per page regardless of data retrieved. If say
10 rows are filled with data then the rest are blanks. If 25 rows of data is
retrieved then the first page is filled and next page has 5 rows filled with
the rest blank. I also need a running row number in the first column. I've
managed that with "RowNumber(Nothing)" but I am not sure if it will work with
blank rows too. Is there any way to accomplish this? Through report
designer or custom code?
Thanks,
RodneyI would add rows to the source dataset. If the data is coming from a stored
procedure, append a dummy column to the end of the dataset and put a 1 in it
if the row contained data and a 2 if it did not (this way you have something
to sort on so that the blank rows are at the end of the report. Use a
count() in the stored procedure to determine how many rows to append to the
end of the dataset.
"Rodney" wrote:
> Hello,
> I need to create a report with a fixed number of table rows. For example:
> the table is fixed at 20 rows per page regardless of data retrieved. If say
> 10 rows are filled with data then the rest are blanks. If 25 rows of data is
> retrieved then the first page is filled and next page has 5 rows filled with
> the rest blank. I also need a running row number in the first column. I've
> managed that with "RowNumber(Nothing)" but I am not sure if it will work with
> blank rows too. Is there any way to accomplish this? Through report
> designer or custom code?
> Thanks,
> Rodney|||How would I append blank rows to a select query? Are you suggesting a
temporary table where the data and blank rows are dumped into? Would this be
a problem in a multiuser app?
"Kebler" wrote:
> I would add rows to the source dataset. If the data is coming from a stored
> procedure, append a dummy column to the end of the dataset and put a 1 in it
> if the row contained data and a 2 if it did not (this way you have something
> to sort on so that the blank rows are at the end of the report. Use a
> count() in the stored procedure to determine how many rows to append to the
> end of the dataset.
> "Rodney" wrote:
> > Hello,
> >
> > I need to create a report with a fixed number of table rows. For example:
> > the table is fixed at 20 rows per page regardless of data retrieved. If say
> > 10 rows are filled with data then the rest are blanks. If 25 rows of data is
> > retrieved then the first page is filled and next page has 5 rows filled with
> > the rest blank. I also need a running row number in the first column. I've
> > managed that with "RowNumber(Nothing)" but I am not sure if it will work with
> > blank rows too. Is there any way to accomplish this? Through report
> > designer or custom code?
> >
> > Thanks,
> >
> > Rodney

add another column to sproc output

Say i have sproc that return rows with x columns.
Say I now want to add another column to it.
For eg: Say I am running sp_who2
But now Say i want to add a getdate column to it and i want to do something
like
Select getdate() + exec sp_who2There *might* be *better* ways to do this, but my general purpose sp_who2
script might help you with this specific requirement..
go
if OBJECT_ID('tempdb..#spwho') > 0 drop table #spwho
go
create table #spwho (
SPID int not null
, Status varchar (255) not null
, Login varchar (255) not null
, HostName varchar (255) not null
, BlkBy varchar(10) not null
, DBName varchar (255) null
, Command varchar (255) not null
, CPUTime int not null
, DiskIO int not null
, LastBatch varchar (255) not null
, ProgramName varchar (255) null
, SPID2 int not null
)
go
insert #spwho
exec sp_who2
go
select getdate(), *
from #spwho
--where SPID > 50 and login != SUSER_SNAME()
order by SPID --LastBatch desc
go
if OBJECT_ID('tempdb..#spwho') > 0 drop table #spwho
HTH
Regards,
Greg Linwood
SQL Server MVP
"Hassan" <hassanboy@.hotmail.com> wrote in message
news:eH74sHz6FHA.472@.TK2MSFTNGP15.phx.gbl...
> Say i have sproc that return rows with x columns.
> Say I now want to add another column to it.
> For eg: Say I am running sp_who2
> But now Say i want to add a getdate column to it and i want to do
> something like
> Select getdate() + exec sp_who2
>
>|||Hassan,
This technique might be more generally useful to you:
SELECT getDate() as CurrentTime, *
FROM OPENROWSET ('SQLOLEDB',
'Server=(local);Database=master;Trusted_
Connection=yes', 'SET FMTONLY OFF;
exec sp_who2');
You may have to change the connection string if you are not using Windows
authentication for some reason.
Cheers,
Chris
"Hassan" wrote:

> Say i have sproc that return rows with x columns.
> Say I now want to add another column to it.
> For eg: Say I am running sp_who2
> But now Say i want to add a getdate column to it and i want to do somethin
g
> like
> Select getdate() + exec sp_who2
>
>

add a column default value from other column

Hi,
I need to add a column A for my table, this column A is the same type as
another one column B. For the exsiting rows I would like this new column A to
have same value as column B, how can I do that? Thanks.ALTER TABLE YourTable
ADD COLUMN ColumnA DATATYPE_HERE NULL
UPDATE YourTable
SET ColumnA = ColumnB
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Jen" <Jen@.discussions.microsoft.com> wrote in message
news:0676BB0D-ACAF-4AF7-A698-335F84928371@.microsoft.com...
> Hi,
> I need to add a column A for my table, this column A is the same type as
> another one column B. For the exsiting rows I would like this new column A
to
> have same value as column B, how can I do that? Thanks.|||There are serveral ways you can accomplish this.
You can create your new column on the table, and then update it to be the
same as ColumnB
UPDATE tablename
set columnA = ColumnB
--
Simon Worth
"Jen" <Jen@.discussions.microsoft.com> wrote in message
news:0676BB0D-ACAF-4AF7-A698-335F84928371@.microsoft.com...
> Hi,
> I need to add a column A for my table, this column A is the same type as
> another one column B. For the exsiting rows I would like this new column A
to
> have same value as column B, how can I do that? Thanks.

add a column default value from other column

Hi,
I need to add a column A for my table, this column A is the same type as
another one column B. For the exsiting rows I would like this new column A to
have same value as column B, how can I do that? Thanks.
ALTER TABLE YourTable
ADD COLUMN ColumnA DATATYPE_HERE NULL
UPDATE YourTable
SET ColumnA = ColumnB
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"Jen" <Jen@.discussions.microsoft.com> wrote in message
news:0676BB0D-ACAF-4AF7-A698-335F84928371@.microsoft.com...
> Hi,
> I need to add a column A for my table, this column A is the same type as
> another one column B. For the exsiting rows I would like this new column A
to
> have same value as column B, how can I do that? Thanks.
|||There are serveral ways you can accomplish this.
You can create your new column on the table, and then update it to be the
same as ColumnB
UPDATE tablename
set columnA = ColumnB
Simon Worth
"Jen" <Jen@.discussions.microsoft.com> wrote in message
news:0676BB0D-ACAF-4AF7-A698-335F84928371@.microsoft.com...
> Hi,
> I need to add a column A for my table, this column A is the same type as
> another one column B. For the exsiting rows I would like this new column A
to
> have same value as column B, how can I do that? Thanks.

add a column default value from other column

Hi,
I need to add a column A for my table, this column A is the same type as
another one column B. For the exsiting rows I would like this new column A t
o
have same value as column B, how can I do that? Thanks.ALTER TABLE YourTable
ADD COLUMN ColumnA DATATYPE_HERE NULL
UPDATE YourTable
SET ColumnA = ColumnB
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Jen" <Jen@.discussions.microsoft.com> wrote in message
news:0676BB0D-ACAF-4AF7-A698-335F84928371@.microsoft.com...
> Hi,
> I need to add a column A for my table, this column A is the same type as
> another one column B. For the exsiting rows I would like this new column A
to
> have same value as column B, how can I do that? Thanks.|||There are serveral ways you can accomplish this.
You can create your new column on the table, and then update it to be the
same as ColumnB
UPDATE tablename
set columnA = ColumnB
Simon Worth
"Jen" <Jen@.discussions.microsoft.com> wrote in message
news:0676BB0D-ACAF-4AF7-A698-335F84928371@.microsoft.com...
> Hi,
> I need to add a column A for my table, this column A is the same type as
> another one column B. For the exsiting rows I would like this new column A
to
> have same value as column B, how can I do that? Thanks.

Actual execution plan vs Estimated execution plan

The benefit of the actual execution plan is that you can see the actual number of rows passing through each step - compared to the estimated number of rows.

But what about the "cost percentages" ?

I believe I've read somewhere that these percentages is still just an estimate and is not based on the real execution.

Does anyone know this and preferable have a link to something that documents it?

ThanksThe cost percentages is what let you know if you need to change table placement in JOINS or use index to improve performance. The information you are looking is in SQL Server Profiler in Management Studio or Enterprise Manager. You use the Profiler to find duration of SQL statements and more. Run a search for Profiler in SQL Server BOL (books online). Hope this helps.

Actual and Estimated Rows

I have a stored procedure that will execute with less than 1,000 reads one
time (with a specified set of parameters), then with a different set of
parameters the procedure executes with close to 500,000 reads (according to
Profiler).

In comparing the execution plans, they are the same, except for the actual
and estimated number of rows. When the proc runs with parameters that produce
reads that are less than 1,000 the actual and estimated number of rows equal
1. When the proc runs with parameters that produce reads are near 500,000 the
actual rows are approximately 85,000 and the estimated rows equal 1.

Then I run:
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

If I then reverse the order of execution by executing the procedure that
initially executes with close to 500,000 reads first, the reads drop to less
than 2,000. The execution plan shows the acutual number of rows equal to 1,
and the estimated rows equal to 2.27. Then when I run the procedure that
initially executed with less than 1,000 reads, it continues to run at less
than 1,000 reads, and the actual number of rows is equal to 1 and the
estimated rows equal to 2.27. When run in this order, there is consistency in
the actual and estimated number of rows and the reads for both executions
with differing parameters are within reason.

Do I need to run DBCC DROPCLEANBUFFERS and DBCC FREEPROCCACHE on production
and then ensure that the procedure that ran close to 500,000 reads is run
first to ensure the proper plan, as well as using a KEEP PLAN option? Or,
what other options might you recommend?

I am running SQL 2000 SP4.

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forum...eneral/200609/1So perhaps a little more information will be helpful.

First of all, this is the query statement that generates the differing actual
versus estimated rows:

(The variables are of type int, sent as arguments to the stored procedure.)

SELECT y.rpFID, x.dCode, x.Desc, x.dcUID
FROM dbo.Table_X x
JOIN dbo.Table_Y y
ON x.dcUID = y.dcFID
AND x.MainID = y.MainID
JOIN dbo.Table_Z z
ON y.rpFID = z.rpUID
AND y.MainID = z.MainID
WHERE z.pFID = @.pID
AND z.MainID = @.MainID
AND x.MainID = @.MainID
AND y.MainID = @.MainID

I have run:
UPDATE STATISTICS dbname.Table_X WITH FULLSCAN
UPDATE STATISTICS dbname.Table_Y WITH FULLSCAN
UPDATE STATISTICS dbname.Table_Z WITH FULLSCAN

When I execute the stored proc with the following arguments and in this order
I get the following results:
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
go
exec dbo.dem_sel_combined @.pID = 440, @.MainID = 576021
go
exec dbo.dem_sel_combined @.pID = 476, @.MainID = 576003
go

What this did was drop the reads from 500,000 to 250,000, while the estimated
versus actual dropped on one clustered index seek (on Table_Z) from 85,000
actual and 1 estimated to 4 actual and 1 estimated. On the other hand, the
clustered index seek (on Table_Y) still reports 85,000 actual and 1
estimated.

If I just pull this query out and run on its own, there are 4 records
returned.

When I execute the stored proc with the following arguments and in this order
(note the arguments) I get the following results:
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
go
exec dbo.dem_sel_combined @.pID = 476, @.MainID = 576003
go
exec dbo.dem_sel_combined @.pID = 440, @.MainID = 576021
go

What this did was drop the reads from 250,000 to 79, while the estimated
versus actual on one clustered index seek (on Table_Z) remained 4 actual and
1 estimated while the clustered index seek (on Table_Y) dropped from 85,000
actual and 1 estimated to 5 actual and 1 estimated.

Any ideas how to always have the plan optimized to the lower read count, or
stated another way, optimized to the correct actual versus estimated row
counts?

cbrichards wrote:

Quote:

Originally Posted by

>I have a stored procedure that will execute with less than 1,000 reads one
>time (with a specified set of parameters), then with a different set of
>parameters the procedure executes with close to 500,000 reads (according to
>Profiler).
>
>In comparing the execution plans, they are the same, except for the actual
>and estimated number of rows. When the proc runs with parameters that produce
>reads that are less than 1,000 the actual and estimated number of rows equal
>1. When the proc runs with parameters that produce reads are near 500,000 the
>actual rows are approximately 85,000 and the estimated rows equal 1.
>
>Then I run:
>DBCC DROPCLEANBUFFERS
>DBCC FREEPROCCACHE
>
>If I then reverse the order of execution by executing the procedure that
>initially executes with close to 500,000 reads first, the reads drop to less
>than 2,000. The execution plan shows the acutual number of rows equal to 1,
>and the estimated rows equal to 2.27. Then when I run the procedure that
>initially executed with less than 1,000 reads, it continues to run at less
>than 1,000 reads, and the actual number of rows is equal to 1 and the
>estimated rows equal to 2.27. When run in this order, there is consistency in
>the actual and estimated number of rows and the reads for both executions
>with differing parameters are within reason.
>
>Do I need to run DBCC DROPCLEANBUFFERS and DBCC FREEPROCCACHE on production
>and then ensure that the procedure that ran close to 500,000 reads is run
>first to ensure the proper plan, as well as using a KEEP PLAN option? Or,
>what other options might you recommend?
>
>I am running SQL 2000 SP4.

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forum...eneral/200609/1|||Adding on to my previous reply, when I execute the procedures in the
following order (getting the high reads and large actual rows), I obtain the
following explain plan:

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
go
exec dbo.dem_sel_combined @.pID = 440, @.MainID = 576021
go
exec dbo.dem_sel_combined @.pID = 476, @.MainID = 576003
go

Rows Executes StmtText
-- --- ---
4 1 Nested Loops(Inner Join, OUTER REFERENCES:([y].
[dcFID]) OPTIMIZED)
4 1 |--Nested Loops(Inner Join, OUTER REFERENCES:([y].
[rpFID]) OPTIMIZED)
82331 1 | |--Clustered Index Seek(OBJECT:([MyDB].[dbo].
[Table_Y].[IX_MainID_rpdcUID] AS [y]), SEEK:([y].[MainID]=[@.MainID]) ORDERED
FORWARD)
4 82331 | |--Clustered Index Seek(OBJECT:([MyDB].[dbo].
[Table_Z].[IX_MainID_rpUID] AS [z]), SEEK:([z].[MainID]=[@.MainID] AND [z].
[rpUID]=[MyDB].[dbo].[Table_Y].[rpFID] as [y].[rpFID]), WHERE:([MyDB].[dbo].
[Table_Z].[pFID] as [z].[pFID]=[@.pID]) ORDERED FORWARD)
4 4 |--Clustered Index Seek(OBJECT:([MyDB].[dbo].
[Table_X].[IX_MainID_dcUID] AS [x]), SEEK:([x].[MainID]=[@.MainID] AND [x].
[dcUID]=[MyDB].[dbo].[Table_Y].[dcFID] as [y].[dcFID]) ORDERED FORWARD)

when I execute the procedures in the following order (getting the low number
of reads and low number of actual rows), I obtain the following explain plan:

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
go
exec dbo.dem_sel_combined @.pID = 476, @.MainID = 576003
go
exec dbo.dem_sel_combined @.pID = 440, @.MainID = 576021
go

Rows Executes StmtText
-- --- ---
4 1 Nested Loops(Inner Join, OUTER REFERENCES:([y].
[dcFID]) OPTIMIZED)
4 1 |--Nested Loops(Inner Join, OUTER REFERENCES:([z].
[rpUID]))
5 1 | |--Index Seek(OBJECT:([MyDB].[dbo].[Table_Z].
[IDX1_pFID] AS [z]), SEEK:([z].[pFID]=[@.pID]), WHERE:([MyDB].[dbo].[Table_Z].
[MainID] as [z].[MainID]=[@.MainID]) ORDERED FORWARD)
4 5 | |--Clustered Index Seek(OBJECT:([MyDB].[dbo].
[Table_Y].[IX_MainID_rpdcUID] AS [y]), SEEK:([y].[MainID]=[@.MainID] AND [y].
[rpFID]=[MyDB].[dbo].[Table_Z].[rpUID] as [z].[rpUID]) ORDERED FORWARD)
4 4 |--Clustered Index Seek(OBJECT:([MyDB].[dbo].
[Table_X].[IX_MainID_dcUID] AS [x]), SEEK:([x].[MainID]=[@.MainID] AND [x].
[dcUID]=[MyDB].[dbo].[Table_Y].[dcFID] as [y].[dcFID]) ORDERED FORWARD)

Please help explain how I might always obtain the second explain plan, if
possible.

cbrichards wrote:

Quote:

Originally Posted by

>So perhaps a little more information will be helpful.
>
>First of all, this is the query statement that generates the differing actual
>versus estimated rows:
>
>(The variables are of type int, sent as arguments to the stored procedure.)
>
>SELECT y.rpFID, x.dCode, x.Desc, x.dcUID
>FROM dbo.Table_X x
>JOIN dbo.Table_Y y
ON x.dcUID = y.dcFID
AND x.MainID = y.MainID
>JOIN dbo.Table_Z z
ON y.rpFID = z.rpUID
AND y.MainID = z.MainID
>WHERE z.pFID = @.pID
>AND z.MainID = @.MainID
>AND x.MainID = @.MainID
>AND y.MainID = @.MainID
>
>I have run:
>UPDATE STATISTICS dbname.Table_X WITH FULLSCAN
>UPDATE STATISTICS dbname.Table_Y WITH FULLSCAN
>UPDATE STATISTICS dbname.Table_Z WITH FULLSCAN
>
>When I execute the stored proc with the following arguments and in this order
>I get the following results:
>DBCC DROPCLEANBUFFERS
>DBCC FREEPROCCACHE
>go
>exec dbo.dem_sel_combined @.pID = 440, @.MainID = 576021
>go
>exec dbo.dem_sel_combined @.pID = 476, @.MainID = 576003
>go
>
>What this did was drop the reads from 500,000 to 250,000, while the estimated
>versus actual dropped on one clustered index seek (on Table_Z) from 85,000
>actual and 1 estimated to 4 actual and 1 estimated. On the other hand, the
>clustered index seek (on Table_Y) still reports 85,000 actual and 1
>estimated.
>
>If I just pull this query out and run on its own, there are 4 records
>returned.
>
>When I execute the stored proc with the following arguments and in this order
>(note the arguments) I get the following results:
>DBCC DROPCLEANBUFFERS
>DBCC FREEPROCCACHE
>go
>exec dbo.dem_sel_combined @.pID = 476, @.MainID = 576003
>go
>exec dbo.dem_sel_combined @.pID = 440, @.MainID = 576021
>go
>
>What this did was drop the reads from 250,000 to 79, while the estimated
>versus actual on one clustered index seek (on Table_Z) remained 4 actual and
>1 estimated while the clustered index seek (on Table_Y) dropped from 85,000
>actual and 1 estimated to 5 actual and 1 estimated.
>
>Any ideas how to always have the plan optimized to the lower read count, or
>stated another way, optimized to the correct actual versus estimated row
>counts?
>

Quote:

Originally Posted by

>>I have a stored procedure that will execute with less than 1,000 reads one
>>time (with a specified set of parameters), then with a different set of

>[quoted text clipped - 27 lines]

Quote:

Originally Posted by

>>
>>I am running SQL 2000 SP4.

--
Message posted via http://www.sqlmonster.com|||cbrichards via SQLMonster.com (u3288@.uwe) writes:

Quote:

Originally Posted by

I have a stored procedure that will execute with less than 1,000 reads
one time (with a specified set of parameters), then with a different set
of parameters the procedure executes with close to 500,000 reads
(according to Profiler).
>
In comparing the execution plans, they are the same, except for the
actual and estimated number of rows. When the proc runs with parameters
that produce reads that are less than 1,000 the actual and estimated
number of rows equal 1. When the proc runs with parameters that produce
reads are near 500,000 the actual rows are approximately 85,000 and the
estimated rows equal 1.
>
Then I run:
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
>
If I then reverse the order of execution by executing the procedure that
initially executes with close to 500,000 reads first, the reads drop to
less than 2,000. The execution plan shows the acutual number of rows
equal to 1, and the estimated rows equal to 2.27. Then when I run the
procedure that initially executed with less than 1,000 reads, it
continues to run at less than 1,000 reads, and the actual number of rows
is equal to 1 and the estimated rows equal to 2.27. When run in this
order, there is consistency in the actual and estimated number of rows
and the reads for both executions with differing parameters are within
reason.

So this is what is happening: SQL Server engage in something known as
parameter sniffing. When SQL Server runs a stored procedure for which
there is no plan available in the procedure cache, the optimizer builds
a plan for the procedure. It then uses the actual values of the input
parameters as guidance for the plan.

This can sometimes backfire, for instance if the first invocation is
for an atypical value. It appears that in your case, there is little
difference for one of the input cases, but the balance tips over in
favour of a plan that is detrimental for the other input cases.

The situation is not entirely trivial to handle. There are several
options, but all have their drawbacks.

To start with DBCC FREEPROCACHE, since this affects the entire server, this
is a really bad idea.

If you always want the same plan, you can use an index hint, so that
say

dbo.Table_Z z WITH (INDEX = IDX1_pFID)

to force the use of the non-clustred index. This may still not give
the plan you want, though, as the optimizer can choose some other
combination. (On SQL 2005 you can force the exact plan if you wish.) A
more seroius problem with this approach is that in two years when
the data has changed, another plan is much better. So index hints should
only be used when you are convinced that the index in question is
always the best.

Another alternative is to add WITH RECOMPILE to the procedure definition.
In this case the procedure gets recompiled every time it's called, which
means that you can get a new plan each time. The obvious drawback is
that you get a higher overhead. If the procedure is just that statement,
it's not that big deal, but if the procedure is several hundread lines
long, there is reason for concern.

--
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|||cbrichards via SQLMonster.com wrote:

Quote:

Originally Posted by

So perhaps a little more information will be helpful.
>
First of all, this is the query statement that generates the differing actual
versus estimated rows:
>
(The variables are of type int, sent as arguments to the stored procedure.)
>
SELECT y.rpFID, x.dCode, x.Desc, x.dcUID
FROM dbo.Table_X x
JOIN dbo.Table_Y y
ON x.dcUID = y.dcFID
AND x.MainID = y.MainID
JOIN dbo.Table_Z z
ON y.rpFID = z.rpUID
AND y.MainID = z.MainID
WHERE z.pFID = @.pID
AND z.MainID = @.MainID
AND x.MainID = @.MainID
AND y.MainID = @.MainID

Something likely unrelated to your issue (Erland answered that one
already) as it does not change the semantics of your statement but I
though I mention it nevertheless: it seems two of the last lines of this
SQL statement are pretty superfluous as all three tables are joined on
MainID anyway. So I'd leave only "AND x.MainID = @.MainID" in there
because Table_X seems to be your major table.

Ah, and another remark: I find these table and column names highly
unspecific. Changing them to something more meaningful (identifiers are
not restricted to 8 characters) will probably help maintain the
application.

Kind regards

robert