Adjust values in db base in the value modified on the form?

Hi everyone,

Here is the problem I am facing with. I have a form which has multiple fields including Price (read only), Discount(read/write), TotalSellPrice(read/write), Quantity(read/write) ... What I need to do is I need to adjust TotalSellPrice value if there was a new Discount value entered and vise versa. If both values have been changed I should use Discount value entered and calculate the TotalSellPrice. I am having hard time figuring the query out. Any thoughts or ideas in what direction should I go.

Thanks for your help!Hi everyone,

Here is the problem I am facing with. I have a form which has multiple fields including Price (read only), Discount(read/write), TotalSellPrice(read/write), Quantity(read/write) ... What I need to do is I need to adjust TotalSellPrice value if there was a new Discount value entered and vise versa. If both values have been changed I should use Discount value entered and calculate the TotalSellPrice. I am having hard time figuring the query out. Any thoughts or ideas in what direction should I go.

Thanks for your help!

Paste your query or the tables involved and explain what you want in new one?|||I don't have any query yet, I need to come up with the query that will check which value has been changed and do update approprietly.

Ex. #1 - Discount value is changed
Price is $100
Quantity was 0 (changed to) 2
Discount was 0% (changed to) 10%
TotalSellPrice $0 (hasn't been changed) -> I need to calculate it then. it becomes $180

Ex. #2 - Discount and TotalSellPrice values are changed
Price is $100
Quantity was 0 (changed to) 2
Discount was 0% (changed to) 10%
TotalSellPrice $0 (changed to) $150
Then I need to calculate TotalSellPrice since both Discount and TotalSellPrice values have been changed. TotalSellPrice becomes $180

Ex. #3 - TotalSellPrcie value is changed
Price is $100
Quantity was 0 (changed to) 2
Discount was 0% (hasn't been changed) -> I need to calculate it. Discount is 25%
TotalSellPrice $0 (changed to) $150

Ex. #4 - TotalSellPrice value is changed to number bigger then it's original one then I have to keep Discount at 0% don't go negative
Price is $100
Quantity was 0 (changed to) 2
Discount was 0% (hasn't been changed) -> I need to keep Discount at 0%
TotalSellPrice $0 (changed to) $250|||I don't have any query yet, I need to come up with the query that will check which value has been changed and do update approprietly.

Whatever I make out from the above problem is, you want to adjust the data according to the changes in the other fields.I think you can easily do it at the front end and send the update query to the database according to its invoice no.
An user can see the store data and update it anytime by changing the different fields.You can do the neccessary calculation at the front end.And pass a simple update query to the database.That will do the trick..|||I think you can easily do it at the front end and send the update query to the database according to its invoice no.
The thing is it would not be efficient from a programming stand point, because I have to grab all the information from the database and store it somewhere in the session, then compare it to what user enters, then create a query dynamically instead of using stored procedure. I need a back end query that will handle all these stuff for me.

Any idea?|||Any idea?

And you need not have to create a stored procedure for such a small calculation.I think so...;)|||Maybe I explained it incorrectly, I am sorry.
What I do I initially populate those fields with the default data from the database

Ex. - Initial page load
Price -> "$100"
Quantity -> "0"
Discount -> "0%"
TotalSellPrice -> "$0"

After user updates any of writable fields I do update and populate that form again but base on the rules I described before, I hope that explains everything.|||Maybe I explained it incorrectly, I am sorry.
What I do I initially populate those fields with the default data from the database

Ex. - Initial page load
Price -> "$100"
Quantity -> "0"
Discount -> "0%"
TotalSellPrice -> "$0"

After user updates any of writable fields I do update and populate that form again but base on the rules I described before, I hope that explains everything.
So...?? You can write the adjustment code in asp page,and simply update the record.Mind it you are not doing anything more than that..so I don't think on the point of efficiency ,use of stored procedure will make any difference...|||After user updates any of writable fields I do update and populate that form again but base on the rules I described before...You think it is efficient or good programming practice to make a call to the database every time a user changes a value on a form?

It's not, and you certainly wouldn't design scalable enterprise applications this way.

Your form should get complete recordsets from the database (even defaults), and should submit complete recordsets to the database.

And frankly, you don't have to store all the detail data to keep track of the new total. NewTotal = OldTotal - OldValue + NewValue. That is just three variables.

Address1, Address2 and Address3

I have a requirement to have a mailing label the can have up to 3 Address
fields
Homer Simpson
Address Line 1
Address Line 2
Address Line 3
Springfield, IL 999999
If 2 an 3 are not used I would like the City State and Zip to move up under
Address line 1, right now there are blank lines between Address 1 and
City/State.
I'm using a Table RDL element to display the fields.
JeffExcellent idea! I tried that on one of my reports and it seems to work
well.
--
Keith
"Sunny" <Sunny@.discussions.microsoft.com> wrote in message
news:8ABAF59D-0CFB-4FC3-8221-9FD7EA7927A5@.microsoft.com...
> i am suggesting just after reading your question
> if each address is on a seperate row in the table...why don't u HIDE the
> rows Address2 and Address3 depending on wheather they are nothing or
empty as
> Hidden = iif(isnothing(Address2),True,False)
> let us know if this works...
> "Jeff" wrote:
> > I have a requirement to have a mailing label the can have up to 3
Address
> > fields
> >
> > Homer Simpson
> > Address Line 1
> > Address Line 2
> > Address Line 3
> > Springfield, IL 999999
> >
> > If 2 an 3 are not used I would like the City State and Zip to move up
under
> > Address line 1, right now there are blank lines between Address 1 and
> > City/State.
> >
> > I'm using a Table RDL element to display the fields.
> >
> > Jeff|||Hey Sunny,
Your solution Hides the row (makes invisible) but the rows don't get removed
to bring Address 1 and Address 3, if there is no value in Address 2.
Can you suggest a solution?
It seam like we need a property to "align" the rows "Bring Together"
Jeff
"Sunny" <Sunny@.discussions.microsoft.com> wrote in message
news:8ABAF59D-0CFB-4FC3-8221-9FD7EA7927A5@.microsoft.com...
> i am suggesting just after reading your question
> if each address is on a seperate row in the table...why don't u HIDE the
> rows Address2 and Address3 depending on wheather they are nothing or
empty as
> Hidden = iif(isnothing(Address2),True,False)
> let us know if this works...
> "Jeff" wrote:
> > I have a requirement to have a mailing label the can have up to 3
Address
> > fields
> >
> > Homer Simpson
> > Address Line 1
> > Address Line 2
> > Address Line 3
> > Springfield, IL 999999
> >
> > If 2 an 3 are not used I would like the City State and Zip to move up
under
> > Address line 1, right now there are blank lines between Address 1 and
> > City/State.
> >
> > I'm using a Table RDL element to display the fields.
> >
> > Jeff|||Keith, what other properties (expressions) did you set for the Fields?
Jeff
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:O9mirS9fEHA.704@.TK2MSFTNGP09.phx.gbl...
> Excellent idea! I tried that on one of my reports and it seems to work
> well.
> --
> Keith
>
> "Sunny" <Sunny@.discussions.microsoft.com> wrote in message
> news:8ABAF59D-0CFB-4FC3-8221-9FD7EA7927A5@.microsoft.com...
> > i am suggesting just after reading your question
> >
> > if each address is on a seperate row in the table...why don't u HIDE the
> > rows Address2 and Address3 depending on wheather they are nothing or
> empty as
> > Hidden = iif(isnothing(Address2),True,False)
> >
> > let us know if this works...
> >
> > "Jeff" wrote:
> >
> > > I have a requirement to have a mailing label the can have up to 3
> Address
> > > fields
> > >
> > > Homer Simpson
> > > Address Line 1
> > > Address Line 2
> > > Address Line 3
> > > Springfield, IL 999999
> > >
> > > If 2 an 3 are not used I would like the City State and Zip to move up
> under
> > > Address line 1, right now there are blank lines between Address 1 and
> > > City/State.
> > >
> > > I'm using a Table RDL element to display the fields.
> > >
> > > Jeff
>|||Got it!.. need to set the expression on the Row, I was doing it on the
Field/Cell...
Works perfect...
Thanks guys
"Jeff" <Jeff@.discussions.microsoft.com> wrote in message
news:23A578CF-E937-4A0B-BDAA-CBA2468F24C8@.microsoft.com...
> I have a requirement to have a mailing label the can have up to 3 Address
> fields
> Homer Simpson
> Address Line 1
> Address Line 2
> Address Line 3
> Springfield, IL 999999
> If 2 an 3 are not used I would like the City State and Zip to move up
under
> Address line 1, right now there are blank lines between Address 1 and
> City/State.
> I'm using a Table RDL element to display the fields.
> Jeff|||nothing.
I selected the row (click on the grey box outside [to the left] of the
table) and then navigated to the properties window. Expand visibility. I
added the following expression under Hidden:
=iif(isnothing(Fields!AddlInfo.Value),True,False)
(My column is called AddlInfo)
--
Keith
"Jeff" <ernzddy@.msn.com> wrote in message
news:ukGHiV9fEHA.3536@.TK2MSFTNGP12.phx.gbl...
> Keith, what other properties (expressions) did you set for the Fields?
> Jeff
> "Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
> news:O9mirS9fEHA.704@.TK2MSFTNGP09.phx.gbl...
> > Excellent idea! I tried that on one of my reports and it seems to work
> > well.
> >
> > --
> > Keith
> >
> >
> > "Sunny" <Sunny@.discussions.microsoft.com> wrote in message
> > news:8ABAF59D-0CFB-4FC3-8221-9FD7EA7927A5@.microsoft.com...
> > > i am suggesting just after reading your question
> > >
> > > if each address is on a seperate row in the table...why don't u HIDE
the
> > > rows Address2 and Address3 depending on wheather they are nothing or
> > empty as
> > > Hidden = iif(isnothing(Address2),True,False)
> > >
> > > let us know if this works...
> > >
> > > "Jeff" wrote:
> > >
> > > > I have a requirement to have a mailing label the can have up to 3
> > Address
> > > > fields
> > > >
> > > > Homer Simpson
> > > > Address Line 1
> > > > Address Line 2
> > > > Address Line 3
> > > > Springfield, IL 999999
> > > >
> > > > If 2 an 3 are not used I would like the City State and Zip to move
up
> > under
> > > > Address line 1, right now there are blank lines between Address 1
and
> > > > City/State.
> > > >
> > > > I'm using a Table RDL element to display the fields.
> > > >
> > > > Jeff
> >
>

Additional Global fields within RS

Hi,
the usage of GLOBALS in RS is very useful because they could be used
within the definition of data sources as parameter as well as content
to fill report text fields etc.
Has anybody an idea, how to define an additional GLOBAL var and fill it
with a value' (e.g. via code extension)
Thanks for your suggestions
best regards
BruceOne way to do this is to create a parameter and leave the prompt empty... It
will not be shown with the other parameters, but it still part of the
parameters collection...
You might also try to create Statics variables in the REport Code section.
--
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
"BB_Reporting" <Bruce.Baessler@.web.de> wrote in message
news:1107776267.070741.229380@.c13g2000cwb.googlegroups.com...
> Hi,
> the usage of GLOBALS in RS is very useful because they could be used
> within the definition of data sources as parameter as well as content
> to fill report text fields etc.
> Has anybody an idea, how to define an additional GLOBAL var and fill it
> with a value' (e.g. via code extension)
> Thanks for your suggestions
> best regards
> Bruce
>

Adding User Define fields together

This is driving me crazy! The SQL Statement refenced is shown at the end of this email.

When I try and run the statement, an error is raised saying that Undrawn_GT5MIL_LE365Days is invalid (likewise for Undrawn_LE5MIL_LE365Days). From what I can gather, it is saying that I cannot include a User Defined variable in another argument. This is unlike Access. Any suggestions?

SQL View......

SELECT TOP 100 PERCENT QRY_FacNew_Term.Category, QRY_FacNew_Term.Fac_No, QRY_FacNew_Term.Client_Number, QRY_FacNew_Term.Client_Name,

Undrawn_GT5MIL_LE365Days = CASE WHEN Undrawn_CDN >= 5000000 AND Term <= 365 THEN Undrawn_CDN ELSE 0 END,

Undrawn_GT5MIL_GT365Days = CASE WHEN (Undrawn_CDN >= 5000000 AND Term > 365) OR

(Cr_Limit_CDN IN (0, 1)) THEN Undrawn_CDN ELSE 0 END, [Undrawn_GT5MIL_LE365Days]+[Undrawn_GT5MIL_GT365Days] AS Total

FROM dbo.QRY_FacNew_Term

WHERE (Exclude <> 'Y')

ORDER BY Category, Client_NameUse brackets if name includes space:

[drawn_GT5MIL_LE365Days ] = CASE WHEN Undrawn_CDN >= 5000000 AND Term <= 365 THEN Undrawn_CDN ELSE 0 END,

adding up fields

My problem right now is I have three layers in my sheet.
Objective level
measure level
region level
facility level
The facility level is not a problem, just a Field!Facility.Value, it's a
straight pull from the db.
Region is fine, it's a sum(Field!Facility.Value)
Measure is a bit tricky, but it's fine - (Sum(Field!PassFail.Value)/Sum(Field!
TotalFacility))*Field!MeasureScore.Value
My problem now is that when you add all the measures, they should equal the
Objective score. So say I have 2 measures that work out to
.61
.24
my objective score should be .85
The problem is, I can't sum the measure scores because I can't Sum an
aggregate function.
So, I changed one of my views so that I could calculate a measure score in
the datafield and just pull it to the spreadsheet. The problem is, when I
Sum these fields, it works for adding up ALL of the objectives, not for each
objective. And when I put in more than once facility, it adds up the scores
for all of the objectives for all of the facilities.
My guess is that I am grouping incorrectly in the view. I'm pulling a bunch
of columns into my view and grouping on them. Is it possible to pull a
column into a select statement without grouping by it? I am grouping by
region, facility, objective and measure where I think I should just be
grouping by objective. When I try to just group by one column, I get errors
all over the place.
thanks for any help you can give.Okay, so I have four levels and not three:) Shows just how confused and
frustrated I am :)
ravensensei wrote:
>My problem right now is I have three layers in my sheet.
>Objective level
>measure level
>region level
>facility level
>The facility level is not a problem, just a Field!Facility.Value, it's a
>straight pull from the db.
>Region is fine, it's a sum(Field!Facility.Value)
>Measure is a bit tricky, but it's fine - (Sum(Field!PassFail.Value)/Sum(Field!
>TotalFacility))*Field!MeasureScore.Value
>My problem now is that when you add all the measures, they should equal the
>Objective score. So say I have 2 measures that work out to
>.61
>.24
>my objective score should be .85
>The problem is, I can't sum the measure scores because I can't Sum an
>aggregate function.
>So, I changed one of my views so that I could calculate a measure score in
>the datafield and just pull it to the spreadsheet. The problem is, when I
>Sum these fields, it works for adding up ALL of the objectives, not for each
>objective. And when I put in more than once facility, it adds up the scores
>for all of the objectives for all of the facilities.
>My guess is that I am grouping incorrectly in the view. I'm pulling a bunch
>of columns into my view and grouping on them. Is it possible to pull a
>column into a select statement without grouping by it? I am grouping by
>region, facility, objective and measure where I think I should just be
>grouping by objective. When I try to just group by one column, I get errors
>all over the place.
>thanks for any help you can give.

Adding two dates

I have two fields, Date Purchased (smalldatetime) and warranty in months (integer).

can anyone help me to formulate a query to show the date when the warranty ends, and/or the remaining days/months in the warranty please?

I'm not sure if its doable, but i would really appreciate it if anyone can help me!

Give a look to the DATEADD function in books online; it looks like this:

declare @.example table
( rid integer,
datePurchased smalldatetime,
warranty integer
)

insert into @.example
select 1, '4/8/7', 3 union all
select 2, '5/14/7', 12

select rid,
convert(varchar(10), datePurchased, 101) as datePurchased,
warranty,
dateadd (mm, warranty, datePurchased) as warrantyEndDate,
datediff (day, getdate(), dateadd (mm, warranty, datePurchased)) as daysRemaining,
datediff (mm, getdate(), dateadd (mm, warranty, datePurchased)) as monthsRemaining
from @.example

/*
rid datePurchased warranty warrantyEndDate daysRemaining monthsRemaining
-- - -- - -
1 04/08/2007 3 2007-07-08 00:00:00 45 2
2 05/14/2007 12 2008-05-14 00:00:00 356 12
*/

adding subreport to crosstab

Hi all
My first question is
Is it possible to add a subreport in crosstab report
I am doing a crosstab report and have to add two more fields from another view
is it possible to add a sub report
ThanksIt is possible to add Sub report in a Cross-tab report.

Adding parameters during .updating event handler

Not receiving any errors. The update runs perfectly on all fields, except for the added parameter during the sub. I need to change the hidden field's value after parsing out several fields in the form and generating a logfile entry of sorts. This needs to happen after all the form fields are updated, but before the update is executed.

aspx: (relevant parts only...)
<asp:SqlDataSourceID="TicketDetails"runat="server"ConnectionString="<%$ ConnectionStrings:myConnectionString %>"
UpdateCommand="UPDATE Tickets SET TicketSuspense = @.TicketSuspense, TicketPriority = @.TicketPriority, TicketLastUpdated = CURRENT_TIMESTAMP, TicketStatus = 'Assigned', TicketTechnicianNotes = @.TicketTechnicianNotes WHERE (TicketID = @.TicketID)">
<UpdateParameters>
<asp:ParameterName="TicketPriority"Type="String"/>
<asp:ParameterName="TicketSuspense"Type="DateTime"/>
<asp:ParameterName="TicketID"Type="Int32"/>
</UpdateParameters>
</asp:SqlDataSource>

codebehind: (once again, the relevant parts only)
ProtectedSub TicketDetails_Updating(ByVal senderAsObject,ByVal eAs System.Web.UI.WebControls.SqlDataSourceCommandEventArgs)Handles TicketDetails.Updating
TicketTechnicianNotesHiddenField.Value ="some text..."& TicketTechnicianNotesHiddenField.Value
TicketDetails.UpdateParameters.Add(New Parameter("TicketTechnicianNotes", TypeCode.String, TicketTechnicianNotesHiddenField.Value))
EndSub

Thanks,

- Brad

What about this:

ProtectedSub TicketDetails_Updating(ByVal senderAsObject,ByVal eAs System.Web.UI.WebControls.SqlDataSourceCommandEventArgs)Handles TicketDetails.Updating
TicketTechnicianNotesHiddenField.Value ="some text..."& TicketTechnicianNotesHiddenField.Value

e.Command.Parameters("@.TicketTechnicianNotes").Value = TicketTechnicianNotesHiddenField.Value

EndSub

|||

I think that was the only variation I hadn't yet tried. It worked.

I have to ask... what caused the "Add" not to work?

Thanks,

- Brad

|||

"TheSqlDataSource control will also automatically create parameters based on values passed by a data-bound control... "

You can find more information from this link:

http://msdn2.microsoft.com/en-us/library/z72eefad.aspx

Adding or subtracting

I'm in trouble. I have a table with some fields, one of them is an amount of money.
One field indicates whether the amount is negetive or positive. This fields has only two possible values, 'N' and 'R'. 'N' means the amount is negative and 'R' means the amount is positive.

The problem I have is that I have to sum these amounts to give a total for each customer and report them as

customer1 total amount1
customer2 total amount2

I don't know how to make a query to bring these results. I know that sum would bring me the total amount for each customer but I don't know how to specify which fields are supposed to be negative so that it subtracts them instead of adding them.

By the way I'm not allowed to change the structure of the table.

Could you help me try to create this query?you need to use a CASE stmt. check out BOL for some samples.

hth|||Could you give me an example through this thread please. I have no access to other internet pages. I can only access this one.

I tried something like this but it didn't work out

select r.numpolso, r.numofic,
case r.tdocumen
when 'N' then sum (r.prmtotrb*'-1')
when 'R' then sum (r.prmtotrb)
end as r.prmtotrb
from trecibos r
where r.numpolso = '1010' and r.ramo = '90' and r.subramo = '14' and r.numofic = '001'
group by r.numofic, r.numpolso, r.prmtotrb

I got a syntax error msg

could you give me an example or another way of doing this?

thanks in advance

Adding new Guid to destination

Source table has two fields, int and string. Destination has three fields, int, string and Uniqueidentifier (Guid). For each source row added to the destination, I need to add a new Guid. I know how to make a new Guid in VB.NET script, but how do I inject it into the transformation for each row. I see the “foreach loop container” component and I suspect I need to use it, but I am stumped. Any help would be most appreciated

Paul SullivanUse script component in the DataFlow (added between source and destination). When you edit it, mark your guid column as read/write input column and in the script template write something like

Row.MyGuid = ...|||

Hi,

I have the same task as you have.

Did you make the script component for adding Guid to each source row?

Could you please help me to understand it?

If you could share some source code and some settings for it - would be great!

Thanks.

|||

Vita wrote:

Hi,

I have the same task as you have.

Did you make the script component for adding Guid to each source row?

Could you please help me to understand it?

If you could share some source code and some settings for it - would be great!

Thanks.

Imports System

.

.

.

.

Row.MyGuid = System.Guid.NewGuid()

Regards

Jamie

|||

I did not think that configuration for it would be so easy.

Thanks a lot.

Adding new Guid to destination

Source table has two fields, int and string. Destination has three fields, int, string and Uniqueidentifier (Guid). For each source row added to the destination, I need to add a new Guid. I know how to make a new Guid in VB.NET script, but how do I inject it into the transformation for each row. I see the “foreach loop container” component and I suspect I need to use it, but I am stumped. Any help would be most appreciated

Paul SullivanUse script component in the DataFlow (added between source and destination). When you edit it, mark your guid column as read/write input column and in the script template write something like

Row.MyGuid = ...|||

Hi,

I have the same task as you have.

Did you make the script component for adding Guid to each source row?

Could you please help me to understand it?

If you could share some source code and some settings for it - would be great!

Thanks.

|||

Vita wrote:

Hi,

I have the same task as you have.

Did you make the script component for adding Guid to each source row?

Could you please help me to understand it?

If you could share some source code and some settings for it - would be great!

Thanks.

Imports System

.

.

.

.

Row.MyGuid = System.Guid.NewGuid()

Regards

Jamie

|||

I did not think that configuration for it would be so easy.

Thanks a lot.

Adding new columns with check constraints using same batch

I'm using a stored procedure to add fields to an existing table.

These fields must have check constraints and I need to use one T-SQL batch.

In Sql2000 Ok. In Sql2005, if table exists, I get error "invalid column xxxxx" in Add Constraint statement before new column creation.

the code is

Declare @.Setup bit

Set @.Setup = 1

if @.Setup = 1 Begin

--Alter Table MyTable Add MyField Numeric(1, 0) Not Null Default 3

Exec mySp_Add_Column 'MyTable', 'MyField', 'Numeric (1, 0) Not Null', '3'

If IsNull(ObjectProperty(Object_Id('xCK_MyTable_MyField'), 'IsConstraint'), 0) = 0

Alter Table MyTable Add Constraint xCK_MyTable_MyField Check (MyField >= 1 And MyField <= 3)

End Else Begin

-- drop column

End

GO

If MyTable does not exist and, naturally, I add it before of check constraints (using another Sp which add tables) ok.

If I add FK to new fields, ok.

Now I have to split batch in two parts as workaround...

Can anyone tell me if this is a bug or a "fix" for previous versions?

Many thanks,

Giulio

Interesting, it probably is an undocumented "fix" that exists pretty much because it is illogical to add a check constraint when something doesn't exist, so when they parse it (before they execute it) it fails.

I would personally just add the check constraint creation to the ADD column proc, if I were you. It would just require a parameter, and it would be easy enough. The parameter I would add would be @.checkCriteria. Then pass in 'MyField >= 1 and MyField <= 3)'

Then you can formulate the name (or at worst add another parameter as override) and build the statement in the proc. You can add the constraint when you are adding the column too:

create table test
(
testId int primary key
)
go
alter table test
add newColumn varchar(2000) not null
constraint ckTestNewColumn check (newColumn = 1)

|||Another workaround might be this:

If IsNull(ObjectProperty(Object_Id('xCK_MyTable_MyField'), 'IsConstraint'), 0) = 0
exec('
Alter Table MyTable Add Constraint xCK_MyTable_MyField Check (MyField >= 1 And MyField <= 3)')

Steve Kass
Drew University
www.stevekass.com

|||

Thanks Louis and Steve.

I've just resolved problem. Since in Sql 2000 this problem did not exist (using same T-Sql code) I'd like to know if it's a bug...

Really, if I use Alter Table, instead of mySp_Add_Column to add new columns, I get error too... So if I can understand parser can't know what SP wants to do, why doesn't parser understand I'll add new column before of check constraints? Why can I add FKs on new fields while I can't add CKs?

It seems a parser bug...

Bye,

Giulio

|||

Really? When I execute:

create table test
(
testId int primary key
)
alter table test
add newColumn varchar(2000) not null
constraint ckTestNewColumn check (newColumn = 1)

in a single batch, it works just fine. What error are you getting?

|||

ok, in this way it works...

Only when I add a column to an existing table and, after, I add a check constraint, all in one batch, I get error...

And only after I installed SqlSrv 2k5... on SqlSrv 2k it works fine...

Adding new column fields into a big table issue

I have an existing table which has about 70 columns with 3 million rows in it. I was asked to add additional 50 new columns into the table. I have tried to add them in through the Enterprise manager design table but experiencing some problems. The adding process seemed never going to be end. Is there any good efficient way to do it? I appreciate the help!

J8You're trying to add 150 million pieces of data (3 million rows times 50 columns) to your table, which now has 210 million pieces (3 million rows times 70 columns). That is significant growth (about 70%), so it will probably take quite a while.

If the columns are all NULL-able, then I'd create a second "child" table that had the primary key from the first table and all of the new columns in it. That would allow you to populate them much more gracefully, possibly in stages.

If you really need to add these columns to the existing table because of Referential Integrity issues or due to other reasons, then I'd strongly recommend declaring downtime (so you can force the users off of the system), then making the changes from Query Analyzer using the ALTER TABLE command. It still won't be fast, but it will be faster than any other method for doing this kind of job.

-PatP|||Pat,

For whatever reason, I have to stick those new columns into this big table. You can consider this table as kind of 'feed' table.

Thanks for the tips.

J8|||You should not have problems adding 50 columns providing they all allow NULL. If not then they must have a default, and you may bring your database down very easily (which what I suspect has happened) because every row must be updated with default value for that column or columns.|||You can script the addition of columns and run it from query analyzer

alter table mytable add col71 int null, col72 int null, ...

as long as all of the columns are nullable this should happen instantly. I would expect EM to add them instantly too unless there is some difference between the default ANSI settings of the DB, Table, and your EM Session.

Adding multiple fields together

Okay hopefully this is quick and easy.

I have 3 fields (Annual Salary, Medical, Pension) and I now need to working out Cost To Company which is just adding those three together.

I tried to do that in a formula but it doesn't return me anything... This is what I had.

=(Sum(Fields!AnnualSalary.Value, "CoreEmployeeData")+Sum(Fields!AnnualMedicalAid.Value, "CoreEmployeeData")+Sum(Fields!AnnualPension.Value, "CoreEmployeeData"))

So it should add the 3 records?

While you are here :), Can you tell me how I can put a currency symbol in the front of these numbers and add the thousand comma delimiter?

ok, What is the "CoreEmployeeData" section, I would think this is affecting your calcs.

Also i would braket each individual sum too as below, I have also added in the ￡ and thousand delimiter too.

="￡" & round(cdec((Sum(Fields!AnnualSalary.Value))+(Sum(Fields!AnnualMedicalAid.Value))+(Sum(Fields!AnnualPension.Value), 2)), 2)

I have not tested this but should provide what you are looking for.

Andy

|||CoreEmployeeData is the DataSet name.
If I leave that out I get an out of scope error.

If I leave it there I still get no response at all....?|||Okay well it's working now.
I had to paste the code in the "Format" and "Value" fields.

Not sure why it was required in both but it seems to work now. I think I need some more practice :)

Thanks,
Gavin|||

the easiest way to get aorund this issue is split out your sum to individual columns, create 3 new columns in your report, and put the Sum() in each, make sure that return the correct data in the new columns.

If so then just copy and paste each one into your total column and + them together making sure you bracket each one and wrapper the whole thing in brackets too.

((sum(A)) + (sum(B)) + (sum(B)))

Now add in my code above to format to dec and add ￡ and you should be ok

|||

Cool! - Its is one of those things, to be honest i dont use the format area at all but add my formatting only into the expression for each field, at least then you only have to change one area of needed.

SSRS certianly has its quirks but you get used to it.

Andy

Adding more keys in a table

Hi All,
I'm running merge replication.
I want to add some keys for existing fields in a replicated table (composite
keys). Is it possible? How to do that?
Thanks
use sp_repladdcolumn for this. When I hear the term composite keys I think
a primary key consisting of more than one column. Replication normally
complains when you change a PK.
"echo" <echo@.discussions.microsoft.com> wrote in message
news:462ECC14-6DF7-4D12-9B46-3744B388B563@.microsoft.com...
> Hi All,
> I'm running merge replication.
> I want to add some keys for existing fields in a replicated table
(composite
> keys). Is it possible? How to do that?
> Thanks
|||Hi Hilary,
That's why. The column is already exist. I just want to change/modify it as
(another) primary key.
Should I do create dummy column --> copy the contents --> drop the column
--> create the new one --> copy from dummy?
The replication runs in continues mode. Will it stops immediately when I do
these changes? What should I do next?
TIA
Echo
btw do you have merge replication book? how can I get it? thanks )
"Hilary Cotter" wrote:

> use sp_repladdcolumn for this. When I hear the term composite keys I think
> a primary key consisting of more than one column. Replication normally
> complains when you change a PK.
> "echo" <echo@.discussions.microsoft.com> wrote in message
> news:462ECC14-6DF7-4D12-9B46-3744B388B563@.microsoft.com...
> (composite
>
>

Adding Fields To A Table

I am new to SQL Server 2005 and I am trying to add two fields to an existing table. The table has 15 Million records in it and the save is not completing. How do I add the new fields?How are you doing it now?|||Through the Management Studio interface. Modify the table, add the fields, save the table. I get a timeout expired message.|||are you adding them to the end of the table, or inserting between 2 other fields, the latter taking a longer time.

look at Alter Table...|||Did you save the script?

And are you moving the columns into places not that are the last

It will make a temp copy of the table, copy all of the rows, rebuild the new table, then copy the data over, then do an sp_rename, then drop the original

Lot of overhead

just do this

CREATE TABLE myTable99(col1 int IDENTITY(1,1), Col2 datetime DEFAULT(GetDate()), Col3 Char(1))
GO

INSERT INTO myTable99(Col3)
SELECT 'a' UNION ALL SELECT 'b' UNION ALL SELECT 'c'
GO

SELECT * FROM myTable99
GO

ALTER TABLE myTable99 ADD Col4 binary
GO

SELECT * FROM myTable99
GO

DROP TABLE myTable99
GO|||I am adding the fields to a specific place in the table (not at the end). Why does this matter? I tried adding to the end and it saved fine. What do I need to do to add the fields where I would like them to be in the table. Some existing processes rely on the order of the fields.|||see the last line in Bretts signature. this violates relational theory. your application should not act like this.|||The reason we have it this way is to simplify our archival processes. By using the field indexes instead of field names the code does not have to change when the physical structure of the tables changes as long as both tables have the same structure.|||The problem you are facing is that Management studio is going to do the following to accomplish this:

1) Create a new table with the name tmp_yourtablename with all the columns in the order you want.
2) Transfer all of the data from the old table to the tmp_ table. Yes. all 15 million rows will be doubled up.
3) Drop the old table (usually with no error checking)
4) Rename the tmp_table as the original table name.

Contrast that with the alter table command which would append the two new columns on the end of the table, and takes a few seconds to run.

How can the archival process be simpler by using the field index? i would think that any of the columns "pushed out" by the insertion of new fields in the "middle" of the table to cause much larger problems.|||Some existing processes rely on the order of the fields.

Why would that be?

SELECT * perhaps?|||The reason we have it this way is to simplify our archival processes. By using the field indexes instead of field names the code does not have to change when the physical structure of the tables changes as long as both tables have the same structure.

OK, so what does that have to do with not adding them to the end?

In any case, The logging is what's going to kill you

I might bcp the data out
CREATE the new table
bcp the data back in, using a format card, into the new table|||The archival is being done in Visual Basic, using ADO recordsets. When you reference the table's Fields collection using the index, as long as field 1 is ID in one table and 1 is ID in the other table then it doesn't matter the names of the fields, but the order is important.|||The problem with adding them to the end is, the only difference in the table structure is the last field in the archive table. It is the datetime the record was archived.|||The archival is being done in Visual Basic, using ADO recordsets.

Shoot me now|||How many indexes are on this table? You might consider dropping them before you add the new columns, and then recreating them.|||I am adding the fields to a specific place in the table (not at the end). Why does this matter? I tried adding to the end and it saved fine. What do I need to do to add the fields where I would like them to be in the table. Some existing processes rely on the order of the fields.

I believe SQL need to create a temp table of the table, then insert the data back when you insert, as oppose to add fields to the end of the table. ColIDs change when inserting new fields, as opposed to adding.

Adding Fields to a large table

Dear all
I implemented a datawarehouse for a midsized retail company on SQL Server
2005. One of their facttables is a daily stock snapshot with about 50
millions of records.
Business now want to create a new report. This report requires several
calculated values. The requirement says this values should be stored in the
warehouse.
Here is my question: When I add 3 more decimal fields (18,9) to this stock
table - how does this affect the size of the record/table when they have NULL
values or a value?
Thanks in advance for your help,
Regards,
Marc
Marc,
They will take up 9 bytes each regardless of whether or not they are null.
-- Bill
"Marc" <Roger_Rombooth@.community.nospam> wrote in message
news:CB83237B-BDE4-467F-8C4B-AD9C88D972B5@.microsoft.com...
> Dear all
> I implemented a datawarehouse for a midsized retail company on SQL Server
> 2005. One of their facttables is a daily stock snapshot with about 50
> millions of records.
> Business now want to create a new report. This report requires several
> calculated values. The requirement says this values should be stored in
> the
> warehouse.
> Here is my question: When I add 3 more decimal fields (18,9) to this stock
> table - how does this affect the size of the record/table when they have
> NULL
> values or a value?
> Thanks in advance for your help,
> Regards,
> Marc
>
|||Hello Marc,
As Bill mentioned, fixed length data will take up the bytes no matter it is
Null or not. Also, in SQL Server 2000, a NULL bitmap is always present,
even if no NULLs are allowed in any column. You may want to refer to the
following link for more details:
Poking about with DBCC PAGE (Part 1 of ?)
https://blogs.msdn.com/sqlserverstorageengine/archive/2006/08/09/692806.aspx
If you have any further questions or comments, please feel free to let's
know. Thank you!
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Thanks Bill and Peter!
Best Regards,
Marc

Adding Fields to a large table

Dear all
I implemented a datawarehouse for a midsized retail company on SQL Server
2005. One of their facttables is a daily stock snapshot with about 50
millions of records.
Business now want to create a new report. This report requires several
calculated values. The requirement says this values should be stored in the
warehouse.
Here is my question: When I add 3 more decimal fields (18,9) to this stock
table - how does this affect the size of the record/table when they have NUL
L
values or a value?
Thanks in advance for your help,
Regards,
MarcMarc,
They will take up 9 bytes each regardless of whether or not they are null.
-- Bill
"Marc" <Roger_Rombooth@.community.nospam> wrote in message
news:CB83237B-BDE4-467F-8C4B-AD9C88D972B5@.microsoft.com...
> Dear all
> I implemented a datawarehouse for a midsized retail company on SQL Server
> 2005. One of their facttables is a daily stock snapshot with about 50
> millions of records.
> Business now want to create a new report. This report requires several
> calculated values. The requirement says this values should be stored in
> the
> warehouse.
> Here is my question: When I add 3 more decimal fields (18,9) to this stock
> table - how does this affect the size of the record/table when they have
> NULL
> values or a value?
> Thanks in advance for your help,
> Regards,
> Marc
>|||Hello Marc,
As Bill mentioned, fixed length data will take up the bytes no matter it is
Null or not. Also, in SQL Server 2000, a NULL bitmap is always present,
even if no NULLs are allowed in any column. You may want to refer to the
following link for more details:
Poking about with DBCC PAGE (Part 1 of ?)
https://blogs.msdn.com/sqlserversto.../09/692806.aspx
If you have any further questions or comments, please feel free to let's
know. Thank you!
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
========================================
==========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
<http://msdn.microsoft.com/subscript...ps/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscript...rt/default.aspx>.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.|||Thanks Bill and Peter!
Best Regards,
Marc

adding fields in page footer

Hi Every one
I have problem in using page footer field from the storeprocedure. I have a procedure and defined two fields one is page header and page footer. When I drag and drop them repective area it displaying error message you can't use fields for header and footer than how can I use them please help me. I am new to this reporting services.Please, I got this issue too.
Does anyone get good solution?
thanks.|||I don't know if I understand you correctly but....if you are using visual studio.net
you can only use globals! and textboxes with text in them.

I hope this is what you are looking for. I am pretty new at this also.

Hope you find an answer to your problem,
Kerrie|||Can you put them in the table header & table footer?|||

Hi,
For some reason VS.net is very limited on what you can do in the headers and footers. No fields or tools can go in them. VS.net will only accept globals and textboxes with text.

I have not tried any constants-i dont know about that one.

I hoped that helped.

thanx and have a great day...

Kerrie

|||You can use fields in the page header and footer by using a hidden parameter. You select the fields in your stored procedure or SQL just like normal fields and then set up a hidden parameter, referencing the field. In the SSRS 2000 version, leave the Prompt value blank – this makes it hidden. In SSRS 2005, set the Hidden check box on the Report Parameters dialog box.

Next, add a new parameter to your report and set the “default value” by selecting the field from your dataset.
Then, create a text box whose value will be the new parameter - i.e. “=Parameters!Footer.Value”

adding fields

Win 2K & SQL 2K
I have several SQL servers replicating to one server and
I need to add a field to a replicated table can I....
Add the field to the subscriber, then via the Properties-
>Filter Columns->Add Column to Table add the same field
to the publisher?
Will this cause a problem with the other publishers when
they attempt to replicate and there is an addition al
field in the destination table, but has not been added on
all the publishers?
Logically, this seems like it will work. I really do not
want to drop all the publications, alter the tables, then
recreate all the publications with no sync.
HELP!!!
the preferred way to add a column to articles in an existing publication is
to use sp_repladdcolumn.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"larry" <anonymous@.discussions.microsoft.com> wrote in message
news:757d01c4765b$2188ee20$a401280a@.phx.gbl...
> Win 2K & SQL 2K
> I have several SQL servers replicating to one server and
> I need to add a field to a replicated table can I....
> Add the field to the subscriber, then via the Properties-
> to the publisher?
> Will this cause a problem with the other publishers when
> they attempt to replicate and there is an addition al
> field in the destination table, but has not been added on
> all the publishers?
> Logically, this seems like it will work. I really do not
> want to drop all the publications, alter the tables, then
> recreate all the publications with no sync.
> HELP!!!