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..

Last Name Only

I am fairly new to Crystal Reports and need some help with a report design. I am trying to extract just the Last Name from a field that contains both First and Last Name in one string.
Any help would be greatly appreciated.
Thanks,
Hanseleg. Create a formula and write this
stringVar sp;
sp:="Test1 test2";
mid(sp,instr(sp," ",1)+1,len(sp))

Last Day of the Month Subscription

Does anyone have a way to set up a Subscription for the last day of the
month?
We've been changing reports so they only have data on the last day of the
month, but the report also needs to run on demand and return data.
There must be a way.
Thanks,
MikeSo far, I have not found a simple way to do this (thanks Microsoft for
leaving out the obvious). Interestingly MSDN and Technet both have articles
that say you can run a report 'on the last day of the month' but never
provide details. I have to run my report on the last day of the month...it
cannot be on the first day (which is easy).
My first thought had been to set it to run on 31st for all months and
perhaps Reporting Services is smart enough to figure out that there aren't 31
days in a particular month...but I am afraid that it simply won't fire on
these months...
The only solution I have come up (but not tested) with requires creating 3
separate subscriptions and setting them to run on the last day of specific
months (check only the months you want that version of the subscription to
run).
28 -29 - Feb (uses a range - would probably generate an extra report in
those years with 29 days in Feb...)
30 - April, June, Sept, Nov
31 - Jan, March, May, July, Aug, Oct, Dec

Last Cube Process Date/Time

Is there anyway to programmatically retrieve the last date/time that an SSAS cube was processed?

I would like to display this date/time in reports.

I could add a step to the job that runs the process to add a row to a table upon successful completion, but don't want to re-invent the wheel.

Thanks!
BobPIt is available via both DSO (for AS2K) and AMO (for AS2K5).
Just look in BOL and you will see it.

_-_-_ Dave|||

hi,

can you post an example to show the date cube was last updated in a text box on a report.

Thanks,

|||

using System;

using System.Collections.Generic;

using System.Text;

using Microsoft.AnalysisServices;

namespace GetCubeProcessingTime

{

class Program

{

static void Main(string[] args)

{

string date = string.Empty;

string cubeName = string.Empty;

try

{

// Connect to the SSAS server

Server server = new Server();

server.Connect(@."Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Adventure Works DW;Data Source=localhost\YUKON");

// Get the Adventure Works cube(s)

Database database = server.Databases.GetByName("Adventure Works DW");

foreach (Cube cube in database.Cubes)

{

date = cube.LastProcessed.ToString("yyyy-MM-dd HH:mm:ss");

cubeName = cube.Name;

Console.WriteLine(string.Format("Cube [{0}] was processed: {1}", cubeName, date));

}

}

catch (Exception exception)

{

// Uups

Console.WriteLine(exception.Message);

}

}

}

}

Last Cube Process Date/Time

Is there anyway to programmatically retrieve the last date/time that an SSAS cube was processed?

I would like to display this date/time in reports.

I could add a step to the job that runs the process to add a row to a table upon successful completion, but don't want to re-invent the wheel.

Thanks!
BobPIt is available via both DSO (for AS2K) and AMO (for AS2K5).
Just look in BOL and you will see it.

_-_-_ Dave|||

hi,

can you post an example to show the date cube was last updated in a text box on a report.

Thanks,

|||

using System;

using System.Collections.Generic;

using System.Text;

using Microsoft.AnalysisServices;

namespace GetCubeProcessingTime

{

class Program

{

static void Main(string[] args)

{

string date = string.Empty;

string cubeName = string.Empty;

try

{

// Connect to the SSAS server

Server server = new Server();

server.Connect(@."Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Adventure Works DW;Data Source=localhost\YUKON");

// Get the Adventure Works cube(s)

Database database = server.Databases.GetByName("Adventure Works DW");

foreach (Cube cube in database.Cubes)

{

date = cube.LastProcessed.ToString("yyyy-MM-dd HH:mm:ss");

cubeName = cube.Name;

Console.WriteLine(string.Format("Cube [{0}] was processed: {1}", cubeName, date));

}

}

catch (Exception exception)

{

// Uups

Console.WriteLine(exception.Message);

}

}

}

}

Large TempDB

Hi
I have many reports running from a SQL Server DB, from Crystal Enterprise
reporting system. However I have noticed some performance degradation, and
I noticed that my TempDB had ballooned in size. Is there a way to reset
this? I tried Shrink, but this did not reduce the size of the DB?
Kind Regards
Ricky
(SQL2K-SP3A/WIN2K-Client/WIN2K-Server/Crystal Enterprise 9.0)http://www.aspfaq.com/show.asp?id=2446
David Portas
SQL Server MVP
--
"rikesh patel" <trikky@.rukp28.freeserve.co.uk> wrote in message
news:ubgD8RnxFHA.3124@.TK2MSFTNGP12.phx.gbl...
> Hi
> I have many reports running from a SQL Server DB, from Crystal Enterprise
> reporting system. However I have noticed some performance degradation,
> and
> I noticed that my TempDB had ballooned in size. Is there a way to reset
> this? I tried Shrink, but this did not reduce the size of the DB?
> Kind Regards
> Ricky
> (SQL2K-SP3A/WIN2K-Client/WIN2K-Server/Crystal Enterprise 9.0)
>

Large String Parameters Causing Performance Issues

Hi All,
Bit of strange one this. I have a key field in one of my databases that is a
Char(18). I use this as a parameter field in one of my reports.
When I run the report and specify the key field parameter manually the
report runs instantly. If I attempt to set this as a parameter variable and
enter the criteria in the parameter box, the report takes over a minute to
run. The 2 statements are as follows.
1) select sum(sales_value) as sales from production.dbo.epos_transactions
where transaction_ref = '085505010500074995'
When run as above, the report runs instantly.
2) select sum(sales_value) as sales from production.dbo.epos_transactions
where transaction_ref = @.TREF
When I enter the parameter and run the report it takes over a minute to run.
Running the query through query analyser causes no issues on any of the
servers involved and the response time on all servers is instant.
I'd be grateful if anyone can shed any light on this.
Many thanks for any help.
--
Nick Colebourn
DBA
United Coop LtdHi Nick,
Could you supply a generic repro for this issue. An RDL for Northwind or a
simple DB. I would love to take a look at it.
| Thread-Topic: Large String Parameters Causing Performance Issues
| thread-index: AcUC1GzdZAw/tJ+RRciPnzP3TypLNA==| X-WBNR-Posting-Host: 213.146.135.115
| From: "=?Utf-8?B?TmljayBDb2xlYm91cm4=?="
<NickColebourn@.discussions.microsoft.com>
| Subject: Large String Parameters Causing Performance Issues
| Date: Tue, 25 Jan 2005 03:53:03 -0800
| Lines: 29
| Message-ID: <EFE8F146-E9CC-49C8-A5B8-4E29E3F57EC2@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
| Path: cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.reportingsvcs:40737
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| Hi All,
| Bit of strange one this. I have a key field in one of my databases that
is a
| Char(18). I use this as a parameter field in one of my reports.
| When I run the report and specify the key field parameter manually the
| report runs instantly. If I attempt to set this as a parameter variable
and
| enter the criteria in the parameter box, the report takes over a minute
to
| run. The 2 statements are as follows.
|
| 1) select sum(sales_value) as sales from production.dbo.epos_transactions
| where transaction_ref = '085505010500074995'
|
| When run as above, the report runs instantly.
|
| 2) select sum(sales_value) as sales from production.dbo.epos_transactions
| where transaction_ref = @.TREF
|
| When I enter the parameter and run the report it takes over a minute to
run.
|
| Running the query through query analyser causes no issues on any of the
| servers involved and the response time on all servers is instant.
|
| I'd be grateful if anyone can shed any light on this.
|
| Many thanks for any help.
|
| --
| Nick Colebourn
| DBA
| United Coop Ltd
||||Hi Brad,
Many thanks for your reply. I'll see if I can get it to do the same
with a northwind query and post back asap. For your info, I've created an
in-line table valued function containing the sql with the parameter and
called this as the dataset instead of the sql text itself. This works ok
either with static criteria or using a parameter passed across to the
function. The performance issue only seems to happen when you pass the
parameter value directly across to the sql text.
Many thanks
Nick
""Brad Syputa - MS"" wrote:
> Hi Nick,
> Could you supply a generic repro for this issue. An RDL for Northwind or a
> simple DB. I would love to take a look at it.
>
> --
> | Thread-Topic: Large String Parameters Causing Performance Issues
> | thread-index: AcUC1GzdZAw/tJ+RRciPnzP3TypLNA==> | X-WBNR-Posting-Host: 213.146.135.115
> | From: "=?Utf-8?B?TmljayBDb2xlYm91cm4=?="
> <NickColebourn@.discussions.microsoft.com>
> | Subject: Large String Parameters Causing Performance Issues
> | Date: Tue, 25 Jan 2005 03:53:03 -0800
> | Lines: 29
> | Message-ID: <EFE8F146-E9CC-49C8-A5B8-4E29E3F57EC2@.microsoft.com>
> | MIME-Version: 1.0
> | Content-Type: text/plain;
> | charset="Utf-8"
> | Content-Transfer-Encoding: 7bit
> | X-Newsreader: Microsoft CDO for Windows 2000
> | Content-Class: urn:content-classes:message
> | Importance: normal
> | Priority: normal
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> | Newsgroups: microsoft.public.sqlserver.reportingsvcs
> | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
> | Path: cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl
> | Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.reportingsvcs:40737
> | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
> |
> | Hi All,
> | Bit of strange one this. I have a key field in one of my databases that
> is a
> | Char(18). I use this as a parameter field in one of my reports.
> | When I run the report and specify the key field parameter manually the
> | report runs instantly. If I attempt to set this as a parameter variable
> and
> | enter the criteria in the parameter box, the report takes over a minute
> to
> | run. The 2 statements are as follows.
> |
> | 1) select sum(sales_value) as sales from production.dbo.epos_transactions
> | where transaction_ref = '085505010500074995'
> |
> | When run as above, the report runs instantly.
> |
> | 2) select sum(sales_value) as sales from production.dbo.epos_transactions
> | where transaction_ref = @.TREF
> |
> | When I enter the parameter and run the report it takes over a minute to
> run.
> |
> | Running the query through query analyser causes no issues on any of the
> | servers involved and the response time on all servers is instant.
> |
> | I'd be grateful if anyone can shed any light on this.
> |
> | Many thanks for any help.
> |
> | --
> | Nick Colebourn
> | DBA
> | United Coop Ltd
> |
>

Large numbers of orphaned/expired requests

We are experiencing a situation where the SRS (2000 SP2) report server will no longer render reports. In the log file, there are many instances of

w3wp!runningjobs!434!3/23/2007-10:12:57:: i INFO: Adding: 8 running jobs to the database
w3wp!runningjobs!434!3/23/2007-10:13:57:: i INFO: RunningJobContext.IsClientConnected; found orphaned request
w3wp!runningjobs!434!3/23/2007-10:13:57:: i INFO: RunningJobContext.IsExpired; found expired request
w3wp!runningjobs!434!3/23/2007-10:13:57:: i INFO: RunningJobContext.IsExpired; found expired request
w3wp!runningjobs!434!3/23/2007-10:13:57:: i INFO: RunningJobContext.IsExpired; found expired request
w3wp!runningjobs!434!3/23/2007-10:13:57:: i INFO: RunningJobContext.IsClientConnected; found orphaned request
w3wp!runningjobs!434!3/23/2007-10:13:57:: i INFO: RunningJobContext.IsClientConnected; found orphaned request
w3wp!runningjobs!434!3/23/2007-10:13:57:: i INFO: RunningJobContext.IsExpired; found expired request
w3wp!runningjobs!434!3/23/2007-10:14:57:: i INFO: RunningJobContext.IsClientConnected; found orphaned request
w3wp!runningjobs!434!3/23/2007-10:14:57:: i INFO: RunningJobContext.IsExpired; found expired request
w3wp!runningjobs!434!3/23/2007-10:14:57:: i INFO: RunningJobContext.IsExpired; found expired request
w3wp!runningjobs!434!3/23/2007-10:14:57:: i INFO: RunningJobContext.IsExpired; found expired request
w3wp!runningjobs!434!3/23/2007-10:14:57:: i INFO: RunningJobContext.IsClientConnected; found orphaned request
w3wp!runningjobs!434!3/23/2007-10:14:57:: i INFO: RunningJobContext.IsClientConnected; found orphaned request
w3wp!runningjobs!434!3/23/2007-10:14:57:: i INFO: RunningJobContext.IsExpired; found expired request

What could be causing this? The reports are making queries through an OLE DB provider. There are no scheduled jobs, and the load doesn't seem that heavy.
Orphaned request indicates that the HTTP request has gone away before we finished processing the report. Generally this is because people request a report, and then close IE.

Large number of reports

Hi

I have a large number of reports that are very similar in structure (only some query and report heading changes).

Creation of these is simple by creating a template or modifying the project file and saving as a new project. But whenever there is some change to be made (format or query change), it has to be made individually in all of the reports, which makes it very time consuming and error prone.

Can anyone give some suggestions as to how to automate this process, or some other way to improve maintainability in this situation.

Thanks

Shomik

Unfortunately RS does not support CSS which would be ideal. One solution is to make certain formatting options data driven. Seeing as most properties are expression based, you could have a styles table and have the report query the styles at runtime and set colours and fonts based on fields from a dataset. Any change would effect all supporting reports.|||

I have made the formatting options and even the static messages shown, database driven wherever possible. But still cannot do it everywhere, because of which even a position change of a textbox has to be made in all of the reports manually.

I tried using a template, but that is ok for creating the new report. I again have the same problem when it comes to maintenance.

Can anyone suggest any changes to the way I am doing this or another approach for tackling this issue ?

Thanks,

Shomik

Large move from Access to SQL Server 200

Hi,

My client has a rather large database with some very large reports. Some of the reports have around 20 sub-reports a piece. We have decided to move the client's application to a .NET web application and would migrate them to SQL Server 2000.

The only problem is now, designing the reports. I have tried doing what Microsoft says (converting to stored procedures and views) but I keep getting syntax errors on the SQL side of things when I cut and paste.

For example, the following code is taken from Access :

SELECT tblProjects.fldCountry, tblProjects.fldDescription, tblOrganizations.fldAcronym, tblProjects.fldProjID, Max(tblProjYears.fldStartDate) AS MaxOffldStartDate, Max(tblProjYears.fldEndDate) AS MaxOffldEndDate, qryProjLocsWithFEData.fldProjPeriodID
FROM (tblProjects INNER JOIN tblOrganizations ON tblProjects.fldOrgID = tblOrganizations.fldOrgID) INNER JOIN (tblProjYears INNER JOIN qryProjLocsWithFEData ON tblProjYears.fldProjPeriodID = qryProjLocsWithFEData.fldProjPeriodID) ON tblProjects.fldProjID = tblProjYears.fldProjID
GROUP BY tblProjects.fldCountry, tblProjects.fldDescription, tblOrganizations.fldAcronym, tblProjects.fldProjID, qryProjLocsWithFEData.fldProjPeriodID
ORDER BY tblProjects.fldCountry, tblOrganizations.fldAcronym, tblProjects.fldProjID;

But when I try that in SQL Query Analyzer i get the error : The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

I'm pretty sure it's on the tblProjects.fldDescription Group By, but if I leave it out, it still throws an error. Anybody have any ideas?

ThanksGROUP BY Description?

Another Classic Access example...

LOOK OUT! RUN AWAY TRAIN...ummmm developers...

Do you have a lot of nested Access queries as well?

Have you tried just linked the SQL Server tables?

Have you given any thought to a re-write?|||Actually we can't link to the Access db. Everything has to be stored in the SQL database.|||I meant the old access app, with no tables, linked to the sql server database...

are the table and column names still all the same?

If you link it, it should all run...

might be very slow though...

but at least you'll have all your reports...|||Without and more infomation, it looks like the Access upgrade thingy (whatever they call it now) has morphed soem of your text fields to TEXT or NTEXT. If you want to be able to join on these fields, they need to be CHAR, VARCHAR (or NCHAR, NVACHAR).

Your first step after running that thing is to go through the whole database, field by field, and see what has happened to your data in the translation.

Almost as easy to do it manually.

-bpd|||I ended up doing all of the queries as views and getting rid of the one text field, then on the final query, joined the text field, thus by-passing the Group By clause for that field. Thanks for all the help though.

large excel reports not being emailed

*this was also posted on the web forum
sql 2005 sp2
exchange 2003 sp2
i've got the report server setup to relay through the exchange server for
delivering reports. all reports are exported to excel and emailed
successfully with the exception of the largest report at about 16mb. the smtp
logs on the exchange server show the report server starting a session but no
"DATA" is every sent for this report and emails do not arrive. i am assuming
due to the size of the report there is some timeout out issue between the
smtp client and server. any ideas on how to resolve this issue? maybe a
timeout setting on the exchange smtp server? i'm trying to avoid installing a
local smtp server on the report server.
thanks in advance
nitzOn Oct 9, 12:33 pm, nitz <n...@.discussions.microsoft.com> wrote:
> *this was also posted on the web forum
> sql 2005 sp2
> exchange 2003 sp2
> i've got the report server setup to relay through the exchange server for
> delivering reports. all reports are exported to excel and emailed
> successfully with the exception of the largest report at about 16mb. the smtp
> logs on the exchange server show the report server starting a session but no
> "DATA" is every sent for this report and emails do not arrive. i am assuming
> due to the size of the report there is some timeout out issue between the
> smtp client and server. any ideas on how to resolve this issue? maybe a
> timeout setting on the exchange smtp server? i'm trying to avoid installing a
> local smtp server on the report server.
> thanks in advance
> nitz
You might want to start w/the root of IIS at the Default Website level
(right-click My Computer >> select Manage >> select Services and
Applications >> Internet Information Services >> Web Sites >> Default
Web Site >> right-click >> select Properties) and increase the timeout
value (Connection Timeout). Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||i've already increased the timeout value for previous reports. this might
have been a good idea if this report was experiencing a timeout when executed
from a web browser and exported to excel. however, it executes just fine with
the current webconfig settings. any other ideas?
i will note that when symantec AV internet email auto-protect option is
checked (this scans any outgoing smtp mail orginating from the client, not
just in outlook per say) it would log error stating the exhange server
disconnected the session. i did also try with AV disabled to make sure with
the same result.
thanks
"EMartinez" wrote:
> On Oct 9, 12:33 pm, nitz <n...@.discussions.microsoft.com> wrote:
> > *this was also posted on the web forum
> >
> > sql 2005 sp2
> >
> > exchange 2003 sp2
> >
> > i've got the report server setup to relay through the exchange server for
> > delivering reports. all reports are exported to excel and emailed
> > successfully with the exception of the largest report at about 16mb. the smtp
> > logs on the exchange server show the report server starting a session but no
> > "DATA" is every sent for this report and emails do not arrive. i am assuming
> > due to the size of the report there is some timeout out issue between the
> > smtp client and server. any ideas on how to resolve this issue? maybe a
> > timeout setting on the exchange smtp server? i'm trying to avoid installing a
> > local smtp server on the report server.
> >
> > thanks in advance
> >
> > nitz
>
> You might want to start w/the root of IIS at the Default Website level
> (right-click My Computer >> select Manage >> select Services and
> Applications >> Internet Information Services >> Web Sites >> Default
> Web Site >> right-click >> select Properties) and increase the timeout
> value (Connection Timeout). Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>|||It's possible the email is getting blocked due to it's size. Is there a
maximum email size defined in your exchange or filtering server (eg.
Mailmarshal) ? Also note, that due to smtp mime encoding, files sent out via
email are 20-30% larger than the actual file size due to the encoding.
"nitz" wrote:
> i've already increased the timeout value for previous reports. this might
> have been a good idea if this report was experiencing a timeout when executed
> from a web browser and exported to excel. however, it executes just fine with
> the current webconfig settings. any other ideas?
> i will note that when symantec AV internet email auto-protect option is
> checked (this scans any outgoing smtp mail orginating from the client, not
> just in outlook per say) it would log error stating the exhange server
> disconnected the session. i did also try with AV disabled to make sure with
> the same result.
> thanks
> "EMartinez" wrote:
> > On Oct 9, 12:33 pm, nitz <n...@.discussions.microsoft.com> wrote:
> > > *this was also posted on the web forum
> > >
> > > sql 2005 sp2
> > >
> > > exchange 2003 sp2
> > >
> > > i've got the report server setup to relay through the exchange server for
> > > delivering reports. all reports are exported to excel and emailed
> > > successfully with the exception of the largest report at about 16mb. the smtp
> > > logs on the exchange server show the report server starting a session but no
> > > "DATA" is every sent for this report and emails do not arrive. i am assuming
> > > due to the size of the report there is some timeout out issue between the
> > > smtp client and server. any ideas on how to resolve this issue? maybe a
> > > timeout setting on the exchange smtp server? i'm trying to avoid installing a
> > > local smtp server on the report server.
> > >
> > > thanks in advance
> > >
> > > nitz
> >
> >
> > You might want to start w/the root of IIS at the Default Website level
> > (right-click My Computer >> select Manage >> select Services and
> > Applications >> Internet Information Services >> Web Sites >> Default
> > Web Site >> right-click >> select Properties) and increase the timeout
> > value (Connection Timeout). Hope this helps.
> >
> > Regards,
> >
> > Enrique Martinez
> > Sr. Software Consultant
> >
> >