Showing posts with label row. Show all posts
Showing posts with label row. Show all posts

Sunday, March 25, 2012

Calling SQL 2005 stored procedure that returns a value

I have a stored procedure on a SQL server, which is workign correctly to check some date/time parameters and then insert a row into a log table.

I am calling it from an ASP page. Initially I just called it and didn't worry about the return value. However the Stored procedure now will return a value to determine if it made the change or not and if not why (ie log entry was at incorrect time etc).

I woudl liek to capture this returned value in my code to display a message to the user but am havign problems finding the right way to get the value.

I am calling the SP as follows:

Shared Sub createlogentry(ByVal ID, ByVal tme, ByVal val)
Dim result As String
Dim cs As String = ConfigurationManager.ConnectionStrings("connecttion1").ToString
Using con As New System.Data.SqlClient.SqlConnection(cs)
con.Open()
Dim cmd As New System.Data.SqlClient.SqlCommand()
cmd.Connection = con
cmd.CommandType = Data.CommandType.StoredProcedure
cmd.CommandText = "CreateLogEntry"
cmd.Parameters.Add("@.ChID", Data.SqlDbType.Int)
cmd.Parameters("@.ChID").Value = ID
cmd.Parameters.Add("@.Value", Data.SqlDbType.Int)
cmd.Parameters("@.Value").Value = val
result = cmd.ExecuteNonQuery().ToString
End Using
End Sub

I have tried amending the ExecuteNonQuery line to ExecuteReader()

Any help appreciated

Regards

Clive

Greetings Clive

I would have made the stored procedure use an output parameter as more outputs can always be added. A return can only return one value!

So as output you would add the lines:

cmd.Parameters.Add("Fred"), Data.SqlDbType.Int)

cmd.Parameters("Fred").Direction = Output;

after the result add

Dim iResult As Integer = md.Parameters("Fred").Value

|||

Thanks for that - you are right the ability to return more than one value would be useful.

At the moment in my T-SQL I use the RETURN construct to generate the return value. How would I change that to use an output parameter instead?

regards

Clive

|||

As in

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Clive Chinery
-- Create date: 13Aug2007
-- Description: Test for records for today and tomorrow
-- =============================================
CREATE PROCEDURE dbo.DoDateTest
@.Test1 BIT OUTPUT,
@.Test2 BIT OUTPUT
AS
SET NOCOUNT ON;
IF EXISTS(SELECT * FROM tblToday WHERE Date = GetDate()) SET @.Test1 = 1 ELSE SET @.Test1 = 0
IF EXISTS(SELECT * FROM tblToday WHERE Date = DateAdd(day, 1, GetDate()))
SET @.Test2 = 1 ELSE SET @.Test2 = 0
------- This is the End ----
GO

This returns two output values. I normally put the output parameters at the end of the declaration.

Regards

Clive Chinery

Thursday, March 22, 2012

Calling functions in an expression

I have a function in the Report Code of one of my reports and an expression
that references it. Here is the expression that is in the details row:
=Iif(Code.Check(Fields!fullname.Value), 0,
Code.AddClient(Fields!fullname.Value))
The Check function basically looks in an array to see if the name is in the
array already. If it is, the expression should return 0. If it is not in the
array, it should call AddClient() to add the client and increment the
variable Total (Which is also declared in the Report Code). However, whenever
this expression runs, Total gets incrememnted no matter what. Even if the IIf
condition is false.
Is there something wrong with the way I am doing this?It is ok, is there any error in that case what exactly is the error?
Amarnath
"Matt M" wrote:
> I have a function in the Report Code of one of my reports and an expression
> that references it. Here is the expression that is in the details row:
> =Iif(Code.Check(Fields!fullname.Value), 0,
> Code.AddClient(Fields!fullname.Value))
> The Check function basically looks in an array to see if the name is in the
> array already. If it is, the expression should return 0. If it is not in the
> array, it should call AddClient() to add the client and increment the
> variable Total (Which is also declared in the Report Code). However, whenever
> this expression runs, Total gets incrememnted no matter what. Even if the IIf
> condition is false.
> Is there something wrong with the way I am doing this?|||There is no error, but rather this is what is being displayed in the report:
0 Case1
1 Case2
2 Case3
3 Case4
4 Case5
The first column displays the Total variable that gets incremented by the
AddClient() function. It should only be incremented if the Check() returns
true in any of the Cases. But instead, it gets incremented every row.
"Amarnath" wrote:
> It is ok, is there any error in that case what exactly is the error?
> Amarnath
> "Matt M" wrote:
> > I have a function in the Report Code of one of my reports and an expression
> > that references it. Here is the expression that is in the details row:
> >
> > =Iif(Code.Check(Fields!fullname.Value), 0,
> > Code.AddClient(Fields!fullname.Value))
> >
> > The Check function basically looks in an array to see if the name is in the
> > array already. If it is, the expression should return 0. If it is not in the
> > array, it should call AddClient() to add the client and increment the
> > variable Total (Which is also declared in the Report Code). However, whenever
> > this expression runs, Total gets incrememnted no matter what. Even if the IIf
> > condition is false.
> >
> > Is there something wrong with the way I am doing this?|||You still need to evaluate the expression... (i.e. you need an = sign)
change it to:
=Iif(Code.Check(Fields!fullname.Value) = 1, 0,
Code.AddClient(Fields!fullname.Value))
and I am assuming that you are returning a zero for false and 1 for true...
=-Chris
"Matt M" <MattM@.discussions.microsoft.com> wrote in message
news:2FE6A608-EE89-407D-AAED-A17385D6952F@.microsoft.com...
>I have a function in the Report Code of one of my reports and an expression
> that references it. Here is the expression that is in the details row:
> =Iif(Code.Check(Fields!fullname.Value), 0,
> Code.AddClient(Fields!fullname.Value))
> The Check function basically looks in an array to see if the name is in
> the
> array already. If it is, the expression should return 0. If it is not in
> the
> array, it should call AddClient() to add the client and increment the
> variable Total (Which is also declared in the Report Code). However,
> whenever
> this expression runs, Total gets incrememnted no matter what. Even if the
> IIf
> condition is false.
> Is there something wrong with the way I am doing this?|||I'm actually returning a boolean true or false with it. I tried returning a 0
or a 1 and still does the same thing.
"Chris Conner" wrote:
> You still need to evaluate the expression... (i.e. you need an = sign)
> change it to:
> =Iif(Code.Check(Fields!fullname.Value) = 1, 0,
> Code.AddClient(Fields!fullname.Value))
> and I am assuming that you are returning a zero for false and 1 for true...
> =-Chris
> "Matt M" <MattM@.discussions.microsoft.com> wrote in message
> news:2FE6A608-EE89-407D-AAED-A17385D6952F@.microsoft.com...
> >I have a function in the Report Code of one of my reports and an expression
> > that references it. Here is the expression that is in the details row:
> >
> > =Iif(Code.Check(Fields!fullname.Value), 0,
> > Code.AddClient(Fields!fullname.Value))
> >
> > The Check function basically looks in an array to see if the name is in
> > the
> > array already. If it is, the expression should return 0. If it is not in
> > the
> > array, it should call AddClient() to add the client and increment the
> > variable Total (Which is also declared in the Report Code). However,
> > whenever
> > this expression runs, Total gets incrememnted no matter what. Even if the
> > IIf
> > condition is false.
> >
> > Is there something wrong with the way I am doing this?
>
>|||It means it is always returning true... Try hard code just for testing
purpose. hard code it to false and see whether it increments.
Amarnath
"Matt M" wrote:
> There is no error, but rather this is what is being displayed in the report:
> 0 Case1
> 1 Case2
> 2 Case3
> 3 Case4
> 4 Case5
> The first column displays the Total variable that gets incremented by the
> AddClient() function. It should only be incremented if the Check() returns
> true in any of the Cases. But instead, it gets incremented every row.
>
> "Amarnath" wrote:
> > It is ok, is there any error in that case what exactly is the error?
> >
> > Amarnath
> >
> > "Matt M" wrote:
> >
> > > I have a function in the Report Code of one of my reports and an expression
> > > that references it. Here is the expression that is in the details row:
> > >
> > > =Iif(Code.Check(Fields!fullname.Value), 0,
> > > Code.AddClient(Fields!fullname.Value))
> > >
> > > The Check function basically looks in an array to see if the name is in the
> > > array already. If it is, the expression should return 0. If it is not in the
> > > array, it should call AddClient() to add the client and increment the
> > > variable Total (Which is also declared in the Report Code). However, whenever
> > > this expression runs, Total gets incrememnted no matter what. Even if the IIf
> > > condition is false.
> > >
> > > Is there something wrong with the way I am doing this?

Monday, March 19, 2012

Calling a Web Service with data from a SQL Server table one row at a time

Hello

I am trying to call a Web Servie within a SSIS package. I want to pull a row at a time from a SQL Server 2005 table and pass this data values onto the web service.

Any help is highly appreciated!

Kind Regards

Gayatri

I guess it rather depends on what you want to do with the Web Service. Can you give us more details?

You can certainly call a web service row-by-row from a script component in the data flow. This may work for you, but it rather depends on what you want to do once you have called the service.

Donald

|||

Hi Donald

Many thanks for your reply. Web Service is written to accept fields of the records from the table I am reading. I've gone as far as accessing record fields in a script task whlie the records are being read. Where I am lost is assigning those field values to the web service input fields.

My Web service interface is as follows:

Service: PersonWebserviceService

Method: CreatePersonRecord

CreatePersonRecord Method accepts as input a structure to hold fields data values as follows:

(On the input tab)

Name: Key

Type: LegacysystemData

Values: each value in turn have a Name, Type and Value components (eg: Name: ClientNo Type: String and Value). Value is the field I need to access in the script task to assign the record field value I read in)

Your help is much appreciated.

Gayatri

|||

Hi Donald

During the course of the day I managed to Create a web service proxy for my web service and reference it in the script task. But when I run the SSIS Package I get the following error:

Could not load file or assembly 'PersonWebserviceService, Version=0.0.0.0, Culture=neutral, PublicKeyToken=null' or one of its dependencies. The system cannot find the file specified.

Hope you could shed some light

many thanks


Gayatri

Wednesday, March 7, 2012

Call Stored procedure for last 4 months then combine into final result set row

I want to call my stored proc for that last 4 months. Basically all I need to do is pass each month's first date and it will do the rest. Should I shove this into a UDF first? I'm not sure if I can do that. The struction is here behind my stored proc: http://www.webfound.net/storedproc.txt

EXEC IT_Get_Dashboard_Monthly '2006-05-03 12:03:43.910' <-- change to UDF or leave it? Then how can I loop and change each month to cover the last 4 months?

I also need to ensure all 4 values returned in each interation show up in one row in the final result set that is produced

use a simple while loop
declare @.date datetime,
@.end_date datetime
select @.date = '' -- your start date
select @.end_date = dateadd(month, -4, @.date)
while (@.date >= @.end_date)
begin
exec IT_Get_Dashboard_Monthly @.date
select @.date = dateadd(month, -1, @.date)
end

Saturday, February 25, 2012

Call a Stored Procedure within an SQL statement

Hi All,
I created a stored procedure which updates a specific row whose row ID is passed.
But what I want to do now, (which is a one time requirement) is to update all rows
is it possible to call this stored procedure within an sql statement and pass each row ID to it?

(would i have to go to cursor approach -- damn will have to learn that )
thanks

Quote:

Originally Posted by Shashi Sadasivan

Hi All,
I created a stored procedure which updates a specific row whose row ID is passed.
But what I want to do now, (which is a one time requirement) is to update all rows
is it possible to call this stored procedure within an sql statement and pass each row ID to it?

(would i have to go to cursor approach -- damn will have to learn that )
thanks


You can call a function from a SQL statement and not a STORED PROCEDURE.

Restrictions on calling a Function from a SQL statement:

1. Function should not have RETURN TYPE as BOOLEAN
2. Function should not perform any side effects. (ie No DML operations)|||

Quote:

Originally Posted by Shashi Sadasivan

Hi All,
I created a stored procedure which updates a specific row whose row ID is passed.
But what I want to do now, (which is a one time requirement) is to update all rows
is it possible to call this stored procedure within an sql statement and pass each row ID to it?

(would i have to go to cursor approach -- damn will have to learn that )
thanks


if it's a one time thing, can you just write a query in query analyzer to update the field you need? what field are you updating? what kind of update?

Friday, February 24, 2012

Calculations in Matrix report

I have a Report Designer problem, whereby I need to perform a calculation on
each row of data laid out in a crosstab.
My datasource is a flat xml file (read into a dataset), and consists of the
following repeat base units:-
<base>
<StartDate>2006-04-10</StartDate>
<EndDate>2006-04-17</EndDate>
<Category>Books</Category>
<Amount>100</Amount>
</base>
<base>
<StartDate>2006-04-18</StartDate>
<EndDate>2006-04-25</EndDate>
<Category>CDs</Category>
<Amount>200</Amount>
</base>
...etc
I need to present this in the following way:-
Week 1 Week 2
10th-17th April 18th-25th April Calculation
Category
Books 100 300 week2 - week1
CDs 200 400 week2 - week1
..etc
The two options I have in the designer toolbox are:-
1) To use a matrix - this allows variable column data (period start date to
period end date), with variable row data (category), to be laid out as a
crosstab, but I can find no way of identifying individual cells within the
matrix to use in the calculation at the end of each row. Matrices only
appear to support totals/subtotals'
2) To use a table - is it possible to present the repeat base units shown
above in the form of a table? I can find the max EndDate in the dataset, but
is there some method of filtering the data so I can work out (period + 1
week), (period + 2 weeks), etc and then reference individual cells in the
table for the calculation'
Neither approach appears to allow crosstab representation of the data, with
calculations on each row. Is this possible?
I would like to avoid having to assemble the raw data in the form of fixed
'buckets', i.e.
<base>
<Category>Books</Category>
<Period1>2006-04-10</Period1>
<Amount1>100</Amount1>
<Period2>2006-04-18</Period2>
<Amount2>200</Amount2>
<Period3>2006-04-26</Period3>
<Amount3>400</Amount3>
</base>
<base>
<Category>CDs</Category>
<Period1>2006-04-10</Period1>
<Amount1>100</Amount1>
<Period2>2006-04-18</Period2>
<Amount2>200</Amount2>
<Period3>2006-04-26</Period3>
<Amount3>400</Amount3>
</base>
...etc
Can anyone see a solution?Hi Pete,
Thank you for using MSDN Managed Newsgroup Support.
From your description, my undestanding of this issue is : You want to add a
Calculation column in the Matrix or Table region. If I misunderstood your
concern, please feel free to let me know.
Based on my scope, I think you could add a calculated field in the dataset
and then try to present this field in the layout:
1. In the Data page in the Report Designer, click the Edit Selected dataset
button.
2. In the Dataset Dialogbox, please click the Fields Page and then type the
Field name, choose the Type as Calculated Field and then type the
Expression you want to use.
After doing this, I think you could put the calculated field in any data
region.
Hope this information will be helpful!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Thanks for your prompt response Wei.
Unfortunately, my issue is not as simple as adding a calculation to the
matrix/table.
My data is represented by a crosstab. I wish to add a calculation column,
which is an expression that references two preceding cells in the same row,
for example...
---Calculation
Row1 Cell1 Cell2 Cell3 Cell4 Cell4/Cell3
---
This does not appear possible with a matrix. And because I have variable
columns, it does not appear possible to lay the data out in tabular form
either (which would allow me to reference individual cells).
Can you see a solution to this more complex issue?
Thanks
Pete
"Wei Lu [MSFT]" <weilu@.online.microsoft.com> wrote in message
news:SaUsE8MoGHA.2028@.TK2MSFTNGXA01.phx.gbl...
> Hi Pete,
> Thank you for using MSDN Managed Newsgroup Support.
> From your description, my undestanding of this issue is : You want to add
> a
> Calculation column in the Matrix or Table region. If I misunderstood your
> concern, please feel free to let me know.
> Based on my scope, I think you could add a calculated field in the dataset
> and then try to present this field in the layout:
> 1. In the Data page in the Report Designer, click the Edit Selected
> dataset
> button.
> 2. In the Dataset Dialogbox, please click the Fields Page and then type
> the
> Field name, choose the Type as Calculated Field and then type the
> Expression you want to use.
> After doing this, I think you could put the calculated field in any data
> region.
> Hope this information will be helpful!
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Hi Pete,
Thank you for the update.
I would like to know what do you mean about the variable columns. Since
Reporting Services use dataset to get the data from the datasource, I would
like to know how you implement variable columns in the dataset.
If the field name does not consist, I don't think you could refer any field
in the Expression. So please let me know the exactly meaning of "variable
columns" so that I may provide further assistance. Thank you!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Wei,
The dataset generated by our datasource consists of the following fields:
StartDate
EndDate
Category
Amount
i.e. the data is periodic (weekly). We need to present this as follows:
Week 1 Week 2 Week 3
10th-17th April 18th-25th April 26th-3rd
Calculation
Category
Books 100 300 200
week3 - week2
CDs 200 400 150
week3 - week2
..etc
We can do this using a matrix, with 3 groupings: Category (row), StartDate
(column), EndDate (column).
What I meant was the columns are variable in Start/EndDate. But we know no
way of performing the calculation. Is this possible?
Or is there a way to do it if we use a table instead of a matrix?
Thanks in advance
Pete
"Wei Lu [MSFT]" <weilu@.online.microsoft.com> wrote in message
news:7vnx8XOoGHA.2028@.TK2MSFTNGXA01.phx.gbl...
> Hi Pete,
> Thank you for the update.
> I would like to know what do you mean about the variable columns. Since
> Reporting Services use dataset to get the data from the datasource, I
> would
> like to know how you implement variable columns in the dataset.
> If the field name does not consist, I don't think you could refer any
> field
> in the Expression. So please let me know the exactly meaning of "variable
> columns" so that I may provide further assistance. Thank you!
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Sorry, my last post might be confusing... the matrix should look like:
Week 1 Week 2 Week 3
10th-17th 18th-25th 26th-3rd Calculation
Category
Books 100 300 200 week3 - week2
CDs 200 400 150 week3 - week2
..etc
"Pete D" <peted@.community.nospam> wrote in message
news:u7enVZQoGHA.4176@.TK2MSFTNGP05.phx.gbl...
> Hi Wei,
> The dataset generated by our datasource consists of the following fields:
> StartDate
> EndDate
> Category
> Amount
> i.e. the data is periodic (weekly). We need to present this as follows:
> Week 1 Week 2 Week 3
> 10th-17th April 18th-25th April 26th-3rd
> Calculation
> Category
> Books 100 300 200
> week3 - week2
> CDs 200 400 150
> week3 - week2
> ..etc
> We can do this using a matrix, with 3 groupings: Category (row), StartDate
> (column), EndDate (column).
> What I meant was the columns are variable in Start/EndDate. But we know no
> way of performing the calculation. Is this possible?
> Or is there a way to do it if we use a table instead of a matrix?
> Thanks in advance
> Pete
>
> "Wei Lu [MSFT]" <weilu@.online.microsoft.com> wrote in message
> news:7vnx8XOoGHA.2028@.TK2MSFTNGXA01.phx.gbl...
>> Hi Pete,
>> Thank you for the update.
>> I would like to know what do you mean about the variable columns. Since
>> Reporting Services use dataset to get the data from the datasource, I
>> would
>> like to know how you implement variable columns in the dataset.
>> If the field name does not consist, I don't think you could refer any
>> field
>> in the Expression. So please let me know the exactly meaning of "variable
>> columns" so that I may provide further assistance. Thank you!
>> Sincerely,
>> Wei Lu
>> Microsoft Online Community Support
>> ==================================================>> When responding to posts, please "Reply to Group" via your newsreader so
>> that others may learn and benefit from your issue.
>> ==================================================>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>|||Hi Pete,
Thank you for the update. I would like to get your report file to do more
research.
Would you pleae send the report file to me?
You may send to me directly. I understand the information may be sensitive
to you, my direct email address is weilu@.ONLINE.microsoft.com ( Please
remove ONLINE when you send the email ), you may send the file to me
directly and I will keep it secure.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi,
Are you able to solve this issue with Matrix, i have the similar kind
of requirement. If yes pls let me know how could you achieve it
FYI: my req is as mentioned( hope i am clear, else let me know)
Thanks in advance..
Venkat
*******************
Hello,
I am using matrix control for which the rows, columns are all dynamic(
based on the group i specify). I am able to get the Subtotal for both
the rows and colums, which is Great!!..
my problem is i need to add more columns(some formula columns) which
might have "% difference", "varience" etc..
the report should look something like this (M1, M2 are model_id's)
Div/Sec M1 M2 Variance % Difference
----==AD--
O3580 0.71 1.47 -0.76 -107.04
8040 1.33 1.33 0 0
8110 9.98 14.47 -4.49 -44.99
11210 5.44 6.36 -0.92 -16.91
and my query is :
select div_sec, model_ID, model_time from table1
the output of my query is below( seperated by commas)
div_sec, model_ID, model_time
O3580,M1,0.7
O3580,M2,1.47
8040 ,M1,1.33
8040 ,M2,1.33
8110 ,M1,9.98
8110 ,M2,14.47
11210 ,M1,5.44
11210 ,M2,6.36
Any help is greatly appreciated.
Thanks,
Venkat
Wei Lu [MSFT] wrote:
> Hi Pete,
> Thank you for the update. I would like to get your report file to do more
> research.
> Would you pleae send the report file to me?
> You may send to me directly. I understand the information may be sensitive
> to you, my direct email address is weilu@.ONLINE.microsoft.com ( Please
> remove ONLINE when you send the email ), you may send the file to me
> directly and I will keep it secure.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D
> This posting is provided "AS IS" with no warranties, and confers no right=s=2E|||Hi Pete,
Thank you for the file.
After some research, I found that we could not meet the requirement in
Matrix. The Static column in Matrix does not meet your requirement.
As for the table control, it is not suitable to use table as a dynamic
columns data region.
I will try to contact internal to check whether there is an suitable
workaround for you. I appreciate your patience.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Wei,
Appreciate if you can look into my problem too(its up in this thread)..
I think its almost the same.
Let me know if you find any solution for this issue. This is a Top
reqirement for lots of my reports.
Thanks,
Venkat
Wei Lu [MSFT] wrote:
> Hi Pete,
> Thank you for the file.
> After some research, I found that we could not meet the requirement in
> Matrix. The Static column in Matrix does not meet your requirement.
> As for the table control, it is not suitable to use table as a dynamic
> columns data region.
> I will try to contact internal to check whether there is an suitable
> workaround for you. I appreciate your patience.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.|||I've posted details of my report/requirement to Wei to determine whether
there's a solution to my problem.
Its seems that Venkat and "dbro101" both have very similar issues (re:
separate postings on 12th July)
If I become aware of a solution I'll post details here.
Pete
<venkat.oar@.gmail.com> wrote in message
news:1152883485.115457.194350@.h48g2000cwc.googlegroups.com...
> Hi Wei,
> Appreciate if you can look into my problem too(its up in this thread)..
> I think its almost the same.
> Let me know if you find any solution for this issue. This is a Top
> reqirement for lots of my reports.
> Thanks,
> Venkat
> Wei Lu [MSFT] wrote:
>> Hi Pete,
>> Thank you for the file.
>> After some research, I found that we could not meet the requirement in
>> Matrix. The Static column in Matrix does not meet your requirement.
>> As for the table control, it is not suitable to use table as a dynamic
>> columns data region.
>> I will try to contact internal to check whether there is an suitable
>> workaround for you. I appreciate your patience.
>> Sincerely,
>> Wei Lu
>> Microsoft Online Community Support
>> ==================================================>> When responding to posts, please "Reply to Group" via your newsreader so
>> that others may learn and benefit from your issue.
>> ==================================================>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>|||Hi Pete,
Thank you for your followup. Since Wei is absent due to some urgent issues,
I'm continueing to help you on this issue. I've got the problem description
and sample report files from wei and will perform some research on them. I
will update you as soon as possible.
Thank you for the patience and understanding.
Sincerely,
Steven Cheng
Microsoft MSDN Online Support Lead|||Hello Pete,
Sorry for keep you waiting. After some further research, we've come out to
the same results as Wei has told you in the previous message, the built-in
reporting service report items(Table or Matrix) are not able to accomplish
the task. I've also involved some other SQL engineers to discuss the issue
and they've help confirmed on this. So far they pointed out that it may be
possible to use a custom data processing extension to precaculate the
additional results, and the key point is that you need to correctly pass
the parameters into the custom data processing engine. I'm sorry that we
haven't been able to help you further on this. If there is anything else we
can help, please feel free to let me know.
Thanks for your understanding!
Sincerely,
Steven Cheng
Microsoft MSDN Online Support Lead
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi
I was just wondering if there was any progress regarding this subject.
I am sitting with a similar problem and am unable to find a workaround.
Thanks for all the effort that has been put into solving the problem thus far.
"Steven Cheng[MSFT]" wrote:
> Hello Pete,
> Sorry for keep you waiting. After some further research, we've come out to
> the same results as Wei has told you in the previous message, the built-in
> reporting service report items(Table or Matrix) are not able to accomplish
> the task. I've also involved some other SQL engineers to discuss the issue
> and they've help confirmed on this. So far they pointed out that it may be
> possible to use a custom data processing extension to precaculate the
> additional results, and the key point is that you need to correctly pass
> the parameters into the custom data processing engine. I'm sorry that we
> haven't been able to help you further on this. If there is anything else we
> can help, please feel free to let me know.
> Thanks for your understanding!
> Sincerely,
> Steven Cheng
> Microsoft MSDN Online Support Lead
> ==================================================>
> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||I know you can put a percent of total dim calculation on a dynamic matrix
report by using the year matrix group as a component of the calculation, i.e,
"matrix1_Year". See Brian Larson's Delivering Business Intelligence book
page 596 - 597. I wish there were an example of doing a variance column, but
if it can do this calculated field, then it looks like using the same
approach could do others. This example is based on a matrix report sourced
from an Analysis Services 2005 cube.
"Glaciered Pyro" wrote:
> Hi
> I was just wondering if there was any progress regarding this subject.
> I am sitting with a similar problem and am unable to find a workaround.
> Thanks for all the effort that has been put into solving the problem thus far.
> "Steven Cheng[MSFT]" wrote:
> > Hello Pete,
> >
> > Sorry for keep you waiting. After some further research, we've come out to
> > the same results as Wei has told you in the previous message, the built-in
> > reporting service report items(Table or Matrix) are not able to accomplish
> > the task. I've also involved some other SQL engineers to discuss the issue
> > and they've help confirmed on this. So far they pointed out that it may be
> > possible to use a custom data processing extension to precaculate the
> > additional results, and the key point is that you need to correctly pass
> > the parameters into the custom data processing engine. I'm sorry that we
> > haven't been able to help you further on this. If there is anything else we
> > can help, please feel free to let me know.
> >
> > Thanks for your understanding!
> >
> > Sincerely,
> >
> > Steven Cheng
> >
> > Microsoft MSDN Online Support Lead
> >
> > ==================================================> >
> >
> > This posting is provided "AS IS" with no warranties, and confers no rights.
> >
> >|||I Have found that the easiest solution to this problem is
to write custom code using vb.net or c#. Though it might
not be the most efficient... it works. Just add a Table next to
the matrix, write the code to collect the data and calculate
whatever you need to, then simply call it from the table...

Sunday, February 19, 2012

Calculation Error

I am designing a report and am getting an error when I run this calculation
in the row. Here is my expression;
"=iif(Fields!Hours.Value > 0 and Fields!SUM.Value > 0,
Fields!SUM.Value/Fields!Hours.Value,0)"
Basically, I am doing a divide calculation of two columns, but sometimes
there is a zero in the columns which causes an error. In my expression
above, I am saying if column A and column B are >0, then divide, if not, then
0.
Any ideas on what I am doing wrong? I have about 100 reports that I am
cranking out, so learning a lot.
Thanks in advance,
RyanOn Oct 19, 10:11 am, Ryan Mcbee <RyanMc...@.discussions.microsoft.com>
wrote:
> I am designing a report and am getting an error when I run this calculation
> in the row. Here is my expression;
> "=iif(Fields!Hours.Value > 0 and Fields!SUM.Value > 0,
> Fields!SUM.Value/Fields!Hours.Value,0)"
> Basically, I am doing a divide calculation of two columns, but sometimes
> there is a zero in the columns which causes an error. In my expression
> above, I am saying if column A and column B are >0, then divide, if not, then
> 0.
> Any ideas on what I am doing wrong? I have about 100 reports that I am
> cranking out, so learning a lot.
> Thanks in advance,
> Ryan
If I remember correctly, > 0 does not exclude null; so, you might want
to change your expression to something like:
=iif(Fields!Hours.Value > 0 and Fields!Hours.Value <> Nothing and
Fields!SUM.Value > 0 and Fields!SUM.Value <> Nothing , Fields!
SUM.Value/Fields!Hours.Value, 0)
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||Check the article here for expression examples using the IIF statement:
http://msdn2.microsoft.com/en-us/library/ms157328.aspx
All of the examples I've seen have involved using one expression in each
IIF so if you are doing Fields!Hours.Value > 0 and Fields!SUM.Value > 0
then you would need two nested IIFs. Also, Enrique is correct. You should
check for NULL before doing any comparisions to make sure you're getting
valid data.
Here is an example expression you can try:
=iif(Fields!Hours.Value > 0, IIF(Fields!SUM.Value > 0,
Fields!SUM.Value/Fields!Hours.Value,0),0)
If you do think you'll have NULLs in your data use:
=iif(IsNothing(Fields!Hours.Value), 0,
iif(IsNothing(Fields!SUM.Value),0,iif(Fields!Hours.Value > 0,
IIF(Fields!SUM.Value > 0, Fields!SUM.Value/Fields!Hours.Value,0),0))
Didn't test the above so don't hammer me for any mistakes I may have put in
there :)
--
Chris Alton, Microsoft Corp.
SQL Server Developer Support Engineer
This posting is provided "AS IS" with no warranties, and confers no rights.
--
> Thread-Topic: Calculation Error
> thread-index: AcgSYkRUoT7qoNO7S9WCFbXNnevQBA==> X-WBNR-Posting-Host: 207.46.19.168
> From: =?Utf-8?B?UnlhbiBNY2JlZQ==?= <RyanMcbee@.discussions.microsoft.com>
> Subject: Calculation Error
> Date: Fri, 19 Oct 2007 08:11:04 -0700
> Lines: 15
> Message-ID: <562C6023-A07E-4FA8-83B4-2B486EDDE038@.microsoft.com>
> MIME-Version: 1.0
> I am designing a report and am getting an error when I run this
calculation
> in the row. Here is my expression;
> "=iif(Fields!Hours.Value > 0 and Fields!SUM.Value > 0,
> Fields!SUM.Value/Fields!Hours.Value,0)"
> Basically, I am doing a divide calculation of two columns, but sometimes
> there is a zero in the columns which causes an error. In my expression
> above, I am saying if column A and column B are >0, then divide, if not,
then
> 0.
> Any ideas on what I am doing wrong? I have about 100 reports that I am
> cranking out, so learning a lot.
> Thanks in advance,
> Ryan
>|||On Oct 22, 2:08 pm, cal...@.online.microsoft.com (Chris Alton [MSFT])
wrote:
> Check the article here for expression examples using the IIF statement:http://msdn2.microsoft.com/en-us/library/ms157328.aspx
> All of the examples I've seen have involved using one expression in each
> IIF so if you are doing Fields!Hours.Value > 0 and Fields!SUM.Value > 0
> then you would need two nested IIFs. Also, Enrique is correct. You should
> check for NULL before doing any comparisions to make sure you're getting
> valid data.
> Here is an example expression you can try:
> =iif(Fields!Hours.Value > 0, IIF(Fields!SUM.Value > 0,
> Fields!SUM.Value/Fields!Hours.Value,0),0)
> If you do think you'll have NULLs in your data use:
> =iif(IsNothing(Fields!Hours.Value), 0,
> iif(IsNothing(Fields!SUM.Value),0,iif(Fields!Hours.Value > 0,
> IIF(Fields!SUM.Value > 0, Fields!SUM.Value/Fields!Hours.Value,0),0))
> Didn't test the above so don't hammer me for any mistakes I may have put in
> there :)
> --
> Chris Alton, Microsoft Corp.
> SQL Server Developer Support Engineer
> This posting is provided "AS IS" with no warranties, and confers no rights.
> --
>
> > Thread-Topic: Calculation Error
> > thread-index: AcgSYkRUoT7qoNO7S9WCFbXNnevQBA==> > X-WBNR-Posting-Host: 207.46.19.168
> > From: =?Utf-8?B?UnlhbiBNY2JlZQ==?= <RyanMc...@.discussions.microsoft.com>
> > Subject: Calculation Error
> > Date: Fri, 19 Oct 2007 08:11:04 -0700
> > Lines: 15
> > Message-ID: <562C6023-A07E-4FA8-83B4-2B486EDDE...@.microsoft.com>
> > MIME-Version: 1.0
> > I am designing a report and am getting an error when I run this
> calculation
> > in the row. Here is my expression;
> > "=iif(Fields!Hours.Value > 0 and Fields!SUM.Value > 0,
> > Fields!SUM.Value/Fields!Hours.Value,0)"
> > Basically, I am doing a divide calculation of two columns, but sometimes
> > there is a zero in the columns which causes an error. In my expression
> > above, I am saying if column A and column B are >0, then divide, if not,
> then
> > 0.
> > Any ideas on what I am doing wrong? I have about 100 reports that I am
> > cranking out, so learning a lot.
> > Thanks in advance,
> > Ryan- Hide quoted text -
> - Show quoted text -
Another way to work around this is with a custom code function.
In Layout mode, click Report > Report Properties > Code to open the
Custom Code editor.
In the editor, type:
Public Function DivideBy(exp1, exp2)
If exp2 = 0 Then
DivideBy = Nothing
Else DivideBy = exp1/exp2
End If
End Function
Now whereever you need to divide (with the potential for divide by
zero errors) insert this expression:
=code.divideby(exp1,exp2)
For you it would be:
"=code.divideby(Fields!SUM.Value,Fields!Hours.Value)"
If your shop has SQL 2005 enterprise edition, you can create a custom
assembly out of the function.|||On Oct 23, 11:55 am, toolman <t...@.infocision.com> wrote:
> On Oct 22, 2:08 pm, cal...@.online.microsoft.com (Chris Alton [MSFT])
> wrote:
>
>
> > Check the article here for expression examples using the IIF statement:http://msdn2.microsoft.com/en-us/library/ms157328.aspx
> > All of the examples I've seen have involved using one expression in each
> > IIF so if you are doing Fields!Hours.Value > 0 and Fields!SUM.Value > 0
> > then you would need two nested IIFs. Also, Enrique is correct. You should
> > check for NULL before doing any comparisions to make sure you're getting
> > valid data.
> > Here is an example expression you can try:
> > =iif(Fields!Hours.Value > 0, IIF(Fields!SUM.Value > 0,
> > Fields!SUM.Value/Fields!Hours.Value,0),0)
> > If you do think you'll have NULLs in your data use:
> > =iif(IsNothing(Fields!Hours.Value), 0,
> > iif(IsNothing(Fields!SUM.Value),0,iif(Fields!Hours.Value > 0,
> > IIF(Fields!SUM.Value > 0, Fields!SUM.Value/Fields!Hours.Value,0),0))
> > Didn't test the above so don't hammer me for any mistakes I may have put in
> > there :)
> > --
> > Chris Alton, Microsoft Corp.
> > SQL Server Developer Support Engineer
> > This posting is provided "AS IS" with no warranties, and confers no rights.
> > --
> > > Thread-Topic: Calculation Error
> > > thread-index: AcgSYkRUoT7qoNO7S9WCFbXNnevQBA==> > > X-WBNR-Posting-Host: 207.46.19.168
> > > From: =?Utf-8?B?UnlhbiBNY2JlZQ==?= <RyanMc...@.discussions.microsoft.com>
> > > Subject: Calculation Error
> > > Date: Fri, 19 Oct 2007 08:11:04 -0700
> > > Lines: 15
> > > Message-ID: <562C6023-A07E-4FA8-83B4-2B486EDDE...@.microsoft.com>
> > > MIME-Version: 1.0
> > > I am designing a report and am getting an error when I run this
> > calculation
> > > in the row. Here is my expression;
> > > "=iif(Fields!Hours.Value > 0 and Fields!SUM.Value > 0,
> > > Fields!SUM.Value/Fields!Hours.Value,0)"
> > > Basically, I am doing a divide calculation of two columns, but sometimes
> > > there is a zero in the columns which causes an error. In my expression
> > > above, I am saying if column A and column B are >0, then divide, if not,
> > then
> > > 0.
> > > Any ideas on what I am doing wrong? I have about 100 reports that I am
> > > cranking out, so learning a lot.
> > > Thanks in advance,
> > > Ryan- Hide quoted text -
> > - Show quoted text -
> Another way to work around this is with a custom code function.
> In Layout mode, click Report > Report Properties > Code to open the
> Custom Code editor.
> In the editor, type:
> Public Function DivideBy(exp1, exp2)
> If exp2 = 0 Then
> DivideBy = Nothing
> Else DivideBy = exp1/exp2
> End If
> End Function
> Now whereever you need to divide (with the potential for divide by
> zero errors) insert this expression:
> =code.divideby(exp1,exp2)
> For you it would be:
> "=code.divideby(Fields!SUM.Value,Fields!Hours.Value)"
> If your shop has SQL 2005 enterprise edition, you can create a custom
> assembly out of the function.- Hide quoted text -
> - Show quoted text -
Actually,
Public Function DivideBy(exp1, exp2)
If exp2 = 0 Then
DivideBy = 0
Else DivideBy = exp1/exp2
End If
End Function
is better so you get the zero as output.

Thursday, February 16, 2012

Calculating table size

I have a BIG database with 8 tables growing rapidly. I would like to estimate the size of a row in disk space so I can predict how big my bd will grow in time. I know the math to calculate this but I'm look for a tool to automate this task. A tool that takes into accout calculating index size aside from table size.

I would really apreciate any help on this.

regards

? Since you already have the tables, with rows of real data, I think you can do better than an estimation tool. Take the current table size: EXEC sp_spaceused 'yourTable' ... then get the number of rows: SELECT COUNT(*) FROM yourTable ... and divide. This will give you a much more realistic estimate than any tool can. -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <Jorge González@.discussions.microsoft.com> wrote in message news:acc81bad-c554-4d2b-8289-fb1482786568@.discussions.microsoft.com... I have a BIG database with 8 tables growing rapidly. I would like to estimate the size of a row in disk space so I can predict how big my bd will grow in time. I know the math to calculate this but I'm look for a tool to automate this task. A tool that takes into accout calculating index size aside from table size. I would really apreciate any help on this. regards|||

The Disk Usage report in SQL Server Management Studio shows you the current size of your database breaking down the statistics by table, row size, index, partitions, etc.

I'm putting together a blog series on Management Studio reports. In the first post, I put together a 2 minute demo of how to view the reports and export them to Microsoft Excel 2007.

http://blogs.msdn.com/sqlrem/archive/2006/05/16/SSMS_Reports_1.aspx

-Paul

|||? Hi Paul, I'll check out your webcast, but I'd just like to give a bit of feedback on the reports themselves. Although I really love the look and feel and depth of content of these reports, I find that they're too heavy to run on a busy production server -- especially one with a lot of databases and/or a lot of tables. Would it be possible to modify the reports for a future release such that the drill-down data is "lazy loaded" on demand? This would make the reports a lot more useable for enterprise scenarios. -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <Paul Mestemaker - MSFT@.discussions.microsoft..com> wrote in message news:7fb4862e-7192-496e-b484-e1547a88728d@.discussions.microsoft.com... The Disk Usage report in SQL Server Management Studio shows you the current size of your database breaking down the statistics by table, row size, index, partitions, etc. I'm putting together a blog series on Management Studio reports. In the first post, I put together a 2 minute demo of how to view the reports and export them to Microsoft Excel 2007. http://blogs.msdn.com/sqlrem/archive/2006/05/16/SSMS_Reports_1.aspx -Paul|||

I apologize for the delay in response, I have not setup the alerts properly.

We are somewhat limited in how we can load data into the ReportViewer control in Management Studio. There are two ways the control supports an interactive experience.

Drill-in
This is what we do today. We load in all of the data we need to render the report and show/hide information giving a simulated interactive experience.
|||

Hi Jorge,

You can try the following script on SQL 2000 / 2005

CREATE TABLE #SpaceUsed (name sysname,rows bigint,reserved sysname,data sysname,index_size sysname,unused sysname)

DECLARE @.Counter int
DECLARE @.Max int
DECLARE @.Table sysname

SELECT name, IDENTITY(int,1,1) ROWID
INTO #TableCollection
FROM sysobjects
WHERE xtype = 'U'
ORDER BY lower(name)

SET @.Counter = 1
SET @.Max = (SELECT Max(ROWID) FROM #TableCollection)

WHILE (@.Counter <= @.Max)
BEGIN
SET @.Table = (SELECT name FROM #TableCollection WHERE ROWID = @.Counter)
INSERT INTO #SpaceUsed
EXECUTE sp_spaceused @.Table
SET @.Counter = @.Counter + 1
END

SELECT * FROM #SpaceUsed

DROP TABLE #TableCollection
DROP TABLE #SpaceUsed

Regards

Calculating table size

I have a BIG database with 8 tables growing rapidly. I would like to estimate the size of a row in disk space so I can predict how big my bd will grow in time. I know the math to calculate this but I'm look for a tool to automate this task. A tool that takes into accout calculating index size aside from table size.

I would really apreciate any help on this.

regards

? Since you already have the tables, with rows of real data, I think you can do better than an estimation tool. Take the current table size: EXEC sp_spaceused 'yourTable' ... then get the number of rows: SELECT COUNT(*) FROM yourTable ... and divide. This will give you a much more realistic estimate than any tool can. -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <Jorge González@.discussions.microsoft.com> wrote in message news:acc81bad-c554-4d2b-8289-fb1482786568@.discussions.microsoft.com... I have a BIG database with 8 tables growing rapidly. I would like to estimate the size of a row in disk space so I can predict how big my bd will grow in time. I know the math to calculate this but I'm look for a tool to automate this task. A tool that takes into accout calculating index size aside from table size. I would really apreciate any help on this. regards|||

The Disk Usage report in SQL Server Management Studio shows you the current size of your database breaking down the statistics by table, row size, index, partitions, etc.

I'm putting together a blog series on Management Studio reports. In the first post, I put together a 2 minute demo of how to view the reports and export them to Microsoft Excel 2007.

http://blogs.msdn.com/sqlrem/archive/2006/05/16/SSMS_Reports_1.aspx

-Paul

|||? Hi Paul, I'll check out your webcast, but I'd just like to give a bit of feedback on the reports themselves. Although I really love the look and feel and depth of content of these reports, I find that they're too heavy to run on a busy production server -- especially one with a lot of databases and/or a lot of tables. Would it be possible to modify the reports for a future release such that the drill-down data is "lazy loaded" on demand? This would make the reports a lot more useable for enterprise scenarios. -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <Paul Mestemaker - MSFT@.discussions.microsoft..com> wrote in message news:7fb4862e-7192-496e-b484-e1547a88728d@.discussions.microsoft.com... The Disk Usage report in SQL Server Management Studio shows you the current size of your database breaking down the statistics by table, row size, index, partitions, etc. I'm putting together a blog series on Management Studio reports. In the first post, I put together a 2 minute demo of how to view the reports and export them to Microsoft Excel 2007. http://blogs.msdn.com/sqlrem/archive/2006/05/16/SSMS_Reports_1.aspx -Paul|||

I apologize for the delay in response, I have not setup the alerts properly.

We are somewhat limited in how we can load data into the ReportViewer control in Management Studio. There are two ways the control supports an interactive experience.

Drill-in
This is what we do today. We load in all of the data we need to render the report and show/hide information giving a simulated interactive experience.
|||

Hi Jorge,

You can try the following script on SQL 2000 / 2005

CREATE TABLE #SpaceUsed (name sysname,rows bigint,reserved sysname,data sysname,index_size sysname,unused sysname)

DECLARE @.Counter int
DECLARE @.Max int
DECLARE @.Table sysname

SELECT name, IDENTITY(int,1,1) ROWID
INTO #TableCollection
FROM sysobjects
WHERE xtype = 'U'
ORDER BY lower(name)

SET @.Counter = 1
SET @.Max = (SELECT Max(ROWID) FROM #TableCollection)

WHILE (@.Counter <= @.Max)
BEGIN
SET @.Table = (SELECT name FROM #TableCollection WHERE ROWID = @.Counter)
INSERT INTO #SpaceUsed
EXECUTE sp_spaceused @.Table
SET @.Counter = @.Counter + 1
END

SELECT * FROM #SpaceUsed

DROP TABLE #TableCollection
DROP TABLE #SpaceUsed

Regards

Tuesday, February 14, 2012

Calculating Row Size including blobs

I've seen plenty of posts regarding the estimation of table size,
usually in the processing of planning for server storage needs.

Well, I've got a different problem. I need to know how much data each
of our Customers are using in a Database. (1 SQL DB stores multiple
customers).

Basically, I want to be able to say: Customer A: 45.5 MB, Customer B:
655 MB.

So, how can I ask SQL Server how much data each Row in each table is
taking up? I want to be able to calculate nightly the total size, so I
would take each row in each table that belong to the customer, and add
all the sizes together. I want to take into account blobs that are
storing images and PDF files also.

Thanks in advance,

Jesse Wolgamott[posted and mailed, please reply in news]

Jesse Wolgamott (jesse.wolgamott@.gmail.com) writes:
> I've seen plenty of posts regarding the estimation of table size,
> usually in the processing of planning for server storage needs.
> Well, I've got a different problem. I need to know how much data each
> of our Customers are using in a Database. (1 SQL DB stores multiple
> customers).
> Basically, I want to be able to say: Customer A: 45.5 MB, Customer B:
> 655 MB.
> So, how can I ask SQL Server how much data each Row in each table is
> taking up? I want to be able to calculate nightly the total size, so I
> would take each row in each table that belong to the customer, and add
> all the sizes together. I want to take into account blobs that are
> storing images and PDF files also.

This is not a problem with a clearcut answer, since if a table gets
fragmented, that extra space is not really any particular customer,
but presumably someone should be accounted for it.

Therefore I would for the non-blob data take the number of rows for
each customer and scale that with the number of rows for each customer.
For the blob data I would simply sum the sizes of each field:

SELECT t.CustomerID,
MB = ((1E0 * COUNT(*) * i.reserved / i.rows) * 8192 +
SUM(coalesce(datalength(t.blobcol), 0)) / 1E6
FROM tbl t
CROSS JOIN sysindexes i
WHERE i.id = object_id('tbl')
AND i.indid IN (0, 1)
GROUP BY t.CustomerID

You should run DBCC UPDATEUSAGE on the database prior to running this-

The result will be skewed if you have tables with long non-blob
character/binary columns where different customer have very differing
average length on these columns. Also, if you are using the "text in row",
customers with small blobs may be accounted twice for the same thing.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Calculating Row Size

A couple of questions regading table size:
1. Why is the size of a table reported by Enterprise Manager in Table Info
so different than the size that is returned when using sp_spaceused?
2. I've found the calculations but was wondering if there exists a system
stored procedure that would return the size of a row in a table? For example
,
I'm looking to capture the max, min, and avg size of a row in a particular
table.
Any input is greatly appreciated!
Thanks!1. I don't know. Sorry!
2. No, but you could do this to get the information you want
CREATE TABLE ColumnSizes (table_name varchar(50), column_name varchar(50),
min_size int, max_size int)
GO
DECLARE @.table_name varchar(100)
DECLARE @.column_name varchar(100)
DECLARE @.querystring nvarchar(1000)
SET @.querystring = ''
DECLARE columns_cursor CURSOR STATIC FORWARD_ONLY
FOR
SELECT t1.table_name, t2.column_name
FROM INFORMATION_SCHEMA.TABLES t1
INNER JOIN INFORMATION_SCHEMA.COLUMNS t2
ON t1.table_name = t2.table_name
WHERE t1.table_type = 'BASE TABLE' AND t1.table_name <> 'columnsizes'
OPEN columns_cursor
FETCH NEXT FROM columns_cursor INTO @.table_name, @.column_name
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
SET @.querystring = 'INSERT INTO ColumnSizes SELECT ''' + @.table_name +
''', ''' +
@.column_name + ''', MIN(DATALENGTH([' + @.column_name + '])),
MAX(DATALENGTH([' +
@.column_name + '])) FROM [' + @.table_name + ']'
--PRINT @.querystring
EXEC sp_executesql @.querystring
FETCH NEXT FROM columns_cursor INTO @.table_name, @.column_name
END
CLOSE columns_cursor
DEALLOCATE columns_cursor
select * from columnsizes
"A. Robinson" wrote:

> A couple of questions regading table size:
> 1. Why is the size of a table reported by Enterprise Manager in Table Info
> so different than the size that is returned when using sp_spaceused?
> 2. I've found the calculations but was wondering if there exists a system
> stored procedure that would return the size of a row in a table? For examp
le,
> I'm looking to capture the max, min, and avg size of a row in a particular
> table.
> Any input is greatly appreciated!
> Thanks!|||Thanks!!
One quick question:
The database I'm running this against has 4500+ attributes/columns. I
started running the procedure last night and left for the evening. When I go
t
back in, it was still running - 18+ hours...
Is that normal?!?
Thanks!!
"Mark Williams" wrote:
> 1. I don't know. Sorry!
> 2. No, but you could do this to get the information you want
> CREATE TABLE ColumnSizes (table_name varchar(50), column_name varchar(50),
> min_size int, max_size int)
> GO
> DECLARE @.table_name varchar(100)
> DECLARE @.column_name varchar(100)
> DECLARE @.querystring nvarchar(1000)
> SET @.querystring = ''
> DECLARE columns_cursor CURSOR STATIC FORWARD_ONLY
> FOR
> SELECT t1.table_name, t2.column_name
> FROM INFORMATION_SCHEMA.TABLES t1
> INNER JOIN INFORMATION_SCHEMA.COLUMNS t2
> ON t1.table_name = t2.table_name
> WHERE t1.table_type = 'BASE TABLE' AND t1.table_name <> 'columnsizes'
> OPEN columns_cursor
> FETCH NEXT FROM columns_cursor INTO @.table_name, @.column_name
> WHILE (@.@.FETCH_STATUS = 0)
> BEGIN
> SET @.querystring = 'INSERT INTO ColumnSizes SELECT ''' + @.table_name +
> ''', ''' +
> @.column_name + ''', MIN(DATALENGTH([' + @.column_name + '])),
> MAX(DATALENGTH([' +
> @.column_name + '])) FROM [' + @.table_name + ']'
> --PRINT @.querystring
> EXEC sp_executesql @.querystring
> FETCH NEXT FROM columns_cursor INTO @.table_name, @.column_name
> END
> CLOSE columns_cursor
> DEALLOCATE columns_cursor
> select * from columnsizes
>
> --
>
> "A. Robinson" wrote:
>|||On Fri, 27 Jan 2006 09:00:03 -0800, A. Robinson wrote:

>Thanks!!
>One quick question:
>The database I'm running this against has 4500+ attributes/columns. I
>started running the procedure last night and left for the evening. When I g
ot
>back in, it was still running - 18+ hours...
>Is that normal?!?
Hi A.,
I just checked the script Mark posted, and I'm not surprised.
For each column, a query is executed to find MIN(DATALENGTH(column)) and
MAX(DATALENGTH(column)). That requires a full table scan for each
column. So if you have a 100 million row table with 40 columns, the 100
million rows will be scanned 40 times.
Hugo Kornelis, SQL Server MVP|||I didn't consider a database that large. (I live in my own little world with
little databases).
Other than adding WITH (NOLOCK) to the select statement, I can't think of
much else to modify the script as is. In reality, my script really didn't ge
t
what you wanted, which was the minimum and maximum size of a row in a table.
I gave you the minimum and maximum sizes of columns in the tables. Since
physical storage sizes for most of the datatypes are fixed (ie int, datetime
,
smalldatetime, ..) it would probably be better to only run the SELECT
MAX(DATELENGTH()), MIN(DATALENGTH()) statement on columns that had variable
physical size data capabilities (ie varchar,nvarchar,text,ntext, and so on).
Unfortunately, I can't whip that one up right now, but I will work on it and
post it here.|||an obvious optimization would be to issue one select per table, not one
per column. naturally that would require more complex programming.
makes sense?|||an obvious optimization would be to issue one select per table, not one
per column. naturally that would require more complex programming.
makes sense?|||Give this a try. The whole thing takes about 3 seconds for Northwind
It runs through a set of nested cursors for only the variable length
datatypes, then joins the output table with information_schema.columns to ge
t
the totals with the fixed length datatypes.
drop table variablerowsizes
go
CREATE TABLE VariableRowSizes (table_name varchar(50), min_size int,
max_size int, avgsize int)
GO
DECLARE @.table_name nvarchar(100)
DECLARE @.column_name nvarchar(100)
DECLARE @.column_list nvarchar(4000)
DECLARE @.querystring nvarchar(4000)
SET @.querystring = ''
set @.column_list = ''
DECLARE tables_cursor CURSOR STATIC FORWARD_ONLY
FOR
select distinct t1.table_name
from information_schema.tables t1
inner join information_schema.columns t2
on t1.table_name = t2.table_name
where t1.table_type = 'BASE TABLE'
and t2.data_type in
('varchar','nvarchar','text','ntext','va
rbinary','image','sql_variant')
order by t1.table_name
OPEN tables_cursor
FETCH NEXT FROM tables_cursor INTO @.table_name
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
set @.querystring = ''
set @.column_list = ''
declare columns_cursor cursor static forward_only
for
select distinct column_name
from information_schema.columns
where table_name = @.table_name
and data_type in
('varchar','nvarchar','text','ntext','va
rbinary','image','sql_variant')
open columns_cursor
fetch next from columns_cursor into @.column_name
WHILE (@.@.FETCH_STATUS = 0)
begin
set @.column_list = @.column_list + N'DATALENGTH([' + @.column_name + N'])
+ '
fetch next from columns_cursor into @.column_name
end
close columns_cursor
deallocate columns_cursor
set @.column_list = SUBSTRING(@.column_list,1,LEN(@.column_lis
t) - 2)
set @.querystring = N'insert into variablerowsizes select '''
+ @.table_name + N''', min(' + @.column_list + N'), max(' + @.column_list
+ N'), avg(' + @.column_list
+ N') from ' + @.table_name
--print @.querystring
EXEC sp_executesql @.querystring
FETCH NEXT FROM tables_cursor INTO @.table_name
END
CLOSE tables_cursor
DEALLOCATE tables_cursor
select t3.table_name, t3.[fixed column sizes] + isnull(t4.[bit sizes],0) +
isnull(t5.min_size,0) AS "minimum row size",
t3.[fixed column sizes] + isnull(t4.[bit sizes],0) + isnull(t5.max_size,0)
AS "maximum row size",
t3.[fixed column sizes] + isnull(t4.[bit sizes],0) + isnull(t5.avgsize,0) AS
"average row size"
from (
select t1.table_name,
sum(
case
when t2.data_type = 'uniqueidentifier' then 16
when t2.data_type in ('char','nchar') then t2.character_octet_length
when t2.data_type in ('bigint','money','datetime','timestamp'
) then 8
when t2.data_type in ('int','smallmoney','smalldatetime','rea
l') then 4
when t2.data_type = 'smallint' then 2
when t2.data_type = 'tinyint' then 1
when t2.data_type in ('decimal','numeric') then
case
when t2.numeric_precision between 1 and 9 then 5
when t2.numeric_precision between 10 and 19 then 9
when t2.numeric_precision between 20 and 28 then 13
else 17
end
when t2.data_type = 'float' then
case
when t2.numeric_precision between 1 and 24 then 4
else 8
end
when t2.data_type = 'binary' then t2.character_octet_length + 4
else 0
end) as "fixed column sizes"
from information_schema.tables t1 left join information_schema.columns t2
on t1.table_name = t2.table_name
where t1.table_type = 'BASE TABLE'
group by t1.table_name
) t3 left join
(
select t1.table_name, ((count(t2.data_type) - 1) / 8) + 1 as "bit sizes"
from information_schema.tables t1
left join information_schema.columns t2
on t1.table_name = t2.table_name
where t1.table_type = 'BASE TABLE'
and t2.data_type = 'bit'
group by t1.table_name
) t4 on t3.table_name = t4.table_name
left join variablerowsizes t5
on t3.table_name = t5.table_name
"A. Robinson" wrote:

> A couple of questions regading table size:
> 1. Why is the size of a table reported by Enterprise Manager in Table Info
> so different than the size that is returned when using sp_spaceused?
> 2. I've found the calculations but was wondering if there exists a system
> stored procedure that would return the size of a row in a table? For examp
le,
> I'm looking to capture the max, min, and avg size of a row in a particular
> table.
> Any input is greatly appreciated!
> Thanks!

Sunday, February 12, 2012

Calculating a percentage using aggregate task

Hi,

I have a column called QTY and I need to calculate the percentage for each row based on the total sum of the column. They are all decimals. I have an OLE DB source that is reading the table used an aggregate task using the QTY column with operation SUM and called the output SUM OF QTY. Then I have a Derived Column task that takes the QTY column / SUM OF QTY * 100 to create the percentage. My numbers are not coming out correctly and when I put a data viewer from the aggregate task, it shows the same number for both columns:

QTY SUM OF QTY

0.01833 0.01833 (this should be the sum of all the decimals in the QTY col)

What am I doing wrong?

Thanks in advance!

Isabelle

You might have to use a multitask component to get this to work. The aggregate transformation does not pass fields through. So from the multitask output, you can take one path to the aggregate, and another to a sort. Take the output of the aggregate and put it in a sort as well. Then, take the two sorts and hook them up with a merge join on the common keys. Then, you'll have your original data, along with the aggregate column.|||

Thanks for your response. I tried what you suggested and I still could not get it to work. Even when I created a sort column for both and then used a join merge, I still got the same amound in both columns. I ended up doing the conversion using Execute SQL task and saved the output as a variable and then just called the variable in a derived column and that worked.

Isabelle

Calculating a percentage using aggregate task

Hi,

I have a column called QTY and I need to calculate the percentage for each row based on the total sum of the column. They are all decimals. I have an OLE DB source that is reading the table used an aggregate task using the QTY column with operation SUM and called the output SUM OF QTY. Then I have a Derived Column task that takes the QTY column / SUM OF QTY * 100 to create the percentage. My numbers are not coming out correctly and when I put a data viewer from the aggregate task, it shows the same number for both columns:

QTY SUM OF QTY

0.01833 0.01833 (this should be the sum of all the decimals in the QTY col)

What am I doing wrong?

Thanks in advance!

Isabelle

You might have to use a multitask component to get this to work. The aggregate transformation does not pass fields through. So from the multitask output, you can take one path to the aggregate, and another to a sort. Take the output of the aggregate and put it in a sort as well. Then, take the two sorts and hook them up with a merge join on the common keys. Then, you'll have your original data, along with the aggregate column.|||

Thanks for your response. I tried what you suggested and I still could not get it to work. Even when I created a sort column for both and then used a join merge, I still got the same amound in both columns. I ended up doing the conversion using Execute SQL task and saved the output as a variable and then just called the variable in a derived column and that worked.

Isabelle

Friday, February 10, 2012

calculated value based on values in other row

hello,
in a table i have 3 fields
custnr wnr profit
example
1 1 100
1 2 110
1 3 120
1 4 150
i want to calculate how much the profit grows referenced from wnr 1 and
the relative to the precedent w
1 1 0% 0%
1 2 10% 10%
1 3 20% 10%
1 4 50% 30%
thanksXavier wrote:

> i want to calculate how much the profit grows referenced from wnr
> 1 and the relative to the precedent w
> 1 1 0% 0%
> 1 2 10% 10%
> 1 3 20% 10%
> 1 4 50% 30%
This should work:
select profit/ (select top 1 TB2.profit from table TB2 where TB2.wnr
< TB1.wnr order by wnr DESC) from table TB1
HTH,
Stijn Verrept.|||thanks for your help,
in the table there are a lot of custnr.
The corect form of the example table is analog to the custNr 1 there are
also Nr like
custnr wnr profit
example
1 1 100
1 2 110
1 3 120
1 4 150
2 1 100
2 2 120
2 3 140
2 4 160
3 1 100
3 2 120
3 3 130
3 4 140
...
how can i extend to have a corect calculation for all customer
best regards
"Stijn Verrept" wrote:

> Xavier wrote:
>
>
> This should work:
> select profit/ (select top 1 TB2.profit from table TB2 where TB2.wnr
> < TB1.wnr order by wnr DESC) from table TB1
> --
> HTH,
> Stijn Verrept.
>|||Xavier wrote:

> ...
> how can i extend to have a corect calculation for all customer
select profit/ (select top 1 TB2.profit from table TB2 where TB2.wnr
< TB1.wnr and TB2.custnr
= TB1.custnr order by wnr DESC) from table TB1
HTH,
Stijn Verrept.|||yes, it works, thanks
best regards
Xavier