How can i assign customize data to sql report

hi folks,
i want to assign customize data to sql report. i have one record related to a customer. i have its current balance. i have to show in report that after specific months how much its amount will be.
i have to show each years amount that user will get. i am showing report in my aspx page. how can i assign this customize dataset to report

Good Morning or Good Evening!
I could be mising something based upon your post - but -
When you develop a report in REporting Services - you have to define a DataSet
The dataset can "grab" data from Sql and you can define a text string (Select whatever, count(1) from whatevertableyouwant)
The dataset could also execute a stored procedure and if you have this information available within your database a T-SQL stored proc ) can be executed via the dataset definition of reporting services - then -
you should have no problems in defining the report display elements
still not sure - but I think not so hard!
best regards.

How calculate space that occupied?

I want to know following memory space related question.

1 . How can i get a Database Size by means of a SQL query?

2. How can i Get a field size (not allocated space) .. ie. i am
storing some textual data to a field..
after a insertion i want to know how much space has been occupied by
that particular field .

ex sno product_name Description
1 sample1 dfkjsdkfj kldsjfkdjk
sdkjdfskdjk vcmvxcvmcvnksdjfkdsn m

Here i want know the space occupied by the field name "description"
for the product id=1.

Thanks in adavance

Regards
Visu.On Jun 12, 12:35 pm, visu <k.vis...@.gmail.comwrote:

Quote:

Originally Posted by

I want to know following memory space related question.
>
1 . How can i get a Database Size by means of a SQL query?
>
2. How can i Get a field size (not allocated space) .. ie. i am
storing some textual data to a field..
after a insertion i want to know how much space has been occupied by
that particular field .
>
ex sno product_name Description
1 sample1 dfkjsdkfj kldsjfkdjk
sdkjdfskdjk vcmvxcvmcvnksdjfkdsn m
>
Here i want know the space occupied by the field name "description"
for the product id=1.
>
Thanks in adavance
>
Regards
Visu.

1. How can i get a Database Size by means of a SQL query

sp_helpdb 'dbname'

2. Here i want know the space occupied by the field name "description"
for the product id=1.

SELECT SUM(DATALENGTH(description)) FROM table
WHERE product_id=1.|||On Jun 12, 10:47 am, M A Srinivas <masri...@.gmail.comwrote:

Quote:

Originally Posted by

On Jun 12, 12:35 pm, visu <k.vis...@.gmail.comwrote:
>
>
>
>
>

Quote:

Originally Posted by

I want to know following memory space related question.

>

Quote:

Originally Posted by

1 . How can i get a Database Size by means of a SQL query?

>

Quote:

Originally Posted by

2. How can i Get a field size (not allocated space) .. ie. i am
storing some textual data to a field..
after a insertion i want to know how much space has been occupied by
that particular field .

>

Quote:

Originally Posted by

ex sno product_name Description
1 sample1 dfkjsdkfj kldsjfkdjk
sdkjdfskdjk vcmvxcvmcvnksdjfkdsn m

>

Quote:

Originally Posted by

Here i want know the space occupied by the field name "description"
for the product id=1.

>

Quote:

Originally Posted by

Thanks in adavance

>

Quote:

Originally Posted by

Regards
Visu.

>
1. How can i get a Database Size by means of a SQL query
>
sp_helpdb 'dbname'
>
2. Here i want know the space occupied by the field name "description"
for the product id=1.
>
SELECT SUM(DATALENGTH(description)) FROM table
WHERE product_id=1.- Hide quoted text -
>
- Show quoted text -

Use the length function

SELECT LENGTH(description) FROM tablename WHERE id = 1

This will return the length of the description field for the row
identified by id = 1.|||undercups (dwang@.woodace.co.uk) writes:

Quote:

Originally Posted by

Use the length function
>
SELECT LENGTH(description) FROM tablename WHERE id = 1
>
This will return the length of the description field for the row
identified by id = 1.

There is no LENGTH function in SQL Server.

You may be thinking of len(), but since Visu asked for the space
consumption, it does not fit the bill for two reasons:

1) It ignores trailing spaces.
2) It counts characters, not bytes, which makes a difference for nvarchar.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||M A Srinivas (masri999@.gmail.com) writes:

Quote:

Originally Posted by

2. Here i want know the space occupied by the field name "description"
for the product id=1.
>
SELECT SUM(DATALENGTH(description)) FROM table
WHERE product_id=1.

Should be:

SELECT SUM(2 + DATALENGTH(description)) FROM table
WHERE product_id=1.

Add 2 for the length stored for the field.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

How are the aggregate details being calculated on a click through report?

Hi -

I need help figuring out what setting I need to tweak to get the correct calculations for the default aggregate attributes for the related entities of the one I am drilling into. Right now it is calculating the total across all for every row and not slicing by sub-customer.

Example:

I have a customer with a one-to-many relationship to incidents. Both have a count aggregate that is part of the default aggregates for the entity. There are 58 rows in my table. If I run a report with CustomerName and #Incidents, I correctly get different sub totals for each customer, totalling to 58 for the grand total. However, if I run a summary report on customers and drill into the customers using click through, the #Incidents is displayed but it is 58 for all customers - every row.

If I go into the defaultDetailAttributes of the Customer and add the #Incidents to it and run the previous test, then the correct number of incidents are shown for the customer, then the incorrect number of incidents follow (from getting the aggregates from the children).

The query generated is huge and I am sure it has something to do with my OptionalMany relationships between the tables, but I can't understand why...

Can anyone help me out?

Thanks in advance,

Toni Fielder

I had a similar issue from before. I'm assuming you are using multiple datasets in this one table? I really struggled to find another way to dish this stuff out correctly, but the only efficient way I could get what I wanted (the same thing you are looking for) was to create a giant stored procedure combining the tables I was planning on using. Another way I found (the less efficient way) was to create subreports for each subcategory and call the information into the subreports that each subcategory was grouped by. This way will really increase the amount of time your report will be rendering for, but if you cannot do a stored procedure then this may be your only choice. Hope I could help.|||

Thanks for the message - I think I have a bit of a different situation.... I am constrained by using models and report builder - but I think custom click-through reports might be the equivalent of what you had to do.... unfortunately this has to be a generic model that is sold to multiple customers so custom click through reports has to be my last choice....

I now have another question that is related. I used my smallest object to try and figure out how this stuff is working. I changed all it's relationships (incorrectly) to One-One so it had no many relationships (or optional) from which to grab other aggregates. And I am getting all manner of aggregates from different entities in my model that really have nothing to do with my base entity. It has to be getting them from some extended relationship (all entities are interrelated - like they all share the users table under the covers to get Insert/Update information) but I am thoroughly confused at the moment.

Thanks for the help everyone.

How "NOT" to expire the ReportSession and ControlID in the request

This question is related to VSTS and above parameters in the request. Question is:

A script created with VSTS for testers has hard coded values for ReportSession and ControlID in the requests, These IDs are expired after some time and scripts fails to run after few hours. Can some one tell me how to set the session expiration time on these ID's in the Report server so I can increase the value to 60*24*365 (1 year) this would solve my problem with hard coded values in the scripts for above mentioned IDs.

You cannot set this stuff for only some sessions -- it's a global property (<SessionTimeOut> in rsreportserver.config) that will apply to all sessions. Setting this to 1 year would be a very bad idea as we store data for each session in the reportservertempdb database until the session expires: You'd be holding onto data for every single connection ever made to SSRS for a year at a time :)

You can keep the session alive by simply pinging the SSRS web service now and then (maybe return a list of folders or something with the ListChildren method call) using the SessionID. If you do every 10 minutes (the default timeout), the session will stay up.

|||

Thanks for the comment Russell. After making the change to 525600 (1 year) if I set it back to defalt would that clean up the Temp DB? for any sessions that were kept.

Reason I need this value big is because these ID's are hard coded in the Visual Studio Team Tester, since they are dynamic in nature I can create the script (which would have hard coded dynamic IDs) but the script would fail as soon ID's expire. So for now solution is keep it alive as I run though my load test and reset it back to 10 min once I am done with the load test :) what do you think!!!

|||

I honestly don't know - I assume it would, but since I've never tried it myself, who knows :)

|||

It won't. The expiration time is computed when the session is created.

Can you provide an example of the URL which VSTS generates and the steps you went through in VSTS to generate it?

|||

Hi John, Here is the request that VSTS records when you are creating the script. Performance requirements for this particular report are that 450 users should be able to run this report for 4 hours, to fulfil this requirement I created a .webtest that goes to reports URL and once script is recorded one of the request looks like

GET /Reports/Reserved.ReportViewerWebControl.axd?ReportSession=o2k22t45jovswpqfkyswbn45&ControlID=d78e217e-d7f3-4952-ad32-cc9f6bb483f6&Culture=1033&UICulture=9&ReportStack=1&OpType=SessionKeepAlive&TimerMethod=KeepAliveMethodctl160TouchSession0&CacheSeed=Thu%20Feb%2022%2010%3A20%3A18%202007

Report Session and Control ID in this request are dynamic and change every few mins. once I try running this script after few hours these request (with session ID hard coded start failing). So I asked the question on the forum on how to keep the session alive for long time so I can get through my Performance testing cycle (next couple of months).

With respect to load testing with 450 users VSTS will simulate same user 450 times so only one session ID will live for 1 year but you would probably know better what would the Temp DB look like after the script is done running for few hours. One thing I did notice from the rsreport.config file it did not had any SessionTimeout key, it has the following though.

<Add Key="CleanupCycleMinutes" Value="10"/>
<Add Key="SQLCommandTimeoutSeconds" Value="60"/>
<Add Key="MaxActiveReqForOneUser" Value="20"/>
<Add Key="DatabaseQueryTimeout" Value="120"/>
<Add Key="RunningRequestsScavengerCycle" Value="60"/>
<Add Key="RunningRequestsDbCycle" Value="60"/>
<Add Key="RunningRequestsAge" Value="30"/>
<Add Key="MaxScheduleWait" Value="5"/>

Does this mean I have to add SessionTimeout key or first key above "cleanupcycleminutes" is the same as SessionTimeOut key.

Thanks for your input.

|||can some one reply to this please|||

The "SessionTimeout" is a system property. You have to set it via SOAP API or rs.exe

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=525110&SiteId=1

How "NOT" to expire the ReportSession and ControlID in the request

This question is related to VSTS and above parameters in the request. Question is:

A script created with VSTS for testers has hard coded values for ReportSession and ControlID in the requests, These IDs are expired after some time and scripts fails to run after few hours. Can some one tell me how to set the session expiration time on these ID's in the Report server so I can increase the value to 60*24*365 (1 year) this would solve my problem with hard coded values in the scripts for above mentioned IDs.

You cannot set this stuff for only some sessions -- it's a global property (<SessionTimeOut> in rsreportserver.config) that will apply to all sessions. Setting this to 1 year would be a very bad idea as we store data for each session in the reportservertempdb database until the session expires: You'd be holding onto data for every single connection ever made to SSRS for a year at a time :)

You can keep the session alive by simply pinging the SSRS web service now and then (maybe return a list of folders or something with the ListChildren method call) using the SessionID. If you do every 10 minutes (the default timeout), the session will stay up.

|||

Thanks for the comment Russell. After making the change to 525600 (1 year) if I set it back to defalt would that clean up the Temp DB? for any sessions that were kept.

Reason I need this value big is because these ID's are hard coded in the Visual Studio Team Tester, since they are dynamic in nature I can create the script (which would have hard coded dynamic IDs) but the script would fail as soon ID's expire. So for now solution is keep it alive as I run though my load test and reset it back to 10 min once I am done with the load test :) what do you think!!!

|||

I honestly don't know - I assume it would, but since I've never tried it myself, who knows :)

|||

It won't. The expiration time is computed when the session is created.

Can you provide an example of the URL which VSTS generates and the steps you went through in VSTS to generate it?

|||

Hi John, Here is the request that VSTS records when you are creating the script. Performance requirements for this particular report are that 450 users should be able to run this report for 4 hours, to fulfil this requirement I created a .webtest that goes to reports URL and once script is recorded one of the request looks like

GET /Reports/Reserved.ReportViewerWebControl.axd?ReportSession=o2k22t45jovswpqfkyswbn45&ControlID=d78e217e-d7f3-4952-ad32-cc9f6bb483f6&Culture=1033&UICulture=9&ReportStack=1&OpType=SessionKeepAlive&TimerMethod=KeepAliveMethodctl160TouchSession0&CacheSeed=Thu%20Feb%2022%2010%3A20%3A18%202007

Report Session and Control ID in this request are dynamic and change every few mins. once I try running this script after few hours these request (with session ID hard coded start failing). So I asked the question on the forum on how to keep the session alive for long time so I can get through my Performance testing cycle (next couple of months).

With respect to load testing with 450 users VSTS will simulate same user 450 times so only one session ID will live for 1 year but you would probably know better what would the Temp DB look like after the script is done running for few hours. One thing I did notice from the rsreport.config file it did not had any SessionTimeout key, it has the following though.

<Add Key="CleanupCycleMinutes" Value="10"/>
<Add Key="SQLCommandTimeoutSeconds" Value="60"/>
<Add Key="MaxActiveReqForOneUser" Value="20"/>
<Add Key="DatabaseQueryTimeout" Value="120"/>
<Add Key="RunningRequestsScavengerCycle" Value="60"/>
<Add Key="RunningRequestsDbCycle" Value="60"/>
<Add Key="RunningRequestsAge" Value="30"/>
<Add Key="MaxScheduleWait" Value="5"/>

Does this mean I have to add SessionTimeout key or first key above "cleanupcycleminutes" is the same as SessionTimeOut key.

Thanks for your input.

|||can some one reply to this please|||

The "SessionTimeout" is a system property. You have to set it via SOAP API or rs.exe

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=525110&SiteId=1

Hotfixes available SQL Server

Hello,
Is there a MS service available or a MS site that lists all (not just secuirty related) generally available hotfixes for SQL Server?
I am aware of the security notification. And also the information available here.
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=37
Just wondering if I can be alerted when all new hotfixes are available.
TIA
take a look at http://support.microsoft.com/default.aspx?kbid=290211 a link
seems to list all the fixes.
Tim S
"Joe" <anonymous@.discussions.microsoft.com> wrote in message
news:6549916A-680D-4F9F-A848-A4FA25F01CF1@.microsoft.com...
> Hello,
> Is there a MS service available or a MS site that lists all (not just
secuirty related) generally available hotfixes for SQL Server?
> I am aware of the security notification. And also the information
available here.
> http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=37
> Just wondering if I can be alerted when all new hotfixes are available.
> TIA
>

Hotfixes available SQL Server

Hello,
Is there a MS service available or a MS site that lists all (not just secuir
ty related) generally available hotfixes for SQL Server?
I am aware of the security notification. And also the information available
here.
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=37
Just wondering if I can be alerted when all new hotfixes are available.
TIAtake a look at http://support.microsoft.com/default.aspx?kbid=290211 a link
seems to list all the fixes.
Tim S
"Joe" <anonymous@.discussions.microsoft.com> wrote in message
news:6549916A-680D-4F9F-A848-A4FA25F01CF1@.microsoft.com...
> Hello,
> Is there a MS service available or a MS site that lists all (not just
secuirty related) generally available hotfixes for SQL Server?
> I am aware of the security notification. And also the information
available here.
> http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=37
> Just wondering if I can be alerted when all new hotfixes are available.
> TIA
>

Hotfix for Bug # 473675 (SQL Server could not spawn process_loginread thread)

Hi,

I get "SQL Server could not spawn process_loginread thread." errors. This is related to a bug in SQL 2000 and there is a hotfix for SP3a.

I am running SQL2000 with SP4. I checked the fix list of SP4 & this bug is not fixed in this SP4.

Can I apply the hotfix to resolve this problem on my server?

tia.

Hi,

SP4 is a superset of SP3,SP2,SP1

So this hotfix must be included in SP4.

You can't easily relate error messages and hotfixes, it could be another reason you are getting this message. Read carefully what the hotfix fixes, relate it to the situation you are dealing with and judge if the hotfix is relevant enough to your problem, so as to risk applying it.

Some more info (complete error message, hotfix info, when and where you get the error message) would help

|||

I am also facing the same problem..though we are using SP3.

Will upgrading to SP4 help?

Do we need to do something else to get rid of the problem?

|||

The most common cause of this message is a lack of free virtual address space. The most common cause of virtual address starvation on SQL 2000 are misbehaving com components from the use of sp_oacreate, misbehaving extended stored procedures, or poorly written OLEDB providers that are loaded by the use of linked servers.

Start by reviewing your use of these items and remove them from the system as possible.

|||

Jerome,

This is an event that happens on our production server about once a week. It only lasts for 2-5 minutes. We are running SQL 2000 SP4. Is there any way to adjust the memory allocation?

Thanks in advance.

Ron

Hotfix for Bug # 473675 (SQL Server could not spawn process_loginread thread)

Hi,

I get "SQL Server could not spawn process_loginread thread." errors. This is related to a bug in SQL 2000 and there is a hotfix for SP3a.

I am running SQL2000 with SP4. I checked the fix list of SP4 & this bug is not fixed in this SP4.

Can I apply the hotfix to resolve this problem on my server?

tia.

Hi,

SP4 is a superset of SP3,SP2,SP1

So this hotfix must be included in SP4.

You can't easily relate error messages and hotfixes, it could be another reason you are getting this message. Read carefully what the hotfix fixes, relate it to the situation you are dealing with and judge if the hotfix is relevant enough to your problem, so as to risk applying it.

Some more info (complete error message, hotfix info, when and where you get the error message) would help

|||

I am also facing the same problem..though we are using SP3.

Will upgrading to SP4 help?

Do we need to do something else to get rid of the problem?

|||

The most common cause of this message is a lack of free virtual address space. The most common cause of virtual address starvation on SQL 2000 are misbehaving com components from the use of sp_oacreate, misbehaving extended stored procedures, or poorly written OLEDB providers that are loaded by the use of linked servers.

Start by reviewing your use of these items and remove them from the system as possible.

|||

Jerome,

This is an event that happens on our production server about once a week. It only lasts for 2-5 minutes. We are running SQL 2000 SP4. Is there any way to adjust the memory allocation?

Thanks in advance.

Ron

Hotfix for Bug # 473675 (SQL Server could not spawn process_loginread thread)

Hi,

I get "SQL Server could not spawn process_loginread thread." errors. This is related to a bug in SQL 2000 and there is a hotfix for SP3a.

I am running SQL2000 with SP4. I checked the fix list of SP4 & this bug is not fixed in this SP4.

Can I apply the hotfix to resolve this problem on my server?

tia.

Hi,

SP4 is a superset of SP3,SP2,SP1

So this hotfix must be included in SP4.

You can't easily relate error messages and hotfixes, it could be another reason you are getting this message. Read carefully what the hotfix fixes, relate it to the situation you are dealing with and judge if the hotfix is relevant enough to your problem, so as to risk applying it.

Some more info (complete error message, hotfix info, when and where you get the error message) would help

|||

I am also facing the same problem..though we are using SP3.

Will upgrading to SP4 help?

Do we need to do something else to get rid of the problem?

|||

The most common cause of this message is a lack of free virtual address space. The most common cause of virtual address starvation on SQL 2000 are misbehaving com components from the use of sp_oacreate, misbehaving extended stored procedures, or poorly written OLEDB providers that are loaded by the use of linked servers.

Start by reviewing your use of these items and remove them from the system as possible.

|||

Jerome,

This is an event that happens on our production server about once a week. It only lasts for 2-5 minutes. We are running SQL 2000 SP4. Is there any way to adjust the memory allocation?

Thanks in advance.

Ron