Change from Crystal Reports to SQL Server 2005 Reporting Services?

Hi all,
We have a web application that generates statistical reports in PDF format
based on customer data and we're thinking of migrating to SQL Server 2005
Reporting Services but I'd like to know if what we are doing is possible.
Basically, the customer logs onto the system via a regular browser, this
creates a folder on the backend based on their session info. Then they
select a couple of different reports, enter the required parameters and hit
print. The web app runs a VB program that loads the RPT file, modifies the
connections so it's getting the data from the correct customer database,
then exports the report as a PDF into the previously created folder. The
user is then redirected to that file so they can download it.
Whether the report is shown in a browser window or not is all dependent on
the customers settings for PDF documents. We don't need interactive
web-based reports, we don't need to drill-down, we don't need OLAP. It's a
simple "I want a report", "Here it is" scenario, but there is the definate
potential of having several hundred customers all requesting reports at the
same time, which is something our current solution can't handle.
Comments and suggestions are welcome.2005 has the ability to change the datasource at runtime. You do not need to
do any of this creating a folder in the background etc. Everything that you
want is really very simple.When your web app renders it can request it as
PDF. Also, you can send the report a parameter and based on that parameter
pick the appropriate database.
Implementing your security is the biggest issue (and what comes up the most
with an internet application). You will need to do forms based
authentication (which I have not done and do not know much about).
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Andrew Hayes" <AndrewHayes@.discussions.microsoft.com> wrote in message
news:OOzh8DapGHA.4408@.TK2MSFTNGP04.phx.gbl...
> Hi all,
> We have a web application that generates statistical reports in PDF format
> based on customer data and we're thinking of migrating to SQL Server 2005
> Reporting Services but I'd like to know if what we are doing is possible.
> Basically, the customer logs onto the system via a regular browser, this
> creates a folder on the backend based on their session info. Then they
> select a couple of different reports, enter the required parameters and
> hit print. The web app runs a VB program that loads the RPT file, modifies
> the connections so it's getting the data from the correct customer
> database, then exports the report as a PDF into the previously created
> folder. The user is then redirected to that file so they can download it.
> Whether the report is shown in a browser window or not is all dependent on
> the customers settings for PDF documents. We don't need interactive
> web-based reports, we don't need to drill-down, we don't need OLAP. It's a
> simple "I want a report", "Here it is" scenario, but there is the definate
> potential of having several hundred customers all requesting reports at
> the same time, which is something our current solution can't handle.
> Comments and suggestions are welcome.
>|||Thanks for the reply Bruce. Application security is already handled.
Maybe you can guide me through my understanding of how SSRS would handle
this?
a) User opens web-page.
b) User selects report from drop-down list, and the postback shows the
correct parameter options.
c) User enters the parameter values he needs.
d) User clicks on print.
e) Pop-up appears asking the user to wait while their report is prepared.
f) A few minutes later, the pop-up disappears to be replaced by the usual
File Download dialog.
g) User clicks Save and the PDF is saved to their PC.
We can do this in Crystal Reports without any fuss, but when more than 30
users are performing the same actions at the same time, the queue of report
requests ends up being pretty large as it takes quite some time to produce
each report. We're hoping that SSRS will let us "farm" out the report
generation to multiple servers.
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:OJlmwOcpGHA.3324@.TK2MSFTNGP05.phx.gbl...
> 2005 has the ability to change the datasource at runtime. You do not need
> to do any of this creating a folder in the background etc. Everything that
> you want is really very simple.When your web app renders it can request it
> as PDF. Also, you can send the report a parameter and based on that
> parameter pick the appropriate database.
> Implementing your security is the biggest issue (and what comes up the
> most with an internet application). You will need to do forms based
> authentication (which I have not done and do not know much about).
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Andrew Hayes" <AndrewHayes@.discussions.microsoft.com> wrote in message
> news:OOzh8DapGHA.4408@.TK2MSFTNGP04.phx.gbl...
>> Hi all,
>> We have a web application that generates statistical reports in PDF
>> format based on customer data and we're thinking of migrating to SQL
>> Server 2005 Reporting Services but I'd like to know if what we are doing
>> is possible.
>> Basically, the customer logs onto the system via a regular browser, this
>> creates a folder on the backend based on their session info. Then they
>> select a couple of different reports, enter the required parameters and
>> hit print. The web app runs a VB program that loads the RPT file,
>> modifies the connections so it's getting the data from the correct
>> customer database, then exports the report as a PDF into the previously
>> created folder. The user is then redirected to that file so they can
>> download it.
>> Whether the report is shown in a browser window or not is all dependent
>> on the customers settings for PDF documents. We don't need interactive
>> web-based reports, we don't need to drill-down, we don't need OLAP. It's
>> a simple "I want a report", "Here it is" scenario, but there is the
>> definate potential of having several hundred customers all requesting
>> reports at the same time, which is something our current solution can't
>> handle.
>> Comments and suggestions are welcome.
>>
>|||Although implemented in a different way, the scenario described below is no
problem. RS has support for web farms. The Reporting Services app (which is
a 2.0 dotnet application) can run on a web farm. They all use the same
backend database for object/metadata caching. You do have to purchase a SQL
Server 2005 server license for each box running
PDF processing is more resource intensive than html or csv but RS 2005 has
improved this over RS 2000.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Andrew Hayes" <AndrewHayes@.discussions.microsoft.com> wrote in message
news:eqzqBohpGHA.1548@.TK2MSFTNGP04.phx.gbl...
> Thanks for the reply Bruce. Application security is already handled.
> Maybe you can guide me through my understanding of how SSRS would handle
> this?
> a) User opens web-page.
> b) User selects report from drop-down list, and the postback shows the
> correct parameter options.
> c) User enters the parameter values he needs.
> d) User clicks on print.
> e) Pop-up appears asking the user to wait while their report is prepared.
> f) A few minutes later, the pop-up disappears to be replaced by the usual
> File Download dialog.
> g) User clicks Save and the PDF is saved to their PC.
> We can do this in Crystal Reports without any fuss, but when more than 30
> users are performing the same actions at the same time, the queue of
> report requests ends up being pretty large as it takes quite some time to
> produce each report. We're hoping that SSRS will let us "farm" out the
> report generation to multiple servers.
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:OJlmwOcpGHA.3324@.TK2MSFTNGP05.phx.gbl...
>> 2005 has the ability to change the datasource at runtime. You do not need
>> to do any of this creating a folder in the background etc. Everything
>> that you want is really very simple.When your web app renders it can
>> request it as PDF. Also, you can send the report a parameter and based on
>> that parameter pick the appropriate database.
>> Implementing your security is the biggest issue (and what comes up the
>> most with an internet application). You will need to do forms based
>> authentication (which I have not done and do not know much about).
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Andrew Hayes" <AndrewHayes@.discussions.microsoft.com> wrote in message
>> news:OOzh8DapGHA.4408@.TK2MSFTNGP04.phx.gbl...
>> Hi all,
>> We have a web application that generates statistical reports in PDF
>> format based on customer data and we're thinking of migrating to SQL
>> Server 2005 Reporting Services but I'd like to know if what we are doing
>> is possible.
>> Basically, the customer logs onto the system via a regular browser, this
>> creates a folder on the backend based on their session info. Then they
>> select a couple of different reports, enter the required parameters and
>> hit print. The web app runs a VB program that loads the RPT file,
>> modifies the connections so it's getting the data from the correct
>> customer database, then exports the report as a PDF into the previously
>> created folder. The user is then redirected to that file so they can
>> download it.
>> Whether the report is shown in a browser window or not is all dependent
>> on the customers settings for PDF documents. We don't need interactive
>> web-based reports, we don't need to drill-down, we don't need OLAP. It's
>> a simple "I want a report", "Here it is" scenario, but there is the
>> definate potential of having several hundred customers all requesting
>> reports at the same time, which is something our current solution can't
>> handle.
>> Comments and suggestions are welcome.
>>
>>
>

Change Data Source at run time

Hi,
I am using a web Report viewer control for viewing reports on a SRS 2005
report server.
Is it possible to change the data source of the report at run-time?
e.g. I have 2 identical databases - the live database and a test
database. I want to be able to set the data source to point to one of
these depending whether I am testing or not.
This was easily achieved with Crystal, but I cannot work out how to do
it using SQL Reporting Services.
Any help apprectiated.
Thanks.
PaulIf you are using the VS 2005 viewer control then I think you can set it in
there. Another option might be to base the report on a stored procedure
that returns records based on a parameter sent. For example, if parameter =1 then select from live database and if = 0 select from test database.
David
"Paul Cheetham" <PAC.News@.dsl.pipex.com> wrote in message
news:ODJVQZm2HHA.5884@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I am using a web Report viewer control for viewing reports on a SRS 2005
> report server.
> Is it possible to change the data source of the report at run-time?
> e.g. I have 2 identical databases - the live database and a test database.
> I want to be able to set the data source to point to one of these
> depending whether I am testing or not.
> This was easily achieved with Crystal, but I cannot work out how to do it
> using SQL Reporting Services.
> Any help apprectiated.
> Thanks.
>
> Paul|||see ths 2005 BOL at
http://msdn2.microsoft.com/en-us/library/ms156450.aspx
"Paul Cheetham" <PAC.News@.dsl.pipex.com> wrote in message
news:ODJVQZm2HHA.5884@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I am using a web Report viewer control for viewing reports on a SRS 2005
> report server.
> Is it possible to change the data source of the report at run-time?
> e.g. I have 2 identical databases - the live database and a test database.
> I want to be able to set the data source to point to one of these
> depending whether I am testing or not.
> This was easily achieved with Crystal, but I cannot work out how to do it
> using SQL Reporting Services.
> Any help apprectiated.
> Thanks.
>
> Paul

change charset

I changed the charset in a web page using (<META> ).
Browse IE can see the contents changed.
But when I view the source, the charset of data does not changed.
Can I change the charset of the data ?This has nothing to do with SQL Server, so you will
probably have better luck in another newsgroup, but
here's a guess:
When you view source, the meta tags will not be
interpreted, since the point of viewing source is to
do no interpretation, and just see the underlying
page description.
The character set used to display the page source is
most likely determined by your operating system
character set.
You might be able to find a text editor that allows
you to specify the code page of the source text.
Steve Kass
Drew University
Win wrote:

>I changed the charset in a web page using (<META> ).
>Browse IE can see the contents changed.
>But when I view the source, the charset of data does not changed.
>Can I change the charset of the data ?
>
>
>
>

change case sensitivity after database set up

I have a SQL Server database hosted with a web hosting company. The
SQL Server was clearly set up to be case sensitive, however, I want
this particular database to be case-insensitive.

I have searched high and low, the best suggestion I can find is to
reinstall SQL Server and select case-insensitive. But since this is
the web host's SQL, that isn't an option here.

With default language I can use the sp_defaultlanguage to change to
British settings (for example). Is there something similar I can use
to make just this database case insensitive?

--

Popular uprising?
http://www.blairfacedlies.org/statue.htm

captain(underscore)flack(squirlything)hotmail(you know what)comIf you are using SQL 2000, you can change the default database collation
with ALTER DATABASE:

ALTER DATABASE MyDatabase
COLLATE SQL_Latin1_General_CP1_CI_AS

Note that a column collation is determined when the column is created so
changing the database setting will not affect existing tables. You'll
need to alter existing columns to the desired collation or recreate the
tables. The collation used for identifiers (table names, column names,
etc.) will take affect immediately.

--
Hope this helps.

Dan Guzman
SQL Server MVP

--------
SQL FAQ links (courtesy Neil Pike):

http://www.ntfaq.com/Articles/Index...epartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--------

"Captain Flack" <captain.flack@.trumpton-firebrigade.dontevenbother>
wrote in message news:o7qhqv8tpvglr61idpqj24kkaqf8p1cruq@.4ax.com...
> I have a SQL Server database hosted with a web hosting company. The
> SQL Server was clearly set up to be case sensitive, however, I want
> this particular database to be case-insensitive.
> I have searched high and low, the best suggestion I can find is to
> reinstall SQL Server and select case-insensitive. But since this is
> the web host's SQL, that isn't an option here.
> With default language I can use the sp_defaultlanguage to change to
> British settings (for example). Is there something similar I can use
> to make just this database case insensitive?
>
>
> --
> Popular uprising?
> http://www.blairfacedlies.org/statue.htm
> captain(underscore)flack(squirlything)hotmail(you know what)com|||On Wed, 05 Nov 2003 13:36:34 GMT, "Dan Guzman"
<danguzman@.nospam-earthlink.net> wrote:

>If you are using SQL 2000, you can change the default database collation
>with ALTER DATABASE:
> ALTER DATABASE MyDatabase
> COLLATE SQL_Latin1_General_CP1_CI_AS
>Note that a column collation is determined when the column is created so
>changing the database setting will not affect existing tables. You'll
>need to alter existing columns to the desired collation or recreate the
>tables. The collation used for identifiers (table names, column names,
>etc.) will take affect immediately.

Thanks, I will test this out. It is SQL 2000 so it looks to be exactly
what I was looking for :)

--

Popular uprising?
http://www.blairfacedlies.org/statue.htm

captain(underscore)flack(squirlything)hotmail(you know what)com

Chaching Sqldatasource datasets... can it be done? and a performance question.

I'm using visual basic coded datasets right now, and caching them, so as all of my web app users access the cache once the first person's gone to my site and initiallized the page.

If Cache("ds_cache")IsNothingThenDim dsAsNew DataSet

blah blah code blah blah blah

Cache.Insert("ds_cache", ds)EndIf

What I was wondering is, if it's possible to cache the datasets that get created and are used by Sqldatasources. Or if by chance caching is automatically done and controlled for these datasets, as it's the server that makes and handles them.

The reason I wonder about this because I'm not sure how you go about referencing one of those datasets, because you can't give them a name/id. And I've not found mention anywhere of you being able to do so.

The real point or question I'm trying to get at is to see which performs better , or uses less requests back to the server for the data as a lot of my data will be repeatedly used by all of my users and so I'd like to save myself from using as many repeated connections as possibles in regards to using a typed dataset versus filling my controls from a sqldatasource?

Thank you in advance for your response.

You know what, I pretty much found my answer:

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

The question still remains of if a sqldatasource would outperform a typed dataset?

It makes you wonder, as a typed dataset you don't have to worry about time spent on conversions, etc. but you would think that server built and controlled dataset might perform better, or atleast it would probably build faster when it's first created... please do reply if you have insight.

Chaching Sqldatasource datasets... can it be done? and a performance question.

I'm using visual basic coded datasets right now, and caching them, so as all of my web app users access the cache once the first person's gone to my site and initiallized the page.

If Cache("ds_cache") Is Nothing Then

Dim ds As New DataSet

blah blah code blah blah blah

Cache.Insert("ds_cache", ds)

End If

What I was wondering is, if it's possible to cache the datasets that get created and are used by Sqldatasources. Or if by chance caching is automatically done and controlled for these datasets, as it's the server that makes and handles them.

The reason I wonder about this because I'm not sure how you go about referencing one of those datasets, because you can't give them a name/id. And I've not found mention anywhere of you being able to do so.

The real point or question I'm trying to get at is to see which performs better , or uses less requests back to the server for the data as a lot of my data will be repeatedly used by all of my users and so I'd like to save myself from using as many repeated connections as possibles in regards to using a typed dataset versus filling my controls from a sqldatasource?

Thank you in advance for your response.

You know what, I pretty much found my answer:

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

The question still remains of if a sqldatasource would outperform a typed dataset?

It makes you wonder, as a typed dataset you don't have to worry about time spent on conversions, etc. but you would think that server built and controlled dataset might perform better, or atleast it would probably build faster when it's first created... please do reply if you have insight.

Cause of DB restarting

Hello,
I have a SQL Server that drives some of my companies web
sites. There are several DBs on this server, however, only
one is having an issue.
It repeatedly restarts itself. This issue started this
past Friday. On that day the DB was in suspect mode.
Any idea on what would cause this one DB to restart and
not affect any other DB on that same server?
Any help would be appreciated.have you run DBCC CHECKDB()
Error logs ?
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Jim C" <tjnaz2001@.yahoo.com> wrote in message
news:7f2a01c344ef$093e5ba0$a401280a@.phx.gbl...
> Hello,
> I have a SQL Server that drives some of my companies web
> sites. There are several DBs on this server, however, only
> one is having an issue.
> It repeatedly restarts itself. This issue started this
> past Friday. On that day the DB was in suspect mode.
> Any idea on what would cause this one DB to restart and
> not affect any other DB on that same server?
> Any help would be appreciated.
>|||In addition to Allan's post:
What do you mean by "restart"? Did you set the autoclose database option?
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Jim C" <tjnaz2001@.yahoo.com> wrote in message news:7f2a01c344ef$093e5ba0$a401280a@.phx.gbl...
> Hello,
> I have a SQL Server that drives some of my companies web
> sites. There are several DBs on this server, however, only
> one is having an issue.
> It repeatedly restarts itself. This issue started this
> past Friday. On that day the DB was in suspect mode.
> Any idea on what would cause this one DB to restart and
> not affect any other DB on that same server?
> Any help would be appreciated.
>

categories...

Hi,

i'm devoloping a web application that contains categories for example

Category1

Category11

Category111

Category1111

.........

Category112

Category12

Category2

categories with categories with categories... someone can help me about designing an elegant table for this situation.

Thanks

Fdo.

ive successfully used a technique very much like this:http://www.developerfusion.co.uk/show/4633/2/

this articel also offer a somewhat different approach that i've not tried:http://www.sitepoint.com/article/hierarchical-data-database/2

|||

excelent.

thanks Mike

Categories and Sub Categories

Hello,
I am working on a web site that makes documents available to students.
I am using tags to classify each document content.
So I have the following tables:
Documents, Tags, DocumentsTags
I also need to categorize the documents:
Subject
|-- Level
|-- Year
For example:
Document_1 - Math > University Level > Second Year
How can I create a table to create such classification?
Thank You,
MiguelHere is one way.
Have a table called Subjects, and add SubjectID to the Documents table with
a foreign key to Subjects (so that you only store "Math" once and it is easy
to update).
Add a column called Level to the Documents table.
Add a column called Year to the Documents table.
"shapper" <mdmoura@.gmail.com> wrote in message
news:24124f92-40c9-411e-8221-17119c78c7da@.j22g2000hsf.googlegroups.com...
> Hello,
> I am working on a web site that makes documents available to students.
> I am using tags to classify each document content.
> So I have the following tables:
> Documents, Tags, DocumentsTags
> I also need to categorize the documents:
> Subject
> |-- Level
> |-- Year
> For example:
> Document_1 - Math > University Level > Second Year
> How can I create a table to create such classification?
> Thank You,
> Miguel|||On Apr 24, 1:31 pm, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> Here is one way.
> Have a table called Subjects, and add SubjectID to the Documents table with
> a foreign key to Subjects (so that you only store "Math" once and it is easy
> to update).
> Add a column called Level to the Documents table.
> Add a column called Year to the Documents table.
> "shapper" <mdmo...@.gmail.com> wrote in message
> news:24124f92-40c9-411e-8221-17119c78c7da@.j22g2000hsf.googlegroups.com...
> > Hello,
> > I am working on a web site that makes documents available to students.
> > I am using tags to classify each document content.
> > So I have the following tables:
> > Documents, Tags, DocumentsTags
> > I also need to categorize the documents:
> > Subject
> > |-- Level
> > |-- Year
> > For example:
> > Document_1 - Math > University Level > Second Year
> > How can I create a table to create such classification?
> > Thank You,
> > Miguel
In the future I might need other categories. I was thinking in
something more flexible then that. Something like:
http://www.sqllessons.com/categories.html
I have two other tables:
Documents > DocumentID, Title, Description
DocumentsCategories > DocumentID, CategoryID
Then I was using Adjacency Model to create the table Categories.
This way I could have a Document associated to one or more categories
and at the same time I could have various levels of categories.
I am using LINQ to SQL so I would like to find a way to make this work
also with LINQ.
I am not sure if this is the best way but after Goggling this is the
closer I could find to solve my problem.
Thanks,
Miguel

Catching SQL Exceptions for ConnStrings in Web.Config

Hi,

I have a connection string in my web.config - to which I then refer to in my code through all my controls, they're all databound to it.

Anyway - how do I catch any errors - such as when I want to view the website on a train, if I'm working on it.

I don't want it to crash and burn [the site, not the train] - if I dont have access to the sql server.

How can I wrap it in a try block!?- How do i then deal with controls which refer to the connection string?

One solution I thought of - is to programmatically set all the databinding - and not just with the GUI. As that way I can wrap everything in a try{}catch{} block.

Any other - site-wide way of doing this?

Thank you,

R

If it's a connstrings error, then one way would be to try them when the application starts up.

If you are looking for error handling during the select/insert/delete events of a sqldatasource, then check the errors in the selected/inserted/deleted events, and set e.errorhandled property to true (or not if you want the default error handling). Of course, that's not site wide.

|||

i want to capture an error at web.config stage.

so if you have something like this in your web.config:

<add name="DataStoreConnectionString" connectionString="Data Source=.\sqlexpress;Initial Catalog=mehDB;Integrated Security=True;uid=submit;pwd=" providerName="System.Data.SqlClient"/>

then have it produce an error on the site - and not just prevent the website from loading. as the database component althogh significant, only is necessary for logged in users - so not to affect users that are just browsing.

how do i catch the error at such an early stage?

thank you.

Catching an exception from a Web service task

Hi!

I am quite new using SSIS and I have a problem with catching an (SOAP) exception from a Web service task. Some times my web service task can fail and when the web service is failing, it is throwing an exception. When the task succeeds the result is being put into a variable, That part is not a problem.

But catching an exception is. I have tried to use a script task and tried to get exception from the dts object model. I have not yet succeeded on that. But it might be a possible way to go. A different approach might be creating an OnError event on my web service task which I can create a task when triggered. But I have not found any solution yet and I hope some people out there have done this before or have a solution on this.

Regards

Geir F

I forget to tell that I want the error description text from the exception that is thrown from the web service task because I want to log the errors. So what I hope is to have a script task that executes when web service fails and to write some code to catch the error description.

Current status on this is that the script task executes when the WS fails (red arrow). Nice. The web service returns an object, say TestOutput variable. When the WS succeeds, I can convert the object into a integer datatype and catch the value. But when it fails, I don't know to catch the error description. When executing the package, I can see the error description on th progress tab, so there must be a way for me to get the error too :-)

Regards

GF