Launching the Reports From Browser

Hi friends,

We have developed the Reports using SQL Server Reporting Services 2005.

In order to make our all reports dynamic we are referancing DLL in all our reports.

That DLL we have pasted in

D:\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportServer\bin

C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PublicAssemblies

So when we observer the reports from Preview tab then we can see the effect of our DLL and all reports are working fine.

Now we want to launch all reports on browser. So we are giving the URL path as

http://localhost/ReportServer

But as per the SQL Server 2005 Reporting Services by Brian Larson we have pasted the following code into rssrvpolicy.config file

<CodeGroup

class ="UnionCodeGroup"

version="1"

PermissionSetName="Execution"

Name="WeatherWebServiceCodeGroup"

Description ="Code group for the Weather Web Service">

<IMembershipCondition Class ="UrlMembershipCodndion"

version ="1"

Url="http://localhost/ReportServer"/>

</CodeGroup>

and second code is

<CodeGroup

class="UnionCodeGroup"

version ="1"

PermissionSetName="FullTrust"

Name="MSSQLRSCodeGroup"

Description="Code group for the MS SQL RS Book Custom Assemblies">

<IMembershipCondition

class="StrongNameMembershipCondition"

version="1"

PublicKeyBlob="0024000004800000940000000602000000240000525341310004000001000100B9F74F2D5B0AAD33AA619B00D7BB8B0F7678393A0F4CD586C9036D72455F8D1E85BF635C9FB1DA9817DD0F751DCEE77D9A47959E8728028B9B6CC7C25EB1E59CB3DE01BB516D46FC6AC6AF27AA6E71B65F6AB91B9576886F2EF39417F17B567AD200E151FC744C6DA72FF5882461E6CA786EB2997FA968302B7B2F24BDBFF7A5"/>

</CodeGroup>

so now we are checking the site

http://localhost/ReportServer

so we are getting following error

Configuration Error

Description: An error occurred during the processing of a configuration file required to service this request. Please review the specific error details below and modify your configuration file appropriately.

Parser Error Message: Could not load file or assembly 'ReportingServicesWebServer, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The parameter is incorrect. (Exception from HRESULT: 0x80070057 (E_INVALIDARG))

Source Error:

Line 27: <assemblies> Line 28: <clear /> Line 29: <add assembly="ReportingServicesWebServer" /> Line 30: </assemblies> Line 31: </compilation>

Source File: D:\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportServer\web.config Line: 29

---

Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.42

so where i am going wrong ?

can anyone help me out plz ?

anyhelp will be greatly appreciated

sandy

You have some typos in the first CodeGroup element:

<IMembershipCondition class ="UrlMembershipCondition"

You don't need this element anyway unless your DLL accesses a web service. In the second CodeGroup, it looks like PublicKeyBlob is copied from the book. Make sure that it matches the public key of your own assembly.

-Albert

|||

hi Albert,

thx for ur help. First that I did is I removed the following code

<CodeGroup

class ="UnionCodeGroup"

version="1"

PermissionSetName="Execution"

Name="WeatherWebServiceCodeGroup"

Description ="Code group for the Weather Web Service">

<IMembershipCondition Class ="UrlMembershipCodndion"

version ="1"

Url="http://localhost/ReportServer"/>

</CodeGroup>

so now I am not getting the configuration error.

Secondly whatever you said that I simply copied the Public Key Blob so now I have replaced it with my current DLL Public Key.

But Albert still I cant see the effect of my function on URL ie browser.

I can see the same effect on my Preview Tab but cant on Browser so where I am going wrong ?

--sandy

|||The easiest way for testing is to add the following:

<CodeGroup class="UnionCodeGroup"
version="1"
PermissionSetName="FullTrust"
Name="MyCodeGroup2"
Description="Code group for my data processing extension">
<IMembershipCondition class="UrlMembershipCondition"
version="1"
Url="C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportServer\bin\[your assembly].dll"
/>
</CodeGroup>

just below the

<CodeGroup
class="UnionCodeGroup"
version="1"
PermissionSetName="FullTrust">
<IMembershipCondition
class="UrlMembershipCondition"
version="1"
Url="$CodeGen$/*"
/>
</CodeGroup>

entry.

If that works you can try to reduce the Permissions and try the Public Key-Stuff..
Have you looked at the logfiles? Sometimes you need to assert the permissions for other dlls that you have referenced..
|||

Hi binni,

The code which ur talking about , I have already incorported in my rsspolicy.config file.

Secondly I made sure that my current assembly is not using any other assembly also.

Then I checked all log file which are in

C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\LogFiles

there also I didnt find any log file saying this problem.

Still I cant able to see the effect of my function on Browser.

-sandyee

|||

When you say you don't see the effect of your function, does that mean you see an error message? How do you know it is not working?

-Albert

|||

Hi Friends,

what we have done is that first of all we have stored all the values in one table like background color , forground colro , font size , font weight etc. Now in our function we are refering that table. And whatever values we have stored in the table that values function takes and set for the report. So when we are saying font size of 12 and when we apply that function to the Report Header , Report Header will be of font size 12 , and this effect we can see in Preview tab. But when see the report on browser Report Header is of font 8 only.

This I exactly mean by function not working,

Sandeep

|||

If the report is running and is not producing any errors, then either your code is not being called, or it's being called and not producing the correct result. Have you checked your code to make sure there are no problems? As a last resort you could try debugging the ASP.Net process.

-Albert

|||

Hi friends,

As my reports are working fine on Browser do I need to make any changes in any other configuartion file other that ReportServer config file (rssvpolicy.config)

for eg do I need to make any changes in Report Manager Config file (rsmgrpolicy.config) ?

-sandeep

|||

No, you do not need to make any changes in Report Manager config.

-Albert

|||To provide a solution for other people:
Maybe sandeep has forgotten the:
<Assembly: System.Security.AllowPartiallyTrustedCallers()> VB.NET
[Assembly: System.Security.AllowPartiallyTrustedCallers] C#.NET
for his assembly.

I got the same problem: my assembly just didn't work and the ReportServer-logfiles didn't contain any information of the error! Visual Studio 2005 showed the error, that my assembly doesn't allow partially trusted caller.
sql

Launching Reports on Browser with Custom Assembly

Hi friends,

We have developed the Reports using SQL Server Reporting Services 2005.

In order to make our all reports dynamic we are refering DLL in all our reports.

That DLL we have pasted in

C:\Program Files \Microsoft SQL Server\MSSQL.3\Reporting Services\ReportServer\bin

C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PublicAssemblies

So when we observer the reports from Preview tab then we can see the effect of our DLL and all reports are working fine.

Now we want to launch all reports on browser so we have pasted the following code into rssrvpolicy.config file

<CodeGroup class="UnionCodeGroup"

version="1"

PermissionSetName="FullTrust"

Attributes="LevelFinal"

Name="Wizard_1"

Description="Codegroup generated by the .NET Configuration tool">

<IMembershipCondition class="StrongNameMembershipCondition"

version="1"

PublicKeyBlob="0024000004800000940000000602000000240000525341310004000001000100DD11C5519C419099881AE462DC4D257CF2A1C126A3D06FEDFA1BF89A84993ADCC495032E92A1CB28A6400FE2D5667358155D6D35637DAB62CE95995B1FC66D23E2C0664AE8E2021BE66080DA0E5972C18B58E658470B82FC14D6E575EC8903367E16C16B168A90A6D8B20A9D9F91ED7CF95A45FA52435F76058D4F32807F9CF8"

AssemblyVersion="1.0.0.0"/>

and sencond code is

<CodeGroup class="UnionCodeGroup"

version="1"

PermissionSetName="FullTrust"

Name="MyNewCodeGroup"

Description="A special code group for my custom assembly.">

<IMembershipCondition

class="UrlMembershipCondition"

version="1"

Url="C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportServer\bin\ReportStyle.dll"/>

</CodeGroup>

so now we are checking the site

http://localhost/ReportServer

we can't see the effect of our DLL. So where excatly I am making mistake ?

This is now becoming very critical issue for our project so any help will be grately appreciated.

-- sandeep -

Hi,
I've a similar scenario right now. The problem seems to be the signing of the assembly!!! I don't know if this is one of MS great "it's not a bug, it's a feature"-Stuff, but try:
-DO NOT sign the assembly!
-delete the code-group with strongNameMembership (the first one) and only use the UrlMembership..
-have fun

@.MS: ReportServer does not throw any error, it just displays "#Error" if you use a method of a signed assembly in a textfield. If you run the report in local debug you get:
"Warning: Der Value-Ausdruck für das Textfeld-Objekt 'textbox1' enth?lt einen Fehler: Die Assembly l?sst keine Aufrufer zu, die nicht voll vertrauenswürdig sind. (rsRuntimeErrorInExpression)".
In english something like:
"Warning: The Value-Expression for textfield 'textbox1' contains an error: the assembly doesn't accept callers without fulltrust"

Benni

|||

Got it working?

edit:

After searching the internet I found what the error message means and that this is really rather a feature than a bug. So just add:
<Assembly: System.Security.AllowPartiallyTrustedCallers()> (VB)
[Assembly: System.Security.AllowPartiallyTrustedCallers] (C#)

to your signed Assembly..

But I think it's still a bug that the ReportServer doesn't write the cause of the error to the logfiles..

Large Views need index

I have couple of large views to data for reporting. That will be wonderful i
f
there is a way I can put indexs on a view. I could'n find a way to do so.
Any ideas?
Thanks a lot.[url]http://www.novicksoftware.com/Articles/Indexed-Views-Basics-in-SQL-Server.htm[/url
]
(But remember that Index Views are only available in SQL2kEE)
HTH, Jens Suessmeyer.
"Catelin Wang" <CatelinWang@.discussions.microsoft.com> schrieb im
Newsbeitrag news:6C62F500-8C4C-4BF8-A8D4-A554D71A60C9@.microsoft.com...
>I have couple of large views to data for reporting. That will be wonderful
>if
> there is a way I can put indexs on a view. I could'n find a way to do so.
> Any ideas?
> Thanks a lot.
>|||Very good, I was thinking about the indexed view, but I was sure the
overheads it brings. We have a one-day old history database for reporting
only, may be indexed view is the best way . Thanks a lot.
"Jens Sü?meyer" wrote:

> [url]http://www.novicksoftware.com/Articles/Indexed-Views-Basics-in-SQL-Server.htm[/u
rl]
> (But remember that Index Views are only available in SQL2kEE)
> HTH, Jens Suessmeyer.
>
> "Catelin Wang" <CatelinWang@.discussions.microsoft.com> schrieb im
> Newsbeitrag news:6C62F500-8C4C-4BF8-A8D4-A554D71A60C9@.microsoft.com...
>
>|||Jens,
FYI, We can create Indexed views on all editions of SQL Server 2000.
Info from books online
Note Indexed views can be created in any edition of SQL Server 2000. In SQL
Server 2000 Enterprise Edition, the query optimizer will automatically
consider the indexed view. To use an indexed view in all other editions, the
NOEXPAND hint must be used.
Thanks
Hari
SQL Server MVP
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:uMj89WnfFHA.1416@.TK2MSFTNGP09.phx.gbl...
> [url]http://www.novicksoftware.com/Articles/Indexed-Views-Basics-in-SQL-Server.htm[/u
rl]
> (But remember that Index Views are only available in SQL2kEE)
> HTH, Jens Suessmeyer.
>
> "Catelin Wang" <CatelinWang@.discussions.microsoft.com> schrieb im
> Newsbeitrag news:6C62F500-8C4C-4BF8-A8D4-A554D71A60C9@.microsoft.com...
>|||>I have couple of large views to data for reporting. That will be wonderful
>if
> there is a way I can put indexs on a view.
In most cases, if you correctly index the base tables, you shouldn't need an
index on the view...|||I used union to combine 2 tables in a view, may not be indexed on the view..
.
"Catelin Wang" wrote:

> I have couple of large views to data for reporting. That will be wonderful
if
> there is a way I can put indexs on a view. I could'n find a way to do so.
> Any ideas?
> Thanks a lot.
>|||Thanks, I knew. Since I am not a fan of using Optimizer Hints, this is not
an option for me and I wouldnt suggest that to anybody.
Jens.
"Hari Prasad" <hari_prasad_k@.hotmail.com> schrieb im Newsbeitrag
news:uFnBFpnfFHA.1412@.TK2MSFTNGP09.phx.gbl...
> Jens,
> FYI, We can create Indexed views on all editions of SQL Server 2000.
> Info from books online
> Note Indexed views can be created in any edition of SQL Server 2000. In
> SQL Server 2000 Enterprise Edition, the query optimizer will automatically
> consider the indexed view. To use an indexed view in all other editions,
> the NOEXPAND hint must be used.
> Thanks
> Hari
> SQL Server MVP
> "Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
> in message news:uMj89WnfFHA.1416@.TK2MSFTNGP09.phx.gbl...
>|||"Catelin Wang" <CatelinWang@.discussions.microsoft.com> wrote in message
news:09C9D493-57C5-464C-A198-1D799FFE0B3E@.microsoft.com...[vbcol=seagreen]
>I used union to combine 2 tables in a view, may not be indexed on the
>view...
> "Catelin Wang" wrote:
>
Perhaps you can create two indexed views and union those. But perhaps there
is a more direct way to improve your performance.
If you post the table DDL, the view text and a description of how many rows
are in each base table, and how many different values each of the important
columns has, and how many rows you will get back from the query, someone may
suggest an optimization either to your base table indexes or your SQL
design.
David

Large Views need index

I have couple of large views to data for reporting. That will be wonderful if
there is a way I can put indexs on a view. I could'n find a way to do so.
Any ideas?
Thanks a lot.
http://www.novicksoftware.com/Articl...SQL-Server.htm
(But remember that Index Views are only available in SQL2kEE)
HTH, Jens Suessmeyer.
"Catelin Wang" <CatelinWang@.discussions.microsoft.com> schrieb im
Newsbeitrag news:6C62F500-8C4C-4BF8-A8D4-A554D71A60C9@.microsoft.com...
>I have couple of large views to data for reporting. That will be wonderful
>if
> there is a way I can put indexs on a view. I could'n find a way to do so.
> Any ideas?
> Thanks a lot.
>
|||Very good, I was thinking about the indexed view, but I was sure the
overheads it brings. We have a one-day old history database for reporting
only, may be indexed view is the best way . Thanks a lot.
"Jens Sü?meyer" wrote:

> http://www.novicksoftware.com/Articl...SQL-Server.htm
> (But remember that Index Views are only available in SQL2kEE)
> HTH, Jens Suessmeyer.
>
> "Catelin Wang" <CatelinWang@.discussions.microsoft.com> schrieb im
> Newsbeitrag news:6C62F500-8C4C-4BF8-A8D4-A554D71A60C9@.microsoft.com...
>
>
|||Jens,
FYI, We can create Indexed views on all editions of SQL Server 2000.
Info from books online
Note Indexed views can be created in any edition of SQL Server 2000. In SQL
Server 2000 Enterprise Edition, the query optimizer will automatically
consider the indexed view. To use an indexed view in all other editions, the
NOEXPAND hint must be used.
Thanks
Hari
SQL Server MVP
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:uMj89WnfFHA.1416@.TK2MSFTNGP09.phx.gbl...
> http://www.novicksoftware.com/Articl...SQL-Server.htm
> (But remember that Index Views are only available in SQL2kEE)
> HTH, Jens Suessmeyer.
>
> "Catelin Wang" <CatelinWang@.discussions.microsoft.com> schrieb im
> Newsbeitrag news:6C62F500-8C4C-4BF8-A8D4-A554D71A60C9@.microsoft.com...
>
|||>I have couple of large views to data for reporting. That will be wonderful
>if
> there is a way I can put indexs on a view.
In most cases, if you correctly index the base tables, you shouldn't need an
index on the view...
|||I used union to combine 2 tables in a view, may not be indexed on the view...
"Catelin Wang" wrote:

> I have couple of large views to data for reporting. That will be wonderful if
> there is a way I can put indexs on a view. I could'n find a way to do so.
> Any ideas?
> Thanks a lot.
>
|||Thanks, I knew. Since I am not a fan of using Optimizer Hints, this is not
an option for me and I wouldnt suggest that to anybody.
Jens.
"Hari Prasad" <hari_prasad_k@.hotmail.com> schrieb im Newsbeitrag
news:uFnBFpnfFHA.1412@.TK2MSFTNGP09.phx.gbl...
> Jens,
> FYI, We can create Indexed views on all editions of SQL Server 2000.
> Info from books online
> Note Indexed views can be created in any edition of SQL Server 2000. In
> SQL Server 2000 Enterprise Edition, the query optimizer will automatically
> consider the indexed view. To use an indexed view in all other editions,
> the NOEXPAND hint must be used.
> Thanks
> Hari
> SQL Server MVP
> "Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
> in message news:uMj89WnfFHA.1416@.TK2MSFTNGP09.phx.gbl...
>
|||"Catelin Wang" <CatelinWang@.discussions.microsoft.com> wrote in message
news:09C9D493-57C5-464C-A198-1D799FFE0B3E@.microsoft.com...[vbcol=seagreen]
>I used union to combine 2 tables in a view, may not be indexed on the
>view...
> "Catelin Wang" wrote:
Perhaps you can create two indexed views and union those. But perhaps there
is a more direct way to improve your performance.
If you post the table DDL, the view text and a description of how many rows
are in each base table, and how many different values each of the important
columns has, and how many rows you will get back from the query, someone may
suggest an optimization either to your base table indexes or your SQL
design.
David

Large Views need index

I have couple of large views to data for reporting. That will be wonderful if
there is a way I can put indexs on a view. I could'n find a way to do so.
Any ideas?
Thanks a lot.http://www.novicksoftware.com/Articles/Indexed-Views-Basics-in-SQL-Server.htm
(But remember that Index Views are only available in SQL2kEE)
HTH, Jens Suessmeyer.
"Catelin Wang" <CatelinWang@.discussions.microsoft.com> schrieb im
Newsbeitrag news:6C62F500-8C4C-4BF8-A8D4-A554D71A60C9@.microsoft.com...
>I have couple of large views to data for reporting. That will be wonderful
>if
> there is a way I can put indexs on a view. I could'n find a way to do so.
> Any ideas?
> Thanks a lot.
>|||Very good, I was thinking about the indexed view, but I was sure the
overheads it brings. We have a one-day old history database for reporting
only, may be indexed view is the best way . Thanks a lot.
"Jens Sü�meyer" wrote:
> http://www.novicksoftware.com/Articles/Indexed-Views-Basics-in-SQL-Server.htm
> (But remember that Index Views are only available in SQL2kEE)
> HTH, Jens Suessmeyer.
>
> "Catelin Wang" <CatelinWang@.discussions.microsoft.com> schrieb im
> Newsbeitrag news:6C62F500-8C4C-4BF8-A8D4-A554D71A60C9@.microsoft.com...
> >I have couple of large views to data for reporting. That will be wonderful
> >if
> > there is a way I can put indexs on a view. I could'n find a way to do so.
> > Any ideas?
> >
> > Thanks a lot.
> >
> >
>
>|||Jens,
FYI, We can create Indexed views on all editions of SQL Server 2000.
Info from books online:)
Note Indexed views can be created in any edition of SQL Server 2000. In SQL
Server 2000 Enterprise Edition, the query optimizer will automatically
consider the indexed view. To use an indexed view in all other editions, the
NOEXPAND hint must be used.
Thanks
Hari
SQL Server MVP
"Jens Süßmeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:uMj89WnfFHA.1416@.TK2MSFTNGP09.phx.gbl...
> http://www.novicksoftware.com/Articles/Indexed-Views-Basics-in-SQL-Server.htm
> (But remember that Index Views are only available in SQL2kEE)
> HTH, Jens Suessmeyer.
>
> "Catelin Wang" <CatelinWang@.discussions.microsoft.com> schrieb im
> Newsbeitrag news:6C62F500-8C4C-4BF8-A8D4-A554D71A60C9@.microsoft.com...
>>I have couple of large views to data for reporting. That will be wonderful
>>if
>> there is a way I can put indexs on a view. I could'n find a way to do
>> so.
>> Any ideas?
>> Thanks a lot.
>>
>|||>I have couple of large views to data for reporting. That will be wonderful
>if
> there is a way I can put indexs on a view.
In most cases, if you correctly index the base tables, you shouldn't need an
index on the view...|||I used union to combine 2 tables in a view, may not be indexed on the view...
"Catelin Wang" wrote:
> I have couple of large views to data for reporting. That will be wonderful if
> there is a way I can put indexs on a view. I could'n find a way to do so.
> Any ideas?
> Thanks a lot.
>|||Thanks, I knew. Since I am not a fan of using Optimizer Hints, this is not
an option for me and I wouldn´t suggest that to anybody.
Jens.
"Hari Prasad" <hari_prasad_k@.hotmail.com> schrieb im Newsbeitrag
news:uFnBFpnfFHA.1412@.TK2MSFTNGP09.phx.gbl...
> Jens,
> FYI, We can create Indexed views on all editions of SQL Server 2000.
> Info from books online:)
> Note Indexed views can be created in any edition of SQL Server 2000. In
> SQL Server 2000 Enterprise Edition, the query optimizer will automatically
> consider the indexed view. To use an indexed view in all other editions,
> the NOEXPAND hint must be used.
> Thanks
> Hari
> SQL Server MVP
> "Jens Süßmeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
> in message news:uMj89WnfFHA.1416@.TK2MSFTNGP09.phx.gbl...
>> http://www.novicksoftware.com/Articles/Indexed-Views-Basics-in-SQL-Server.htm
>> (But remember that Index Views are only available in SQL2kEE)
>> HTH, Jens Suessmeyer.
>>
>> "Catelin Wang" <CatelinWang@.discussions.microsoft.com> schrieb im
>> Newsbeitrag news:6C62F500-8C4C-4BF8-A8D4-A554D71A60C9@.microsoft.com...
>>I have couple of large views to data for reporting. That will be
>>wonderful if
>> there is a way I can put indexs on a view. I could'n find a way to do
>> so.
>> Any ideas?
>> Thanks a lot.
>>
>>
>|||"Catelin Wang" <CatelinWang@.discussions.microsoft.com> wrote in message
news:09C9D493-57C5-464C-A198-1D799FFE0B3E@.microsoft.com...
>I used union to combine 2 tables in a view, may not be indexed on the
>view...
> "Catelin Wang" wrote:
>> I have couple of large views to data for reporting. That will be
>> wonderful if
>> there is a way I can put indexs on a view. I could'n find a way to do
>> so.
>> Any ideas?
Perhaps you can create two indexed views and union those. But perhaps there
is a more direct way to improve your performance.
If you post the table DDL, the view text and a description of how many rows
are in each base table, and how many different values each of the important
columns has, and how many rows you will get back from the query, someone may
suggest an optimization either to your base table indexes or your SQL
design.
David

Large report to fit onto 1 page

Hi,

I've browsed the forums and found other posts outlining the same issue and the answer was no..there is no scale to fit option in reporting services.. now a year later i was wondering if anything had been done about this?

Is there anyway to get a 20 column report to fit onto 1 page on printing?

Many thanks

Dave

Have you submitted this suggestion to https://connect.microsoft.com/SQLServer/Feedback ?

If so, let me know the URL and I will also vote on it.

There is nothing in SSRS now that allows "fit to page", but there really should be, especially for the matrix control.

BobP

Large OLTP DB design ponderings

I am in the middle of designing a large OLTP DB app that will also be expected to provide complex reporting capabilities with reasonable response times. There will be tens of thousands of entries a day.

I am torn between minimally indexing my DB and trying to achieve a balance between write speed and read speed or leaving the DB in a heap and creating a linked DB that would mirror the original DB but would be fully indexed with partitions and indexed views, etc. I will probably have a third DB that would serve only as a mirrored backup to the primary with a witness.

Does this seem a reasonable approach? Or am I going over the "overkill" edge?

Fast-response OLTP apps and complex reports don't usually mix particularly well. As a test, why don't you try loading your database with a predicted couple of years' worth of test data and seeing how your design performs?

If you have the ability to add a server dedicated to reporting at this early stage then it would be a lot easier to factor the second server into the design now than it would be to incorporate it later on.

Chris

|||

Chris,

Thanks. That is what I am beginning to do. We are about half way through the project and I was brought on board to "tune" the system.

I guess I was asking if there ids anything inherently wrong with leaving the "entry" DB in a heap and creating a "reporting" DB indexed out the wazoo. I am assuming both would run faster but there would be a lag as indexes were applied to the transaction logs as they were shipped. I can live with some latency in the data if it will make both processes (read & write) "pop".

|||

There's nothing wrong with doing that at all - you're simply optimising each copy of the database for its intended purpose.

I would keep your options open in terms of indexing on the entry table - don't forget that a Primary Key is actually an index. I don't know if your OLTP application needs to read real-time data from the entry table, if it does then you might find that a strategically placed index (or two) will actually help without significantly degrading inserts.

How are you planning to copy data from the primary OLTP database to the secondary database? If you are using Database Mirroring then you will not be able to read data from the mirrored copy of the database during normal operation (due to the database being in a recovering state), however you would be able to create a database snapshot on the mirrored copy which would allow you to read the data, but frozen at a point in time. For this reason you might want to consider using transational replication instead to maintain the report server's copy of the data.

Chris

|||

Actually I was thinking of a system that combined a primary-witness-mirror/quorum system for availability with a 2nd instance on each server having a read only copy that would be updated via transaction log shipping at short intervals.

Currently every table has a clustered index/primary key field, usually an identity field. It will probably be best to leave that in place, give an ample fill factor and rebuild the DB as part of routine (nightly) maintenance.

|||

Personally, unless you are going to be performing a lot of updates to the data, I'd leave the fill factor set at 0 (or 100) on tables where the clustered index is on an identity column - any other setting will result in wasted disk space. If you are expecting updates then a value of 80-90 should be OK. Again, it would be good to test this to find the optimal setting.

If you are going to copy the data over by either shipping the transaction logs or by mirroring, then your reporting indexes would have to exist in the primary database for them to be available on the secondary server. With replication you can choose to execute a 'post-snapshot script' that could be used to apply the indexes to the subscription (secondary) database once the initial snapshot has been applied.

Chris

|||

I just converted a client from a single database system to a 2 database system, and they were able to reap other benefits as well.

They had their reports pulling from the OLTP system, but that would interfere with the business users (slow the system down) and cause the reports to be slow.

I installed a new server, put SQL Server 2005 on it, and I import the OLTP daily. I have setup a data warehouse for their reporting needs, and now the reports are very fast, and I dont interfere with the OLTP system.

BobP

|||

Thanks Bob.

That is probably the type of system I am working toward. I think I have to have data more current than daily, but that detail can be worked out. Do you import a snapshot and then run a script to index the data or is it indexed in the OLTP DB?

|||

There are some indexes in the OLTP, but what I do is using SSIS, I transform the data to make it more report friendly.

Then I indexed the heck out of it :-D

If you need more than daily, my suggestion, and I have done this in the past, is to setup a transactional replication from the OLTP server to a staging server. Then import as many times as you want from the staging server. (The staging server can even be your reporting server, if you have enough disk space)

BobP

|||

Thanks again.....I think that is my solution as eventually we are building a BI system for them.

I'll try transactional replication to a staging server and set the frequency to allow enough time for indexes to be applied. I can run daily reports from the staging DB (heck, you can get a terabyte for $500 these days) and ship the data off to the warehouse to do all the BI stuff - that I'm still learning :-)

Thanks again Chris and Bob (I love this board!)

Large number of records and Small page size?

Hi there. I'm a beginner of reporting service, and so I'm not sure if
this question belongs to the FAQ.
Suppose a report has a query which can potentially retrieves large
number of records, say, 500,000 records. The report has a only a page
size of 50 records (i.e. 50 records to be displayed at a time). When a
user view this report in the reporting service at the IIS, does the
report service pull all 500,000 records from the database server to the
IIS, or does it only pull 50 record from database server to IIS at a
time?
Thanks
DomI forgot to mention that I'm using SQL Server 2000 as the data-source,
and Reporting Service 2000 (the one published in 2004).
Thanks
Dom
dom...@.hotmail.com wrote:
> Hi there. I'm a beginner of reporting service, and so I'm not sure if
> this question belongs to the FAQ.
> Suppose a report has a query which can potentially retrieves large
> number of records, say, 500,000 records. The report has a only a page
> size of 50 records (i.e. 50 records to be displayed at a time). When a
> user view this report in the reporting service at the IIS, does the
> report service pull all 500,000 records from the database server to the
> IIS, or does it only pull 50 record from database server to IIS at a
> time?
> Thanks
> Dom|||Hi,
The reporting service run the query and pull all the data once. it does not
pull 50 record each time you click next. once it get the record it pulls from
the memory and show to you. The custom paging of pulling the record from
database each time when you click next is not done by report manager or
reporing service.
Thanks
Bava
"domtam@.hotmail.com" wrote:
> I forgot to mention that I'm using SQL Server 2000 as the data-source,
> and Reporting Service 2000 (the one published in 2004).
> Thanks
> Dom
> dom...@.hotmail.com wrote:
> > Hi there. I'm a beginner of reporting service, and so I'm not sure if
> > this question belongs to the FAQ.
> >
> > Suppose a report has a query which can potentially retrieves large
> > number of records, say, 500,000 records. The report has a only a page
> > size of 50 records (i.e. 50 records to be displayed at a time). When a
> > user view this report in the reporting service at the IIS, does the
> > report service pull all 500,000 records from the database server to the
> > IIS, or does it only pull 50 record from database server to IIS at a
> > time?
> >
> > Thanks
> > Dom
>|||Thanks for your reply.
If the query can potentially return large number of records (say,
500,000) records, all these records will be put to the memory of the
IIS server hosting the report even though each page may show ony 50
records. Is this what you mean?
In this case, this can impose lots of stress on the memory of the IIS
server, especially there are multiple users viewing reports of large
size. How should we handle large report (of small page size) in this
situation? I believe this is a common scenario in Reporting Service
community, right?
If it is the ASP.NET datagrid (instead of report) that displays these
records, we can use custom paging to handle this kinda large result-set
query situation. In other words, we will pull 50 records at a time. Is
there any similar technique for Reporting Service?
Thanks
Dominic
Bava Mani wrote:
> Hi,
> The reporting service run the query and pull all the data once. it does not
> pull 50 record each time you click next. once it get the record it pulls from
> the memory and show to you. The custom paging of pulling the record from
> database each time when you click next is not done by report manager or
> reporing service.
> Thanks
> Bava
> "domtam@.hotmail.com" wrote:
> > I forgot to mention that I'm using SQL Server 2000 as the data-source,
> > and Reporting Service 2000 (the one published in 2004).
> >
> > Thanks
> > Dom
> > dom...@.hotmail.com wrote:
> > > Hi there. I'm a beginner of reporting service, and so I'm not sure if
> > > this question belongs to the FAQ.
> > >
> > > Suppose a report has a query which can potentially retrieves large
> > > number of records, say, 500,000 records. The report has a only a page
> > > size of 50 records (i.e. 50 records to be displayed at a time). When a
> > > user view this report in the reporting service at the IIS, does the
> > > report service pull all 500,000 records from the database server to the
> > > IIS, or does it only pull 50 record from database server to IIS at a
> > > time?
> > >
> > > Thanks
> > > Dom
> >
> >

Large Inserts, TempDB Growing

I have a query that's joining a messload of tables to populate a single
table used later for OLAP reporting.
The source tables, and the OLAP table are in different databases.
Basic Form:
INSERT INTO OLAPDB.dbo.SomeTable
SELECT lots_of_columns
FROM atables
INNER JOIN lots_of_tables...
No ORDER BYs... no GROUP BYs
The query dies because there isn't enough disk space for TempDB.
When I look at the files, tempdb is HUGE, and the OLAPDB (destination)
is tiny.
Is there a way to insert the data straight into the destination?
without it using tempdb as an intermediate?
any ideas?
thanks
-MarkMark,
Maybe add a WHERE clause and perform the INSERT in multiple parts.
HTH
Jerry
"Mark" <AnonymousPerson12345@.gmail.com> wrote in message
news:1127429078.936480.53900@.o13g2000cwo.googlegroups.com...
>I have a query that's joining a messload of tables to populate a single
> table used later for OLAP reporting.
> The source tables, and the OLAP table are in different databases.
> Basic Form:
> INSERT INTO OLAPDB.dbo.SomeTable
> SELECT lots_of_columns
> FROM atables
> INNER JOIN lots_of_tables...
> No ORDER BYs... no GROUP BYs
> The query dies because there isn't enough disk space for TempDB.
> When I look at the files, tempdb is HUGE, and the OLAPDB (destination)
> is tiny.
> Is there a way to insert the data straight into the destination?
> without it using tempdb as an intermediate?
> any ideas?
> thanks
> -Mark
>|||Mark wrote:
> I have a query that's joining a messload of tables to populate a
> single table used later for OLAP reporting.
> The source tables, and the OLAP table are in different databases.
> Basic Form:
> INSERT INTO OLAPDB.dbo.SomeTable
> SELECT lots_of_columns
> FROM atables
> INNER JOIN lots_of_tables...
> No ORDER BYs... no GROUP BYs
> The query dies because there isn't enough disk space for TempDB.
> When I look at the files, tempdb is HUGE, and the OLAPDB (destination)
> is tiny.
> Is there a way to insert the data straight into the destination?
> without it using tempdb as an intermediate?
> any ideas?
> thanks
> -Mark
An INSERT INTO is a fully logged operation. A SELECT INTO OTOH is a bulk
logged operation. Instead of using tempdb, you could use a regular table
in a database of your choosing. However, if you are running out of
space in tempdb, you could make sure tempdb is adequately sized to begin
with and can auto-grow if needed. You could also try using SELECT INTO
which will keep transaction logging to a minimum, but does require SQL
Server create the table for you based on the columns in the query. You
could also try speeding up the query by using a stored procedure that
pulls data from the tables in a more efficient manner - if that's
possible.
David Gugick
Quest Software
www.imceda.com
www.quest.com

Large files(Excel) coming out of Reporting Services how can I compress them for email

I have a 29000 row spreadsheet coming out of Reporting Services that is producing a large file that we email. How can I create the most compact spreadsheet/file for scheduled daily emailing out of Reporting Services?

I'm pretty sure there isn't any built-in way to compress the report attached to an email in Reporting Services. There aren't really any tricks that you can use to reduce the size of the report generated, other than making sure that you use numbers instead of strings whenever possible -- numbers will generally take less space in the Excel format than strings. Depending on how you are sending the emails, you might be able to use some third party code or write your own code to compress the exported report and then mail it, but it isn't something that is part of RS.

Large files(Excel) coming out of Reporting Services how can I compress them for email

I have a 29000 row spreadsheet coming out of Reporting Services that is producing a large file that we email. How can I create the most compact spreadsheet/file for scheduled daily emailing out of Reporting Services?

I'm pretty sure there isn't any built-in way to compress the report attached to an email in Reporting Services. There aren't really any tricks that you can use to reduce the size of the report generated, other than making sure that you use numbers instead of strings whenever possible -- numbers will generally take less space in the Excel format than strings. Depending on how you are sending the emails, you might be able to use some third party code or write your own code to compress the exported report and then mail it, but it isn't something that is part of RS.